SQLite, libSQL and Cloudflare D1

Since 3.7.0 the package runs on SQLite with dialect: "sqlite". You build the Drizzle instance with your SQLite driver and pass it as db; the module does not open SQLite connections itself. Reads, writes, filters, relations, upsert and keyset pages work as on PostgreSQL, with RETURNING. Full-text search, row locks and the PostgreSQL-only operators are a 400. Bulk writes need a driver with async transactions, such as libSQL.

How do I connect to SQLite or libSQL?Copy Anchor Link

Install @libsql/client, build the Drizzle instance and pass it as db:

pnpm add @libsql/client

import { Module } from '@nestjs/common';
import { createClient } from '@libsql/client';
import { drizzle } from 'drizzle-orm/libsql';
import { DrizzleCrudModule } from '@avytrix/nestjs-drizzle-crud';
import * as schema from './db/schema';

const client = createClient({
  url: process.env.DATABASE_URL!, // file:local.db, or libsql://... on Turso
  authToken: process.env.DATABASE_AUTH_TOKEN,
});

@Module({
  imports: [
    DrizzleCrudModule.forRoot({
      dialect: 'sqlite',
      db: drizzle(client, { schema }),
    }),
  ],
})
export class AppModule {}

A db you pass in is yours to close. connectionString is not accepted for "sqlite": it is a startup error that tells you to pass db.

How should I declare the SQLite tables?Copy Anchor Link

Use sqliteTable from drizzle-orm/sqlite-core. Two details matter to the service:

• Timestamps and soft delete need date columns. The service writes a JavaScript Date into created_at, updated_at and deleted_at, so declare them as integer('created_at', { mode: 'timestamp_ms' }) (or mode: 'timestamp'). A plain text column does not take a Date.
• Foreign keys must be on. SQLite only enforces references with PRAGMA foreign_keys = ON. libSQL turns it on for its local databases; with another driver, run the pragma when you open the connection. Without it, a missing parent is not an error at all, so there is no 400 or 409 to map.

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

export const posts = sqliteTable('posts', {
  id: integer('id').primaryKey({ autoIncrement: true }),
  title: text('title').notNull(),
  authorId: integer('author_id').notNull().references(() => authors.id),
  createdAt: integer('created_at', { mode: 'timestamp_ms' }),
  updatedAt: integer('updated_at', { mode: 'timestamp_ms' }),
  deletedAt: integer('deleted_at', { mode: 'timestamp_ms' }),
});

What works on SQLite, and what is a 400?Copy Anchor Link

Driver errors map to HTTP the same way as on the other dialects: a UNIQUE or primary key violation is a 409 DuplicateEntityException, a FOREIGN KEY violation is a 400 on a write and a 409 on a delete, and NOT NULL or CHECK is a 400 ValidationFailedException. SQLite does not report the duplicate value, so the message names the column only.

How do I use Cloudflare D1?Copy Anchor Link

Build the instance from the binding with drizzle-orm/d1 and pass it as db, with dialect: 'sqlite'. D1 rejects BEGIN and SAVEPOINT, so everything that needs a transaction throws a TransactionException before it sends a statement:

• massCreate, massUpdate, massUpsert, massSoftDelete, massRestore, massDelete and massForceDelete
• a create with nested relations
• attach, detach and sync

The single-row methods (create, update, upsert, softDelete, restore, delete, every read) do not need one. For several writes that must succeed together on D1, call db.batch([...]) with Drizzle yourself.

Common questionsCopy Anchor Link

Next stepsCopy Anchor Link