Multi-Driver

Build applications that can run with multiple database drivers using db.DialectSQL, SQL files, and the shared DB API.

The shared db package can be used in applications that support more than one database driver.

A multi-driver application keeps repository code mostly independent from the selected database engine, while still keeping SQL explicit.

This is useful when:

the same application can run with MySQL or PostgreSQL
different tenants use different database engines
tests run against a different driver than production
a migration or tooling package needs to support multiple databases
Scylla and SQL databases share some application-level workflows

Basic Idea

Multi-driver support is built around three concepts:

Concept	Purpose
`db.Conn`	Runtime connection interface implemented by each driver
`db.DialectSQL`	Holds SQL or CQL variants for supported drivers
`db.Dialect`	Selects the correct SQL or CQL for the active connection

Instead of hard-coding one query string in repository code, keep driver-specific query text in a db.DialectSQL value.

type Queries struct {
	GetUser db.DialectSQL `json:"GetUser"`
}

Then execute the query through the active connection.

user, found, err := db.GetDialect[User](ctx, conn, queries.GetUser, id)

The active driver decides which SQL or CQL string is used.

DialectSQL

db.DialectSQL stores one query per supported driver.

type DialectSQL struct {
	Postgres   string `json:"postgres"`
	Mysql      string `json:"mysql"`
	Scylla     string `json:"scylla"`
}

Example:

var getUser = db.DialectSQL{
	Mysql: `
		SELECT *
		FROM users
		WHERE id = ?
		LIMIT 1
	`,
	Postgres: `
		SELECT *
		FROM users
		WHERE id = $1
		LIMIT 1
	`,
	Scylla: `
		SELECT *
		FROM users_by_id
		WHERE id = ?
	`,
}

Use the same repository function with different connections.

func GetUser(ctx context.Context, conn db.Conn, id any) (User, bool, error) {
	return db.GetDialect[User](ctx, conn, getUser, id)
}

Why Dialect SQL Exists

The shared db API is the same across drivers, but SQL syntax is not always the same.

Examples:

Topic	MySQL	Postgres	Scylla
Placeholders	`?`	`$1`, `$2`	`?`
Insert ID	`LastInsertId()`	`RETURNING id`	application-generated ID
Query tables	usually normalized tables	usually normalized tables	query-driven tables
SQL file	`model.sql`	`model.psql`	`model.cql`

db.DialectSQL keeps those differences explicit.

It does not try to translate SQL automatically.

Using db.Dialect

Use db.Dialect when you want to build a db.Query manually.

q, err := db.Dialect(conn, queries.GetUser, id)
if err != nil {
	return User{}, false, err
}

return db.GetQuery[User](ctx, conn, q)

This is useful when:

you want low-level query control
you want to log or inspect db.Query
you want to pass the query into another helper
you want to use ExecQuery, GetQuery, ValueQuery, or MapsQuery

High-Level Dialect Helpers

For most code, use the high-level dialect helpers.

users, err := db.ListDialect[User](ctx, conn, queries.ListUsers, limit)

user, found, err := db.GetDialect[User](ctx, conn, queries.GetUser, id)

total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers)

rows, err := db.MapsDialect(ctx, conn, queries.Report, from, to)

These helpers select the correct query and execute it in one step.

Query Model

A common pattern is to define a Queries struct.

type Queries struct {
	ListUsers  db.DialectSQL `json:"ListUsers"`
	GetUser    db.DialectSQL `json:"GetUser"`
	CountUsers db.DialectSQL `json:"CountUsers"`
}

Then load it once during application startup.

func LoadQueries(conn db.Conn) (Queries, error) {
	var queries Queries

	if err := db.LoadModel(conn, ".", &queries); err != nil {
		return Queries{}, err
	}

	return queries, nil
}

After loading, repository functions can accept the loaded queries.

func ListUsers(ctx context.Context, conn db.Conn, queries Queries) ([]User, error) {
	return db.ListDialect[User](ctx, conn, queries.ListUsers)
}

