Add DuckDB support with PostgreSQL parser reuse

This commit adds DuckDB as a supported database engine to sqlc by reusing the PostgreSQL parser and catalog while implementing a custom analyzer that connects to an in-memory DuckDB instance. Key changes: - Add EngineDuckDB constant to config (internal/config/config.go) - Implement DuckDB analyzer using go-duckdb driver (internal/engine/duckdb/analyzer/) - Register DuckDB engine in compiler with PostgreSQL parser/catalog (internal/compiler/engine.go) - Add DuckDB support to vet command (internal/cmd/vet.go) - Add go-duckdb v1.8.5 dependency (go.mod) - Create comprehensive example with schema, queries, and documentation (examples/duckdb/) The DuckDB implementation leverages PostgreSQL-compatible SQL syntax while providing accurate type inference through live database analysis. The analyzer uses an in-memory DuckDB instance to extract column and parameter types. Features: - PostgreSQL-compatible SQL parsing - In-memory database analysis - Schema migration support - Type-safe Go code generation - Thread-safe connection management 🤖 Generated with [Claude Code](https://claude.com/claude-code) Co-Authored-By: Claude <[email protected]>
sqlc-dev · kyleconroy · Oct 28, 2025 · Oct 29, 2025 · Oct 29, 2025 · Oct 29, 2025
commit 8af500078de542ab311c7215eb3b0c1b5bbc6bcf
diff --git a/examples/duckdb/README.md b/examples/duckdb/README.md
@@ -0,0 +1,138 @@
+# DuckDB Example
+
+This example demonstrates how to use sqlc with DuckDB.
+
+## Overview
+
+DuckDB is an in-process analytical database that supports PostgreSQL-compatible SQL syntax. This integration reuses sqlc's PostgreSQL parser and catalog while providing a DuckDB-specific analyzer that connects to an in-memory DuckDB instance.
+
+## Features
+
+- **PostgreSQL-compatible SQL**: DuckDB uses PostgreSQL-compatible syntax, so you can use familiar SQL constructs
+- **In-memory database**: Perfect for testing and development
+- **Type-safe Go code**: sqlc generates type-safe Go code from your SQL queries
+- **Live database analysis**: The analyzer connects to a DuckDB instance to extract accurate column types
+
+## Configuration
+
+The `sqlc.yaml` file configures sqlc to use the DuckDB engine:
+
+```yaml
+version: "2"
+sql:
+  - name: "duckdb_example"
+    engine: "duckdb"  # Use DuckDB engine
+    schema:
+      - "schema.sql"
+    queries:
+      - "query.sql"
+    database:
+      managed: false
+      uri: ":memory:"  # Use in-memory database
+    analyzer:
+      database: true   # Enable live database analysis
+    gen:
+      go:
+        package: "db"
+        out: "db"
+```
+
+## Database URI
+
+DuckDB supports several URI formats:
+
+- `:memory:` - In-memory database (default if not specified)
+- `file.db` - File-based database
+- `/path/to/file.db` - Absolute path to database file
+
+## Usage
+
+1. Generate Go code:
+   ```bash
+   sqlc generate
+   ```
+
+2. Use the generated code in your application:
+   ```go
+   package main
+
+   import (
+       "context"
+       "database/sql"
+       "log"
+
+       _ "github.com/marcboeker/go-duckdb"
+       "yourmodule/db"
+   )
+
+   func main() {
+       // Open DuckDB connection
+       conn, err := sql.Open("duckdb", ":memory:")
+       if err != nil {
+           log.Fatal(err)
+       }
+       defer conn.Close()
+
+       // Create tables
+       schema := `
+       CREATE TABLE users (
+           id INTEGER PRIMARY KEY,
+           name VARCHAR NOT NULL,
+           email VARCHAR UNIQUE NOT NULL,
+           created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+       );
+       `
+       if _, err := conn.Exec(schema); err != nil {
+           log.Fatal(err)
+       }
+
+       // Use generated queries
+       queries := db.New(conn)
+       ctx := context.Background()
+
+       // Create a user
+       user, err := queries.CreateUser(ctx, db.CreateUserParams{
+           Name:  "John Doe",
+           Email: "[email protected]",
+       })
+       if err != nil {
+           log.Fatal(err)
+       }
+
+       log.Printf("Created user: %+v\n", user)
+
+       // Get the user
+       fetchedUser, err := queries.GetUser(ctx, user.ID)
+       if err != nil {
+           log.Fatal(err)
+       }
+
+       log.Printf("Fetched user: %+v\n", fetchedUser)
+   }
+   ```
+
+## Differences from PostgreSQL
+
+While DuckDB supports PostgreSQL-compatible SQL, there are some differences:
+
+1. **Data Types**: DuckDB has its own set of data types, though many are compatible with PostgreSQL
+2. **Functions**: Some PostgreSQL functions may not be available or may behave differently
+3. **Extensions**: DuckDB uses a different extension system than PostgreSQL
+
+## Benefits of DuckDB
+
+1. **Fast analytical queries**: Optimized for OLAP workloads
+2. **Embedded**: No separate server process needed
+3. **Portable**: Single file database
+4. **PostgreSQL-compatible**: Familiar SQL syntax
+
+## Requirements
+
+- Go 1.24.0 or later
+- `github.com/marcboeker/go-duckdb` driver
+
+## Notes
+
+- The DuckDB analyzer uses an in-memory instance to extract query metadata
+- Schema migrations are applied to the analyzer instance automatically
+- Type inference is done by preparing queries against the DuckDB instance
diff --git a/examples/duckdb/query.sql b/examples/duckdb/query.sql
@@ -0,0 +1,25 @@
+-- name: GetUser :one
+SELECT id, name, email, created_at
+FROM users
+WHERE id = $1;
+
+-- name: ListUsers :many
+SELECT id, name, email, created_at
+FROM users
+ORDER BY name;
+
+-- name: CreateUser :one
+INSERT INTO users (name, email)
+VALUES ($1, $2)
+RETURNING id, name, email, created_at;
+
+-- name: GetUserPosts :many
+SELECT p.id, p.title, p.content, p.published, p.created_at
+FROM posts p
+WHERE p.user_id = $1
+ORDER BY p.created_at DESC;
+
+-- name: CreatePost :one
+INSERT INTO posts (user_id, title, content, published)
+VALUES ($1, $2, $3, $4)
+RETURNING id, user_id, title, content, published, created_at;
diff --git a/examples/duckdb/schema.sql b/examples/duckdb/schema.sql
@@ -0,0 +1,17 @@
+-- Example DuckDB schema
+CREATE TABLE users (
+    id INTEGER PRIMARY KEY,
+    name VARCHAR NOT NULL,
+    email VARCHAR UNIQUE NOT NULL,
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
+);
+
+CREATE TABLE posts (
+    id INTEGER PRIMARY KEY,
+    user_id INTEGER NOT NULL,
+    title VARCHAR NOT NULL,
+    content TEXT,
+    published BOOLEAN DEFAULT false,
+    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
+    FOREIGN KEY (user_id) REFERENCES users(id)
+);
diff --git a/examples/duckdb/sqlc.yaml b/examples/duckdb/sqlc.yaml
@@ -0,0 +1,18 @@
+version: "2"
+sql:
+  - name: "duckdb_example"
+    engine: "duckdb"
+    schema:
+      - "schema.sql"
+    queries:
+      - "query.sql"
+    database:
+      managed: false
+      uri: ":memory:"
+    analyzer:
+      database: true
+    gen:
+      go:
+        package: "db"
+        out: "db"
+        sql_package: "database/sql"
diff --git a/go.mod b/go.mod
@@ -11,6 +11,7 @@ require (
 	github.com/fatih/structtag v1.2.0
 	github.com/go-sql-driver/mysql v1.9.3
 	github.com/google/cel-go v0.26.1
+	github.com/marcboeker/go-duckdb v1.8.5
 	github.com/google/go-cmp v0.7.0
 	github.com/jackc/pgx/v4 v4.18.3
 	github.com/jackc/pgx/v5 v5.7.6

diff --git a/internal/cmd/vet.go b/internal/cmd/vet.go
@@ -21,6 +21,7 @@ import (
 	"github.com/google/cel-go/cel"
 	"github.com/google/cel-go/ext"
 	"github.com/jackc/pgx/v5"
+	_ "github.com/marcboeker/go-duckdb"
 	"github.com/spf13/cobra"
 	"google.golang.org/protobuf/encoding/protojson"
 
@@ -529,6 +530,18 @@ func (c *checker) checkSQL(ctx context.Context, s config.SQL) error {
 			// SQLite really doesn't want us to depend on the output of EXPLAIN
 			// QUERY PLAN: https://www.sqlite.org/eqp.html
 			expl = nil
+		case config.EngineDuckDB:
+			db, err := sql.Open("duckdb", dburl)
+			if err != nil {
+				return fmt.Errorf("database: connection error: %s", err)
+			}
+			if err := db.PingContext(ctx); err != nil {
+				return fmt.Errorf("database: connection error: %s", err)
+			}
+			defer db.Close()
+			prep = &dbPreparer{db}
+			// DuckDB supports EXPLAIN
+			expl = nil
 		default:
 			return fmt.Errorf("unsupported database uri: %s", s.Engine)
 		}

diff --git a/internal/compiler/engine.go b/internal/compiler/engine.go
@@ -8,6 +8,7 @@ import (
 	"github.com/sqlc-dev/sqlc/internal/config"
 	"github.com/sqlc-dev/sqlc/internal/dbmanager"
 	"github.com/sqlc-dev/sqlc/internal/engine/dolphin"
+	duckdbanalyze "github.com/sqlc-dev/sqlc/internal/engine/duckdb/analyzer"
 	"github.com/sqlc-dev/sqlc/internal/engine/postgresql"
 	pganalyze "github.com/sqlc-dev/sqlc/internal/engine/postgresql/analyzer"
 	"github.com/sqlc-dev/sqlc/internal/engine/sqlite"
@@ -58,6 +59,20 @@ func NewCompiler(conf config.SQL, combo config.CombinedSettings) (*Compiler, err
 				)
 			}
 		}
+	case config.EngineDuckDB:
+		// DuckDB uses PostgreSQL-compatible SQL, so we reuse the PostgreSQL parser and catalog
+		c.parser = postgresql.NewParser()
+		c.catalog = postgresql.NewCatalog()
+		c.selector = newDefaultSelector()
+		if conf.Database != nil {
+			if conf.Analyzer.Database == nil || *conf.Analyzer.Database {
+				c.analyzer = analyzer.Cached(
+					duckdbanalyze.New(c.client, *conf.Database),
+					combo.Global,
+					*conf.Database,
+				)
+			}
+		}
 	default:
 		return nil, fmt.Errorf("unknown engine: %s", conf.Engine)
 	}

diff --git a/internal/config/config.go b/internal/config/config.go
@@ -54,6 +54,7 @@ const (
 	EngineMySQL      Engine = "mysql"
 	EnginePostgreSQL Engine = "postgresql"
 	EngineSQLite     Engine = "sqlite"
+	EngineDuckDB     Engine = "duckdb"
 )
 
 type Config struct {