SQL Syntax Reference

This page documents all SQL annotations and modifiers supported by SQG.

DBeaver Compatibility

SQG’s syntax is designed to be fully compatible with DBeaver, the popular open-source database IDE. The @set variable syntax and ${variable} references are native DBeaver features, which means you can:

• Develop queries interactively with full autocomplete for tables and columns
• Execute and test queries directly in DBeaver before generating code
• Modify @set values and re-run to test different parameter scenarios
• Debug with query plans to optimize performance

This compatibility lets you use DBeaver as your primary SQL development environment—write and test queries there, then run sqg to generate type-safe code.

File Structure

An SQG SQL file contains multiple blocks, each starting with a comment annotation:

Complete File Structure Example

Example showing all block types in an SQG SQL file

Try in Playground

-- MIGRATE 1
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

-- MIGRATE 2
ALTER TABLE users ADD COLUMN email TEXT;

-- TESTDATA test1
INSERT INTO users VALUES (1, 'Test User', '[email protected]');

-- QUERY get_all_users
SELECT * FROM users;

-- QUERY get_user :one
@set id = 1
SELECT * FROM users WHERE id = ${id};

-- EXEC create_user
@set name = 'John'
@set email = '[email protected]'
INSERT INTO users (name, email) VALUES (${name}, ${email});

Free-form comments

You can put -- line comments and /* ... */ block comments anywhere in the file — at the top of the file, between blocks, or inside a block’s SQL body. They are ignored by SQG.

The one caveat: a -- comment whose first word matches a SQG keyword (QUERY, EXEC, MIGRATE, BASELINE, TESTDATA, TABLE) followed by whitespace is treated as an annotation, not a comment. If you want a free-form comment that begins with such a word, rephrase it — e.g. write -- Tables in this file: instead of -- TABLES in this file:.

Block Types

MIGRATE

Schema migrations are executed in source order to set up the database structure.

-- MIGRATE 1
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- MIGRATE 2
CREATE INDEX idx_users_name ON users(name);

-- MIGRATE add_email
ALTER TABLE users ADD COLUMN email TEXT UNIQUE;

Rules:

• The migration name is an arbitrary identifier — 1, 2, add_email, etc. It is used to track which migrations have been applied; it does not control execution order.
• Migrations run in the order they appear in the source file. If you split migrations across multiple files, list those files in the order you want migrations to run in sqg.yaml.
• Each migration name must be unique within a file.
• Each migration is executed once during type introspection.
• Generated code includes a getMigrations() static method returning migration SQL strings.
• Use the built-in applyMigrations() function (see below) or manage migration tracking yourself.

BASELINE

Schema that is created outside SQG (for example by an ETL job, a sibling service, or a third-party tool) but that your queries need to type-check against.

-- BASELINE schema
CREATE TABLE clusters (id INTEGER PRIMARY KEY);

-- MIGRATE 1
ALTER TABLE clusters ADD COLUMN list_uri TEXT;

-- QUERY getCluster :one
@set id = 1
SELECT id, list_uri FROM clusters WHERE id = ${id};

Rules:

• BASELINE blocks run before MIGRATE blocks during type introspection, so MIGRATE statements can reference baseline tables.
• BASELINE blocks are not included in the generated getMigrations() array — your application is expected to obtain the baseline schema from elsewhere.
• Use BASELINE when SQG is not the owner of (part of) the schema, but still needs to know the schema to validate queries.

When to reach for BASELINE:

• Tables created by an ETL job, a sibling service, or a third-party tool that your queries read from.
• A schema managed by another migration system that you don’t want SQG to re-apply.
• An external database attached at runtime (for example a production Postgres attached into DuckDB). The schema goes in BASELINE so SQG can type-check against it at generation time, while your application performs the real ATTACH at runtime.

:source=<name> — Postgres sources

A BASELINE block tagged :source=<name> describes the schema of a postgres source declared in sqg.yaml:

-- BASELINE prod_orders :source=prod
CREATE TABLE orders (id BIGINT PRIMARY KEY, customer TEXT NOT NULL);

sqg.yaml

sources:
  - name: prod
    type: postgres

During generation SQG starts a throwaway Postgres testcontainer, applies these :source=prod blocks to it natively (so introspection sees real Postgres types), attaches it into the DuckDB introspection connection as prod, and type-checks queries that read from prod.public.…. The container, schema, and generation-time ATTACH are not emitted. For runtime, SQG generates a typed attach<Source> helper (e.g. attachProd(connectionString)) so your application attaches the real database under the same alias. Requires Docker (or a url to introspect an existing database without Docker), and a duckdb generator. See the Attach a Postgres database recipe.

