TypeScript + SQLite

Generate synchronous TypeScript code for SQLite databases using the better-sqlite3 driver.

Overview

Property	Value
Generator	typescript/sqlite (or typescript/sqlite/better-sqlite3)
Runtime	Node.js
API Style	Synchronous
Driver	better-sqlite3

When to Use

Choose typescript/sqlite when:

• Building Node.js applications with SQLite
• Need synchronous API for simpler code flow
• Want maximum performance for single-connection scenarios

Installation

pnpm add -D @sqg/sqg        # pnpm
npm install -D @sqg/sqg     # npm
yarn add -D @sqg/sqg        # yarn

# Install runtime dependencies
pnpm add better-sqlite3
pnpm add -D @types/better-sqlite3

Example Configuration

sqg.yaml

version: 1
name: my-app

sql:
  - files:
      - queries.sql
    gen:
      - generator: typescript/sqlite
        output: ./src/generated/
        config:
          migrations: true     # generates applyMigrations() function
          # safeIntegers: true # opt in to bigint for INTEGER/BIGINT columns

config.safeIntegers

By default, BIGINT columns are typed as number for SQLite generators because all SQLite drivers (better-sqlite3, node:sqlite, libsql, turso) return JS numbers for INTEGER/BIGINT values out of the box. JS numbers are safe up to Number.MAX_SAFE_INTEGER (≈ 9×10¹⁵).

If your application enables the driver’s safe-integer mode (e.g. db.defaultSafeIntegers(true) in better-sqlite3), set config.safeIntegers: true so that generated types use bigint instead. The generated code will then expect bigints throughout.

config.safeIntegers	BIGINT type	When to use
false (default)	number	Driver returns JS numbers (default for every SQLite driver)
true	bigint	Driver returns BigInt (you’ve called defaultSafeIntegers(true) or equivalent)

Quick Start

1. Initialize a project

   sqg init --generator typescript/sqlite

2. Write your SQL

   TypeScript SQLite Complete Example

   Full example with migrations, queries, and exec statements

   Try in Playground

   -- MIGRATE 1
   CREATE TABLE users (
     id INTEGER PRIMARY KEY AUTOINCREMENT,
     name TEXT NOT NULL,
     email TEXT UNIQUE,
     created_at TEXT DEFAULT CURRENT_TIMESTAMP
   );
   
   -- QUERY all_users
   SELECT * FROM users ORDER BY created_at DESC;
   
   -- QUERY get_user :one
   @set id = 1
   SELECT * FROM users WHERE id = ${id};
   
   -- QUERY find_users_by_name
   @set name = 'John'
   SELECT * FROM users WHERE name LIKE '%' || ${name} || '%';
   
   -- EXEC create_user
   @set name = 'John Doe'
   @set email = '[email protected]'
   INSERT INTO users (name, email) VALUES (${name}, ${email});
   
   -- EXEC update_user
   @set id = 1
   @set name = 'Jane Doe'
   UPDATE users SET name = ${name} WHERE id = ${id};
   
   -- EXEC delete_user
   @set id = 1
   DELETE FROM users WHERE id = ${id};

3. Generate code

   sqg sqg.yaml

4. Use the generated code

   import Database from 'better-sqlite3';
   import { MyApp } from './generated/my-app';
   
   // Create database connection
   const db = new Database('app.db');
   
   // Apply migrations (with built-in tracking)
   MyApp.applyMigrations(db);
   
   // Create query instance
   const queries = new MyApp(db);
   
   // Insert data
   queries.createUser('Alice', '[email protected]');
   queries.createUser('Bob', '[email protected]');
   
   // Query data
   const users = queries.allUsers();
   console.log(users);
   // [{ id: 1, name: 'Alice', email: '[email protected]', created_at: '...' }, ...]
   
   const user = queries.getUser(1);
   console.log(user?.name); // 'Alice'
   
   // Update data
   queries.updateUser(1, 'Alice Smith');
   
   // Delete data
   queries.deleteUser(2);

Generated Code Structure

The generator creates a single TypeScript file with:

• Type definitions for each query result
• A class with methods for each query/exec
• Static getMigrations() method returning migration SQL strings
• Static applyMigrations() method (when config.migrations: true) that tracks and applies migrations automatically

Generated TypeScript

From: TypeScript SQLite Complete Example

See full code in Playground

// ...

  allUsers(): {
    id: number;
    name: string;
    email: string | null;
    created_at: string | null;
  }[] {
    const stmt = this.prepare<
      [],
      {
        id: number;
        name: string;
        email: string | null;
        created_at: string | null;
      }
    >("all_users", "SELECT * FROM users ORDER BY created_at DESC;");
    return stmt.all();
  }

// ...

Transactions

Use better-sqlite3’s built-in transaction support:

const transfer = db.transaction((fromId: number, toId: number, amount: number) => {
  queries.debit(fromId, amount);
  queries.credit(toId, amount);
});

// Runs atomically
transfer(1, 2, 100);

Connection Management

better-sqlite3 connections are synchronous and don’t require pooling:

// Simple single connection
const db = new Database('app.db');
const queries = new MyApp(db);

// WAL mode for better concurrency
db.pragma('journal_mode = WAL');

// Close when done
db.close();

Type Mapping

SQLite Type	TypeScript Type
INTEGER	number
REAL	number
TEXT	string
BLOB	Buffer
NULL	null

Nullable columns use union types: string | null

Migrations

SQG generates a getMigrations() method that returns migration SQL strings. With config.migrations: true, you also get applyMigrations() which handles tracking automatically:

// Recommended: built-in migration tracking
MyApp.applyMigrations(db);

// Or manual migration management
for (const migration of MyApp.getMigrations()) {
  db.exec(migration);
}

The built-in migration runner uses a _sqg_migrations table to track which migrations have been applied. Multiple sqg projects can share the same database — each project’s migrations are tracked separately using the project name from sqg.yaml.

Best Practices

1. Use WAL mode for better concurrent read performance
2. Run migrations on startup before creating the query instance
3. Use transactions for multiple related writes
4. Close the database when your application shuts down

See Also

• better-sqlite3 documentation