LayerbaseLayerbase

Getting Started with LibSQL

LibSQLDatabasesSQLite

SQLite is fast, reliable, and needs zero configuration. But it has two hard limitations. It can't be accessed over a network (only the process that opens the file can read or write). And it has no built-in replication (no syncing a local copy from a remote primary without third-party tooling).

LibSQL is an open-source fork of SQLite, created by the Turso team, that removes both limitations. It adds server mode (HTTP and WebSocket access for multiple concurrent clients), replication (embedded replicas that sync from a primary), and native vector search (an F32_BLOB type for similarity queries). It's fully SQLite-compatible, so everything you know transfers directly.

We'll build a bookmarks and knowledge base app in one TypeScript file: table creation, JSON queries, vector searches, and batch transactions. Follow along locally, or spin up a managed instance on Layerbase Cloud to jump straight to the code.

Contents

Create a LibSQL Instance

Local with SpinDB

The quickest local setup uses SpinDB, which runs databases without Docker or containers. (What is SpinDB?)

Install SpinDB globally:

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

Or run it directly without installing:

bash
npx spindb create libsql1 -e libsql --start  # npm
pnpx spindb create libsql1 -e libsql --start # pnpm

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

bash
spindb create libsql1 -e libsql --start

SpinDB downloads the LibSQL server binary for your platform, configures it, and starts the HTTP server. Verify it's running:

bash
spindb url libsql1
text
http://127.0.0.1:8080

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

Layerbase Cloud

Rather skip local setup? Layerbase Cloud has managed LibSQL instances ready in seconds. Pick LibSQL from the engine list and grab your connection URL and auth token from the Quick Connect panel.

Cloud instances use TLS, so the connection code is slightly different:

typescript
import { createClient } from '@libsql/client'

const client = createClient({
  url: 'https://cloud.layerbase.dev:PORT',
  authToken: 'YOUR_TOKEN',
})

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 libsql-bookmarks && cd libsql-bookmarks
pnpm init
pnpm add @libsql/client
pnpm add -D tsx typescript

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

The Bookmarks Dataset

Twelve bookmarks for a personal knowledge base. Each has a URL, title, description, and a JSON array of tags. In a real app this might come from a browser extension or an API:

typescript
const bookmarks = [
  {
    url: 'https://sqlite.org/lang_with.html',
    title: 'SQLite Common Table Expressions',
    description:
      'Official documentation for recursive and non-recursive CTEs in SQLite, with examples for hierarchical queries.',
    tags: ['sql', 'sqlite', 'reference'],
  },
  {
    url: 'https://fly.io/blog/all-in-on-sqlite-litestream/',
    title: 'Why Fly.io Went All-In on SQLite',
    description:
      'How Fly.io uses SQLite with Litestream for production workloads, covering replication and disaster recovery.',
    tags: ['sqlite', 'infrastructure', 'case-study'],
  },
  {
    url: 'https://jvns.ca/blog/2014/09/27/how-does-sqlite-work-part-1-pages/',
    title: 'How Does SQLite Work? Part 1: Pages',
    description:
      'Julia Evans breaks down the SQLite file format, explaining how data is stored in pages and how the B-tree structure works.',
    tags: ['sqlite', 'internals', 'learning'],
  },
  {
    url: 'https://turso.tech/blog/introducing-libsql',
    title: 'Introducing LibSQL',
    description:
      'The Turso team explains why they forked SQLite and what LibSQL adds: server mode, replication, and extensibility.',
    tags: ['libsql', 'announcement', 'turso'],
  },
  {
    url: 'https://simonwillison.net/2024/Oct/17/embedding-models/',
    title: 'Embedding Models: From Architecture to Implementation',
    description:
      'How text embedding models work: tokenization, transformer layers, and similarity metrics explained from the ground up.',
    tags: ['machine-learning', 'embeddings', 'tutorial'],
  },
  {
    url: 'https://www.patterns.dev/react/render-props-pattern',
    title: 'React Render Props Pattern',
    description:
      'Explains the render props pattern in React for sharing stateful logic between components without higher-order components.',
    tags: ['react', 'patterns', 'frontend'],
  },
  {
    url: 'https://github.com/asg017/sqlite-vec',
    title: 'sqlite-vec: Vector Search for SQLite',
    description:
      'A SQLite extension for vector similarity search. Works with regular SQLite but requires manual installation and loading.',
    tags: ['sqlite', 'vector-search', 'extension'],
  },
  {
    url: 'https://blog.wesleyac.com/posts/consider-sqlite',
    title: 'Consider SQLite',
    description:
      'Makes the case for using SQLite in production web applications, addressing common concerns about concurrency and scaling.',
    tags: ['sqlite', 'opinion', 'architecture'],
  },
  {
    url: 'https://kentcdodds.com/blog/common-mistakes-with-react-testing-library',
    title: 'Common Mistakes with React Testing Library',
    description:
      'Kent C. Dodds covers frequent testing antipatterns and how to write more maintainable, accessible tests.',
    tags: ['testing', 'react', 'best-practices'],
  },
  {
    url: 'https://www.docker.com/blog/docker-best-practices-using-arg-and-env/',
    title: 'Docker Best Practices: ARG and ENV',
    description:
      'Explains the difference between build-time ARG and runtime ENV variables in Dockerfiles with practical examples.',
    tags: ['docker', 'devops', 'best-practices'],
  },
  {
    url: 'https://mitchellh.com/writing/zig-and-rust',
    title: 'Zig and Rust',
    description:
      'Mitchell Hashimoto compares Zig and Rust from the perspective of someone who uses both, focusing on compile times and memory safety tradeoffs.',
    tags: ['zig', 'rust', 'languages'],
  },
  {
    url: 'https://brandur.org/soft-deletion',
    title: 'Soft Deletion Probably Isn\'t Worth It',
    description:
      'Argues against soft deletion as a default pattern, proposing event logs and audit tables as more reliable alternatives.',
    tags: ['databases', 'patterns', 'opinion'],
  },
]

Connect and Create the Table

Schema definition works just like SQLite. The one addition is the embedding column, which uses LibSQL's native F32_BLOB(384) vector type for similarity search.

typescript
import { createClient } from '@libsql/client'

const client = createClient({
  url: 'http://localhost:8080', // Use the URL from `spindb url libsql1`
})

await client.execute('DROP TABLE IF EXISTS bookmarks')

await client.execute(`
  CREATE TABLE bookmarks (
    id INTEGER PRIMARY KEY,
    url TEXT NOT NULL,
    title TEXT NOT NULL,
    description TEXT NOT NULL,
    tags TEXT NOT NULL,
    embedding F32_BLOB(384),
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
  )
`)

console.log('Created bookmarks table')

The tags column stores a JSON array as TEXT. LibSQL (like SQLite) has no dedicated JSON column type, but its JSON functions handle querying just fine. The embedding column is nullable because we'll insert bookmarks first and add vectors later.

Insert Bookmarks

Insert all 12 bookmarks, serializing the tags array with JSON.stringify():

typescript
for (const bookmark of bookmarks) {
  await client.execute({
    sql: `INSERT INTO bookmarks (url, title, description, tags)
          VALUES (?, ?, ?, ?)`,
    args: [
      bookmark.url,
      bookmark.title,
      bookmark.description,
      JSON.stringify(bookmark.tags),
    ],
  })
}

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

LibSQL uses ? for positional parameters and the args array for values. This prevents SQL injection and handles escaping automatically.

Basic Queries

Standard SQL works as you'd expect:

typescript
// Fetch all bookmarks, most recent first
const all = await client.execute(
  'SELECT id, title, created_at FROM bookmarks ORDER BY created_at DESC',
)

console.log(`\nAll bookmarks (${all.rows.length}):`)
for (const row of all.rows) {
  console.log(`  [${row.id}] ${row.title}`)
}

