# Getting Started with SQLite

> Build a task management app with SQLite and TypeScript: JSON functions, window functions, recursive CTEs, and why the most deployed database is all you need.

Published: 2026-03-02
Canonical: https://layerbase.com/blog/getting-started-with-sqlite

Short version: `npm i -g layerbase` then `lbase create sqlite1 -e sqlite --start` gives you SQLite behind a PostgreSQL-compatible endpoint, so the `pg` client connects and there is nothing to install past the CLI. A managed instance takes a few clicks on [Layerbase Cloud](https://cloud.layerbase.com/create?engine=sqlite), where SQLite is included on the free tier.

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](https://blog.expensify.com/2018/01/08/scaling-sqlite-to-4m-qps-on-a-single-server/) 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](https://cloud.layerbase.com/create?engine=sqlite) to skip the setup.

## Contents

- [Create a SQLite Instance](#create-a-sqlite-instance)
  - [Local with the Layerbase CLI](#local-with-the-layerbase-cli)
  - [Layerbase Cloud](#layerbase-cloud)
- [Set Up the Project](#set-up-the-project)
- [The Dataset](#the-dataset)
- [Connect and Create Tables](#connect-and-create-tables)
- [Insert Data](#insert-data)
- [Queries: Joins, Filters, and Sorting](#queries-joins-filters-and-sorting)
- [JSON Support](#json-support)
- [Window Functions](#window-functions)
- [Recursive CTEs](#recursive-ctes)
- [The RETURNING Clause](#the-returning-clause)
- [When SQLite Is the Right Choice](#when-sqlite-is-the-right-choice)
- [FAQ](#faq)
- [Wrapping Up](#wrapping-up)

## Create a SQLite Instance

### Local with the Layerbase CLI

The [Layerbase CLI](https://layerbase.com/docs/cli) (formerly SpinDB) is the quickest path to a local SQLite instance. It's a CLI that runs databases without Docker. ([What is the Layerbase CLI?](https://layerbase.com/docs/cli))

The CLI 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 the Layerbase CLI globally:

```bash
npm i -g layerbase    # npm
pnpm add -g layerbase # pnpm
```

Or run it directly without installing:

```bash
npx layerbase create sqlite1 -e sqlite --start  # npm
pnpx layerbase create sqlite1 -e sqlite --start # pnpm
```

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

```bash
lbase create sqlite1 -e sqlite --start
```

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

```bash
lbase 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](https://cloud.layerbase.com/create?engine=sqlite) gives you a [managed SQLite instance](/db/sqlite) 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 the Layerbase CLI 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 the CLI's PG proxy, [`better-sqlite3`](https://github.com/WiseLibs/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 the Layerbase CLI.

## 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.

## FAQ

### Is SQLite free on Layerbase Cloud?

Yes, SQLite is a free-tier engine, so a hosted instance costs nothing and needs no card. Free databases sleep after 15 idle minutes and wake on the next connection, which is a good match for a small tool or a side project.

### Why does a cloud SQLite database use a PostgreSQL connection string?

Because Layerbase puts it behind a PostgreSQL-compatible proxy, both locally and in the cloud. That is what turns a single-file database into something a networked application can reach, and it means `pg`, `psql`, and most PostgreSQL tooling work without a SQLite-specific driver. Add `ssl: true` for cloud instances.

### Can I open an interactive shell against the local instance?

Yes, `lbase connect sqlite1` opens one. `lbase stop sqlite1`, `lbase start sqlite1`, and `lbase list` handle the rest of the lifecycle.

### When should I not use SQLite?

When several processes write concurrently, when you need multiple servers hitting the same database, or when you need built-in users, roles, and permissions. File-level locking makes concurrent writers block each other, and SQLite is a file rather than a network service. For reads, WAL mode carries far more traffic than people expect.

## 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](https://sqlite.org/docs.html) 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
lbase stop sqlite1    # Stop the server
lbase start sqlite1   # Start it again
lbase connect sqlite1 # Open an interactive SQL shell
lbase list            # See all your database instances
```

The Layerbase CLI handles [20+ database engines](https://layerbase.com/docs/cli), so you can have SQLite, PostgreSQL, and DuckDB all running side by side from a single CLI. There's also [Layerbase Desktop](https://layerbase.com/desktop/download) if you'd rather manage everything through a GUI on macOS.