sequel

README

Sequel

A Go library that enhances sql.DB for building SQL-backed CRUD microservices with the Microbus framework.

Features at a Glance

• Connection pool management - Prevents database exhaustion in multi-microservice solutions
• Schema migration - Concurrency-safe, incremental database migrations
• Ephemeral test databases - Isolated databases per test with automatic cleanup
• Cross-driver support - MySQL, PostgreSQL, and SQL Server with unified API

Quick Start

import "github.com/microbus-io/sequel"

// Open a database connection
db, err := sequel.Open("", "root:root@tcp(127.0.0.1:3306)/mydb")

// Run migrations
err = db.Migrate("myservice@v1", migrationFilesFS)

// Use db.DB for standard sql.DB operations
rows, err := db.Query("SELECT * FROM users WHERE tenant_id=?", tenantID)

Connection Pool Management

When many microservices connect to the same database, connection exhaustion becomes a concern. Sequel limits the connection pool of a single executable based on client count using a sqrt-based formula:

• maxIdle ≈ sqrt(N) where N is the number of clients
• maxOpen ≈ (sqrt(N) * 2) + 2

This prevents overwhelming the database while maintaining reasonable throughput.

Schema Migration

Sequel performs incremental schema migration using numbered SQL files (1.sql, 2.sql, etc.). Migrations are:

• Concurrency-safe - Distributed locking ensures only one replica executes each migration
• Tracked - A sequel_migrations table records completed migrations
• Driver-aware - Use -- DRIVER: drivername comments for driver-specific SQL

// Embed migration files
//go:embed sql/*.sql
var migrationFS embed.FS

// Run migrations (safe to call from multiple replicas)
err := db.Migrate("unique-sequence-name", migrationFS)

Example migration file with driver-specific syntax:

-- DRIVER: mysql
ALTER TABLE users MODIFY COLUMN email VARCHAR(384) NOT NULL;

-- DRIVER: pgx
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(384);

-- DRIVER: mssql
ALTER TABLE users ALTER COLUMN email NVARCHAR(384) NOT NULL;

Ephemeral Test Databases

OpenTesting creates unique databases per test, providing isolation from other tests:

func TestUserService(t *testing.T) {
    // Creates database: testing_{hour}_mydb_{testID}
    db, err := sequel.OpenTesting("", "root:root@tcp(127.0.0.1:3306)/mydb", t.Name())
    // Database is deleted when closed
    db.Close()
}

Legal

Sequel is the copyrighted work of various contributors. It is licensed to you free of charge by Microbus LLC - a Delaware limited liability company formed to hold rights to the combined intellectual property of all contributors - under the Apache License 2.0.

Documentation

Index

• func ApplyBindings(args ...any) (err error)
• func Nullify[T comparable](value T) any
• type Binder
  • func Bind[T any](binder func(value T) (err error)) *Binder[T]
  • func (n *Binder[T]) Apply() (err error)
• type DB
  • func Open(driverName string, dataSourceName string) (db *DB, err error)
  • func OpenTesting(driverName string, dataSourceName string, uniqueTestID string) (db *DB, err error)
  • func (db *DB) Close() (err error)
  • func (db *DB) ConformArgPlaceholders(stmt string) string
  • func (db *DB) DriverName() string
  • func (db *DB) Migrate(sequenceName string, fileSys fs.FS) (err error)
  • func (db *DB) NowUTC() string
  • func (db *DB) RegexpTextSearch(searchableColumns ...string) string
• type Null
  • func Nullable[T any](ptr *T) *Null[T]
• type UnsafeSQL

Functions

func ApplyBindings

func ApplyBindings(args ...any) (err error)

ApplyBindings should be called after scanning values from the result set to perform all late binding.

func Nullify

func Nullify[T comparable](value T) any

Nullify returns nil if the value equals to the zero value of its Go data type, else it returns the value. Use this construct to convert zero values to nil when writing to a nullable database column.

Example:

db.Exec(
	"INSERT INTO my_table (id, desc, modified_time) VALUES (?,?,?)",
	obj.ID,
	sequel.Nullify(obj.Description),
	sequel.Nullify(obj.ModifiedTime),
)

Types

type Binder

type Binder[T any] struct {
	sql.Null[T]
	// contains filtered or unexported fields
}

Binder is a thin wrapper over sql.Null that allows for late-binding of its value.

