Getting Started with SQLite

SQLite is the most deployed database in the world. Every phone, every browser, thousands of apps you use daily. It's not a server. It's a library that reads and writes directly to a single file. No daemon, no configuration, no network.

That simplicity is its superpower. I reach for SQLite constantly: embedded apps, local-first apps, CLI tools, dev and testing, and more production workloads than you'd think. Expensify runs it in production at 4 million queries per second on a single server.

We'll build a task management app backed by SQLite in one TypeScript file. Run it locally, or grab a managed instance on Layerbase Cloud to skip the setup.

Create a SQLite Instance
- Local with SpinDB
- Layerbase Cloud
Set Up the Project
The Dataset
Connect and Create Tables
Insert Data
Queries: Joins, Filters, and Sorting
JSON Support
Window Functions
Recursive CTEs
The RETURNING Clause
When SQLite Is the Right Choice
Wrapping Up

Create a SQLite Instance

Local with SpinDB

SpinDB is the quickest path to a local SQLite instance. It's a CLI that runs databases without Docker. (What is SpinDB?)

SpinDB runs SQLite behind a PostgreSQL-compatible proxy, so you connect to it using standard PostgreSQL clients and drivers. This means you get SQLite's simplicity with PostgreSQL's ecosystem of tools.

Install SpinDB globally:

bash

npm i -g spindb    # npm
pnpm add -g spindb # pnpm

Or run it directly without installing:

bash

npx spindb create sqlite1 -e sqlite --start  # npm
pnpx spindb create sqlite1 -e sqlite --start # pnpm

If you installed globally, create and start a SQLite instance:

bash

spindb create sqlite1 -e sqlite --start

SpinDB downloads the SQLite PG proxy for your platform, configures it, and starts the server. Get the connection string:

bash

spindb url sqlite1

text

postgresql://127.0.0.1:5432/sqlite1

Leave the server running. We'll connect to it from TypeScript in the next section.

Layerbase Cloud

Don't want to run anything locally? Layerbase Cloud gives you a managed SQLite instance in a few clicks. Select SQLite, and the Quick Connect panel will show your PostgreSQL connection string.

Cloud instances use TLS, so add ssl: true to the connection:

typescript

const client = new pg.Client({
  connectionString: 'postgresql://user:pass@cloud.layerbase.dev:15432/sqlite1',
  ssl: true,
})

Everything else in this guide works identically whether you're running locally or on Layerbase Cloud. Just swap in your connection details.

Set Up the Project

bash

mkdir sqlite-tasks && cd sqlite-tasks
pnpm init
pnpm add pg
pnpm add -D tsx typescript @types/pg

Create a file called tasks.ts. All the code in this post goes into that one file.

Since SpinDB exposes SQLite through a PostgreSQL-compatible proxy, we use the pg (node-postgres) package to connect. The SQL you write is standard SQLite SQL. The proxy translates the wire protocol, not the queries.

Note: If you want to use SQLite directly without SpinDB's PG proxy, better-sqlite3 is the standard Node.js package for embedded SQLite. It gives you synchronous, file-based access with excellent performance. The rest of this guide uses pg through SpinDB.

The Dataset

We're building a task manager. Classic SQLite territory: local app, single user, lightweight reads and writes. Two tables: projects to organize work, and tasks with statuses, priorities, and due dates.

Here's the data we'll insert:

typescript

const projects = [
  { name: 'Website Redesign', description: 'Overhaul the marketing site with new branding' },
  { name: 'Mobile App', description: 'Build iOS and Android app for existing product' },
  { name: 'API v2', description: 'Redesign the public API with breaking changes' },
  { name: 'Infrastructure', description: 'Migrate from VMs to Kubernetes' },
]

