liteflow

README

A Go library working with database/sql providing:

• database schema versioning with automatic updates

• connection initialization

• named statement preparation, separating SQL from Go code

A *liteflow.DB database wraps (without obscuring) a *database/sql.DB instance, adding each of the above features based on filesytem objects (io/fs.FS) passed in the options.

At the moment only SQLite is supported, but support can (and likely will) be added for other database/sql drivers if the database supports DDL statements within a transaction (e.g. PostgreSQL). This library does not plan to support drivers for databases lacking this feature (e.g. MySQL / MariaDB).

Quick Start [_quick_start]

go get pals.dev/liteflow

Assume you’ve got a project with a file structure as follows:

Project directory

.
├── main.go
├── init (init scripts run in lexical sort order)
│   ├── first.sql
│   ├── ignored.txt (ignored: does not match *.sql)
│   └── second.sql
├── queries
│   ├── query-one.sql (query name "query-one")
│   ├── query-two.sql (query name "query-two")
│   └── combined.sql (multiple queries: "combined.*")
└── versions (schema migrations)
    ├── 0001.up.sql   (from version 0 --> 1)
    ├── 0001.down.sql (from version 1 --> 0)
    ├── 0002.up.sql   (from version 1 --> 2)
    └── 0002.down.sql (from version 2 --> 1)

main.go

package main

import (
    "fmt"
    "os"

    _ "github.com/mattn/go-sqlite3"
    "pals.dev/liteflow"
)

func main() {
    db, err := sql.Open("sqlite3", "my.db")
    quitOn(err)
    lf, err := liteflow.New(db, liteflow.Options{
        VersionFS: os.Dir("versions"),
        InitFS: os.Dir("init"),
        QueryFS: os.Dir("queries"),
    })
    quitOn(err)
    // Success, we can now use the liteflow.DB instance.
    row, err := lf.QueryRow("query-one", sql.Named("param", os.Getenv("PARAM")))
    quitOn(err)
    var result int
    err = row.Scan(&result)
    quitOn(err)
    fmt.Println(result)
}

func quitOn(err error) {
    if err != nil {
        fmt.Println(err.Error())
        os.Exit(1)
    }
}

In the above example, after the *liteflow.DB instance has been created without error:

• The schema has been brought up to the latest version.

• Initialization scripts in init have been executed.

• Named SQL statements in queries have been prepared for execution.

Now you can use the *liteflow.DB just like the *database/sql.DB, but instead of passing SQL code + arguments, you pass the named query + arguments.

If errors should occur during query execution, the returned errors then include detailed information about the particular query and passed arguments, rather than only the error from the SQL driver.

Schema Versioning [_schema_versioning]

Schema migrations are SQL files within the Options.VersionFS to be executed in a transaction. Files names are recognized as migrations when all of the following are true:

• end with .sql

• have at least one number, e.g. matches regular expression /\d+/

• contain the string .up. (version increasing) or .down. (version decreasing)

The version number the file relates to is the first unbroken string of digits intepreted as a base 10 number, and ignoring leading zeroes.

The string .up. or .down. indicates whether the contents can migrate a file up to the related version, or down from the related version, respectively.

Example recognized migration names

1.up.sql                        (version: 1)
0001.down.sql                   (version: 1)
20240714-add-middle-name.up.sql (version: 20240714)
2024-07-14-my-feature.up.sql    (version: 2024)
One-2-skip.down.to-my-Lou.sql   (version: 2)
negative.-10.down.sql           (version: 10)

Examples of unrecognized migrations

one.up.sql              (no version)
12-up.sql               (contains neither .up. nor .down.)
feature-number12.up.SQL (does not end in .sql)

When performing migrations, liteflow first extracts the related version number for each migration file, and determines the current schema version. It then applies each migration file in its own transaction, in ascending order, until one of the following happens:

• There are no more migrations greater than the current schema version and less than the desired (optional) Options.MaxVersion.

• An error occurs.

Note that Options.MaxVersion equal to zero effectively means "apply all migrations".

Initialization Files [_initialization_files]

This feature was added specifically to initialize in-memory tables referenced by prepared statements.

Initialization files are run after the schema version has been brought up to date.