func Bind

func Bind[T any](binder func(value T) (err error)) *Binder[T]

Bind applies a binding function to the scanned value.

Example:

var obj Object
args := []any{
	&obj.ID,
	sequel.Bind(func(tags string) {
		return json.Unmarshal([]byte(tags), &obj.Tags)
	}),
	sequel.Bind(func(modifiedTime time.Time) {
		obj.Year, obj.Month, obj.Day = modifiedTime.Date()
		return nil
	}),
}
db.QueryRow("SELECT id, tags, modified_time FROM my_table WHERE id=?", id).Scan(args...)
sequel.ApplyBindings(args...)

func (*Binder[T]) Apply

func (n *Binder[T]) Apply() (err error)

Apply should be called after scanning the columns from the result set.

type DB

type DB struct {
	*sql.DB
	// contains filtered or unexported fields
}

DB is an enhanced database connection that

• Limits the size of the connection pool to each server to approx the sqrt of the number of clients
• Performs schema migration
• Automatically creates and connects to a localhost database while testing

func Open

func Open(driverName string, dataSourceName string) (db *DB, err error)

Open returns a database connection to the named data source.

If a driver name is not provided, it is inferred from the data source name on a best-effort basis. Drivers currently supported: "mysql" (MySQL), "pgx" (Postgres) or "mssql" (SQL Server).

Example data source name for each of the supported drivers:

• mysql: username:password@tcp(hostname:3306)/
• pgx: postgres://username:password@hostname:5432/
• mssql: sqlserver://username:password@hostname:1433

func OpenTesting

func OpenTesting(driverName string, dataSourceName string, uniqueTestID string) (db *DB, err error)

OpenTesting opens a connection to a uniquely named database for testing purposes. A database is created for each unique test at the database instance pointed to by the input DSN.

If a driver name is not provided, it is inferred from the data source name on a best-effort basis. Drivers currently supported: "mysql" (MySQL), "pgx" (Postgres) or "mssql" (SQL Server).

If a data source name is not provided, the following defaults are used based on the driver name:

• (empty): root:root@tcp(127.0.0.1:3306)/
• mysql: root:root@tcp(127.0.0.1:3306)/
• pgx: postgres://postgres:postgres@127.0.0.1:5432/
• mssql: sqlserver://sa:Password123@127.0.0.1:1433

func (*DB) Close

func (db *DB) Close() (err error)

Close closes the database connection.

func (*DB) ConformArgPlaceholders

func (db *DB) ConformArgPlaceholders(stmt string) string

ConformArgPlaceholders replaces the ? arg placeholders in a SQL statement to $1, $2 etc. for a Postgres driver.

func (*DB) DriverName

func (db *DB) DriverName() string

DriverName is the name of the driver: "mysql", "pgx" or "mssql".

func (*DB) Migrate

func (db *DB) Migrate(sequenceName string, fileSys fs.FS) (err error)

Migrate reads all #.sql files from the FS, and executes any new migrations in order of their file name. The order of execution is guaranteed only within the context of a sequence name.

func (*DB) NowUTC

func (db *DB) NowUTC() string

NowUTC is a SQL statement that returns the database server time in UTC.

func (*DB) RegexpTextSearch

func (db *DB) RegexpTextSearch(searchableColumns ...string) string

RegexpTextSearch is a SQL statement that performs a REGEXP_LIKE search on multiple columns. The statement includes a single argument placeholder ? that should be filled with a valid regular expression.

type Null

type Null[T any] struct {
	*Binder[T]
}

Null is a thin wrapper over sql.Null that allows for reading NULL values.

func Nullable

func Nullable[T any](ptr *T) *Null[T]

Nullable is a simple binder that interprets NULL values to be the zero value of their Go data type.

Example:

var obj Object
args := []any{
	&obj.ID,
	sequel.Nullable(&obj.Description),
	sequel.Nullable(&obj.ModifiedTime),
}
db.QueryRow("SELECT id, desc, modified_time FROM my_table WHERE id=?", id).Scan(args...)
sequel.ApplyBindings(args...)

type UnsafeSQL

type UnsafeSQL string

UnsafeSQL wraps a string to indicate not to use an argument placeholder when inserting it into a SQL statement. It should be used to insert values such as NOW() or calculation of other fields. Use with caution to avoid SQL injection.