const tasks = [
  // Website Redesign
  { project: 'Website Redesign', title: 'Design new homepage mockup', status: 'done', priority: 1, dueDays: -10, completedDaysAgo: 12 },
  { project: 'Website Redesign', title: 'Implement responsive nav', status: 'done', priority: 2, dueDays: -5, completedDaysAgo: 6 },
  { project: 'Website Redesign', title: 'Write landing page copy', status: 'in_progress', priority: 1, dueDays: 3 },
  { project: 'Website Redesign', title: 'Set up analytics tracking', status: 'todo', priority: 3, dueDays: 7 },
  // Mobile App
  { project: 'Mobile App', title: 'Set up React Native project', status: 'done', priority: 1, dueDays: -14, completedDaysAgo: 15 },
  { project: 'Mobile App', title: 'Build authentication flow', status: 'in_progress', priority: 1, dueDays: 2 },
  { project: 'Mobile App', title: 'Design push notification system', status: 'todo', priority: 2, dueDays: 10 },
  { project: 'Mobile App', title: 'Implement offline sync', status: 'todo', priority: 1, dueDays: 14 },
  // API v2
  { project: 'API v2', title: 'Document breaking changes', status: 'done', priority: 1, dueDays: -7, completedDaysAgo: 8 },
  { project: 'API v2', title: 'Build versioned endpoint router', status: 'in_progress', priority: 1, dueDays: 5 },
  { project: 'API v2', title: 'Write migration guide', status: 'todo', priority: 2, dueDays: 12 },
  { project: 'API v2', title: 'Set up rate limiting', status: 'todo', priority: 2, dueDays: 15 },
  // Infrastructure
  { project: 'Infrastructure', title: 'Containerize all services', status: 'in_progress', priority: 1, dueDays: 4 },
  { project: 'Infrastructure', title: 'Set up CI/CD pipeline', status: 'todo', priority: 1, dueDays: 8 },
  { project: 'Infrastructure', title: 'Configure monitoring and alerts', status: 'todo', priority: 2, dueDays: 20 },
]

4 projects, 15 tasks spread across statuses and priorities. Enough to show interesting queries without overwhelming the output.

Connect and Create Tables

typescript

import pg from 'pg'

const client = new pg.Client({
  connectionString: 'postgresql://127.0.0.1:5432/sqlite1',
})

await client.connect()
console.log('Connected to SQLite via PG proxy')

// Clean up from previous runs
await client.query('DROP TABLE IF EXISTS tasks')
await client.query('DROP TABLE IF EXISTS projects')

await client.query(`
  CREATE TABLE projects (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    metadata TEXT DEFAULT '{}',
    created_at TEXT DEFAULT (datetime('now'))
  )
`)

await client.query(`
  CREATE TABLE tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    project_id INTEGER NOT NULL REFERENCES projects(id),
    title TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('todo', 'in_progress', 'done')),
    priority INTEGER NOT NULL DEFAULT 3,
    due_date TEXT,
    created_at TEXT DEFAULT (datetime('now')),
    completed_at TEXT
  )
`)

console.log('Created tables: projects, tasks')

A few things worth noting. SQLite uses TEXT for dates (no native DATE or TIMESTAMP). The CHECK constraint locks status to three valid states. AUTOINCREMENT ensures IDs never get reused, even after deletion. And metadata stores JSON as plain text, which we'll query with SQLite's JSON functions later.

Insert Data

typescript

// Insert projects with JSON metadata
const projectIds: Record<string, number> = {}

for (const [i, project] of projects.entries()) {
  const metadata = JSON.stringify({
    team_size: [4, 6, 3, 2][i],
    budget: ['medium', 'high', 'medium', 'low'][i],
    tags: [
      ['design', 'frontend'],
      ['mobile', 'react-native'],
      ['backend', 'api'],
      ['devops', 'cloud'],
    ][i],
  })

  const result = await client.query(
    'INSERT INTO projects (name, description, metadata) VALUES ($1, $2, $3) RETURNING id',
    [project.name, project.description, metadata],
  )
  projectIds[project.name] = result.rows[0].id
}

console.log(`Inserted ${projects.length} projects`)

