SQLite vs LibSQL

You're building something that needs an embedded database. Maybe it's a CLI tool, a local-first app, or an Electron project where you want data on disk without running a server. SQLite is the obvious pick. You've used it before, it works everywhere, and there's nothing to configure. Then you come across LibSQL and start wondering if you're leaving features on the table.

LibSQL is a fork of SQLite that adds server mode, built-in replication, and native vector search while keeping full compatibility with SQLite's SQL dialect and file format. Everything you know about SQLite still applies. The question is whether you need those extras, and what you give up by staying with plain SQLite.

We'll spin up both locally with SpinDB, run the same operations against each, and dig into the differences that actually affect your architecture decisions. (What is SpinDB?)

Contents

• Quick Comparison
• Set Up Both Databases
• Side-by-Side: The Same Operations
  • SQLite (via PG Proxy)
  • LibSQL (via HTTP)
• Key Differences
  • Server Mode and Concurrent Access
  • Replication
  • Native Vector Search
  • ALTER TABLE Extensions
• When to Pick SQLite
• When to Pick LibSQL
• Running Both on Layerbase Cloud
• Wrapping Up

Quick Comparison

The bottom line: LibSQL is a strict superset. Everything in the SQLite column also works in LibSQL. The differences are all additions.

Set Up Both Databases

Install SpinDB if you haven't already:

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

Create and start both instances:

spindb create sqlite1 -e sqlite --start
spindb create libsql1 -e libsql --start

Verify they're running:

spindb url sqlite1
spindb url libsql1

postgresql://127.0.0.1:5432/sqlite1
http://127.0.0.1:8080

Notice the different protocols. SQLite runs behind SpinDB's PostgreSQL-compatible proxy, so you connect with standard PG drivers. LibSQL runs its own HTTP server natively, so you connect with the @libsql/client package.

Set up a project with both clients:

mkdir sqlite-vs-libsql && cd sqlite-vs-libsql
pnpm init
pnpm add pg @libsql/client
pnpm add -D tsx typescript @types/pg

Side-by-Side: The Same Operations

Same workflow in both databases: create a table, insert data, query with joins, use JSON functions. The SQL is identical. Only the connection and execution layer differs.

SQLite (via PG Proxy)

Create a file called sqlite-side.ts:

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')

// Create tables
await client.query('DROP TABLE IF EXISTS articles')
await client.query('DROP TABLE IF EXISTS authors')

await client.query(`
  CREATE TABLE authors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    bio TEXT,
    metadata TEXT DEFAULT '{}'
  )
`)

await client.query(`
  CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    author_id INTEGER NOT NULL REFERENCES authors(id),
    title TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('draft', 'published', 'archived')),
    tags TEXT DEFAULT '[]',
    created_at TEXT DEFAULT (datetime('now'))
  )
`)

// Insert data
const authors = [
  { name: 'Alice Chen', bio: 'Backend engineer', metadata: { specialty: 'databases', years: 8 } },
  { name: 'Marcus Rivera', bio: 'Full-stack dev', metadata: { specialty: 'react', years: 5 } },
]

const authorIds: Record<string, number> = {}
for (const author of authors) {
  const result = await client.query(
    'INSERT INTO authors (name, bio, metadata) VALUES ($1, $2, $3) RETURNING id',
    [author.name, author.bio, JSON.stringify(author.metadata)],
  )
  authorIds[author.name] = result.rows[0].id
}

const articles = [
  { author: 'Alice Chen', title: 'Understanding WAL Mode', status: 'published', tags: ['sqlite', 'performance'] },
  { author: 'Alice Chen', title: 'SQLite in Production', status: 'draft', tags: ['sqlite', 'ops'] },
  { author: 'Marcus Rivera', title: 'React Server Components', status: 'published', tags: ['react', 'frontend'] },
  { author: 'Marcus Rivera', title: 'Building Offline-First Apps', status: 'published', tags: ['architecture', 'sqlite'] },
]

for (const article of articles) {
  await client.query(
    `INSERT INTO articles (author_id, title, status, tags)
     VALUES ($1, $2, $3, $4)`,
    [authorIds[article.author], article.title, article.status, JSON.stringify(article.tags)],
  )
}

