sqlc-gen-crystal

sqlc-gen-crystal

[!CAUTION] Early development - expect breaking changes and rough edges. Please report issues!

Table of Contents

• Installation
• Usage
• Getting Started
  • Real-world Usage
• Options
  • Generated Files
• Query Annotations
• Advanced Features
  • Struct Deduplication
  • JOIN Queries with sqlc.embed()
• Supported Engines
• Type Mappings
  • PostgreSQL
  • MySQL
  • SQLite
• Transactions
  • Manual Transaction Handling
  • Repository Transaction Support
• Roadmap
• Development
  • Building
  • Testing
• Contributing
• License

Installation

Install the plugin using Go:

go install github.com/watzon/sqlc-gen-crystal@latest

The sqlc-gen-crystal binary will be installed to your $GOPATH/bin directory. Make sure this directory is in your $PATH.

Usage

Database Dependencies

Before using the generated code, you must add the appropriate database driver to your Crystal project dependencies and import it in your application code.

Add to your shard.yml:

dependencies:
  # Choose ONE of the following based on your database:
  pg:        # PostgreSQL
    github: will/crystal-pg
  mysql:      # MySQL
    github: crystal-lang/crystal-mysql
  sqlite3:    # SQLite
    github: crystal-lang/crystal-sqlite3

Install dependencies:

shards install

SQLC Configuration

version: "2"
plugins:
  - name: crystal
    process:
      cmd: sqlc-gen-crystal
sql:
  - schema: "schema.sql"
    queries: "query.sql"
    engine: postgresql
    codegen:
      - out: src/db
        plugin: crystal
        options:
          module: "MyApp" # Note: 'module' instead of 'package'
          emit_json_tags: false
          emit_boolean_question_getters: true # Use `getter?` for boolean fields
          generate_connection_manager: true
          generate_repositories: true

Getting Started

Install sqlc following the official documentation.

Create a schema.sql:

CREATE TABLE authors (
  id   BIGSERIAL PRIMARY KEY,
  name text      NOT NULL,
  bio  text
);

Create a query.sql:

-- name: GetAuthor :one
SELECT * FROM authors
WHERE id = $1 LIMIT 1;

-- name: ListAuthors :many
SELECT * FROM authors
ORDER BY name;

-- name: CreateAuthor :one
INSERT INTO authors (
  name, bio
) VALUES (
  $1, $2
)
RETURNING *;

-- name: DeleteAuthor :exec
DELETE FROM authors
WHERE id = $1;

Generate Crystal code:

$ sqlc generate

Use the generated code:

# Required imports
require "pg"                    # or require "mysql" / require "sqlite3"
require "./src/db/database"

# Connect to your database
DB.open("postgres://localhost/myapp") do |db|
  queries = MyApp::Queries.new(db)

  # Create an author
  author = queries.create_author("John Doe", "A great writer")

  # List all authors
  authors = queries.list_authors
  authors.each do |author|
    puts "#{author.name}: #{author.bio || "No bio"}"
  end

  # Delete an author
  queries.delete_author(author.id) if author
end

Important: The database driver import (require "pg", require "mysql", or require "sqlite3") is required before using the generated code. The generated code uses the crystal-db interface, but you need to import the specific driver implementation that matches your database engine.

Real-world Usage

With the generate_connection_manager and generate_repositories options enabled (as shown in the configuration above), you get a clean, ready-to-use API:

# Required imports - make sure to include your database driver
require "pg"                    # or require "mysql" / require "sqlite3"
require "./src/db/database"

# Simple repository usage
author = MyApp::AuthorsRepository.create(name: "Jane Doe", bio: "A prolific writer")
all_authors = MyApp::AuthorsRepository.all

# With transactions
MyApp::AuthorsRepository.transaction do |repo|
  author1 = repo.create(name: "Author 1", bio: "First author")
  author2 = repo.create(name: "Author 2", bio: "Second author")
  # Both inserts succeed or both fail
end

# Direct database access when needed
MyApp::Database.connection.exec("VACUUM ANALYZE authors")

Options

Generated Files

The plugin always generates:

• database.cr - Entry point that requires all other generated files
• models.cr - Crystal structs for database tables
• queries.cr - Type-safe query methods

With generate_connection_manager: true, the database.cr file also includes:

• Singleton database connection manager
• Transaction support methods

With generate_repositories: true, additional files are generated:

• repositories/[table]_repository.cr - Repository class for each table
• Repository methods that wrap the underlying queries
• Transaction support at the repository level

Query Annotations

• :one - Returns 0 or 1 row as T?
• :many - Returns 0 to n rows as Array(T)
• :exec - Executes query without returning rows
• :execrows - Returns number of affected rows as Int64
• :execresult - Returns DB::ExecResult

Advanced Features

Struct Deduplication

The plugin automatically detects when multiple queries return the same set of columns and reuses the same struct instead of generating duplicates. This reduces code size and improves maintainability.

-- Both queries return the same columns
-- name: GetAuthor :one
SELECT id, name, bio FROM authors WHERE id = $1;

-- name: CreateAuthor :one
INSERT INTO authors (name, bio) VALUES ($1, $2)
RETURNING id, name, bio;

Generated code will use the same Author struct for both queries instead of creating GetAuthorRow and CreateAuthorRow.

JOIN Queries with sqlc.embed()

The plugin supports sqlc's embed() function for JOIN queries, creating nested structs that maintain table relationships:

-- name: GetAuthorWithBooks :many
SELECT sqlc.embed(authors), sqlc.embed(books)
FROM authors
LEFT JOIN books ON books.author_id = authors.id
WHERE authors.id = $1;