File sources — ${sources_<name>}

A source without a type is a file source: a path exposed to your queries as an inlined ${sources_<name>} variable (handy for read_parquet, read_csv, or a SQLite ATTACH).

sqg.yaml

sources:
  - path: $HOME/data/appinfo.parquet   # name derived from the basename -> ${sources_appinfo}
  - name: events                       # explicit name -> ${sources_events}
    path: ./data/events.parquet

-- QUERY appInfo
SELECT * FROM read_parquet(${sources_appinfo});

The variable name is sources_ + the source name, or — when name is omitted — the file’s basename without extension. Because the name then silently depends on the filename, prefer an explicit name: when the basename is ambiguous.

Runtime contract (identical across TypeScript, Java, and Python): a ${sources_<name>} reference becomes a runtime method parameter of string type; the generated SQL interpolates that argument into the query. The generation-time absolute path is used only to introspect types and is never baked into the generated code — so the artifact is portable and leaks no developer paths. Call the query with the path your application resolves at runtime:

await db.appInfo('/srv/data/appinfo.parquet');   // the value is interpolated into the SQL

Built-in Migration Runner

SQG can generate an applyMigrations() function that automatically tracks and applies migrations. Enable it with config.migrations: true:

sqg.yaml

gen:
  - generator: typescript/sqlite
    output: ./src/generated/
    config:
      migrations: true

This generates a static applyMigrations() method that:

• Creates a _sqg_migrations tracking table (if it doesn’t exist)
• Checks which migrations have already been applied for this project
• Applies unapplied migrations in order
• Records each applied migration with a timestamp
• Runs everything inside a transaction for safety

The tracking table uses the project name from your sqg.yaml to scope migrations, so multiple sqg projects can share the same database without conflicts.

TypeScript (SQLite):

// Apply migrations with default project name (from sqg.yaml)
MyApp.applyMigrations(db);

// Override project name (for multi-tenant scenarios)
MyApp.applyMigrations(db, 'custom-project-name');

TypeScript (DuckDB):

await MyApp.applyMigrations(connection);

Java (JDBC):

MyApp.applyMigrations(connection);

// Or with a custom project name
MyApp.applyMigrations(connection, "custom-project-name");

The _sqg_migrations table schema:

Column	Type	Description
project	TEXT	Project name (from sqg.yaml)
migration_id	TEXT	Migration identifier (e.g., "1", "2")
applied_at	TIMESTAMP	When the migration was applied

Primary key: (project, migration_id)

TESTDATA

Populate sample data used during type introspection. This data helps SQG understand nullable columns and complex return types.

-- TESTDATA
INSERT INTO users (id, name, email) VALUES
  (1, 'Alice', '[email protected]'),
  (2, 'Bob', '[email protected]');

INSERT INTO posts (id, user_id, title) VALUES
  (1, 1, 'Hello World');

Rules:

• TESTDATA blocks are executed after all migrations
• Use meaningful test data that exercises your queries
• Data is only used during generation, not included in output

QUERY

Select queries that return data.

-- QUERY find_active_users
SELECT * FROM users WHERE active = true;

Generated code returns:

• Array of row objects (default)
• Single row with :one modifier
• Column values with :pluck modifier

EXEC

Execute statements that don’t return rows (INSERT, UPDATE, DELETE).

-- EXEC deactivate_user
@set id = 1
UPDATE users SET active = false WHERE id = ${id};

Generated code returns:

• Database-specific result type (e.g., RunResult for SQLite)
• Typically includes changes count and lastInsertRowid

Add :batch to additionally generate a JDBC batch method — see the :batch modifier below.

TABLE (Bulk Inserts)

Generate high-performance bulk insert code for DuckDB and PostgreSQL tables. Provides significantly faster inserts than individual INSERT statements.

-- TABLE users :appender

Syntax:

• -- TABLE <table_name> :appender - generates bulk insert code for the specified table
• The :appender modifier is required
• SQG introspects the table schema to generate type-safe row types

Supported engines:

• DuckDB — uses the native DuckDB Appender API (TypeScript, Java, Python)
• PostgreSQL — uses COPY BINARY protocol via PgBulkInsert (Java) or psycopg3’s COPY FROM STDIN (Python)

Example:

-- MIGRATE 1
CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    event_type VARCHAR NOT NULL,
    payload VARCHAR,
    created_at TIMESTAMP
);