// Query: join + JSON
const results = await client.query(`
  SELECT
    a.title,
    au.name AS author,
    a.status,
    json_extract(au.metadata, '$.specialty') AS specialty
  FROM articles a
  JOIN authors au ON au.id = a.author_id
  WHERE a.status = 'published'
  ORDER BY a.title
`)

console.log('\nPublished articles:')
for (const row of results.rows) {
  console.log(`  ${row.title} by ${row.author} (${row.specialty})`)
}

// Query: JSON array filtering
const sqliteArticles = await client.query(`
  SELECT a.title, au.name AS author
  FROM articles a
  JOIN authors au ON au.id = a.author_id
  WHERE EXISTS (
    SELECT 1 FROM json_each(a.tags) WHERE value = 'sqlite'
  )
`)

console.log(`\nArticles tagged "sqlite" (${sqliteArticles.rows.length}):`)
for (const row of sqliteArticles.rows) {
  console.log(`  ${row.title} by ${row.author}`)
}

await client.end()

Connected to SQLite via PG proxy

Published articles:
  Building Offline-First Apps by Marcus Rivera (react)
  React Server Components by Marcus Rivera (react)
  Understanding WAL Mode by Alice Chen (databases)

Articles tagged "sqlite" (3):
  Understanding WAL Mode by Alice Chen
  SQLite in Production by Alice Chen
  Building Offline-First Apps by Marcus Rivera

LibSQL (via HTTP)

Create a file called libsql-side.ts:

import { createClient } from '@libsql/client'

const client = createClient({
  url: 'http://localhost:8080',
})

console.log('Connected to LibSQL via HTTP')

// Create tables (identical SQL)
await client.execute('DROP TABLE IF EXISTS articles')
await client.execute('DROP TABLE IF EXISTS authors')

await client.execute(`
  CREATE TABLE authors (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    bio TEXT,
    metadata TEXT DEFAULT '{}'
  )
`)

await client.execute(`
  CREATE TABLE articles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    author_id INTEGER NOT NULL REFERENCES authors(id),
    title TEXT NOT NULL,
    status TEXT NOT NULL CHECK(status IN ('draft', 'published', 'archived')),
    tags TEXT DEFAULT '[]',
    created_at TEXT DEFAULT (datetime('now'))
  )
`)

// Insert data (same data, different driver syntax)
const authors = [
  { name: 'Alice Chen', bio: 'Backend engineer', metadata: { specialty: 'databases', years: 8 } },
  { name: 'Marcus Rivera', bio: 'Full-stack dev', metadata: { specialty: 'react', years: 5 } },
]

const authorIds: Record<string, number> = {}
for (const author of authors) {
  const result = await client.execute({
    sql: 'INSERT INTO authors (name, bio, metadata) VALUES (?, ?, ?) RETURNING id',
    args: [author.name, author.bio, JSON.stringify(author.metadata)],
  })
  authorIds[author.name] = result.rows[0].id as number
}

const articles = [
  { author: 'Alice Chen', title: 'Understanding WAL Mode', status: 'published', tags: ['sqlite', 'performance'] },
  { author: 'Alice Chen', title: 'SQLite in Production', status: 'draft', tags: ['sqlite', 'ops'] },
  { author: 'Marcus Rivera', title: 'React Server Components', status: 'published', tags: ['react', 'frontend'] },
  { author: 'Marcus Rivera', title: 'Building Offline-First Apps', status: 'published', tags: ['architecture', 'sqlite'] },
]

for (const article of articles) {
  await client.execute({
    sql: `INSERT INTO articles (author_id, title, status, tags)
          VALUES (?, ?, ?, ?)`,
    args: [authorIds[article.author], article.title, article.status, JSON.stringify(article.tags)],
  })
}

// Query: join + JSON (identical SQL)
const results = await client.execute(`
  SELECT
    a.title,
    au.name AS author,
    a.status,
    json_extract(au.metadata, '$.specialty') AS specialty
  FROM articles a
  JOIN authors au ON au.id = a.author_id
  WHERE a.status = 'published'
  ORDER BY a.title
`)

