sqlc-multi-db

A code generator that wraps sqlc-generated code to provide a unified interface across multiple database engines (SQLite, PostgreSQL, MySQL).

Overview

When using sqlc with multiple database backends, each engine generates its own Querier interface and model types. sqlc-multi-db reads the Querier interface from your PostgreSQL-generated package and produces:

• generated_querier.go — a common Querier interface in the parent package
• generated_models.go — common domain model types (converted from engine-specific types)
• generated_errors.go — shared sentinel errors (ErrNotFound, ErrMismatchedSlices)
• generated_wrapper_<engine>.go — one wrapper per engine, implementing the common Querier

The wrappers handle engine differences automatically:

• MySQL INSERT ... RETURNING: Simulated using LastInsertId + a GetByID query
• MySQL UPDATE ... RETURNING: Simulated using RowsAffected + a GetByID query
• Bulk operations (@bulk-for annotation): SQLite/MySQL loop over slices; PostgreSQL delegates directly
• Nullable types: sql.NullString, sql.NullInt64, etc. are converted to/from common models

Requirements

• Go 1.24+ (uses the tool directive in go.mod)
• sqlc with queries for the engines you need (e.g. query.sqlite.sql, query.postgres.sql)
• sqlc output packages named to match the --engine flags you pass (siblings of the target package)

Installation

Add sqlc-multi-db as a tool in your go.mod:

go get -tool github.com/kalbasit/sqlc-multi-db@latest

Or manually add the directives:

tool github.com/kalbasit/sqlc-multi-db

require github.com/kalbasit/sqlc-multi-db vX.Y.Z

Usage

sqlc-multi-db --engine name:package [--engine ...] /path/to/source/querier.go

The --engine flag is repeatable and takes the form name:package:

• name — engine identifier used in generated file names (e.g. sqlite, postgres, mysql)
• package — directory name of the sqlc-generated package for that engine (e.g. sqlitedb, postgresdb)

At least one --engine flag is required; the tool exits with an error if none are provided.

Examples

SQLite + PostgreSQL only:

go tool github.com/kalbasit/sqlc-multi-db --engine sqlite:sqlitedb --engine postgres:postgresdb postgresdb/querier.go

All three engines:

go tool github.com/kalbasit/sqlc-multi-db --engine sqlite:sqlitedb --engine postgres:postgresdb --engine mysql:mysqldb postgresdb/querier.go

go:generate

Add a generate.go file in your database package (e.g., pkg/database/generate.go):

package database

//go:generate go tool github.com/kalbasit/sqlc-multi-db --engine sqlite:sqlitedb --engine postgres:postgresdb postgresdb/querier.go

Then run:

go generate ./pkg/database

Expected Directory Layout

pkg/database/
  sqlitedb/        # sqlc-generated (sqlite engine)
  postgresdb/      # sqlc-generated (postgres engine)  ← source of truth
  database.go      # your Open() factory
  errors.go        # your custom errors (IsDeadlockError, etc.)
  generate.go      # //go:generate directive
  generated_errors.go           # generated
  generated_models.go           # generated
  generated_querier.go          # generated
  generated_wrapper_sqlite.go   # generated
  generated_wrapper_postgres.go # generated

Bulk Operations (@bulk-for)

sqlc does not natively support bulk inserts on MySQL/SQLite the same way PostgreSQL does with UNNEST. Use the @bulk-for annotation to declare that a query is the bulk variant of a single-row query:

-- name: AddBookTag :exec
INSERT INTO book_tags (book_id, tag_id) VALUES (?, ?);

-- name: AddBookTags :exec @bulk-for AddBookTag
INSERT INTO book_tags (book_id, tag_id)
SELECT unnest(@book_ids::bigint[]), unnest(@tag_ids::bigint[]);

The generator will:

• On PostgreSQL: delegate AddBookTags directly to the underlying sqlc implementation
• On SQLite/MySQL: generate a loop that calls AddBookTag once per element

Example

The example/ directory contains a working multi-engine project with books, tags, and book_tags tables demonstrating all supported features.

cd example

# Generate sqlc code for all three engines
sqlc generate

# Generate the multi-db wrappers
go generate ./pkg/database

# Build to verify everything compiles
go build ./...

Generated Files

All generated files start with a // Code generated by sqlc-multi-db. DO NOT EDIT. header and are prefixed with generated_. Do not edit them manually; re-run go generate instead.

Library Usage

The generator logic is also available as a library:

import "github.com/kalbasit/sqlc-multi-db/generator"

generator.Run("/path/to/postgresdb/querier.go", []generator.Engine{
    {Name: "sqlite", Package: "sqlitedb"},
    {Name: "postgres", Package: "postgresdb"},
})

License

MIT