SQLite

To use FrogBot with SQLite, install the package @frogbotai/db-sqlite. It leverages Drizzle ORM and libSQL to interact with a SQLite database that you provide. It automatically manages changes to your database for you in development mode, and exposes a full suite of migration controls for you to leverage in order to keep other database environments in sync with your schema. DDL transformations are automatically generated. To configure FrogBot to use SQLite, pass the sqliteAdapter to your FrogBot Config as follows:

Install

pnpm add @frogbotai/db-sqlite

Configure

import { sqliteAdapter } from '@frogbotai/db-sqlite'

export default buildConfig({
  // Your config goes here
  collections: [
    // Collections go here
  ],
  // Configure the SQLite adapter here
  db: sqliteAdapter({
    // SQLite-specific arguments go here.
    // `client.url` is required.
    client: {
      url: process.env.DATABASE_URL,
      authToken: process.env.DATABASE_AUTH_TOKEN,
    },
  }),
})

Collection search

The SQLite adapter implements collection search with SQLite’s FTS5 full-text engine and LibSQL’s native vector functions. Lexical, vector, and hybrid queries each run as a single SQL statement, and ranking happens inside the database after access, where, draft, and locale filters are applied.

import { sqliteAdapter } from '@frogbotai/db-sqlite'
import { buildConfig } from 'frogbot'

export default buildConfig({
  secret: process.env.FROGBOT_SECRET!,
  db: sqliteAdapter({ client: { url: process.env.DATABASE_URL! } }),
  collections: [
    {
      slug: 'articles',
      fields: [
        { name: 'title', type: 'text', required: true },
        { name: 'body', type: 'textarea' },
        { name: 'embedding', type: 'vector', dimensions: 1536 },
      ],
      search: {
        content: {
          lexical: { fields: ['title', 'body'], language: 'english' },
          vector: { field: 'embedding' },
        },
      },
    },
  ],
})

Prerequisites

Each mode needs a feature from the connected database. The adapter checks the connection when it connects, before development push and before migrate:

Mode	Needs
Lexical	The FTS5 module
Vector	LibSQL vector functions (`vector32`, `vector_distance_cos`, `vector_distance_l2`, `vector_top_k`)
Hybrid	Both

The local LibSQL client bundled with the adapter includes both. A remote LibSQL server must provide them too. If a configured index needs a feature the connection lacks, startup fails with SearchCapabilityError and reason missing-prerequisite, naming the collection, index, and mode. Two index options fail at startup with reason engine-gap:

The dotProduct metric. LibSQL has no inner-product distance, so use cosine or euclidean.
A lexical.language other than english or simple.

Storage and indexes

Vector fields keep their ordinary JSON storage. Each search index adds its own objects, all named with a frogbot_search_ prefix:

An FTS5 table over the index’s lexical fields. lexical.language selects the tokenizer: simple (the default) matches Unicode words case-insensitively with diacritics removed, and english adds English stemming, so running matches run.
For an index with vector and approximate left on, a table holding a 32-bit float copy of every vector and a LibSQL vector index (DiskANN) on it, using the index’s metric. Set vector.approximate: false to skip it and always search exactly.

Database triggers keep these copies in sync in the same transaction as every create, update, and delete, so a rolled-back write never leaves stale search entries behind. Collections with drafts get the same objects on the version table, so draft: true queries rank the latest draft of each document. Records without a vector, or with a vector of different dimensions, are not indexed.

Ranking

Mode	`ranking.method`	Score
Lexical	`sqlite-fts5`	Negated BM25. Higher is better.
Vector	`libsql-diskann` or `libsql-exact`	Cosine similarity, or Euclidean distance (lower is better). Computed with 32-bit floats.
Hybrid	`rrf`	Weighted reciprocal rank fusion, `weight / (60 + rank)` summed over both components. Higher is better.

Query text supports web search syntax: the terms must all match, quoted words match as a phrase, or between terms accepts either one, and - excludes a term. Other punctuation separates words and is never parsed as FTS5 syntax. The text is always sent as a bound parameter. Vector and hybrid queries on a vector index report approximate: true. The index cannot apply your filters itself, so it returns the resolved candidate count of nearest records, and the same statement then applies access and where filters and returns the top limit. A selective filter can therefore return fewer than limit hits even when more matching records exist; raise candidates to trade speed for completeness. Exact vector search applies filters first, compares the query with every remaining record, and ignores candidates. Hybrid search takes up to the resolved candidate count from each component, joins them in the database, and returns the top limit by fused score. A record that matches only one component still ranks, with the other component reported as null. When the request already runs in a transaction, search reads through it and sees its uncommitted writes.