console.log('\nPublished articles:')
for (const row of results.rows) {
  console.log(`  ${row.title} by ${row.author} (${row.specialty})`)
}

// Query: JSON array filtering (identical SQL)
const sqliteArticles = await client.execute(`
  SELECT a.title, au.name AS author
  FROM articles a
  JOIN authors au ON au.id = a.author_id
  WHERE EXISTS (
    SELECT 1 FROM json_each(a.tags) WHERE value = 'sqlite'
  )
`)

console.log(`\nArticles tagged "sqlite" (${sqliteArticles.rows.length}):`)
for (const row of sqliteArticles.rows) {
  console.log(`  ${row.title} by ${row.author}`)
}

Connected to LibSQL via HTTP

Published articles:
  Building Offline-First Apps by Marcus Rivera (react)
  React Server Components by Marcus Rivera (react)
  Understanding WAL Mode by Alice Chen (databases)

Articles tagged "sqlite" (3):
  Understanding WAL Mode by Alice Chen
  SQLite in Production by Alice Chen
  Building Offline-First Apps by Marcus Rivera

The output is identical. The SQL is identical. The differences are:

1. Connection: pg.Client with a PostgreSQL connection string vs. createClient with an HTTP URL
2. Query execution: client.query(sql, [params]) vs. client.execute({ sql, args })
3. Parameter placeholders: $1, $2, $3 (pg style) vs. ?, ?, ? (SQLite style)

If your code only needs standard SQL operations, both databases behave identically. The differences show up when you reach for features that SQLite doesn't have.

Key Differences

Server Mode and Concurrent Access

This is the most fundamental difference. SQLite is an embedded library that reads and writes directly to a file on disk. One process at a time. If your web server has SQLite open and you try to connect from a CLI tool, you get a "database is locked" error.

LibSQL runs as a server. Multiple clients connect over HTTP or WebSocket, and the server handles concurrency internally. Web server, background workers, a REPL, admin dashboard: all connected at once, no problem.

// LibSQL: multiple clients, no problem
import { createClient } from '@libsql/client'

const client1 = createClient({ url: 'http://localhost:8080' })
const client2 = createClient({ url: 'http://localhost:8080' })

const [result1, result2] = await Promise.all([
  client1.execute('SELECT COUNT(*) as count FROM articles'),
  client2.execute("SELECT title FROM articles WHERE status = 'published'"),
])

console.log(`Client 1: ${result1.rows[0].count} articles`)
console.log(`Client 2: ${result2.rows.length} published`)

With SQLite, you'd need everything in the same process, or WAL mode for concurrent reads (writes are still serialized).

If your application is a single process, this difference doesn't matter. If you're building a web service where multiple things need database access, it matters a lot.

Replication

SQLite has no built-in replication. You can copy the database file, use Litestream for streaming backups, or use LiteFS for distributed file replication. Solid tools, but external to SQLite itself.

LibSQL has replication built in. Run a primary server and create embedded replicas that sync from it. Replicas are local SQLite files that stay current with the primary: reads hit the local file (fast, no network), writes go to the primary and propagate back.

This is great for edge deployments. Put a replica in each region and reads are always local. Or use it for local-first apps where the client has an embedded replica that works offline and syncs when connectivity returns.

If you're running a single instance, replication is irrelevant. If you need your data in multiple locations, LibSQL handles it natively while SQLite needs external tooling.

Native Vector Search

SQLite can do vector search through the sqlite-vec extension. It works, but you need to download the extension, load it at runtime, and manage the binary for every platform you deploy to. Not my idea of a good time.

LibSQL has vector search built in. The F32_BLOB column type stores float vectors and vector_distance_cos() computes cosine similarity with no extensions needed:

// LibSQL: native vector search
await client.execute(`
  CREATE TABLE documents (
    id INTEGER PRIMARY KEY,
    content TEXT NOT NULL,
    embedding F32_BLOB(384)
  )
`)

