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:

.
├── 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)

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.

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)

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]

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.

An Example [_an_example]

-- 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.

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.