Getting Started

This guide will walk you through installing SQG and generating your first type-safe database access code.

Prerequisites

Before you begin, ensure you have:

• Node.js 18 or later
• pnpm (recommended) or npm/yarn

Installation

Global installation (recommended)

# pnpm (recommended)
pnpm add -g @sqg/sqg
pnpm approve-builds -g  # needed for native dependencies

# npm
npm install -g @sqg/sqg

# yarn
yarn global add @sqg/sqg

Project-local installation

# pnpm
pnpm add -D @sqg/sqg

# npm
npm install --save-dev @sqg/sqg

# yarn
yarn add -D @sqg/sqg

From source

git clone https://github.com/sqg-dev/sqg.git
cd sqg/sqg
pnpm install
pnpm build
pnpm link --global

Quick Start

Option A: Initialize a New Project (Recommended)

The fastest way to get started is using the sqg init command:

# Create a new project with TypeScript + SQLite (default)
sqg init

# Or specify a different generator
sqg init --generator typescript/duckdb

# Java targets
sqg init --generator java/sqlite
sqg init --generator java/duckdb
sqg init --generator java/duckdb/arrow
sqg init --generator java/postgres

This creates:

• sqg.yaml - Project configuration
• queries.sql - Example SQL file with migrations and queries
• ./generated/ - Output directory for generated code

Then run:

sqg sqg.yaml

Option B: Manual Setup

1. Create your SQL file

   Create a file called queries.sql:

   Complete Getting Started Example

   Full example with migrations, queries, and exec statements

   Try in Playground

   -- MIGRATE 1
   CREATE TABLE users (
     id INTEGER PRIMARY KEY,
     name TEXT NOT NULL,
     email TEXT UNIQUE
   );
   
   -- QUERY all_users
   SELECT * FROM users;
   
   -- QUERY get_user :one
   @set id = 1
   SELECT * FROM users WHERE id = ${id};
   
   -- EXEC create_user
   @set name = 'John Doe'
   @set email = '[email protected]'
   INSERT INTO users (name, email) VALUES (${name}, ${email});
   
   -- EXEC delete_user
   @set id = 1
   DELETE FROM users WHERE id = ${id};

2. Create a project configuration

   Create a file called sqg.yaml:

   version: 1
   name: my-app
   
   sql:
     - files:
         - queries.sql
       gen:
         - generator: typescript/sqlite
           output: ./src/generated/

3. Run the generator

   sqg sqg.yaml

   This creates ./src/generated/my-app.ts with fully typed query functions.

4. Use the generated code

   import Database from 'better-sqlite3';
   import { MyApp } from './generated/my-app';
   
   // Initialize database
   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);
   
   // Use typed queries
   queries.createUser('Alice', '[email protected]');
   queries.createUser('Bob', '[email protected]');
   
   const users = queries.allUsers();
   console.log(users);
   // [{ id: 1, name: 'Alice', email: '[email protected]' }, ...]
   
   const user = queries.getUser(1);
   console.log(user?.name); // 'Alice'

Project Configuration

The sqg.yaml file defines your project structure:

version: 1          # Config version (always 1)
name: my-project    # Project name (used for class names)

sql:
  - files:
      - queries.sql # SQL files to process
      - users.sql
    gen:
      - generator: typescript/sqlite
        output: ./src/db/

      - generator: java/sqlite
        output: ./java/src/main/java/db/
        config:
          package: com.myapp.db

Available Generators

Generators follow the format <language>/<engine>[/<driver>]:

Generator	Description	Documentation
typescript/sqlite	TypeScript for SQLite (sync API)	TypeScript + SQLite
typescript/duckdb	TypeScript for DuckDB (async API)	TypeScript + DuckDB
java/sqlite	Java with JDBC for SQLite	Java + JDBC
java/duckdb	Java with JDBC for DuckDB	Java + JDBC
java/duckdb/arrow	Java with Arrow API (high-performance)	Java + DuckDB Arrow
java/postgres	Java with JDBC for PostgreSQL	Java + JDBC

See individual generator documentation for configuration options, type mappings, and usage examples.

Database Engines

Engine	Description	TypeScript	Java
sqlite	Embedded, no server required	typescript/sqlite	java/sqlite
duckdb	Analytics, complex types	typescript/duckdb	java/duckdb, java/duckdb/arrow
postgres	Full-featured SQL server	—	java/postgres

Notes:

• SQLite and DuckDB use in-memory databases for type introspection (no server needed)
• PostgreSQL requires a running server; set SQG_POSTGRES_URL environment variable
• See individual generator pages for installation and usage details

Development Workflow with DBeaver

SQG’s SQL syntax is designed to be compatible with DBeaver, the popular open-source database IDE. The @set variable syntax is a DBeaver feature, which means you can use DBeaver as your primary SQL development environment.

Why DBeaver?

DBeaver natively supports the @set variable syntax that SQG uses. This means you can:

• Execute queries with parameters directly in DBeaver
• Modify parameter values and re-run to test different scenarios
• Get autocomplete for table and column names
• View query execution plans for optimization
• Debug queries before generating code

Recommended Workflow

1. Install DBeaver (free, cross-platform)

2. Connect DBeaver to your database (SQLite file, DuckDB, or PostgreSQL server)

3. Create your SQL file with migrations and queries

4. Develop queries interactively in DBeaver:

   • Run migrations to set up your schema
   • Execute queries and verify results
   • Modify @set values to test edge cases

5. Run SQG to generate code when your queries are ready

Testing Parameters in DBeaver

The @set declarations work as variable definitions in DBeaver. Modify parameter values and re-run queries to test different scenarios:

-- QUERY find_users
@set name = 'Alice'        -- Change this value in DBeaver to test
@set min_age = 25          -- different scenarios
SELECT * FROM users WHERE name = ${name} AND age >= ${min_age};

Select the entire block (including @set lines) and execute - DBeaver will substitute the variables automatically.

SQL Syntax Overview

SQG uses comment annotations to define queries:

-- MIGRATE 1              -- Schema migrations (numbered)
-- TESTDATA               -- Test data for type introspection
-- QUERY name             -- Select queries (returns rows)
-- QUERY name :one        -- Returns single row or undefined
-- QUERY name :pluck      -- Returns array of first column values
-- EXEC name              -- Execute statements (INSERT/UPDATE/DELETE)

Parameters use @set to define and ${name} to reference:

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

See SQL Syntax Reference for complete documentation including complex types, modifiers, and best practices.

CLI Reference

sqg <config-file>           # Generate code from config
sqg --validate <config>     # Validate without generating
sqg --format json <config>  # Output as JSON
sqg init                    # Initialize new project
sqg syntax                  # Show SQL syntax reference
sqg --help                  # Show help

See the CLI Reference for complete documentation including all commands, options, JSON output format, and error codes.

Next Steps

• Generators:
  • TypeScript + SQLite - Sync API with better-sqlite3
  • TypeScript + DuckDB - Async API with complex types
  • Java + JDBC - Standard JDBC for any database
  • Java + DuckDB Arrow - High-performance Arrow API
• SQL Syntax Reference - Complete annotation reference
• CLI Reference - Full command-line documentation
• Playground - Try SQG in your browser
• FAQ - Common questions and troubleshooting