Push and migrations

Search tables, indexes, and triggers are part of your schema. Development push creates them and leaves them untouched while they match your configuration. When an index definition changes, or a push changes a table an index reads from, the adapter drops the search objects and rebuilds them from the stored records. migrate:create writes the same statements into your migration and rebuilds only the indexes whose definition or source tables changed. Changing a field list, metric, dimensions, or approximate produces a rebuild; embeddings are never regenerated.

Limits

Localized fields are ranked on the requested locale’s stored value. Fallback-locale values are not ranked.
If a search runs before its search objects match the configuration, for example before you run migrations, it fails with SearchReadinessError.
The adapter removes objects with the frogbot_search_ prefix that no longer match your configuration. Do not create your own tables, indexes, or triggers with this prefix.

Options

Option	Type	Default	Description
`client`	`Config`	Required	libSQL client options passed to `createClient` from `@libsql/client`, such as `url` and `authToken`.
`push`	`boolean`	`true`	Pushes schema changes to the database with Drizzle’s `db push` on startup. Push never runs when `NODE_ENV` is `production` or during migration commands. Set `false` to rely on migrations only.
`migrationDir`	`string`	First of `src/migrations`, `dist/migrations`, or `migrations` that exists	Directory migrations are written to and read from. See Migrations.
`prodMigrations`	`{ name: string; up: (args: MigrateUpArgs) => Promise<void>; down: (args: MigrateDownArgs) => Promise<void> }[]`	—	Migrations to run on connect when `NODE_ENV` is `production`. See Running migrations in production.
`idType`	`'number' \| 'uuid' \| 'uuidv7'`	`'number'`	Column type for document IDs. `number` IDs are integers; `uuid` and `uuidv7` IDs are strings.
`autoIncrement`	`boolean`	`false`	Adds SQLite AUTOINCREMENT to primary keys, so IDs from deleted rows are never reused.
`allowIDOnCreate`	`boolean`	`false`	Uses an `id` passed in create data, without a custom ID field.
`transactionOptions`	`SQLiteTransactionConfig \| false`	Transactions off	Options for transactions. Transactions are off unless you pass an object, such as `{}`. When a collection sets `auth.signIn` and you leave this unset, FrogBot turns transactions on. See Transactions.
`busyTimeout`	`number`	`1000`	Milliseconds to wait when another process holds the database lock. See Concurrent writes.
`writeLockTimeout`	`number`	`5000`	Milliseconds a write waits for the write or transaction ahead of it in this process before it fails. See Concurrent writes.
`wal`	`boolean \| { journalSizeLimit?: number; synchronous?: 'EXTRA' \| 'FULL' \| 'NORMAL' \| 'OFF' }`	`true` for a `file:` URL, otherwise `false`	Write-Ahead Logging, for better concurrency. Applies only to `file:` URLs. Pass `false` to use SQLite’s rollback journal instead. See WAL mode.
`blocksAsJSON`	`boolean`	`false`	Stores blocks in a JSON column instead of relational tables, which can be faster with many blocks.
`localesSuffix`	`string`	`'_locales'`	Suffix for tables that store localized fields.
`relationshipsSuffix`	`string`	`'_rels'`	Suffix for tables that store relationships.
`versionsSuffix`	`string`	`'_v'`	Suffix for tables that store versions.
`beforeSchemaInit`	`SQLiteSchemaHook[]`	`[]`	Drizzle schema hooks that run before the schema is built. See beforeSchemaInit.
`afterSchemaInit`	`SQLiteSchemaHook[]`	`[]`	Drizzle schema hooks that run after the schema is built. See afterSchemaInit.
`logger`	`boolean \| Logger`	`false`	Drizzle query logger. `true` logs every query; pass a Drizzle `Logger` to handle them yourself.
`generateSchemaOutputFile`	`string`	A generated-schema file in `src/`, or in the project root without `src/`	File that `frogbot.db.generateSchema()` writes the typed Drizzle schema to.
`schemaName`	`string`	—	Accepted for parity with the Postgres adapter. SQLite has no schemas, so it has no effect.

Concurrent writes

SQLite allows one writer at a time. Within one process, the adapter queues writes: each write statement, and each transaction from its start to its commit or rollback, waits for the one ahead of it. Reads don’t wait, so a read without req during an open transaction returns the last committed data at once. A write that waits longer than writeLockTimeout fails with an error saying how long it waited. There are two causes. Most often, code inside a hook wrote through the Local API without passing req, so it waited on the very transaction it was part of. Pass req so the write joins the transaction:

afterChange: [
  async ({ req }) => {
    await req.payload.create({ collection: 'audit-logs', data: { event: 'saved' }, req })
  },
],

Otherwise a transaction held the lock for longer than writeLockTimeout, for example a hook that awaits a slow API call before the write returns. Shorten the transaction, by moving the slow work out of the hook, or raise writeLockTimeout. If a commit fails, for example on a deferred foreign key, the adapter rolls the transaction back and the operation rejects with the commit error, so nothing reports success without being saved. busyTimeout applies only when another process, such as a migration run, holds the lock. The wait blocks the server’s event loop, so keep it short.

Access to Drizzle

After FrogBot is initialized, this adapter will expose the full power of Drizzle to you for use if you need it. You can query the adapter’s runtime tables directly. These queries are untyped:

// To avoid installing Drizzle, you can import everything that drizzle has from our re-export path.
import { eq, sql, and } from '@frogbotai/db-sqlite/drizzle'

const postsTable = frogbot.db.tables.posts

// Drizzle's Querying API: https://orm.drizzle.team/docs/rqb
const posts = await frogbot.db.drizzle.query.posts.findMany()

// Drizzle's Select API: https://orm.drizzle.team/docs/select
const result = await frogbot.db.drizzle
  .select()
  .from(postsTable)
  .where(
    and(
      eq(postsTable.id, 50),
      sql`lower(${postsTable.title}) = 'example post title'`,
    ),
  )

Tables and relations

In addition to exposing Drizzle directly, all of the tables and Drizzle relations are exposed for you via the frogbot.db property as well.

Tables - frogbot.db.tables
Relations - frogbot.db.relations

Prototyping in development mode

Drizzle exposes two ways to work locally in development mode. The first is db push, which automatically pushes changes you make to your FrogBot Config (and therefore, Drizzle schema) to your database so you don’t have to manually migrate every time you change your FrogBot Config. This only works in development mode, and should not be mixed with manually running migrate commands. You will be warned if any changes that you make will entail data loss while in development mode. Push is enabled by default, but you can opt out if you’d like. Alternatively, you can disable push and rely solely on migrations to keep your local database in sync with your FrogBot Config.

Migration workflows

In SQLite, migrations are a fundamental aspect of working with FrogBot and you should become familiar with how they work. For more information about migrations, click here.

Drizzle schema hooks

Apps created with create-frogbot-app already depend on drizzle-orm at the adapter’s version; in other apps, install the same version used by your database adapter.

beforeSchemaInit

Runs before the schema is built. You can use this hook to extend your database structure with tables that won’t be managed by FrogBot.

import { sqliteAdapter } from '@frogbotai/db-sqlite'
import { integer, sqliteTable } from 'drizzle-orm/sqlite-core'

sqliteAdapter({
  beforeSchemaInit: [
    ({ schema, adapter }) => {
      return {
        ...schema,
        tables: {
          ...schema.tables,
          addedTable: sqliteTable('added_table', {
            id: integer('id').primaryKey({ autoIncrement: true }),
          }),
        },
      }
    },
  ],
})

One use case is preserving your existing database structure when migrating to FrogBot. By default, FrogBot drops the current database schema, which may not be desirable in this scenario. To quickly generate the Drizzle schema from your database you can use Drizzle Introspection You should get the schema.ts file which may look like this:

import {
  sqliteTable,
  text,
  uniqueIndex,
  integer,
} from 'drizzle-orm/sqlite-core'

export const users = sqliteTable('users', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  fullName: text('full_name'),
  phone: text('phone', { length: 256 }),
})

export const countries = sqliteTable(
  'countries',
  {
    id: integer('id').primaryKey({ autoIncrement: true }),
    name: text('name', { length: 256 }),
  },
  (countries) => {
    return {
      nameIndex: uniqueIndex('name_idx').on(countries.name),
    }
  },
)

You can import them into your config and append to the schema with the beforeSchemaInit hook like this:

import { sqliteAdapter } from '@frogbotai/db-sqlite'
import { users, countries } from '../drizzle/schema'

sqliteAdapter({
  beforeSchemaInit: [
    ({ schema, adapter }) => {
      return {
        ...schema,
        tables: {
          ...schema.tables,
          users,
          countries,
        },
      }
    },
  ],
})

Make sure FrogBot doesn’t overlap table names with its collections. For example, if you already have a collection with slug “users”, you should either change the slug or dbName to change the table name for this collection.