// Search titles by keyword
const sqliteBookmarks = await client.execute({
  sql: `SELECT id, title FROM bookmarks
        WHERE title LIKE ? OR description LIKE ?`,
  args: ['%SQLite%', '%SQLite%'],
})

console.log(`\nBookmarks mentioning SQLite (${sqliteBookmarks.rows.length}):`)
for (const row of sqliteBookmarks.rows) {
  console.log(`  [${row.id}] ${row.title}`)
}
text
All bookmarks (12):
  [1] SQLite Common Table Expressions
  [2] Why Fly.io Went All-In on SQLite
  [3] How Does SQLite Work? Part 1: Pages
  [4] Introducing LibSQL
  [5] Embedding Models: From Architecture to Implementation
  [6] React Render Props Pattern
  [7] sqlite-vec: Vector Search for SQLite
  [8] Consider SQLite
  [9] Common Mistakes with React Testing Library
  [10] Docker Best Practices: ARG and ENV
  [11] Zig and Rust
  [12] Soft Deletion Probably Isn't Worth It

Bookmarks mentioning SQLite (5):
  [1] SQLite Common Table Expressions
  [2] Why Fly.io Went All-In on SQLite
  [3] How Does SQLite Work? Part 1: Pages
  [7] sqlite-vec: Vector Search for SQLite
  [8] Consider SQLite

Standard SQLite behavior. Every query you know works in LibSQL without modification.

Server Mode: Multiple Clients

This is the fundamental difference. With SQLite, only one process can open the database file. With LibSQL, you connect over HTTP, so multiple clients work simultaneously.

typescript
// Create a second client pointing to the same server
const client2 = createClient({
  url: 'http://localhost:8080',
})

// Both clients can query at the same time
const [fromClient1, fromClient2] = await Promise.all([
  client.execute('SELECT COUNT(*) as count FROM bookmarks'),
  client2.execute(
    "SELECT title FROM bookmarks WHERE title LIKE '%LibSQL%'",
  ),
])

console.log(`\nClient 1 sees ${fromClient1.rows[0].count} bookmarks`)
console.log(`Client 2 found: ${fromClient2.rows[0].title}`)
text
Client 1 sees 12 bookmarks
Client 2 found: Introducing LibSQL

SQLite simply can't do this. Web server, background workers, CLI tools: they all connect to the same LibSQL instance concurrently. No file locking, no "database is locked" errors.

JSON Support

LibSQL inherits SQLite's JSON functions, which handle most use cases. Query the tags column using json_each() to unnest the array and filter by tag:

typescript
// Find all bookmarks tagged with "sqlite"
const sqliteTagged = await client.execute(`
  SELECT b.id, b.title
  FROM bookmarks b, json_each(b.tags) t
  WHERE t.value = 'sqlite'
  ORDER BY b.title
`)

console.log(`\nTagged "sqlite" (${sqliteTagged.rows.length}):`)
for (const row of sqliteTagged.rows) {
  console.log(`  [${row.id}] ${row.title}`)
}

// Count bookmarks per tag
const tagCounts = await client.execute(`
  SELECT t.value as tag, COUNT(*) as count
  FROM bookmarks b, json_each(b.tags) t
  GROUP BY t.value
  ORDER BY count DESC
  LIMIT 5
`)

console.log('\nTop 5 tags:')
for (const row of tagCounts.rows) {
  console.log(`  ${row.tag}: ${row.count}`)
}

// Find bookmarks with a specific combination of tags
const multiTag = await client.execute(`
  SELECT b.id, b.title
  FROM bookmarks b
  WHERE EXISTS (SELECT 1 FROM json_each(b.tags) WHERE value = 'sqlite')
    AND EXISTS (SELECT 1 FROM json_each(b.tags) WHERE value = 'internals')
`)

console.log(`\nTagged both "sqlite" and "internals" (${multiTag.rows.length}):`)
for (const row of multiTag.rows) {
  console.log(`  [${row.id}] ${row.title}`)
}
text
Tagged "sqlite" (5):
  [8] Consider SQLite
  [3] How Does SQLite Work? Part 1: Pages
  [1] SQLite Common Table Expressions
  [2] Why Fly.io Went All-In on SQLite
  [7] sqlite-vec: Vector Search for SQLite

