or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

cli-commands.mdconfiguration.mdindex.mdmysql-api.mdpostgresql-api.mdsinglestore-api.mdsqlite-api.md
tile.json

sqlite-api.mddocs/

SQLite Programmatic API

Programmatic API for SQLite database operations including snapshot generation, migration creation, schema pushing, and Drizzle Studio server management. Supports standard SQLite, Turso (LibSQL), Cloudflare D1, Expo SQLite, and Durable Objects.

Capabilities

Generate SQLite Snapshot

Generates a SQLite schema snapshot from TypeScript schema imports.

/**
 * Generate SQLite snapshot from schema imports
 * @param imports - Record of schema module exports
 * @param prevId - Previous snapshot ID (optional)
 * @param casing - Serialization casing (optional)
 * @returns SQLite schema snapshot
 */
async function generateSQLiteDrizzleJson(
  imports: Record<string, unknown>,
  prevId?: string,
  casing?: 'camelCase' | 'snake_case'
): Promise<SQLiteSchemaKit>;

Usage Example:

import { generateSQLiteDrizzleJson } from 'drizzle-kit/api';
import * as schema from './schema';

// Generate snapshot
const snapshot = await generateSQLiteDrizzleJson(schema);

// With previous ID (for migration chain)
const snapshot2 = await generateSQLiteDrizzleJson(schema, snapshot.id);

// With snake_case naming
const snakeCase = await generateSQLiteDrizzleJson(schema, undefined, 'snake_case');

Generate SQLite Migration

Generates SQL migration statements by comparing two SQLite snapshots.

/**
 * Generate SQL migration from two SQLite snapshots
 * @param prev - Previous snapshot
 * @param cur - Current snapshot
 * @returns Array of SQL statements
 */
function generateSQLiteMigration(
  prev: DrizzleSQLiteSnapshotJSON,
  cur: DrizzleSQLiteSnapshotJSON
): Promise<string[]>;

Usage Example:

import {
  generateSQLiteDrizzleJson,
  generateSQLiteMigration,
} from 'drizzle-kit/api';
import * as oldSchema from './old-schema';
import * as newSchema from './new-schema';

// Generate snapshots
const prevSnapshot = generateSQLiteDrizzleJson(oldSchema);
const curSnapshot = generateSQLiteDrizzleJson(newSchema);

// Generate migration SQL
const sqlStatements = await generateSQLiteMigration(prevSnapshot, curSnapshot);

// Execute or save migration
console.log(sqlStatements.join('\n'));

Push SQLite Schema

Pushes schema changes directly to a SQLite database.

/**
 * Push schema changes to SQLite database
 * @param imports - Record of schema module exports
 * @param drizzleInstance - Drizzle SQLite/LibSQL database instance
 * @returns Push result with warnings and apply function
 */
function pushSQLiteSchema(
  imports: Record<string, unknown>,
  drizzleInstance: LibSQLDatabase<any>
): Promise<PushResult>;

interface PushResult {
  hasDataLoss: boolean;
  warnings: string[];
  statementsToExecute: string[];
  apply: () => Promise<void>;
}

Usage Example:

import { pushSQLiteSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from './schema';

// Create SQLite connection
const sqlite = new Database('./local.db');
const db = drizzle(sqlite);

// Push schema
const result = await pushSQLiteSchema(schema, db);

// Check for warnings
if (result.hasDataLoss) {
  console.warn('Warning: Data loss may occur!');
  result.warnings.forEach(w => console.warn(w));
}

// Review statements
console.log('Statements to execute:');
result.statementsToExecute.forEach(s => console.log(s));

// Apply changes
if (confirm('Apply changes?')) {
  await result.apply();
  console.log('Schema pushed successfully');
}

// Clean up
sqlite.close();

Start SQLite Studio Server

Starts Drizzle Studio server for SQLite database.

/**
 * Start Drizzle Studio server for SQLite
 * @param imports - Record of schema module exports
 * @param credentials - SQLite connection credentials
 * @param options - Server options (optional)
 */
function startStudioSQLiteServer(
  imports: Record<string, unknown>,
  credentials: SqliteCredentials,
  options?: {
    host?: string;
    port?: number;
    casing?: 'camelCase' | 'snake_case';
  }
): Promise<void>;

Usage Example:

import { startStudioSQLiteServer } from 'drizzle-kit/api';
import * as schema from './schema';

// Start Studio with local database
await startStudioSQLiteServer(
  schema,
  {
    url: './local.db',
  }
);

// Start with custom options
await startStudioSQLiteServer(
  schema,
  {
    url: ':memory:',  // In-memory database
  },
  {
    host: '0.0.0.0',
    port: 4983,
    casing: 'camelCase',
  }
);

console.log('Studio started at http://localhost:4983');

// Cloudflare D1 (via HTTP API)
const d1Server = await startStudioSQLiteServer(
  schema,
  {
    driver: 'd1-http',
    accountId: process.env.CF_ACCOUNT_ID!,
    databaseId: process.env.CF_DATABASE_ID!,
    token: process.env.CF_API_TOKEN!,
  }
);

Types

DrizzleSQLiteSnapshotJSON

SQLite schema snapshot type (alias for SQLiteSchemaKit).

type DrizzleSQLiteSnapshotJSON = SQLiteSchemaKit;

interface SQLiteSchemaKit {
  id: string;
  prevId: string;
  version: string;
  dialect: 'sqlite';
  tables: Record<string, Table>;
  views: Record<string, View>;
  _meta: {
    tables: Record<string, string>;
    columns: Record<string, string>;
  };
}

Credentials

SQLite connection credentials with multiple variants.

type SqliteCredentials =
  // File-based SQLite
  | {
      url: string;                // File path or ':memory:'
    }
  // Cloudflare D1 HTTP API
  | {
      driver: 'd1-http';
      accountId: string;          // Cloudflare account ID
      databaseId: string;         // D1 database ID
      token: string;              // API token
    };

Schema Components

interface Table {
  name: string;
  columns: Record<string, Column>;
  indexes: Record<string, Index>;
  foreignKeys: Record<string, ForeignKey>;
  primaryKeys: Record<string, PrimaryKey>;
  uniqueConstraints: Record<string, UniqueConstraint>;
  checkConstraints: Record<string, CheckConstraint>;
}

interface Column {
  name: string;
  type: string;
  primaryKey: boolean;
  notNull: boolean;
  autoincrement: boolean;
  default?: string;
  generated?: {
    as: string;
    type: 'stored' | 'virtual';
  };
}

interface Index {
  name: string;
  columns: string[];
  isUnique: boolean;
  where?: string;
}

interface ForeignKey {
  name: string;
  tableFrom: string;
  tableTo: string;
  columnsFrom: string[];
  columnsTo: string[];
  onDelete?: 'cascade' | 'set null' | 'set default' | 'restrict' | 'no action';
  onUpdate?: 'cascade' | 'set null' | 'set default' | 'restrict' | 'no action';
}

interface PrimaryKey {
  name: string;
  columns: string[];
}

interface UniqueConstraint {
  name: string;
  columns: string[];
}

interface CheckConstraint {
  name: string;
  value: string;
}

interface View {
  name: string;
  definition: string;
}

Supported Features

SQLite API supports:

  • Tables: All SQLite column types, AUTOINCREMENT
  • Indexes: B-tree indexes (only index type in SQLite)
  • Partial Indexes: Indexes with WHERE clauses
  • Constraints: Foreign keys, primary keys, unique, check constraints
  • Views: Regular views
  • Generated Columns: Virtual and stored generated columns

Note: SQLite does not support:

  • Enums (use TEXT with CHECK constraint)
  • Schemas/namespaces (single database)
  • Sequences (use AUTOINCREMENT)
  • Materialized views
  • Multiple index types (only B-tree)

Common Patterns

Better SQLite3 Integration

import { pushSQLiteSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from './schema';

// Create database connection
const sqlite = new Database('./production.db');
const db = drizzle(sqlite);

// Push schema
const result = await pushSQLiteSchema(schema, db);

if (!result.hasDataLoss) {
  await result.apply();
  console.log('Schema updated successfully');
}

sqlite.close();

Turso (LibSQL) Integration

import { pushSQLiteSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/libsql';
import { createClient } from '@libsql/client';
import * as schema from './schema';

// Create Turso client
const client = createClient({
  url: process.env.TURSO_DATABASE_URL!,
  authToken: process.env.TURSO_AUTH_TOKEN!,
});

const db = drizzle(client);

// Push schema to Turso
const result = await pushSQLiteSchema(schema, db);

if (result.statementsToExecute.length > 0) {
  console.log('Applying changes to Turso database');
  await result.apply();
}

Cloudflare D1 Integration

import { pushSQLiteSchema, startStudioSQLiteServer } from 'drizzle-kit/api';
import * as schema from './schema';

// Note: D1 push requires HTTP API credentials
// Studio can work with D1 via HTTP API

const server = await startStudioSQLiteServer(
  schema,
  {
    driver: 'd1-http',
    accountId: process.env.CLOUDFLARE_ACCOUNT_ID!,
    databaseId: process.env.D1_DATABASE_ID!,
    token: process.env.CLOUDFLARE_API_TOKEN!,
  }
);

console.log('Studio started for D1 database');

Expo SQLite Integration

import { pushSQLiteSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/expo-sqlite';
import { openDatabaseSync } from 'expo-sqlite/next';
import * as schema from './schema';

// Open Expo database
const expoDb = openDatabaseSync('mydb.db');
const db = drizzle(expoDb);

// Push schema
const result = await pushSQLiteSchema(schema, db);

if (!result.hasDataLoss) {
  await result.apply();
  console.log('Expo database schema updated');
}

In-Memory Database

import { pushSQLiteSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from './schema';

// Create in-memory database
const sqlite = new Database(':memory:');
const db = drizzle(sqlite);

// Push schema to create tables
const result = await pushSQLiteSchema(schema, db);
await result.apply();

// Database ready for use
// Note: Data lost when sqlite.close() is called

Custom Migration Tool

import {
  generateSQLiteDrizzleJson,
  generateSQLiteMigration,
} from 'drizzle-kit/api';
import { writeFileSync, readFileSync } from 'fs';
import * as schema from './schema';

async function createMigration(name: string) {
  // Load previous snapshot
  const prevSnapshot = JSON.parse(
    readFileSync('./snapshots/latest.json', 'utf8')
  );

  // Generate current snapshot
  const curSnapshot = generateSQLiteDrizzleJson(schema);

  // Generate migration
  const sql = await generateSQLiteMigration(prevSnapshot, curSnapshot);

  // Save migration file with breakpoints
  const timestamp = Date.now();
  const migrationContent = sql.join('\n--> statement-breakpoint\n');
  writeFileSync(
    `./migrations/${timestamp}_${name}.sql`,
    migrationContent
  );

  // Update latest snapshot
  writeFileSync(
    './snapshots/latest.json',
    JSON.stringify(curSnapshot, null, 2)
  );

  console.log(`Migration created: ${timestamp}_${name}.sql`);
}

Schema Validation

import { pushSQLiteSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import * as schema from './schema';

async function validateSchema(dbPath: string): Promise<boolean> {
  const sqlite = new Database(dbPath);
  const db = drizzle(sqlite);

  // Push schema in dry-run mode
  const result = await pushSQLiteSchema(schema, db);

  // Check if schema is in sync
  if (result.statementsToExecute.length === 0) {
    console.log('✓ Schema is in sync');
    sqlite.close();
    return true;
  }

  // Report differences
  console.log('✗ Schema is out of sync');
  console.log('Required changes:');
  result.statementsToExecute.forEach(stmt => console.log(`  ${stmt}`));

  sqlite.close();
  return false;
}

Migration Breakpoints

SQLite requires statement breakpoints because it doesn't support transactional DDL for all operations:

import { generateSQLiteMigration } from 'drizzle-kit/api';

// Generate migration
const statements = await generateSQLiteMigration(prev, cur);

// Add breakpoints for sequential execution
const migration = statements.join('\n--> statement-breakpoint\n');

// Each statement will be executed separately
// This is especially important for ALTER TABLE operations

Backup Before Push

import { pushSQLiteSchema } from 'drizzle-kit/api';
import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';
import { copyFileSync } from 'fs';
import * as schema from './schema';

async function safePushSchema(dbPath: string) {
  // Create backup
  const backupPath = `${dbPath}.backup-${Date.now()}`;
  copyFileSync(dbPath, backupPath);
  console.log(`Backup created: ${backupPath}`);

  try {
    // Push schema
    const sqlite = new Database(dbPath);
    const db = drizzle(sqlite);

    const result = await pushSQLiteSchema(schema, db);

    if (result.hasDataLoss) {
      console.warn('Warning: This operation may cause data loss!');
      if (!confirm('Continue?')) {
        sqlite.close();
        return;
      }
    }

    await result.apply();
    console.log('Schema updated successfully');
    sqlite.close();
  } catch (error) {
    console.error('Error during push:', error);
    console.log('Restore from backup if needed:', backupPath);
    throw error;
  }
}