Files in the Options.InitFS are recognized as initialization files only if they end with .sql.

They are run in the sorted in lexical order, as returned by fs.FS.ReadDir, which is:

1. digits

2. capital letters in alphabetical order

3. lowercase letters in alphabetical order

Each initialization script is run in its own transaction, with rollback on error.

Prepared statements [_prepared_statements]

All SQL statements in the Options.QueryFS are loaded internally as a named statement which are used as a replacements for SQL in function calls.

The name for the SQL statements come from both the file name and comments in the SQL file.

1. The primary name is the file name, without the ending .sql

2. The secondary name (if any) is the first string of unbroken non-whitespace characters following name: (case insensitive) in an SQL comment line (i.e. starting with --).

A statement with the current primary (and, optionally, secondary) name is then prepared when either:

1. The next secondary name is found in the file

2. The file ends

The combined name is then either the primary name by itself, or the two names joined with a . character.

After a secondary name has been encountered, no further statements can be prepared with just the primary name.

An Example [_an_example]

File: FindPerson.sql

-- Look for a person using search text parameter
SELECT id, name FROM people WHERE name LIKE '%' || ? || '%'

-- Name: ByBirthday
SELECT id, name FROM people WHERE birthday = DATE(@Birthday)

-- name:ByStarSign skip people who don't believe in astrology
SELECT id, name FROM people
WHERE starSign = @StarSign AND believesInAstrology = @Believes

Assuming the queries match the schema without error, this prepares three statements:

• FindPerson - requires single positional parameter

• FindPerson.ByBirthday - requires single named parameter

• FindPerson.ByStarSign - requires two named parameters

Then the *liteflow.DB and *liteflow.Tx instance can run the statements using those names, rather than SQL.

*liteflow.Tx inherits from *sql.Tx, so the Commit() and Rollback() methods are there, even when not shown directly in go doc liteflow.Tx

Tests [_tests]

This library contains no tests. This is because it is designed to work with the standard database/sql package, and intentionally has no external dependencies.

To actually run tests would require loading a database driver, creating the dependency.

To get around this, a separate testing package exists at pals.dev/liteflow-test.

Documentation

Overview

Package liteflow encourages keeping SQL statements in separate files. Instead of passing SQL and arguments, you pass a statement name and arguments. To generate the named statements internally, pass an io/fs.FS (e.g.: embed.FS) which contains the SQL files.

This package can also handle database migrations for SQLite.

Index

• Constants
• type DB
  • func New(db *sql.DB, opts Options) (*DB, error)
  • func (db *DB) Begin() (*Tx, error)
  • func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)
  • func (db *DB) Downgrade(vDownTo int) (int, error)
  • func (db *DB) Exec(name string, args ...any) (sql.Result, error)
  • func (db *DB) ExecContext(ctx context.Context, name string, args ...any) (sql.Result, error)
  • func (db *DB) Query(name string, args ...any) (*sql.Rows, error)
  • func (db *DB) QueryContext(ctx context.Context, name string, args ...any) (*sql.Rows, error)
  • func (db *DB) QueryRow(name string, args ...any) (*sql.Row, error)
  • func (db *DB) QueryRowContext(ctx context.Context, name string, args ...any) (*sql.Row, error)
  • func (db *DB) Upgrade(vUpTo int) (int, error)
  • func (db *DB) Version() (int, error)
• type Options
• type Tx
  • func (tx *Tx) Exec(name string, args ...any) (sql.Result, error)
  • func (tx *Tx) ExecContext(ctx context.Context, name string, args ...any) (sql.Result, error)
  • func (tx *Tx) Query(name string, args ...any) (*sql.Rows, error)
  • func (tx *Tx) QueryContext(ctx context.Context, name string, args ...any) (*sql.Rows, error)
  • func (tx *Tx) QueryRow(name string, args ...any) (*sql.Row, error)
  • func (tx *Tx) QueryRowContext(ctx context.Context, name string, args ...any) (*sql.Row, error)

Constants

const UpgradeAll int = 0

UpgradeAll indicates to perform all database upgrades available.

const UpgradeNone int = -1

UpgradeNone indicates to skip upgrading and prepared statements.

Types

type DB

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