afterSchemaInit

Runs after the Drizzle schema is built. You can use this hook to modify the schema with features that aren’t supported by FrogBot, or if you want to add a column that you don’t want to be in the FrogBot config. To extend a table, FrogBot exposes extendTable utility to the args. You can refer to the Drizzle documentation. The following example adds the extra_integer_column column and a composite index on country and city columns.

import { sqliteAdapter } from '@frogbotai/db-sqlite'
import { index, integer } from 'drizzle-orm/sqlite-core'
import { buildConfig } from 'frogbot'

export default buildConfig({
  collections: [
    {
      slug: 'places',
      fields: [
        {
          name: 'country',
          type: 'text',
        },
        {
          name: 'city',
          type: 'text',
        },
      ],
    },
  ],
  db: sqliteAdapter({
    afterSchemaInit: [
      ({ schema, extendTable, adapter }) => {
        extendTable({
          table: schema.tables.places,
          columns: {
            extraIntegerColumn: integer('extra_integer_column'),
          },
          extraConfig: (table) => ({
            country_city_composite_index: index(
              'country_city_composite_index',
            ).on(table.country, table.city),
          }),
        })

        return schema
      },
    ],
  }),
})

Adding tables through rawTables

You can also add tables, columns, and indexes to the adapter’s raw SQL schema through adapter.rawTables in beforeSchemaInit. The raw schema supports fewer features than Drizzle’s table builders.

import { sqliteAdapter } from '@frogbotai/db-sqlite'

sqliteAdapter({
  beforeSchemaInit: [
    ({ schema, adapter }) => {
      // Add a new table
      adapter.rawTables.myTable = {
        name: 'my_table',
        columns: {
          my_id: {
            name: 'my_id',
            type: 'integer',
            primaryKey: true,
          },
        },
      }

      // Add a new column to a table generated by FrogBot
      adapter.rawTables.posts.columns.customColumn = {
        name: 'custom_column',
        type: 'integer',
        notNull: true,
      }

      // Add a new index to a table generated by FrogBot
      adapter.rawTables.posts.indexes.customColumnIdx = {
        name: 'custom_column_idx',
        unique: true,
        on: ['custom_column'],
      }

      return schema
    },
  ],
})

D1 Database

This adapter is currently in beta and could be subject to breaking changes.

We also provide a separate adapter to connect to Cloudflare D1, which is a serverless SQLite database. To use it, install the package @frogbotai/db-d1-sqlite and configure it as follows:

import { sqliteD1Adapter } from '@frogbotai/db-d1-sqlite'

export default buildConfig({
  // Your config goes here
  collections: [
    // Collections go here
  ],
  // Configure the D1 adapter here
  db: sqliteD1Adapter({
    // D1-specific arguments go here.
    // `binding` is required and should match the D1 database binding name in your Cloudflare Worker environment.
    binding: cloudflare.env.D1,
  }),
})

It accepts the SQLite adapter options except client, busyTimeout, and wal, plus these:

Option	Type	Default	Description
`binding`	`D1Database`	Required	The D1 database binding from your Cloudflare Worker environment.
`readReplicas`	`'first-primary'`	—	Experimental. Sends reads to D1 read replicas with the `first-primary` strategy. See D1 Read Replicas.

Unlike @frogbotai/db-sqlite, the D1 adapter doesn’t turn transactions on for collections that set auth.signIn. You can see our Cloudflare D1 template for a full example of how to set this up.

D1 collection search

The D1 adapter implements lexical collection search with the same FTS5 tables, triggers, query syntax, and sqlite-fts5 ranking as the SQLite adapter. Search objects are part of development push and generated migrations, and ranking happens inside D1 after access, where, draft, and locale filters are applied.

import { sqliteD1Adapter } from '@frogbotai/db-d1-sqlite'
import { buildConfig } from 'frogbot'

export default buildConfig({
  db: sqliteD1Adapter({ binding: cloudflare.env.D1 }),
  collections: [
    {
      slug: 'articles',
      fields: [
        { name: 'title', type: 'text', required: true },
        { name: 'body', type: 'textarea' },
        { name: 'embedding', type: 'vector', dimensions: 1536 },
      ],
      search: {
        content: {
          lexical: { fields: ['title', 'body'], language: 'english' },
        },
      },
    },
  ],
})