-- TABLE events :appender

Generated code includes:

• A typed row record/class (e.g., EventsRow)
• DuckDB: appender class with append(), appendMany(), flush(), and close() methods
• PostgreSQL (Java): a bulkInsertEventsRow(Iterable<EventsRow>) method using COPY BINARY
• PostgreSQL (Python): an appender class using COPY FROM STDIN as a context manager

Notes:

• Use appenders for batch inserts (ETL, data pipelines, bulk imports)
• SERIAL/IDENTITY columns are automatically excluded from the row type — PostgreSQL auto-generates their values
• See TypeScript + DuckDB, Java + JDBC, and Python for detailed usage

Modifiers

Modifiers change how query results are returned. Add them after the query name:

-- QUERY name :modifier1 :modifier2

:all (default)

Returns all matching rows as an array.

-- QUERY get_users :all
SELECT * FROM users;

// Generated: User[]
getUsers(): { id: number | null; name: string | null; }[]

:one

Returns a single row or undefined. Use for queries expected to return 0 or 1 rows.

-- QUERY get_user :one
@set id = 1
SELECT * FROM users WHERE id = ${id};

// Generated: User | undefined
getUser(id: number): { id: number | null; name: string | null; } | undefined

:pluck

Extracts values from the first (or only) column. Useful for fetching lists of IDs or scalar values.

-- QUERY get_user_ids :pluck
SELECT id FROM users;

// Generated: (number | null)[]
getUserIds(): (number | null)[]

:result=Name

Names the row type returned by a query so multiple queries can share a single record/class. Java only in the current implementation — Python keeps its per-query row classes and TypeScript inlines anonymous types regardless.

Annotate one query, not all of them. Putting :result=Name on one query is enough — every other query in the same file with the same column shape automatically picks up the same name. You do not need to repeat the modifier:

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

-- :result= on get_user_summary is enough; list_user_summaries shares the
-- same record because its shape matches.
-- QUERY list_user_summaries
SELECT name, email FROM users;

// Both methods return the same record
public record UserSummary(String name, String email) {}
public UserSummary getUserSummary(Integer id) throws SQLException { ... }
public List<UserSummary> listUserSummaries() throws SQLException { ... }

Defaults. Without :result=, each query keeps its own per-query row type (e.g. GetUserByIdResult, ListUsersResult) — SQG does not invent a shared auto-name. The one exception is a full-table match: a SELECT * whose result columns exactly match a -- TABLE <name> annotation’s introspected schema (same count, order, name, type) automatically uses that table’s row type (e.g. UsersRow), with no annotation needed. Skipped when the table has SERIAL/IDENTITY columns (the appender row type omits them).

Provenance safety. When two queries have the same shape but read from different tables (e.g. email FROM users vs email FROM blocked_emails), they are considered distinct groups on SQLite and PostgreSQL — SQG tracks each column’s source table from the driver. On DuckDB the driver does not expose per-column source-table, so the two queries look identical to SQG; if that matters for you, use different :result= names to keep them apart.

Conflicts. Code generation fails with a VALIDATION_ERROR when:

• Two queries with the same shape carry different :result= names — there’s no single name to pick.
• Two queries with different shapes carry the same :result= name (or one matches a TABLE appender’s row type at a different shape) — one record cannot represent both.

:batch

Applies to EXEC statements only. Generates an additional <name>Batch(...) method that uses JDBC’s addBatch() / executeBatch() to send many rows in one round trip. The regular single-row method is still generated alongside it.

Java only — ignored by the TypeScript and Python generators.

-- EXEC insert_user :batch
@set id = 'u1'
@set name = 'Alice'
@set email = '[email protected]'
INSERT INTO users (id, name, email) VALUES (${id}, ${name}, ${email});

For queries with multiple parameters, SQG generates a params record to keep call sites readable:

// Generated
public record InsertUserParams(String id, String name, String email) {}

public int[] insertUserBatch(Iterable<InsertUserParams> params) throws SQLException { ... }

queries.insertUserBatch(List.of(
    new MyApp.InsertUserParams("u1", "Alice", "[email protected]"),
    new MyApp.InsertUserParams("u2", "Bob",   "[email protected]")
));

Single-parameter EXECs skip the record wrapper:

-- EXEC delete_user :batch
@set id = 'u1'
DELETE FROM users WHERE id = ${id};

queries.deleteUserBatch(List.of("u1", "u2", "u3"));

When to use which:

