SQG - SQL Query Generator

Type-safe code generation from SQL. Write SQL, get fully-typed database access code.

What it does

SQG reads annotated .sql files, executes queries against real databases to introspect column types, and generates type-safe code to execute the SQL queries.

The syntax of the .sql file is compatible with DBeaver, this allows to develop the SQL queries with it and then generate the code from the same file.

Features

• Type-safe by design - Generates fully-typed code with accurate column types inferred from your database
• Multiple database engines - Supports SQLite, DuckDB, and PostgreSQL
• Multiple language targets - Generate TypeScript, Java, or Python code from the same SQL files
• Arrow API support - Can generate Apache Arrow API bindings for DuckDB (Java)
• DBeaver compatible - Works seamlessly with DBeaver for database development and testing
• Complex type support - DuckDB: Handles structs, lists, and maps
• Migration management - Built-in support for schema migrations and test data

Installation

pnpm add -g @sqg/sqg
pnpm approve-builds -g  # needed for sqlite dependency

Check if the install was successful:

sqg --help

Quick Start

Option 1: Use sqg init (Recommended)

# Initialize a new project (creates sqg.yaml and queries.sql)
sqg init

# Or with a specific database engine
sqg init --engine duckdb

# Generate code
sqg sqg.yaml

Option 2: Manual Setup

1. Create sqg.yaml in your project root:

version: 1
name: my-project
sql:
  - engine: sqlite    # sqlite, duckdb, or postgres
    files:
      - queries.sql
    gen:
      - generator: typescript/better-sqlite3
        output: src/db.ts

2. Write your SQL file with annotations

For example queries.sql:

-- MIGRATE createUsersTable
CREATE TABLE users (id INTEGER PRIMARY KEY,
                    name TEXT NOT NULL,
                    email TEXT);

-- QUERY getUserById :one
@set id = 1
SELECT id, name, email FROM users WHERE id = ${id};

-- QUERY getUsers
SELECT id, name, email FROM users;

-- EXEC insertUser
@set name = 'John'
@set email = 'john@example.com'
INSERT INTO users (name, email) VALUES (${name}, ${email});

3. Run SQG to generate code:

sqg sqg.yaml

4. Use the generated code:

import Database from 'better-sqlite3';
import { Queries } from './db';

const db = new Database(':memory:');
const queries = new Queries(db);

// Run migrations
for (const sql of Queries.getMigrations()) {
  db.exec(sql);
}

// Type-safe queries
queries.insertUser('Alice', 'alice@example.com');
const user = queries.getUserById(1);
console.log(user?.name);

SQL Annotations

Annotation	Description
-- MIGRATE name	Schema migration (CREATE TABLE, etc.)
-- BASELINE name	Schema created outside SQG (ETL, sibling service). Runs before migrations for type-checking; not emitted in getMigrations()
-- BASELINE name :source=pg	Schema of a type: postgres source. Applied natively to a managed testcontainer and attached into DuckDB for introspection (DuckDB generators; needs Docker)
-- QUERY name	SELECT query returning rows
-- QUERY name :one	Query returning single row or undefined
-- QUERY name :pluck	Return single (first) column value
-- QUERY name :result=Foo	Name the row type (Java). Add it to ONE query and every same-shape query shares it. Full-table SELECT * reuses the TABLE row type without annotation.
-- EXEC name	INSERT/UPDATE/DELETE (no result rows)
-- TESTDATA name	Test data, runs after migrations
-- TABLE name :appender	Generate a type-safe bulk insert appender (DuckDB, PostgreSQL)
@set var = value	Define parameter with sample value
${var}	Reference parameter in query

Supported Databases & Generators

Language	Database	API	Generator	Status
TypeScript	SQLite	better-sqlite3	typescript/sqlite	Tested
TypeScript	DuckDB	@duckdb/node-api	typescript/duckdb	Tested
Java	SQLite/DuckDB/PostgreSQL	JDBC	java/sqlite, java/duckdb, java/postgres	Tested
Java	DuckDB	Apache Arrow	java/duckdb/arrow	Tested
Python	SQLite	sqlite3	python/sqlite	Tested
Python	DuckDB	duckdb	python/duckdb	Tested
Python	PostgreSQL	psycopg3	python/postgres	Tested

CLI Commands

sqg <config>              # Generate code from config file
sqg <config> <config> ... # Generate several projects in one run
sqg init                  # Initialize new project with example files
sqg init --engine duckdb  # Initialize with specific database engine
sqg --validate <config>   # Validate config without generating code
sqg --if-stale <config>   # Generate only if an input changed since the last run
sqg --format json <config> # Output as JSON (for tooling integration)
sqg syntax                # Show SQL annotation syntax reference
sqg mcp                   # Start MCP server for AI assistants
sqg --help                # Show all options

Skipping unchanged projects

--if-stale makes a run a no-op when nothing that affects the output has changed, so generation can be wired into a build, a just recipe or a pre-commit hook and run unconditionally:

git ls-files '*sqg.yaml' | xargs -r sqg --if-stale

Pass every config to one sqg (no xargs -n1): process startup dominates a run that generates nothing, and this way it is paid once. Checking 14 unchanged projects takes ~0.1s in total, against ~1.5s as separate processes.

The check itself costs about a millisecond per project and covers the SQL files, the config, the generator templates, the sqg version, and the generated files themselves — so a hand-edited or deleted output is regenerated too. State lives in a .sqg-cache.json next to the config; add it to .gitignore. Run --verbose to see why a project was regenerated.

Two things are deliberately not content-hashed. File sources are compared by size and modification time, because they are the multi-gigabyte databases and parquet files being introspected. And a type: postgres source with a url disables the cache entirely: its schema lives in a remote database that can change with no local signal.

MCP Server (Model Context Protocol)

SQG includes an MCP server for AI assistants like Claude Code, Claude Desktop, and Cursor. See the Build with AI guide for setup instructions.

Documentation

Full documentation at sqg.dev

License

Apache-2.0