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_sqlite.go — SQLite wrapper implementing the common Querier
• generated_wrapper_postgres.go — PostgreSQL wrapper implementing the common Querier
• generated_wrapper_mysql.go — MySQL/MariaDB wrapper 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 all three engines: query.sqlite.sql, query.postgres.sql, query.mysql.sql
• sqlc output packages named sqlitedb, postgresdb, mysqldb (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

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 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
  mysqldb/         # sqlc-generated (mysql engine)
  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
  generated_wrapper_mysql.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")

License

MIT