SQL Files

Multi-driver applications often keep driver-specific queries in separate files.

model.sql  -> MySQL
model.psql -> PostgreSQL
model.cql  -> Scylla

Each file can contain the same section names with driver-specific query text.

Example model.sql:

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC

--GetUser
SELECT *
FROM users
WHERE id = ?
LIMIT 1

--CountUsers
SELECT COUNT(*)
FROM users

Example model.psql:

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC

--GetUser
SELECT *
FROM users
WHERE id = $1
LIMIT 1

--CountUsers
SELECT COUNT(*)
FROM users

Example model.cql:

--ListUsers
SELECT *
FROM users_by_created_at

--GetUser
SELECT *
FROM users_by_id
WHERE id = ?

--CountUsers
SELECT COUNT(*)
FROM users_count

db.LoadModel reads the file for the active driver and fills the matching fields in your Queries struct.

Repository Example

package main

import (
	"context"
	"time"

	"github.com/netlifeguru/db"
)

type User struct {
	ID        any       `db:"id"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Active    bool      `db:"active"`
	CreatedAt time.Time `db:"created_at"`
}

type Queries struct {
	ListUsers  db.DialectSQL `json:"ListUsers"`
	GetUser    db.DialectSQL `json:"GetUser"`
	CountUsers db.DialectSQL `json:"CountUsers"`
}

func LoadQueries(conn db.Conn) (Queries, error) {
	var queries Queries

	if err := db.LoadModel(conn, ".", &queries); err != nil {
		return Queries{}, err
	}

	return queries, nil
}

func ListUsers(ctx context.Context, conn db.Conn, queries Queries) ([]User, error) {
	return db.ListDialect[User](ctx, conn, queries.ListUsers)
}

func GetUser(ctx context.Context, conn db.Conn, queries Queries, id any) (User, bool, error) {
	return db.GetDialect[User](ctx, conn, queries.GetUser, id)
}

func CountUsers(ctx context.Context, conn db.Conn, queries Queries) (int64, bool, error) {
	return db.ValueDialect[int64](ctx, conn, queries.CountUsers)
}

Application Example

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/joho/godotenv"
)

func main() {
	ctx := context.Background()

	err := godotenv.Load()
	if err != nil {
		log.Println(".env file not found, I'm using system env variables")
	}

	conn, err := connectDB()
	if err != nil {
		log.Fatal(err)
	}

	queries, err := LoadQueries(conn)
	if err != nil {
		log.Fatal(err)
	}

	users, err := ListUsers(ctx, conn, queries)
	if err != nil {
		log.Fatal(err)
	}

	for _, user := range users {
		fmt.Println(user.ID, user.Name, user.Email)
	}
}

Multi-Tenant Drivers

A multi-tenant application may select a different driver or connection per tenant.

For example:

func TenantConn(ctx context.Context, tenant Tenant) (db.Conn, error) {
	switch tenant.Driver {
	case "mysql":
		return connectMySQLTenant(tenant)
	case "postgres":
		return connectPostgreSQLTenant(tenant)
	case "scylla":
		return connectScyllaTenant(tenant)
	default:
		return nil, errors.New("unsupported tenant driver")
	}
}

Repository code can still depend on db.Conn.

func HandleTenantRequest(ctx context.Context, tenant Tenant) error {
	conn, err := TenantConn(ctx, tenant)
	if err != nil {
		return err
	}

	queries, err := LoadQueries(conn)
	if err != nil {
		return err
	}

	users, err := ListUsers(ctx, conn, queries)
	if err != nil {
		return err
	}

	fmt.Println(len(users))
	return nil
}

The repository does not need to know which concrete driver was selected.

Inserts in Multi-Driver Code

Inserts often differ more than selects.

For example:

MySQL commonly uses LastInsertId
PostgreSQL commonly uses RETURNING id
Scylla commonly generates IDs in application code and writes query tables

For shared insert flows, use driver-specific db.DialectSQL and choose the appropriate execution helper.

MySQL-style insert:

q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
	return 0, err
}

result, err := db.ExecQuery(ctx, conn, q)
if err != nil {
	return 0, err
}

return result.LastInsertId(), nil

PostgreSQL-style insert:

q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
	return 0, err
}

id, found, err := db.ValueQuery[int64](ctx, conn, q)
if err != nil {
	return 0, err
}

if !found {
	return 0, errors.New("insert did not return id")
}

return id, nil

Scylla-style insert:

id := gocql.TimeUUID()

q, err := db.Dialect(conn, queries.InsertUserByID, id, email, name, active, createdAt)
if err != nil {
	return "", err
}

if _, err := db.ExecQuery(ctx, conn, q); err != nil {
	return "", err
}

return id.String(), nil

When to Use Multi-Driver Patterns

Use multi-driver patterns when:

your app can run on different database engines
you use SQL files for multiple drivers
repository code should remain driver-independent
each tenant may use a different database engine
you need explicit SQL per driver without duplicating Go code

When Not to Use Them

Do not add multi-driver abstractions if your application will only ever use one database.

For a single driver, simple raw helpers are often clearer.

users, err := db.List[User](ctx, conn, `
	SELECT *
	FROM users
`)

Use dialect SQL when it solves a real driver-selection problem.

Standalone examples are available in the examples repository:

PostgreSQL multi-driver SQL files

The shared db package can be used in applications that support more than one database driver.

A multi-driver application keeps repository code mostly independent from the selected database engine, while still keeping SQL explicit.

This is useful when:

the same application can run with MySQL or PostgreSQL
different tenants use different database engines
tests run against a different driver than production
a migration or tooling package needs to support multiple databases
Scylla and SQL databases share some application-level workflows

Basic Idea

Multi-driver support is built around three concepts:

Concept	Purpose
`db.Conn`	Runtime connection interface implemented by each driver
`db.DialectSQL`	Holds SQL or CQL variants for supported drivers
`db.Dialect`	Selects the correct SQL or CQL for the active connection

Instead of hard-coding one query string in repository code, keep driver-specific query text in a db.DialectSQL value.

type Queries struct {
	GetUser db.DialectSQL `json:"GetUser"`
}

Then execute the query through the active connection.

user, found, err := db.GetDialect[User](ctx, conn, queries.GetUser, id)

The active driver decides which SQL or CQL string is used.

DialectSQL

db.DialectSQL stores one query per supported driver.

type DialectSQL struct {
	Postgres   string `json:"postgres"`
	Mysql      string `json:"mysql"`
	Scylla     string `json:"scylla"`
}

Example:

var getUser = db.DialectSQL{
	Mysql: `
		SELECT *
		FROM users
		WHERE id = ?
		LIMIT 1
	`,
	Postgres: `
		SELECT *
		FROM users
		WHERE id = $1
		LIMIT 1
	`,
	Scylla: `
		SELECT *
		FROM users_by_id
		WHERE id = ?
	`,
}

Use the same repository function with different connections.

func GetUser(ctx context.Context, conn db.Conn, id any) (User, bool, error) {
	return db.GetDialect[User](ctx, conn, getUser, id)
}

Why Dialect SQL Exists

The shared db API is the same across drivers, but SQL syntax is not always the same.

Examples:

Topic	MySQL	Postgres	Scylla
Placeholders	`?`	`$1`, `$2`	`?`
Insert ID	`LastInsertId()`	`RETURNING id`	application-generated ID
Query tables	usually normalized tables	usually normalized tables	query-driven tables
SQL file	`model.sql`	`model.psql`	`model.cql`

db.DialectSQL keeps those differences explicit.

It does not try to translate SQL automatically.

Using db.Dialect

Use db.Dialect when you want to build a db.Query manually.

q, err := db.Dialect(conn, queries.GetUser, id)
if err != nil {
	return User{}, false, err
}

return db.GetQuery[User](ctx, conn, q)

This is useful when:

you want low-level query control
you want to log or inspect db.Query
you want to pass the query into another helper
you want to use ExecQuery, GetQuery, ValueQuery, or MapsQuery

High-Level Dialect Helpers

For most code, use the high-level dialect helpers.

users, err := db.ListDialect[User](ctx, conn, queries.ListUsers, limit)

user, found, err := db.GetDialect[User](ctx, conn, queries.GetUser, id)

total, found, err := db.ValueDialect[int64](ctx, conn, queries.CountUsers)

rows, err := db.MapsDialect(ctx, conn, queries.Report, from, to)

These helpers select the correct query and execute it in one step.

Query Model

A common pattern is to define a Queries struct.

type Queries struct {
	ListUsers  db.DialectSQL `json:"ListUsers"`
	GetUser    db.DialectSQL `json:"GetUser"`
	CountUsers db.DialectSQL `json:"CountUsers"`
}

Then load it once during application startup.

func LoadQueries(conn db.Conn) (Queries, error) {
	var queries Queries

	if err := db.LoadModel(conn, ".", &queries); err != nil {
		return Queries{}, err
	}

	return queries, nil
}

After loading, repository functions can accept the loaded queries.

func ListUsers(ctx context.Context, conn db.Conn, queries Queries) ([]User, error) {
	return db.ListDialect[User](ctx, conn, queries.ListUsers)
}

SQL Files

Multi-driver applications often keep driver-specific queries in separate files.

model.sql  -> MySQL
model.psql -> PostgreSQL
model.cql  -> Scylla

Each file can contain the same section names with driver-specific query text.

Example model.sql:

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC

--GetUser
SELECT *
FROM users
WHERE id = ?
LIMIT 1

--CountUsers
SELECT COUNT(*)
FROM users

Example model.psql:

--ListUsers
SELECT *
FROM users
ORDER BY created_at DESC

--GetUser
SELECT *
FROM users
WHERE id = $1
LIMIT 1

--CountUsers
SELECT COUNT(*)
FROM users

Example model.cql:

--ListUsers
SELECT *
FROM users_by_created_at

--GetUser
SELECT *
FROM users_by_id
WHERE id = ?

--CountUsers
SELECT COUNT(*)
FROM users_count

db.LoadModel reads the file for the active driver and fills the matching fields in your Queries struct.

Repository Example

package main

import (
	"context"
	"time"

	"github.com/netlifeguru/db"
)

type User struct {
	ID        any       `db:"id"`
	Name      string    `db:"name"`
	Email     string    `db:"email"`
	Active    bool      `db:"active"`
	CreatedAt time.Time `db:"created_at"`
}

type Queries struct {
	ListUsers  db.DialectSQL `json:"ListUsers"`
	GetUser    db.DialectSQL `json:"GetUser"`
	CountUsers db.DialectSQL `json:"CountUsers"`
}

func LoadQueries(conn db.Conn) (Queries, error) {
	var queries Queries

	if err := db.LoadModel(conn, ".", &queries); err != nil {
		return Queries{}, err
	}

	return queries, nil
}

func ListUsers(ctx context.Context, conn db.Conn, queries Queries) ([]User, error) {
	return db.ListDialect[User](ctx, conn, queries.ListUsers)
}

func GetUser(ctx context.Context, conn db.Conn, queries Queries, id any) (User, bool, error) {
	return db.GetDialect[User](ctx, conn, queries.GetUser, id)
}

func CountUsers(ctx context.Context, conn db.Conn, queries Queries) (int64, bool, error) {
	return db.ValueDialect[int64](ctx, conn, queries.CountUsers)
}

Application Example

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/joho/godotenv"
)

func main() {
	ctx := context.Background()

	err := godotenv.Load()
	if err != nil {
		log.Println(".env file not found, I'm using system env variables")
	}

	conn, err := connectDB()
	if err != nil {
		log.Fatal(err)
	}

	queries, err := LoadQueries(conn)
	if err != nil {
		log.Fatal(err)
	}

	users, err := ListUsers(ctx, conn, queries)
	if err != nil {
		log.Fatal(err)
	}

	for _, user := range users {
		fmt.Println(user.ID, user.Name, user.Email)
	}
}

Multi-Tenant Drivers

A multi-tenant application may select a different driver or connection per tenant.

For example:

func TenantConn(ctx context.Context, tenant Tenant) (db.Conn, error) {
	switch tenant.Driver {
	case "mysql":
		return connectMySQLTenant(tenant)
	case "postgres":
		return connectPostgreSQLTenant(tenant)
	case "scylla":
		return connectScyllaTenant(tenant)
	default:
		return nil, errors.New("unsupported tenant driver")
	}
}

Repository code can still depend on db.Conn.

func HandleTenantRequest(ctx context.Context, tenant Tenant) error {
	conn, err := TenantConn(ctx, tenant)
	if err != nil {
		return err
	}

	queries, err := LoadQueries(conn)
	if err != nil {
		return err
	}

	users, err := ListUsers(ctx, conn, queries)
	if err != nil {
		return err
	}

	fmt.Println(len(users))
	return nil
}

The repository does not need to know which concrete driver was selected.

Inserts in Multi-Driver Code

Inserts often differ more than selects.

For example:

MySQL commonly uses LastInsertId
PostgreSQL commonly uses RETURNING id
Scylla commonly generates IDs in application code and writes query tables

For shared insert flows, use driver-specific db.DialectSQL and choose the appropriate execution helper.

MySQL-style insert:

q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
	return 0, err
}

result, err := db.ExecQuery(ctx, conn, q)
if err != nil {
	return 0, err
}

return result.LastInsertId(), nil

PostgreSQL-style insert:

q, err := db.Dialect(conn, queries.InsertUser, name, email, active)
if err != nil {
	return 0, err
}

id, found, err := db.ValueQuery[int64](ctx, conn, q)
if err != nil {
	return 0, err
}

if !found {
	return 0, errors.New("insert did not return id")
}

return id, nil

Scylla-style insert:

id := gocql.TimeUUID()

q, err := db.Dialect(conn, queries.InsertUserByID, id, email, name, active, createdAt)
if err != nil {
	return "", err
}

if _, err := db.ExecQuery(ctx, conn, q); err != nil {
	return "", err
}

return id.String(), nil

When to Use Multi-Driver Patterns

Use multi-driver patterns when:

your app can run on different database engines
you use SQL files for multiple drivers
repository code should remain driver-independent
each tenant may use a different database engine
you need explicit SQL per driver without duplicating Go code

When Not to Use Them

Do not add multi-driver abstractions if your application will only ever use one database.

For a single driver, simple raw helpers are often clearer.

users, err := db.List[User](ctx, conn, `
	SELECT *
	FROM users
`)

Use dialect SQL when it solves a real driver-selection problem.

Standalone examples are available in the examples repository:

PostgreSQL multi-driver SQL files

Multi-Driver

Basic Idea

DialectSQL

Why Dialect SQL Exists

Using db.Dialect

High-Level Dialect Helpers

Query Model

SQL Files

Repository Example

Application Example

Multi-Tenant Drivers

Inserts in Multi-Driver Code

When to Use Multi-Driver Patterns

When Not to Use Them

On this page

Multi-Driver

Basic Idea

DialectSQL

Why Dialect SQL Exists

Using db.Dialect

High-Level Dialect Helpers

Query Model

SQL Files

Repository Example

Application Example

Multi-Tenant Drivers

Inserts in Multi-Driver Code

When to Use Multi-Driver Patterns

When Not to Use Them

On this page