// Insert tasks
for (const task of tasks) {
  const dueDate = new Date()
  dueDate.setDate(dueDate.getDate() + task.dueDays)

  let completedAt = null
  if (task.completedDaysAgo) {
    const d = new Date()
    d.setDate(d.getDate() - task.completedDaysAgo)
    completedAt = d.toISOString().split('T')[0]
  }

  await client.query(
    `INSERT INTO tasks (project_id, title, status, priority, due_date, completed_at)
     VALUES ($1, $2, $3, $4, $5, $6)`,
    [
      projectIds[task.project],
      task.title,
      task.status,
      task.priority,
      dueDate.toISOString().split('T')[0],
      completedAt,
    ],
  )
}

console.log(`Inserted ${tasks.length} tasks`)

Notice we're already using RETURNING id on the project inserts. We'll cover that feature in more detail later.

Queries: Joins, Filters, and Sorting

The basics. Find all in-progress tasks with their project names, sorted by priority:

typescript

const inProgress = await client.query(`
  SELECT
    t.title,
    t.priority,
    t.due_date,
    p.name AS project
  FROM tasks t
  JOIN projects p ON p.id = t.project_id
  WHERE t.status = 'in_progress'
  ORDER BY t.priority ASC, t.due_date ASC
`)

console.log('\nIn-progress tasks:')
for (const row of inProgress.rows) {
  console.log(`  [P${row.priority}] ${row.title} (${row.project}) - due ${row.due_date}`)
}

text

In-progress tasks:
  [P1] Write landing page copy (Website Redesign) - due 2026-03-17
  [P1] Build authentication flow (Mobile App) - due 2026-03-16
  [P1] Build versioned endpoint router (API v2) - due 2026-03-19
  [P1] Containerize all services (Infrastructure) - due 2026-03-18

Now find overdue tasks (due date in the past, not yet completed):

typescript

const overdue = await client.query(`
  SELECT
    t.title,
    t.due_date,
    p.name AS project,
    CAST(julianday('now') - julianday(t.due_date) AS INTEGER) AS days_overdue
  FROM tasks t
  JOIN projects p ON p.id = t.project_id
  WHERE t.due_date < date('now')
    AND t.status != 'done'
  ORDER BY t.due_date ASC
`)

console.log('\nOverdue tasks:')
for (const row of overdue.rows) {
  console.log(`  ${row.title} (${row.project}) - ${row.days_overdue} days overdue`)
}

SQLite's julianday() converts dates to Julian day numbers. Subtract two and you get the difference in days. Simple date arithmetic without a DATE type.

JSON Support

SQLite has had solid JSON support since version 3.38. The metadata column stores JSON as text, but SQLite queries it just fine.

Extract specific fields with json_extract():

typescript

const budgets = await client.query(`
  SELECT
    name,
    json_extract(metadata, '$.team_size') AS team_size,
    json_extract(metadata, '$.budget') AS budget
  FROM projects
  ORDER BY json_extract(metadata, '$.team_size') DESC
`)

console.log('\nProject budgets:')
for (const row of budgets.rows) {
  console.log(`  ${row.name}: ${row.team_size} people, ${row.budget} budget`)
}

text

Project budgets:
  Mobile App: 6 people, high budget
  Website Redesign: 4 people, medium budget
  API v2: 3 people, medium budget
  Infrastructure: 2 people, low budget

Use json_each() to explode JSON arrays into rows. Find all projects tagged with "frontend" or "backend":

typescript

const tagged = await client.query(`
  SELECT DISTINCT p.name, j.value AS tag
  FROM projects p, json_each(json_extract(p.metadata, '$.tags')) j
  WHERE j.value IN ('frontend', 'backend')
  ORDER BY p.name
`)

console.log('\nProjects with frontend/backend tags:')
for (const row of tagged.rows) {
  console.log(`  ${row.name}: ${row.tag}`)
}

text

Projects with frontend/backend tags:
  API v2: backend
  Website Redesign: frontend

json_each() turns a JSON array into a virtual table, one row per element. Combine it with a regular WHERE clause and you can filter on array contents without special operators.

