SQLite

The SQLite adapter (@atscript/db-sqlite) connects your .as models to SQLite databases. Zero-config, single-file storage, and no server process make SQLite the fastest way to get started with Atscript's DB layer. Best suited for development, testing, and lightweight production workloads.

Uses better-sqlite3 by default, but any driver implementing the TSqliteDriver interface works — including Node.js built-in node:sqlite.

Installation

pnpm add @atscript/db-sqlite better-sqlite3

better-sqlite3 is an optional peer dependency. You can substitute any SQLite driver that implements the TSqliteDriver interface.

Setup

Create a driver, wrap it in an adapter, and pass the adapter factory to DbSpace:

import { DbSpace } from "@atscript/db";
import { SqliteAdapter, BetterSqlite3Driver } from "@atscript/db-sqlite";
import { User } from "./user.as.js";

// 1. Create driver
const driver = new BetterSqlite3Driver("./myapp.db");

// 2. Create DbSpace with adapter factory
const db = new DbSpace(() => new SqliteAdapter(driver));

// 3. Get typed tables
const users = db.getTable(User);

Or use the convenience shorthand:

import { createAdapter } from "@atscript/db-sqlite";
import { User } from "./user.as.js";

const db = createAdapter("./myapp.db");
const users = db.getTable(User);

Once you have a table, run npx asc db sync to create or update the database schema, then use users.insertOne(...), users.findMany(...), etc. See CRUD Operations for the full API.

Adapter-Specific Annotations

SQLite has no adapter-specific annotations. All generic @db.* annotations work as documented in the Annotations Reference. There is no @db.sqlite.* namespace.

Type Mapping

Atscript types map to SQLite column types as follows:

Atscript Type	SQLite Type	Notes
string	TEXT
number	REAL	INTEGER for primary keys (aliases rowid)
decimal	REAL	Runtime value is string; coerced on read
boolean	INTEGER	Stored as 0 / 1
arrays	TEXT	JSON-serialized
nested objects	flattened columns	parent__child naming convention
T | null	the column of T	nullable; see Nullable and Union Fields
@db.json fields	TEXT	JSON-serialized
db.geoPoint	TEXT	JSON [lng, lat]; haversine-based geo search

Features

Nested Objects

Nested object fields are automatically flattened into __-separated columns. You query with dot-notation and the adapter translates:

@db.table 'contacts'
export interface Contact {
    @meta.id
    id: number

    name: string

    // Becomes columns: address__city, address__zip
    address: {
        city: string
        zip: string
    }
}

// Insert — pass the nested structure naturally
await contacts.insertOne({
  id: 1,
  name: "Alice",
  address: { city: "Portland", zip: "97201" },
});

// Query — use dot-notation for nested fields
const results = await contacts.findMany({
  filter: { "address.city": "Portland" },
  controls: { $sort: { "address.zip": 1 } },
});

// Read — nested objects are reconstructed automatically
// results[0].address -> { city: 'Portland', zip: '97201' }

To store an entire nested object as a single JSON column instead of flattening, annotate it with @db.json. Arrays are always stored as JSON.

JSON-stored values are opaque to queries on SQLite: a descendant path (preferences.theme, tags.0) in a filter, $sort, $select, $groupBy or an aggregate field is rejected with INVALID_QUERY (HTTP 400 over moost-db) before any SQL is built (since 0.1.128); select the parent column and read the value client-side, or expose the leaf as a typed view field. /meta.fields lists JSON and array columns as filterable: false, sortable: false, filterOps: ["$exists"] and does not list their descendants: the column itself accepts only an $exists entry (IS [NOT] NULL, since 0.1.132).

Foreign Key Enforcement

SQLite foreign keys are enforced natively. The adapter enables PRAGMA foreign_keys = ON at connection time, so referential integrity is always active. Cascade and set-null behaviors are controlled via @db.rel.onDelete and @db.rel.onUpdate — see Referential Actions.

Fulltext Search (FTS5)