// Find similar documents
const similar = await client.execute({
  sql: `SELECT id, content,
        vector_distance_cos(embedding, ?) as distance
        FROM documents
        ORDER BY distance ASC
        LIMIT 5`,
  args: [queryVector],
})

If you're building semantic search, recommendations, or RAG, LibSQL lets you keep relational data and vector embeddings in the same database. With SQLite, you'd either wrangle the extension or reach for a separate vector database like Qdrant.

Most applications don't need vector search, so this won't matter. But if you do need it, it's the single biggest reason to pick LibSQL over SQLite.

ALTER TABLE Extensions

SQLite's ALTER TABLE is notoriously limited. You can rename a table, rename a column, and add a column. That's it. Want to drop a column? Create a new table, copy the data, drop the old one, rename the new one. It works, but it's tedious, especially with foreign keys.

LibSQL extends ALTER TABLE with additional operations, including column dropping with proper backfill handling. More quality-of-life than deal-breaker, but if you're iterating on a schema frequently, the extra operations save real time and reduce migration bugs.

When to Pick SQLite

Pick SQLite when:

• Your app is a single process. CLI tools, desktop apps, mobile apps, embedded systems. If only one thing touches the database, SQLite's embedded model is simpler and faster. No network hop, no server to manage.
• You want maximum maturity. SQLite has been around for 25+ years and is tested more thoroughly than almost any software in existence. Every phone, every browser, most operating systems. If you need a database that will not surprise you, this is it.
• You're prototyping or running tests. Spin up a database in milliseconds with zero configuration. Delete a file when you're done.
• You're already in the SQLite ecosystem. If your tooling and deployment pipeline are built around SQLite files, switching to LibSQL adds complexity without clear benefit unless you need one of its specific features.
• Raw performance matters and you control the deployment. In-process SQLite with WAL mode is extremely fast for read-heavy workloads. No serialization overhead, no network latency, no connection pool. It's a function call.

When to Pick LibSQL

Pick LibSQL when:

• Multiple processes need database access. Web servers, background workers, admin tools, REPL sessions. LibSQL's server mode eliminates file locking headaches.
• You need replication. Edge deployments, local-first apps with server sync, read replicas in multiple regions. Built in, no external tools.
• You want vector search without extension management. LibSQL's native F32_BLOB and distance functions are significantly easier than wrangling sqlite-vec binaries across platforms.
• You're deploying to multiple regions. Embedded replicas give you local reads with central writes. SQLite can't do this on its own.
• You're starting fresh and want headroom. If you're not sure whether you'll eventually need server mode or replication, starting with LibSQL costs nothing (the SQL is the same) and saves a migration later.

Running Both on Layerbase Cloud

Both SQLite and LibSQL are available as managed instances on Layerbase Cloud. Create an instance, grab the connection details from the Quick Connect panel, and swap them into your code:

• Create a SQLite instance
• Create a LibSQL instance

Cloud instances use TLS, so the connection code differs slightly from local dev. The Quick Connect panel gives you the exact snippet for your instance.

Wrapping Up

SQLite and LibSQL are more alike than different. Same SQL, same file format, same performance characteristics for single-process workloads. The decision boils down to a few questions:

• Do multiple processes need to connect? LibSQL.
• Do you need replication? LibSQL.
• Do you need vector search without extension management? LibSQL.
• Is your app a single process that owns the database file? SQLite.
• Do you want the most battle-tested option with the smallest surface area? SQLite.

If none of the LibSQL-specific features matter to you, use SQLite. Simpler is better when it gets the job done. If you need any of those features, LibSQL is the obvious choice: same SQL, same engine, more capabilities.

To manage your local instances:

spindb stop sqlite1     # Stop SQLite
spindb stop libsql1     # Stop LibSQL
spindb start sqlite1    # Start SQLite
spindb start libsql1    # Start LibSQL
spindb list             # See all your database instances

SpinDB handles 20+ database engines, so you can run SQLite, LibSQL, PostgreSQL, Redis, and more side by side from one CLI. Layerbase Desktop wraps the same functionality in a macOS GUI if you prefer that.