Window Functions

Window functions compute values across related rows without collapsing them into groups. Here's the completion rate per project:

typescript

const completionRates = await client.query(`
  SELECT
    p.name AS project,
    COUNT(*) AS total_tasks,
    SUM(CASE WHEN t.status = 'done' THEN 1 ELSE 0 END) AS completed,
    ROUND(
      100.0 * SUM(CASE WHEN t.status = 'done' THEN 1 ELSE 0 END) / COUNT(*),
      1
    ) AS completion_pct
  FROM tasks t
  JOIN projects p ON p.id = t.project_id
  GROUP BY p.id, p.name
  ORDER BY completion_pct DESC
`)

console.log('\nCompletion rates:')
for (const row of completionRates.rows) {
  const bar = '█'.repeat(Math.round(Number(row.completion_pct) / 10))
  console.log(`  ${row.project}: ${row.completion_pct}% (${row.completed}/${row.total_tasks}) ${bar}`)
}

text

Completion rates:
  Website Redesign: 50.0% (2/4) █████
  API v2: 25.0% (1/4) ██
  Mobile App: 25.0% (1/4) ██
  Infrastructure: 0.0% (0/3)

Now use a window function to rank tasks within each project by priority and show a running count:

typescript

const ranked = await client.query(`
  SELECT
    p.name AS project,
    t.title,
    t.priority,
    t.status,
    ROW_NUMBER() OVER (
      PARTITION BY t.project_id
      ORDER BY t.priority ASC, t.due_date ASC
    ) AS rank_in_project,
    COUNT(*) OVER (
      PARTITION BY t.project_id
      ORDER BY t.priority ASC, t.due_date ASC
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_count
  FROM tasks t
  JOIN projects p ON p.id = t.project_id
  WHERE t.status != 'done'
  ORDER BY p.name, rank_in_project
`)

console.log('\nTask priority ranking (incomplete tasks):')
let currentProject = ''
for (const row of ranked.rows) {
  if (row.project !== currentProject) {
    currentProject = row.project
    console.log(`\n  ${currentProject}:`)
  }
  console.log(`    #${row.rank_in_project} [P${row.priority}] ${row.title} (${row.status})`)
}

ROW_NUMBER() assigns sequential ranks within each project partition. COUNT() OVER(...) with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives a running total. Same window functions you'd use in PostgreSQL. SQLite supports them all.

Recursive CTEs

CTEs let you build complex queries step by step. Recursive CTEs are where they get really interesting: hierarchical data in a flat table. Let's add task dependencies and query the full chain:

typescript

// Add a parent_task_id column for dependencies
await client.query(`
  ALTER TABLE tasks ADD COLUMN parent_task_id INTEGER REFERENCES tasks(id)
`)

// Create some dependencies: "Build auth flow" depends on "Set up React Native project"
// and "Implement offline sync" depends on "Build auth flow"
await client.query(`
  UPDATE tasks SET parent_task_id = (
    SELECT id FROM tasks WHERE title = 'Set up React Native project'
  ) WHERE title = 'Build authentication flow'
`)

await client.query(`
  UPDATE tasks SET parent_task_id = (
    SELECT id FROM tasks WHERE title = 'Build authentication flow'
  ) WHERE title = 'Implement offline sync'
`)

// Also: "Write migration guide" depends on "Document breaking changes"
await client.query(`
  UPDATE tasks SET parent_task_id = (
    SELECT id FROM tasks WHERE title = 'Document breaking changes'
  ) WHERE title = 'Write migration guide'
`)

// Recursive CTE: find the full dependency chain for any task
const chains = await client.query(`
  WITH RECURSIVE task_chain AS (
    -- Base case: tasks that have dependents (root tasks)
    SELECT
      id,
      title,
      status,
      parent_task_id,
      0 AS depth,
      title AS chain
    FROM tasks
    WHERE id IN (SELECT DISTINCT parent_task_id FROM tasks WHERE parent_task_id IS NOT NULL)
      AND parent_task_id IS NULL

    UNION ALL

    -- Recursive case: find tasks that depend on the current task
    SELECT
      t.id,
      t.title,
      t.status,
      t.parent_task_id,
      tc.depth + 1,
      tc.chain || ' -> ' || t.title
    FROM tasks t
    JOIN task_chain tc ON t.parent_task_id = tc.id
  )
  SELECT * FROM task_chain
  ORDER BY chain, depth
`)