DB is an enhanced SQLite *sql.DB with versioning and named statements.

func New

func New(db *sql.DB, opts Options) (*DB, error)

New creates DB, which is an enhanced *sql.DB with version control and named prepared statements.

See Options documentation for the available configurations.

If the returned database is non-nil, it may still be usable even if there were errors.

func (*DB) Begin

func (db *DB) Begin() (*Tx, error)

Begin is like sql.DB.Begin, but returns a *liteflow.Tx for named queries.

func (*DB) BeginTx

func (db *DB) BeginTx(ctx context.Context, opts *sql.TxOptions) (*Tx, error)

BeginTx is like sql.DB.BeginTx, but returns a *liteflow.Tx for named queries.

func (*DB) Downgrade

func (db *DB) Downgrade(vDownTo int) (int, error)

Downgrade decrements the database to the given version. The actual version and any error are returned.

func (*DB) Exec

func (db *DB) Exec(name string, args ...any) (sql.Result, error)

Exec is sql.DB.Exec but with a query name.

func (*DB) ExecContext

func (db *DB) ExecContext(ctx context.Context, name string, args ...any) (sql.Result, error)

ExecContext is sql.DB.ExecContext but with a query name.

func (*DB) Query

func (db *DB) Query(name string, args ...any) (*sql.Rows, error)

Query is sql.DB.Query but with a query name.

func (*DB) QueryContext

func (db *DB) QueryContext(ctx context.Context, name string, args ...any) (*sql.Rows, error)

QueryContext is sql.DB.QueryContext but with a query name.

func (*DB) QueryRow

func (db *DB) QueryRow(name string, args ...any) (*sql.Row, error)

QueryRow is sql.DB.QueryRow but with a query name.

func (*DB) QueryRowContext

func (db *DB) QueryRowContext(ctx context.Context, name string, args ...any) (*sql.Row, error)

QueryRowContext is sql.DB.QueryRowContext but with a query name.

func (*DB) Upgrade

func (db *DB) Upgrade(vUpTo int) (int, error)

Upgrade increments the database to at _most_ the given version. The actual version and any error are returned. Passing a version of zero will upgrade as far as possible.

func (*DB) Version

func (db *DB) Version() (int, error)

Version returns the current database version.

type Options

type Options struct {
	// MaxVersion is the maximum database upgrade to run. The default zero value
	// indicates to run all available upgrades.
	MaxVersion int

	// NoPreload indicates to skip the normal preloading of all SQL queries into
	// sql.Stmt objects for later use. Setting this to true will not catch
	// errors in SQL statements until they are actually used.
	NoPreload bool

	// VersionFS is the sub-directory in the fs.FS which holds the numbered
	// database migration files.
	VersionFS fs.FS

	// InitFS is the filesystem holding initialization scripts, run in file
	// lexical sort order.
	InitFS fs.FS

	// QueryFS is the sub-directory in the fs.FS which holds all prepared
	// statements.
	QueryFS fs.FS
}

Options are additional options for database upgrade.

type Tx

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

Tx is like sql.Tx but uses named parameters.

func (*Tx) Exec

func (tx *Tx) Exec(name string, args ...any) (sql.Result, error)

Exec is like sql.Tx.Exec but with a query name.

func (*Tx) ExecContext

func (tx *Tx) ExecContext(ctx context.Context, name string, args ...any) (sql.Result, error)

ExecContext is like sql.Tx.ExecContext but with a query name.

func (*Tx) Query

func (tx *Tx) Query(name string, args ...any) (*sql.Rows, error)

Query is like sql.Tx.Query but with a query name.

func (*Tx) QueryContext

func (tx *Tx) QueryContext(ctx context.Context, name string, args ...any) (*sql.Rows, error)

QueryContext is like sql.Tx.QueryContext but with a query name.

func (*Tx) QueryRow

func (tx *Tx) QueryRow(name string, args ...any) (*sql.Row, error)

QueryRow is like sql.Tx.QueryRow but with a query name.

func (*Tx) QueryRowContext

func (tx *Tx) QueryRowContext(ctx context.Context, name string, args ...any) (*sql.Row, error)

QueryRowContext is like sql.Tx.QueryRowContext but with a query name.