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

version: 1
name: my-app

sql:
  - files:
      - queries.sql
    gen:
      - generator: typescript/sqlite
        output: ./src/generated/

Quick Start

Initialize a project
Terminal window
```
sqg init --generator typescript/sqlite
```

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};

Generate code
Terminal window
```
sqg sqg.yaml
```

Use the generated code

import Database from 'better-sqlite3';
import { MyApp } from './generated/my-app';

// Create database connection
const db = new Database('app.db');

// Run migrations
for (const migration of MyApp.getMigrations()) {
  db.exec(migration);
}

// 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

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

Best Practices

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