-- name: GetBookWithAuthor :one
SELECT sqlc.embed(books), sqlc.embed(authors)
FROM books
INNER JOIN authors ON books.author_id = authors.id
WHERE books.id = $1;

-- name: GetAuthorsWithStats :many
SELECT
  sqlc.embed(authors),
  COUNT(books.id) as book_count,
  MAX(books.published) as latest_book
FROM authors
LEFT JOIN books ON books.author_id = authors.id
GROUP BY authors.id, authors.name, authors.bio;

This generates clean, nested structs:

struct GetAuthorWithBooksRow
  include DB::Serializable
  getter author : Author      # Non-nullable (from FROM table)
  getter book : Book?         # Nullable (from LEFT JOIN)
end

struct GetBookWithAuthorRow
  include DB::Serializable
  getter book : Book          # Non-nullable (INNER JOIN)
  getter author : Author      # Non-nullable (INNER JOIN)
end

struct GetAuthorsWithStatsRow
  include DB::Serializable
  getter author : Author      # Embedded author struct
  getter book_count : Int64   # Aggregate column
  getter latest_book : Time?  # Aggregate column (nullable)
end

Usage:

# Fetch author with their books
rows = queries.get_author_with_books(author_id)
rows.each do |row|
  puts "Author: #{row.author.name}"
  if book = row.book
    puts "  Book: #{book.title}"
  else
    puts "  No books"
  end
end

# Fetch authors with statistics
stats = queries.get_authors_with_stats
stats.each do |row|
  puts "#{row.author.name}: #{row.book_count} books"
  if latest = row.latest_book
    puts "  Latest: #{latest}"
  end
end

The plugin automatically handles:

• Nullable embedded structs for LEFT/RIGHT JOINs
• Non-nullable embedded structs for INNER JOINs
• Mixed queries with both embedded tables and aggregate columns
• Proper type safety throughout

Supported Engines

• PostgreSQL via crystal-pg
• MySQL via crystal-mysql
• SQLite3 via crystal-sqlite3

Type Mappings

PostgreSQL

MySQL

SQLite

Transactions

Manual Transaction Handling

DB.transaction do |tx|
  queries = MyApp::Queries.new(tx.connection)

  author = queries.create_author("Jane Doe", "Another writer")
  queries.create_post(author.id, "My First Post", "Content here...")

  # Automatically commits on success, rolls back on exception
end

Repository Transaction Support

When using generate_repositories: true, repositories automatically support transactions:

# Single repository transaction
MyApp::AuthorRepository.transaction do |author_repo|
  author = author_repo.create("Jane Doe", "A writer")
  author_repo.update(author.id, "Jane Smith", "Updated bio")
  # Automatically commits on success, rolls back on exception1
end

# Multiple repositories in same transaction
MyApp::Database.transaction do |tx_queries|
  author_repo = MyApp::AuthorRepository.with_transaction(tx_queries)
  book_repo = MyApp::BookRepository.with_transaction(tx_queries)

  author = author_repo.create("Jane Doe", "A writer")
  book = book_repo.create(author.id, "Her First Book", "978-1234567890")
  # All operations in same transaction
end

Roadmap

Core SQLC Features (Completed)

• Query Generation

  • :one queries (return single optional result)
  • :many queries (return array of results)
  • :exec queries (no return value)
  • :execrows queries (return affected row count)
  • :execresult queries (return DB::ExecResult)

• SQL Operations

  • SELECT queries with complex WHERE clauses
  • INSERT queries with RETURNING support
  • UPDATE queries with parameters
  • DELETE queries with parameters
  • Aggregate functions (COUNT, AVG, etc.)
  • GROUP BY and ORDER BY clauses

• Type System

  • PostgreSQL type mappings
  • MySQL type mappings
  • SQLite type mappings
  • Nullable types with Crystal union syntax
  • Array parameter handling for IN clauses

• Code Generation

  • Struct generation with DB::Serializable
  • Query method generation
  • Parameter handling with optional defaults
  • JSON/YAML serialization support
  • Struct deduplication for identical column sets

• Advanced Features

  • JOIN queries with sqlc.embed() support
  • Nested struct generation for embeds
  • Connection manager generation
  • Repository pattern generation
  • Transaction support

SQLC Features (Partial/In Progress)

• Bulk Operations
  • :copyfrom placeholder (returns stub with TODO)
  • Full bulk insert implementation for Crystal drivers

Missing SQLC Features

• Prepared Statements

  • emit_prepared_queries configuration option
  • Prepare() method generation
  • WithTx() transaction support for prepared statements

• Type Overrides

  • Custom type mappings via overrides configuration
  • Database type overrides (db_type)
  • Column-specific overrides

• Field and Struct Renaming

  • Custom field name mappings via rename configuration
  • Table struct name customization
  • Column name customization
  • Prepared statement lifecycle management
  • Performance optimizations for repeated queries

• Advanced Query Features

  • Dynamic SQL support
  • Custom scalar types
  • Enum generation from database constraints
  • Custom type mappings

• Configuration Options

  • emit_exact_table_names support
  • emit_empty_slices support
  • Custom naming conventions
  • Override built-in type mappings

Development

Building

# Install dependencies
go mod download

# Build the plugin
go build -o bin/sqlc-gen-crystal

# Run tests
go test ./...
make test-integration

Testing

The project includes comprehensive test coverage:

• Unit tests for type mappings and code generation
• Integration tests with real Crystal code compilation
• Support for PostgreSQL, MySQL, and SQLite

Contributing

Pull requests welcome! Please:

• Add tests for new functionality
• Update documentation as needed
• Follow existing code style

License

MIT - See LICENSE for details.