SQLite: Lightweight Embedded Database

Create a free account to save your progress

Earn XP, track streaks, and sync your dashboard across devices.

Lesson

Imagine you need to store data for your application, but setting up PostgreSQL feels like using a sledgehammer to crack a nut. You don't need multiple users, network connections, or complex configuration. You just want a database that works, immediately.

That's SQLite. It's a complete relational database engine packed into a single C library. No server process, no configuration files, no user accounts. Your entire database lives in one file that you can copy, move, or email just like any other document.

Think of SQLite as a Swiss Army knife for data storage. It's not the right tool for every job, but when you need something lightweight and portable, it's unbeatable.

Fun fact

SQLite is the most widely deployed database in the world. It's in your phone (iOS and Android), your browser (Chrome, Firefox, Safari), and thousands of applications you use daily. There's probably over a trillion SQLite databases in active use right now.

When SQLite shines

SQLite is perfect for:

Local development: Spin up a database instantly, throw it away when done
Automated testing: Create test databases in milliseconds
Desktop applications: Your app's data travels with the app
Mobile apps: Offline-first experiences (it's built into iOS and Android)
IoT devices: Tiny footprint, minimal resource usage
Small websites: Handles up to 100,000 visits per day comfortably
Data analysis: Prototype with SQLite, migrate to PostgreSQL when needed

When SQLite struggles

SQLite isn't suitable for:

High-traffic websites: Only one writer at a time
Multi-server setups: No built-in replication or clustering
Advanced analytics: Missing features like window functions (until recently)
Complex security: File-level permissions only, no user management

Your first SQLite database

Let's create a database and see how simple it is:

import sqlite3 from 'sqlite3';
import { open } from 'sqlite';

// Open (or create) a database file
const db = await open({
  filename: './myapp.db',
  driver: sqlite3.Database
});

// Create a table
await db.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// Insert data
await db.run(
  'INSERT INTO users (name, email) VALUES (?, ?)',
  ['Alice', 'alice@example.com']
);

// Read data
const users = await db.all('SELECT * FROM users');
console.log(users);
// [{ id: 1, name: 'Alice', email: 'alice@example.com', created_at: '2024-01-15 10:30:00' }]

// Close the connection
await db.close();

That's it. No CREATE DATABASE command, no user setup, no server configuration. The database file myapp.db appeared in your folder, and that's your entire database.

SQLite data types

SQLite uses dynamic typing with type affinity. This means:

You declare a type (like INTEGER or TEXT)
SQLite stores the data efficiently
But it's flexible, you can technically store a string in an integer column (though you shouldn't)

Storage Class	Used For	Example
INTEGER	Whole numbers	IDs, counts, timestamps
REAL	Decimal numbers	Prices, coordinates
TEXT	Strings	Names, emails, JSON
BLOB	Binary data	Images, files
NULL	Missing values	,

CREATE TABLE products (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  price REAL CHECK (price >= 0),
  description TEXT,
  image BLOB,
  metadata TEXT  -- Can store JSON here
);

Type tip

SQLite doesn't have a dedicated boolean type. Use INTEGER with 0 for false and 1 for true. Same for dates, store them as TEXT in ISO 8601 format ('2024-01-15T10:30:00') or INTEGER as Unix timestamps.

Working with booleans and dates

Since SQLite lacks native boolean and date types, here's the convention:

Booleans (INTEGER)

CREATE TABLE tasks (
  id INTEGER PRIMARY KEY,
  title TEXT,
  completed INTEGER DEFAULT 0 CHECK (completed IN (0, 1))
);

-- Insert
INSERT INTO tasks (title, completed) VALUES ('Buy milk', 0);  -- Not done
INSERT INTO tasks (title, completed) VALUES ('Walk dog', 1);  -- Done

-- Query
SELECT * FROM tasks WHERE completed = 1;  -- Get completed tasks

Dates (TEXT or INTEGER)

-- Recommended: TEXT with ISO 8601 format
CREATE TABLE events (
  id INTEGER PRIMARY KEY,
  name TEXT,
  event_date TEXT,  -- '2024-01-15'
  created_at TEXT DEFAULT CURRENT_TIMESTAMP  -- '2024-01-15 10:30:00'
);

-- Date functions
SELECT date('now');                           -- Current date
SELECT datetime('now');                       -- Current datetime
SELECT datetime('now', '+1 day');             -- Tomorrow
SELECT datetime('now', '-1 month');           -- 1 month ago
SELECT strftime('%Y-%m', created_at) FROM events;  -- Format as YYYY-MM

JSONWhat is json?A text format for exchanging data between systems. It uses key-value pairs and arrays, and every programming language can read and write it. support

Modern SQLite (3.9+) includes JSON functions. This is huge, you get document-store flexibility within a relational database:

-- Store JSON in a TEXT column
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT,
  preferences TEXT  -- JSON: '{"theme":"dark","notifications":true}'
);

-- Extract values
SELECT json_extract(preferences, '$.theme') FROM users;
-- Returns: 'dark'

-- Update specific fields
UPDATE users 
SET preferences = json_set(preferences, '$.theme', 'light')
WHERE id = 1;

-- Query JSON
SELECT * FROM users 
WHERE json_extract(preferences, '$.notifications') = 'true';

-- Check if key exists
SELECT * FROM users 
WHERE json_type(preferences, '$.theme') IS NOT NULL;

Best practice

Use JSON for flexible, nested data that doesn't need to be queried often. For data you search or filter by, use regular columns. JSON is powerful but slower than indexed columns.

Quick reference: SQLite vs PostgreSQL

Feature	SQLite	PostgreSQL
Setup	Zero config	Requires installation
Storage	Single file	Server process
Concurrent writes	1 at a time	Many
Data types	Flexible	Strict
JSON support	Good	Excellent (JSONB)
Best for	Dev, tests, mobile, IoT	Production web apps

SQLite is your training wheels, perfect for learning, prototyping, and small applications. When you outgrow it, your SQLWhat is sql?A language for querying and managing data in relational databases, letting you insert, read, update, and delete rows across tables. knowledge transfers almost entirely to PostgreSQL.

Done

Complete & Next