Cloudflare D1 has no native vector search, so vector and hybrid search are not available. An index that declares vector fails startup with SearchCapabilityError and reason engine-gap, naming the collection and index, before any table is created. Vector fields still store and return supplied vectors without a search index; each vector is one JSON value, so its dimensions are not limited by D1’s bound-parameter count. A lexical.language other than english or simple also fails with reason engine-gap. FTS5 is built into D1, so there is no prerequisite to install. D1 does not wrap writes in transactions by default. Each trigger runs inside the statement that changed the row, so after every statement the search entries match the stored rows. Keep these D1 limits in mind:

Every lexical query reads the matching rows of the FTS5 table and the records it joins, and those reads count toward your plan’s rows-read quota. Broad terms on large collections read more rows and take longer; each query must finish within D1’s query time limit.
wrangler d1 export does not support databases with virtual tables, which include FTS5 search tables. Remove the search indexes and run migrations before exporting, then restore them.
Adding or rebuilding an index fills its search table from every stored record in one statement, which must also finish within the query time limit.
D1 accepts at most 100 bound parameters per statement. As with find, a long in list in where can exceed it; id lists are exempt except in draft queries. A draft: true search also reads its hits back by ID, so keep its limit below 100.

D1 Read Replicas

You can enable read replicas support with the first-primary strategy. This is experimental. You must also enable it on your D1 database in the Cloudflare dashboard. Read more about it in the Cloudflare documentation.

All write queries are still forwarded to the primary database instance. Read replication only improves the response time for read query requests.

import { sqliteD1Adapter } from '@frogbotai/db-d1-sqlite'

export default buildConfig({
  collections: [],
  db: sqliteD1Adapter({
    binding: cloudflare.env.D1,
    // You can also enable read replicas support with the `first-primary` strategy.
    readReplicas: 'first-primary',
  }),
})

You can then verify that they’re being used by checking the logs in your Cloudflare dashboard. You should see logs indicating whether a read or write operation was performed, and on which database instance.

WAL Mode

Write-Ahead Logging (WAL) is on by default for file: databases. In WAL mode, reads don’t wait for a write that’s committing, and a large transaction doesn’t block reads. Pass wal: false to use SQLite’s rollback journal instead. A database that ran in WAL switches back at startup; if another process has it open then, it stays in WAL and the adapter logs a warning. WAL keeps two files beside the database, ending in -wal and -shm; back up, move, or delete all three together.

Example SQLite Adapter with WAL

import { sqliteAdapter } from '@frogbotai/db-sqlite'
export default buildConfig({
  // Your config goes here
  collections: [
    // Collections go here
  ],
  // Configure the SQLite adapter here
  db: sqliteAdapter({
    wal: { synchronous: 'NORMAL' }, // Change a WAL setting
    client: {
      url: process.env.DATABASE_URL,
    },
  }),
})

WAL needs a file database. For an in-memory or remote client.url, WAL stays off; if you pass wal: true with one, the adapter logs a warning. Pass an object to change the WAL settings:

Setting	Type	Default	Description
`journalSizeLimit`	`number`	`67108864` (64 MB)	Maximum size of the WAL file, in bytes, before it’s checkpointed into the main database.
`synchronous`	`'EXTRA' \| 'FULL' \| 'NORMAL' \| 'OFF'`	`'FULL'`	How often data is synced to disk. `EXTRA` is the safest, `NORMAL` trades some safety for speed, and `OFF` is fastest but can lose data.

Start here

Basics

Managing Data

Features

Ecosystem

Deployment

Install

Configure

Collection search

Prerequisites

Storage and indexes

Ranking

Push and migrations

Limits

Options

Concurrent writes

Access to Drizzle

Tables and relations

Prototyping in development mode

Migration workflows

Drizzle schema hooks

beforeSchemaInit

afterSchemaInit

Adding tables through rawTables

D1 Database

D1 collection search

D1 Read Replicas

WAL Mode

​Install

​Configure

​Collection search

​Prerequisites

​Storage and indexes

​Ranking

​Push and migrations

​Limits

​Options

​Concurrent writes

​Access to Drizzle

​Tables and relations

​Prototyping in development mode

​Migration workflows

​Drizzle schema hooks

​beforeSchemaInit

​afterSchemaInit

​Adding tables through rawTables

​D1 Database

​D1 collection search

​D1 Read Replicas

​WAL Mode

Install

Configure

Collection search

Prerequisites

Storage and indexes

Ranking

Push and migrations

Limits

Options

Concurrent writes

Access to Drizzle

Tables and relations

Prototyping in development mode

Migration workflows

Drizzle schema hooks

beforeSchemaInit

afterSchemaInit

Adding tables through rawTables

D1 Database

D1 collection search

D1 Read Replicas

WAL Mode