console.log('\nTask dependency chains:')
for (const row of chains.rows) {
  const indent = '  '.repeat(row.depth)
  const statusIcon = row.status === 'done' ? '[x]' : '[ ]'
  console.log(`  ${indent}${statusIcon} ${row.title}`)
}

text

Task dependency chains:
  [x] Document breaking changes
    [ ] Write migration guide
  [x] Set up React Native project
    [ ] Build authentication flow
      [ ] Implement offline sync

The CTE starts with root tasks (those that have dependents but no parent), then repeatedly joins to find children. depth tracks how deep we are in the tree. This same pattern works for org charts, category trees, bill-of-materials, anything hierarchical in a single table.

The RETURNING Clause

SQLite 3.35+ supports RETURNING, which gives you back affected rows from INSERT, UPDATE, or DELETE without a separate query. We used it earlier for project IDs. Here it is with an update:

typescript

const updated = await client.query(`
  UPDATE tasks
  SET status = 'in_progress',
      priority = 1
  WHERE status = 'todo'
    AND due_date <= date('now', '+5 days')
  RETURNING id, title, due_date
`)

console.log('\nAuto-promoted to in_progress (due within 5 days):')
for (const row of updated.rows) {
  console.log(`  ${row.title} (due ${row.due_date})`)
}

Without RETURNING, you'd need a separate SELECT to know which rows changed. One round trip instead of two. Especially valuable through the PG proxy where each query is a network call.

When SQLite Is the Right Choice

So when should you actually reach for SQLite?

SQLite shines for:

Local-first and offline apps: the database is a single file that travels with the app
CLI tools: embed a full SQL database with zero dependencies
Dev and testing: throwaway databases instantly, no server to manage
Single-server web apps: with WAL mode, SQLite handles significant read traffic alongside moderate writes
Mobile and desktop apps: every iOS and Android device already has SQLite built in
Edge computing: deploy to edge functions or embedded devices where running a server isn't practical
Prototyping: idea to working queries in seconds

Look elsewhere when:

High write concurrency from multiple processes: file-level locking means concurrent writers block each other
Multi-server deployments: SQLite is a local file, not a network service (though LiteFS and Turso are changing this)
Fine-grained access control: no built-in users, roles, or permissions

The simplicity advantage is real. Your entire database is one file. Copy it, back it up with cp, email it to a colleague. No server process to crash, no port conflicts, no connection pooling. For a surprising number of applications, that's all you need.

Wrapping Up

One file. Tables, joins, JSON queries, window functions, recursive CTEs, and RETURNING. All on the world's most widely deployed database. Not bad.

The SQLite documentation is excellent and covers WAL mode, full-text search with FTS5, R-tree indexes for geospatial data, and the session extension for change tracking.

To manage your local SQLite instance:

bash

spindb stop sqlite1    # Stop the server
spindb start sqlite1   # Start it again
spindb connect sqlite1 # Open an interactive SQL shell
spindb list            # See all your database instances

SpinDB handles 20+ database engines, so you can have SQLite, PostgreSQL, and DuckDB all running side by side from a single CLI. There's also Layerbase Desktop if you'd rather manage everything through a GUI on macOS.

Getting Started with SQLite

Contents

Create a SQLite Instance

Local with SpinDB

Layerbase Cloud

Set Up the Project

The Dataset

Connect and Create Tables

Insert Data

Queries: Joins, Filters, and Sorting

JSON Support

Window Functions

Recursive CTEs

The RETURNING Clause

When SQLite Is the Right Choice

Wrapping Up

Something not working?