• :batch — general-purpose; works for INSERT, UPDATE, and DELETE on any JDBC database. For PostgreSQL, add ?reWriteBatchedInserts=true to the JDBC URL for ~2.5× throughput on batched INSERTs (see the insert benchmark).
• TABLE ... :appender — INSERT-only, DuckDB and PostgreSQL only, typically 5–20× faster than :batch because it bypasses SQL parsing entirely (COPY BINARY / DuckDB appender API).

Combining Modifiers

Modifiers can be combined:

-- QUERY count_users :one :pluck
SELECT COUNT(*) FROM users;

// Generated: number | null | undefined
countUsers(): number | null | undefined

-- QUERY get_first_email :one :pluck
SELECT email FROM users ORDER BY id LIMIT 1;

// Generated: string | null | undefined
getFirstEmail(): string | null | undefined

Parameters

Defining Parameters

Use @set to define parameters with sample values:

-- QUERY find_users_by_name
@set name = 'John'
SELECT * FROM users WHERE name = ${name};

The sample value ('John') is used during type introspection. At runtime, the parameter becomes a function argument.

Multiple Parameters

-- QUERY find_users
@set name = 'John'
@set min_age = 18
@set max_age = 65
SELECT * FROM users
WHERE name LIKE ${name}
  AND age >= ${min_age}
  AND age <= ${max_age};

findUsers(name: string, min_age: number, max_age: number): User[]

Parameter Order

Parameters appear in the generated function in the order they’re defined with @set:

-- QUERY example
@set first = 'a'
@set second = 1
@set third = true
SELECT * FROM t WHERE a = ${first} AND b = ${second} AND c = ${third};

example(first: string, second: number, third: boolean): Result[]

Parameter Types

Parameter types are inferred from the sample values:

Sample Value	Inferred Type
'text'	string
123	number (integer)
12.5	number (float)
true / false	boolean

Block Comments

You can use block comments for queries with configuration:

/* QUERY complex_query :one
  result:
    count: integer not null
    email: text not null
*/
SELECT COUNT(*) as count, email FROM users GROUP BY email LIMIT 1;

The YAML-like configuration allows explicit type overrides when automatic inference isn’t sufficient.

Inline Comments

Regular SQL comments within queries are preserved:

-- QUERY get_active_users
SELECT * FROM users
WHERE active = true  -- Filter active users
  AND deleted_at IS NULL;  -- Exclude deleted

Complex Types (DuckDB)

DuckDB supports rich data types that SQG fully maps:

Arrays/Lists

-- QUERY get_tags :one
SELECT ['tag1', 'tag2', 'tag3'] as tags;

// Generated:
{
  tags: {
    items: (string | null)[]
  } | null
}

Structs

-- QUERY get_user_data :one
SELECT {'name': 'John', 'age': 30} as user;

// Generated:
{
  user: {
    entries: {
      name: string | null;
      age: number | null;
    }
  } | null
}

Maps

-- QUERY get_metadata :one
SELECT MAP {'key1': 'value1', 'key2': 'value2'} as meta;

// Generated:
{
  meta: {
    entries: { key: string; value: string | null }[]
  } | null
}

Nested Structures

-- QUERY get_complex :one
SELECT {
  'user': {'id': 1, 'name': 'John'},
  'tags': ['admin', 'user'],
  'settings': MAP {'theme': 'dark'}
} as data;

// Generated:
{
  data: {
    entries: {
      user: { entries: { id: number | null; name: string | null } } | null;
      tags: { items: (string | null)[] } | null;
      settings: { entries: { key: string; value: string | null }[] } | null;
    }
  } | null
}

Best Practices

Naming Conventions

• Use snake_case for query names: get_user_by_id
• Generated code converts to camelCase: getUserById

One Query Per Block

Each query should be in its own block:

-- QUERY get_users
SELECT * FROM users;

-- QUERY get_posts
SELECT * FROM posts;

Use Meaningful Test Data

Test data helps with type inference:

-- TESTDATA
-- Include NULL values to test nullable handling
INSERT INTO users (id, name, email) VALUES
  (1, 'Test', '[email protected]'),
  (2, 'No Email', NULL);

Keep Migrations Atomic

Each migration should be a single logical change:

-- MIGRATE 1
CREATE TABLE users (id INTEGER PRIMARY KEY);

-- MIGRATE 2
ALTER TABLE users ADD COLUMN name TEXT;

-- MIGRATE 3
CREATE INDEX idx_users_name ON users(name);