SQLite supports fulltext search through FTS5 virtual tables. When you annotate fields with @db.index.fulltext, the adapter automatically creates FTS5 virtual tables with sync triggers that keep the index up to date on inserts, updates, and deletes.

@db.table 'articles'
export interface Article {
    @meta.id
    id: number

    @db.index.fulltext
    title: string

    @db.index.fulltext
    body: string
}

The adapter creates a companion articles__fts__<indexName> virtual table and triggers for automatic synchronization. Use the search() method to query:

const results = await articles.search("database optimization", {});

Without a $sort, results are ordered by FTS5 bm25() (with the index's per-field weights), then the primary key (since 0.1.165; before, in rowid order). With $rank: false (since 0.1.175) no bm25() is computed and the order is the primary key — see Unranked search.

Raw SQL against FTS5 tables: bind rowids as integers

better-sqlite3 binds a JavaScript integer as REAL, and FTS5 silently ignores a rowid = ? constraint whose value is REAL — the query returns every match. atscript-db's own queries never bind a rowid (they join on rowid), but in your own SQL write rowid = CAST(? AS INTEGER) or bind a BigInt.

Search term syntax

The $search text is quoted before it reaches FTS5's MATCH, so no input can raise an FTS5 syntax error (-2946, a AND, title:x, ( are searched as plain text). Words are AND-ed (OR-ed under @db.index.fulltext.match "any"); a "quoted phrase" stays a phrase; a trailing * is a prefix match (quok*). FTS5 operators (AND, OR, NOT, NEAR, column filters) are not available through $search.

Language (since 0.1.168): @db.index.fulltext.language "english" builds the FTS5 table with tokenize='porter unicode61', so run finds running. FTS5 ships no stemmer for other languages: with any other language words are indexed unstemmed (FTS5's default unicode61, which folds case for every script) and schema sync logs a warning — unless ftsTokenizers gives that language a tokenizer. Changing the language recreates the FTS5 table. See Language and term matching.

Tokenizer per language

Since 0.1.175. The ftsTokenizers adapter option picks the FTS5 tokenizer for a @db.index.fulltext.language. A tokenizer FTS5 does not ship, such as a Snowball stemmer, comes from a SQLite extension your app loads through the driver's loadExtensions:

import { createAdapter } from "@atscript/db-sqlite";

const db = createAdapter("./data.db", {
  loadExtensions: ["/path/to/fts5stemmer"], // the extension that registers the tokenizer
  ftsTokenizers: { russian: "snowball russian unicode61" },
});
// or: new SqliteAdapter(driver, { ftsTokenizers: { russian: "snowball russian unicode61" } })

• The value is the FTS5 tokenize option: words of letters, digits and _, separated by spaces ("trigram", "unicode61 remove_diacritics 2", "snowball russian unicode61"). Anything else, including quoted arguments, is rejected when the adapter is created. A key that is not a @db.index.fulltext.language value is rejected too.
• It replaces the default for that language: english can be given another tokenizer too. Languages without an entry keep the default. An index without @db.index.fulltext.language is not english on SQLite (it has no stemmer), so no entry applies to it: it keeps unicode61.
• A configured language gets no "no stemmer" warning at sync.
• The tokenizer is part of the schema: changing an entry recreates the FTS5 tables of that language on the next schema sync, as a language change does.
• If SQLite does not know the tokenizer, schema sync fails with an error that names the tokenizer and points at loadExtensions, before it drops an existing FTS5 table.
• Every connection that writes the table must load the extension too: the triggers that keep the FTS5 table current run the tokenizer on each insert, update and delete.
• Search terms go through the same tokenizer as the indexed text, so a stemming tokenizer matches other forms of a word.

Prefix indexes

Since 0.1.175. A search term with a trailing * ($search: "quo*") is a prefix search. It works without any setting, but on a large table FTS5 scans its term list for every prefix. The ftsPrefix adapter option builds FTS5 prefix indexes of the lengths you list, so prefix searches of those lengths read a prebuilt index, which suits search-as-you-type:

const db = createAdapter("./data.db", { ftsPrefix: [2, 3] });
// or: new SqliteAdapter(driver, { ftsPrefix: [2, 3] })

• The lengths are in characters: [2, 3] serves qu* and quo* from the prefix index. A longer prefix still works, through the main index.
• It applies to every FTS5 table of the adapter, so to every fulltext index of the space. Each length makes the index larger. To give only some tables prefix indexes, put them in a space of their own.
• Each value is an integer from 1 to 999, with at most 31 values. Order and duplicates do not matter. Anything else is rejected when the adapter is created.
• The setting is part of the schema: adding, changing or removing it recreates the FTS5 tables on the next schema sync. Without it (or with []) the FTS5 tables are built as before, and existing tables are left alone.

Integer members are left out of the FTS5 table (SQLite stores integers in REAL columns, so FTS5 would index 29461277.0 and a term 0 would match every row). They are matched by exact number instead: (rowid IN (SELECT rowid FROM <fts> WHERE <fts> MATCH ?) OR "ref_no" = ?), which SQLite serves from the FTS5 lookup and the member's btree. A text-only search keeps the JOIN form. An index of integer members only creates no FTS5 table. Schema sync compares an existing FTS5 table's columns with the index's text members and recreates it on drift (for example, a table built before integers were excluded). See Text Search — Integer fields.

$regex on an integer column compares the number's decimal text (CAST(CAST(col AS INTEGER) AS TEXT) LIKE …; the inner cast is needed because the column stores a REAL).

Vector Search

SQLite supports vector similarity search through the optional sqlite-vec extension. Vector search is opt-in — you must install the peer dependency and tell the driver to load the extension.

Install and enable

pnpm add sqlite-vec

import { BetterSqlite3Driver, SqliteAdapter } from "@atscript/db-sqlite";
import { DbSpace } from "@atscript/db";

const driver = new BetterSqlite3Driver("./data.db", { vector: true });
const db = new DbSpace(() => new SqliteAdapter(driver));

When using a custom driver, set hasVectorExt = true after the extension is loaded so the adapter skips its capability probe. If the flag is omitted, the adapter runs SELECT vec_version() once and caches the result.

Declaring a vector field

Use the portable @db.search.vector annotation:

@db.table "documents"
export interface Document {
  @meta.id
  id: number

  title: string

  @db.search.vector 1536, "cosine"
  embedding: number[]
}

dimensions is any positive integer matching your embedding model (Vector Search). Similarity options: cosine (default), euclidean, dotProduct.

Storage

• With sqlite-vec loaded — the adapter creates a companion vec0 virtual table per index (<table>__vec__<indexName>) with sync triggers, and routes vectorSearch() through KNN queries on that shadow table.
• Without the extension — vector fields are stored as JSON TEXT on the main table. The data is preserved, but vectorSearch() throws because no indexed similarity search is available.

Querying

const results = await documents.vectorSearch(queryEmbedding, {
  filter: { status: "published" },
  controls: { $limit: 10, $threshold: 0.8 },
});

$threshold is a normalized similarity score (0–1) matching MongoDB Atlas semantics; the adapter converts it to the appropriate distance for the chosen metric.

TIP

sqlite-vec is a native module — make sure your build target matches the platform you deploy to.

Calendar buckets

Calendar buckets (since 0.1.132) run through a deterministic SQL function, atscript_bucket, that the adapter registers on the connection when it is constructed. It computes labels with the Node.js runtime's time zone data.

Registration goes through the driver's optional registerFunction(name, fn, opts) hook. BetterSqlite3Driver implements it. A custom driver without it reports no bucket units: /meta omits bucketUnits, and a bucket query fails with BUCKET_NOT_SUPPORTED (HTTP 400). Don't register a function of your own named atscript_bucket on a shared connection.

Aggregate expressions

Arithmetic and first / last run in SQL. first / last need window functions, so SQLite 3.25+ (the bundled better-sqlite3 is newer). A result past the double range is ±Infinity.

Filters

All standard filter operators are supported ($eq, $ne, $gt, $gte, $lt, $lte, $in, $nin, $and, $or, $not). Regex patterns are converted to SQL LIKE expressions:

Regex Pattern	SQL LIKE	Matches
^abc	abc%	Starts with "abc"
end$	%end	Ends with "end"
^exact$	exact	Exact match
mid	%mid%	Contains "mid"

// Pattern matching
await users.findMany({
  filter: { name: { $regex: "^Ali" } },
  controls: {},
});
// -> WHERE name LIKE 'Ali%'

Relational predicates

$some / $none filters (since 0.1.147) render as correlated EXISTS (SELECT 1 FROM … WHERE …) / NOT EXISTS subqueries, in reads (FTS and vector search included) and in mutation filters.

SQLite does not index foreign-key columns by itself. Add a @db.index.plain on the foreign-key column of the @db.rel.from side and on both foreign-key columns of a @db.rel.via junction — the same advice as for PostgreSQL — or each predicate scans the related table per candidate row.

Table Recreation

SQLite does not support ALTER COLUMN for type changes. When schema sync detects a column type change, the adapter performs a safe table recreation:

1. Creates a new table with the updated schema
2. Copies data from the old table (with COALESCE for new NOT NULL columns)
3. Drops the old table and renames the new one into place

Since 0.1.138 these steps run in one transaction: a step that fails rolls them all back, so the table keeps its rows and its full-text and vector indexes.

Copy the database before a migration

VACUUM INTO writes a consistent copy of a live database to a new file, on every SQLite driver and without stopping writers. Take one before a schema sync that rebuilds tables or runs data steps, so you can go back:

driver.exec(`VACUUM INTO '/backups/app-${Date.now()}.db'`);
await syncSchema(db, types, { dataSteps });

The target file must not exist yet. There is no adapter API for it — run it on the driver you created. ::: Before, a failure left a <table>__tmp_<ts> table behind. Foreign key checks are temporarily disabled during recreation to avoid constraint errors on intermediate states. To opt a table into this behavior, annotate it with @db.sync.method 'recreate'. See Schema Sync for details.

Read-Only Database Files

An app can serve a prebuilt SQLite file next to its own database, for example a reference catalog or a knowledge pack with fulltext search that ships with each release. Open the file in a second DbSpace over a read-only driver. BetterSqlite3Driver passes better-sqlite3's own options through, so readonly and fileMustExist work as they do there:

@db.table 'pack_articles'
@db.space 'pack'
@db.index.fulltext.language "english"
export interface PackArticle {
    @meta.id
    id: number

    @db.index.fulltext 'ft_text'
    title: string

    @db.index.fulltext 'ft_text'
    body: string
}

import { DbSpace } from "@atscript/db";
import { syncSchema } from "@atscript/db/sync";
import { BetterSqlite3Driver, SqliteAdapter, createAdapter } from "@atscript/db-sqlite";
import { modelsBySpace } from "./atscript.models";

// The app's own database: synced as usual
const db = createAdapter("./app.db");
await syncSchema(db, modelsBySpace.default);

// The prebuilt file: read-only, never synced
const packDriver = new BetterSqlite3Driver("./pack.db", { readonly: true, fileMustExist: true });
const pack = new DbSpace(() => new SqliteAdapter(packDriver, { ftsPrefix: [2, 3] }), {
  onClose: () => packDriver.close(),
});

const articles = pack.getTable(PackArticle);
await articles.search("quo*", {});

• Bind the file's models with @db.space, so the model manifest keeps them out of the app's own list and moost-db controllers find the space you register with provideDbSpace(pack, "pack").
• Never run schema sync against the read-only space. Sync writes its own bookkeeping rows and would fail.
• Build the file with atscript-db: a build script that opens it with createAdapter(path, options), runs syncSchema for the pack models and inserts the rows. The FTS5 tables and the triggers that keep them current then match what the adapter queries. Use the same models and the same ftsTokenizers / ftsPrefix when you build the file and when you serve it. A hand-written FTS5 schema is not supported.
• Reads, search() and aggregates work as on any space. Writes fail with SQLite's attempt to write a readonly database error.
• To update the data, ship a new file and reopen the space on it.

In-Memory Databases

Pass ':memory:' as the path to create an in-memory database — useful for tests and ephemeral data:

const driver = new BetterSqlite3Driver(":memory:");
const db = new DbSpace(() => new SqliteAdapter(driver));

In-memory databases are lost when the process exits or the driver is closed.

Custom Drivers

The SqliteAdapter accepts any object implementing TSqliteDriver. This lets you use node:sqlite, sql.js, or any other SQLite binding:

interface TSqliteDriver {
  run(sql: string, params?: unknown[]): { changes: number; lastInsertRowid: number | bigint };
  all<T>(sql: string, params?: unknown[]): T[];
  get<T>(sql: string, params?: unknown[]): T | null;
  exec(sql: string): void;
  close(): void;
  /** Optional — registers a scalar SQL function; needed for calendar buckets (since 0.1.132). */
  registerFunction?(
    name: string,
    fn: (...args: any[]) => unknown,
    opts?: { deterministic?: boolean },
  ): void;
  /** Optional — set to `true` if the driver has the `sqlite-vec` extension loaded. */
  readonly hasVectorExt?: boolean;
}

When hasVectorExt is omitted, the adapter probes the connection by running SELECT vec_version() on first vector access. Set the flag explicitly to skip the probe.

Example using Node.js built-in node:sqlite:

import { SqliteAdapter } from "@atscript/db-sqlite";
import { DatabaseSync } from "node:sqlite";

const nodeDb = new DatabaseSync(":memory:");
const driver = {
  run(sql, params) {
    const stmt = nodeDb.prepare(sql);
    return stmt.run(...(params ?? []));
  },
  all(sql, params) {
    const stmt = nodeDb.prepare(sql);
    return stmt.all(...(params ?? []));
  },
  get(sql, params) {
    const stmt = nodeDb.prepare(sql);
    return stmt.get(...(params ?? [])) ?? null;
  },
  exec(sql) {
    nodeDb.exec(sql);
  },
  close() {
    nodeDb.close();
  },
  // Optional — enables calendar buckets (Node.js ≥ 22.13)
  registerFunction(name, fn, opts) {
    nodeDb.function(name, { deterministic: opts?.deterministic ?? false }, fn);
  },
};

const adapter = new SqliteAdapter(driver);

BetterSqlite3Driver

The built-in BetterSqlite3Driver accepts either a file path (string) or a pre-created better-sqlite3 Database instance:

// From file path
const driver = new BetterSqlite3Driver("./data.db");

// From existing instance
import Database from "better-sqlite3";
const instance = new Database("./data.db", { verbose: console.log });
const driver = new BetterSqlite3Driver(instance);

Driver options

The second constructor argument accepts a few extra options on top of the standard better-sqlite3 Database options:

new BetterSqlite3Driver("./data.db", {
  vector: true, // load the optional `sqlite-vec` extension
  loadExtensions: ["/path/to/ext1.so"], // pass each path to `Database.loadExtension`
  statementCacheSize: 256, // prepared statements kept for reuse (LRU); 0 disables
  // any other `better-sqlite3` Database options are forwarded
});

Since 0.1.151 the driver keeps the statements it prepares in a bounded least-recently-used cache keyed by SQL text (default 256 per connection), so a repeated query or insert is not re-prepared. Cached statements stay valid across schema changes (SQLite re-prepares them on their next run); PRAGMA statements are never cached.

The driver uses createRequire internally, so better-sqlite3 (and sqlite-vec) remain optional dependencies — they are only loaded when BetterSqlite3Driver is instantiated with the corresponding option.

WAL mode and pragma tuning

The adapter sets PRAGMA foreign_keys = ON at connection time (required for FK enforcement) and toggles foreign_keys / legacy_alter_table during recreate-mode schema sync. It does not override journaling, synchronous, or busy-timeout pragmas — configure those on the underlying better-sqlite3 Database before wrapping it:

import Database from "better-sqlite3";
import { BetterSqlite3Driver, SqliteAdapter } from "@atscript/db-sqlite";

const sqlite = new Database("./data.db");
sqlite.pragma("journal_mode = WAL");
sqlite.pragma("synchronous = NORMAL");
sqlite.pragma("busy_timeout = 5000");

const driver = new BetterSqlite3Driver(sqlite);
const adapter = new SqliteAdapter(driver);

See the better-sqlite3 README for the full list of pragmas.

Concurrency and transactions

A SQLite driver is one synchronous connection, shared by every table of the space. Two async contexts cannot each own a transaction on it, and — worse — a statement from another request that runs while a transaction is open executes inside that transaction (it sees uncommitted rows and is undone by a rollback). Since 0.1.128 the adapter serialises the connection through a per-driver FIFO gate:

• withTransaction takes the gate, runs BEGIN IMMEDIATE (the write lock is taken up front, so a concurrent process waits on busy_timeout instead of failing mid-transaction), and releases the gate on COMMIT / ROLLBACK. Concurrent withTransaction calls queue and run one after the other — no more cannot start a transaction within a transaction.
• A COMMIT that fails with SQLITE_BUSY (another process still holds a lock past busy_timeout) throws the retryable DbError("LOCK_TIMEOUT"), with the driver error as cause (since 0.1.175). The gate stays held until the transaction is rolled back, so no queued statement runs inside it; retry the whole transaction.
• Every read and write outside a transaction waits until no transaction is open, then runs autocommit. Inside your own transaction, statements run immediately (nested calls join as before).
• The gate is keyed by driver instance: construct one driver per database (two BetterSqlite3Driver wrappers around one Database would get two gates).

const db = createAdapter("./data.db", {
  transactionWaitTimeoutMs: 10_000, // waiters give up after 10 s (default: wait forever)
  transactionWaitWarnMs: 2_000, // warn through the adapter logger after 2 s (default 5000; 0 = off)
});
// or: new SqliteAdapter(driver, { transactionWaitTimeoutMs, transactionWaitWarnMs })

The same options object takes viewJoinPruning: false (since 0.1.153) to read managed views as stored instead of skipping their unused joins, and ftsTokenizers (since 0.1.175) to pick the FTS5 tokenizer per language.

A waiter that exceeds transactionWaitTimeoutMs rejects with DbError("TX_WAIT_TIMEOUT"), which @atscript/moost-db maps to 503. The timeout bounds the waiter's total wait, however many transactions run ahead of it. The default is unbounded (like the mysql2 / pg pool defaults): a slow bulk import then delays concurrent requests instead of failing them. transactionWaitTimeoutMs: 0 is a fail-fast mode — a statement or transaction that finds the connection busy rejects immediately instead of queueing.

DO / DON'T

• Don't await external I/O inside withTransaction (or inside a moost-db guardWrite) — a self-call to your own API from inside a transaction waits for itself, forever by default. Set transactionWaitTimeoutMs when you cannot rule that out.
• Schema operations take the same connection gate (since 0.1.128): ensureTable, syncColumns, recreateTable, dropTablesByName and the other schema methods hold the connection exclusively without opening a transaction — they wait for an open transaction to finish, requests wait for them, and their PRAGMA foreign_keys toggles stay effective because the DDL never lands inside a request's transaction. The gate protects each step, not a whole syncSchema run: a multi-step sync releases the connection between steps, so run schema sync before serving traffic.
• Do keep journal_mode = WAL + busy_timeout for multi-process deployments (see WAL mode and pragma tuning); the gate only covers one process, and BEGIN IMMEDIATE blocks the event loop for up to busy_timeout under cross-process contention.
• A transaction on another adapter family (MySQL, MongoDB) is not a SQLite transaction: SQLite statements issued inside it still queue behind SQLite's own transactions and run autocommit — see Transactions.
• Locking reads and isolation (since 0.1.162): { lock } on findOne / findMany / findById is accepted and does nothing extra inside withTransaction, because BEGIN IMMEDIATE already holds the database write lock. Every withTransaction(fn, { isolation }) level is satisfied, since these transactions are serializable. See Locking reads.

Views

Managed views are created with CREATE VIEW IF NOT EXISTS.

Since 0.1.147 a first-row join renders as a correlated scalar subquery in the join's ON (no window functions):

LEFT JOIN "issues" AS "OldestOpenIssue"
  ON "OldestOpenIssue"."id" = (SELECT "OldestOpenIssue"."id" FROM "issues" AS "OldestOpenIssue"
      WHERE <condition> ORDER BY "OldestOpenIssue"."raised_at" ASC, "OldestOpenIssue"."id" ASC LIMIT 1)

It runs once per entry row; an index on the condition's join key plus the order keys ((ticket_id, raised_at, id)) keeps each lookup an index scan. SQLite (and MySQL) sort NULL first in ascending order natively.

Computed columns cast every field and literal operand with CAST(x AS REAL) and divide with NULLIF(divisor, 0), so 7 / 2 = 3.5 and division by zero is NULL.

Derived Columns

A @db.column.derived field (since 0.1.141) is a GENERATED ALWAYS AS (…) VIRTUAL column: json_type() guards the declared type and json_extract() reads the leaf, so a missing key, JSON null or a value of another type is NULL. Nothing is stored unless the column is indexed (SQLite materializes indexed virtual columns). Booleans are 1 / 0 in the column and read back as true / false. @db.column.collate applies after the generated clause. Adding one to a populated table is a plain ADD COLUMN; a changed extraction is a DROP COLUMN + ADD COLUMN (managed indexes dropped first) — no table recreation. Introspection uses PRAGMA table_xinfo (hidden = 2 virtual, 3 stored); a recreateTable copies every column but the generated ones, which the new table recomputes.

Conflict-ignoring inserts

insertMany(rows, { onConflict: "ignore" }) (CRUD) runs one INSERT … ON CONFLICT DO NOTHING per row inside the call's transaction — in-process, so the extra statements are cheap. DO NOTHING without a target covers the primary key and every unique index; changes === 0 marks a skipped row. NOT NULL, CHECK and foreign-key errors still raise (never INSERT OR IGNORE, which would swallow them). Requires SQLite 3.24+. lockConflicts (CRUD) is accepted and has no effect: the database-level write lock already serializes writers.

Limitations

• No ALTER COLUMN type changes — column type modifications require full table recreation. Use @db.sync.method 'recreate' to opt in. See Schema Sync for details.
• FTS5-based fulltext search — fulltext indexes are managed automatically, but FTS5 uses its own match syntax rather than standard SQL pattern matching.
• No database schemas — the @db.schema annotation is ignored (SQLite has no schema namespaces).
• Vector search is opt-in via sqlite-vec — see Vector Search. Without the extension, @db.search.vector fields are stored as JSON TEXT and vectorSearch() throws.
• No native boolean type — booleans are stored as INTEGER (0/1).
• No native array/JSON operations — array patch operators ($push, $pull) use generic read-modify-write instead of native operations.
• Synchronous driver — both better-sqlite3 and node:sqlite are synchronous; the adapter wraps calls in promises for the async BaseDbAdapter contract.
• No native UUID generation — UUIDs must be generated application-side.

Utilities

The package exports buildWhere for constructing SQL WHERE clauses from filter objects — useful when writing custom queries outside the standard CRUD flow:

import { buildWhere } from "@atscript/db-sqlite";

const { sql, params } = buildWhere({ status: "active", age: { $gte: 18 } });
// sql -> 'WHERE "status" = ? AND "age" >= ?'
// params -> ['active', 18]

Next Steps

• PostgreSQL — full-featured adapter with pgvector and transactional DDL
• MongoDB — document-oriented adapter with Atlas Search
• Adapter Overview — feature comparison across all adapters