Top 5 tags:
  sqlite: 5
  best-practices: 2
  opinion: 2
  patterns: 2
  react: 2

Tagged both "sqlite" and "internals" (1):
  [3] How Does SQLite Work? Part 1: Pages

json_each() turns a JSON array into rows you can join, filter, and aggregate like a regular table. json_extract() pulls specific keys from JSON objects. The full SQLite JSON documentation applies to LibSQL.

Native Vector Search

This is LibSQL's standout feature. The F32_BLOB column type stores float vectors, and vector_distance_cos() does cosine similarity search. No extensions, no configuration. It just works.

In production, you'd generate real embeddings with a model like all-MiniLM-L6-v2 via @xenova/transformers (the approach in the Qdrant post). To keep this focused on LibSQL, we'll use placeholder vectors that give similar bookmarks nearby coordinates:

typescript
// Placeholder embeddings: 384-dimensional vectors
// In production, generate these with a model like all-MiniLM-L6-v2
function placeholderEmbedding(seed: number[]): string {
  const vec = new Array(384).fill(0)
  for (let i = 0; i < seed.length; i++) {
    for (let j = 0; j < 384; j++) {
      vec[j] += Math.sin(seed[i] * (j + 1) * 0.1) / seed.length
    }
  }
  return `[${vec.join(',')}]`
}

// Group similar bookmarks with shared seed values
const embeddings: Record<number, string> = {
  1: placeholderEmbedding([1, 2]),       // SQLite + reference
  2: placeholderEmbedding([1, 3]),       // SQLite + infrastructure
  3: placeholderEmbedding([1, 4]),       // SQLite + internals
  4: placeholderEmbedding([5, 6]),       // LibSQL + announcement
  5: placeholderEmbedding([7, 8]),       // ML + embeddings
  6: placeholderEmbedding([9, 10]),      // React + patterns
  7: placeholderEmbedding([1, 7]),       // SQLite + vector search
  8: placeholderEmbedding([1, 3, 11]),   // SQLite + architecture
  9: placeholderEmbedding([9, 12]),      // React + testing
  10: placeholderEmbedding([13, 14]),    // Docker + devops
  11: placeholderEmbedding([15, 16]),    // Languages + comparison
  12: placeholderEmbedding([17, 3]),     // Databases + patterns
}

// Update bookmarks with vector embeddings
for (const [id, vec] of Object.entries(embeddings)) {
  await client.execute({
    sql: 'UPDATE bookmarks SET embedding = vector(?) WHERE id = ?',
    args: [vec, Number(id)],
  })
}

console.log('Added vector embeddings to all bookmarks')

Now query for similar bookmarks using cosine distance:

typescript
// Find bookmarks similar to "How Does SQLite Work? Part 1: Pages" (id=3)
const sourceBookmark = await client.execute({
  sql: 'SELECT id, title, embedding FROM bookmarks WHERE id = ?',
  args: [3],
})

const similar = await client.execute({
  sql: `SELECT id, title,
        vector_distance_cos(embedding, ?) as distance
        FROM bookmarks
        WHERE id != ?
        ORDER BY distance ASC
        LIMIT 5`,
  args: [sourceBookmark.rows[0].embedding, 3],
})

console.log(`\nBookmarks similar to "${sourceBookmark.rows[0].title}":`)
for (const row of similar.rows) {
  console.log(`  ${Number(row.distance).toFixed(4)}  ${row.title}`)
}
text
Bookmarks similar to "How Does SQLite Work? Part 1: Pages":
  0.0412  SQLite Common Table Expressions
  0.0587  Consider SQLite
  0.0834  sqlite-vec: Vector Search for SQLite
  0.1201  Why Fly.io Went All-In on SQLite
  0.3956  Soft Deletion Probably Isn't Worth It

Lower distance = more similar. The SQLite-related bookmarks cluster together because their vectors share similar seed values. With real model-generated embeddings, the similarity would reflect actual semantic meaning.

vector_distance_cos() computes cosine distance. LibSQL also supports vector_distance_l2() for Euclidean distance. The F32_BLOB type stores vectors in a compact binary format, and you can create vector indexes for faster search on large tables.

Batch Transactions

batch() executes multiple statements atomically. All succeed or none do:

typescript
const result = await client.batch(
  [
    {
      sql: `INSERT INTO bookmarks (url, title, description, tags)
            VALUES (?, ?, ?, ?)`,
      args: [
        'https://orm.drizzle.team/docs/overview',
        'Drizzle ORM Documentation',
        'TypeScript ORM with a SQL-like query builder that works with SQLite, PostgreSQL, and MySQL.',
        JSON.stringify(['orm', 'typescript', 'databases']),
      ],
    },
    {
      sql: `INSERT INTO bookmarks (url, title, description, tags)
            VALUES (?, ?, ?, ?)`,
      args: [
        'https://bun.sh/docs/api/sqlite',
        'Bun SQLite API',
        'Built-in SQLite bindings in Bun with synchronous and asynchronous query support.',
        JSON.stringify(['bun', 'sqlite', 'runtime']),
      ],
    },
    {
      sql: 'SELECT COUNT(*) as count FROM bookmarks',
      args: [],
    },
  ],
  'write',
)

const finalCount = result[2].rows[0].count
console.log(`\nBatch insert complete. Total bookmarks: ${finalCount}`)
text
Batch insert complete. Total bookmarks: 14

The 'write' transaction mode runs all three statements in a single transaction. If the second insert failed (duplicate URL, say), the first would roll back too. The count reflects both inserts because it runs in the same transaction.

Batching is also faster than individual calls. One HTTP request instead of three round trips.

LibSQL vs SQLite

LibSQL is a strict superset of SQLite. Everything SQLite does, LibSQL does. The differences are all additions:

FeatureSQLiteLibSQL
SQL compatibilityFullFull (same engine)
Network accessNo (embedded only)HTTP and WebSocket server
Concurrent clientsSingle processMultiple clients over network
ReplicationNot built-inEmbedded replicas that sync from primary
Vector searchRequires extensionsNative F32_BLOB type and distance functions
ALTER TABLELimitedExtended (e.g., drop column backfill)
File format.db / .sqliteCompatible with SQLite format

If your app runs as a single process and doesn't need any of those additions, plain SQLite is simpler with zero network overhead. But if you need network access, concurrent clients, replication, or vector search, LibSQL gives you all of that without leaving the SQLite ecosystem.

When to Use LibSQL

LibSQL fits naturally into several architectures:

  • Local-first apps with server sync: embedded replica on the client, syncing from a remote primary. Instant reads, no network latency, writes replicate in the background
  • Edge applications: embedded replicas at edge locations, each syncing from a central primary. Low-latency reads worldwide
  • Apps that need SQL and vector search together: skip the separate vector database. LibSQL handles both in one process
  • Multi-service architectures: several services querying the same data? Server mode means concurrent connections without file locking
  • Any SQLite use case that outgrows a single process: when you need network access, replication, or concurrent writers, LibSQL is a drop-in upgrade

Wrapping Up

That covered a lot of ground: table creation, inserts, keyword queries, JSON functions, vector similarity, and batch transactions. Almost all of it was standard SQL. The only LibSQL-specific bits are F32_BLOB, vector_distance_cos(), and client.batch().

The LibSQL documentation covers embedded replicas, extensions, and the HTTP API. The @libsql/client README has detailed TypeScript examples.

To manage your local LibSQL instance:

bash
spindb stop libsql1    # Stop the server
spindb start libsql1   # Start it again
spindb list            # See all your database instances

SpinDB works with 20+ engines, so you can run Redis for caching or Qdrant for heavier vector workloads right alongside LibSQL, all from one CLI. Layerbase Desktop wraps the same functionality in a macOS app if you prefer that.

Something not working?