Documentation
¶
Overview ¶
Package sqlitemigration provides a connection pool type that guarantees a series of SQL scripts has been run once successfully before making connections available to the application. This is frequently useful for ensuring tables are created.
Example ¶
package main
import (
"context"
"fmt"
"log"
"os"
"path/filepath"
"zombiezen.com/go/sqlite"
"zombiezen.com/go/sqlite/sqlitemigration"
"zombiezen.com/go/sqlite/sqlitex"
)
func main() {
schema := sqlitemigration.Schema{
// Each element of the Migrations slice is applied in sequence. When you
// want to change the schema, add a new SQL script to this list.
//
// Existing databases will pick up at the same position in the Migrations
// slice as they last left off.
Migrations: []string{
"CREATE TABLE foo ( id INTEGER NOT NULL PRIMARY KEY );",
"ALTER TABLE foo ADD COLUMN name TEXT;",
},
// The RepeatableMigration is run after all other Migrations if any
// migration was run. It is useful for creating triggers and views.
RepeatableMigration: "DROP VIEW IF EXISTS bar;\n" +
"CREATE VIEW bar ( id, name ) AS SELECT id, name FROM foo;\n",
}
// Set up a temporary directory to store the database.
dir, err := os.MkdirTemp("", "sqlitemigration")
if err != nil {
// handle error
log.Fatal(err)
}
defer os.RemoveAll(dir)
// Open a pool. This does not block, and will start running any migrations
// asynchronously.
pool := sqlitemigration.NewPool(filepath.Join(dir, "foo.db"), schema, sqlitemigration.Options{
Flags: sqlite.OpenReadWrite | sqlite.OpenCreate,
PrepareConn: func(conn *sqlite.Conn) error {
// Enable foreign keys. See https://sqlite.org/foreignkeys.html
return sqlitex.ExecuteTransient(conn, "PRAGMA foreign_keys = ON;", nil)
},
OnError: func(e error) {
log.Println(e)
},
})
defer pool.Close()
// Get a connection. This blocks until the migration completes.
conn, err := pool.Get(context.TODO())
if err != nil {
// handle error
}
defer pool.Put(conn)
// Print the list of schema objects created.
const listSchemaQuery = `SELECT "type", "name" FROM sqlite_master ORDER BY 1, 2;`
err = sqlitex.ExecuteTransient(conn, listSchemaQuery, &sqlitex.ExecOptions{
ResultFunc: func(stmt *sqlite.Stmt) error {
fmt.Printf("%-5s %s\n", stmt.ColumnText(0), stmt.ColumnText(1))
return nil
},
})
if err != nil {
// handle error
}
}
Output: table foo view bar
Index ¶
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
Types ¶
type ConnPrepareFunc ¶
type ConnPrepareFunc = sqlitex.ConnPrepareFunc
A ConnPrepareFunc is called for each connection in a pool to set up connection-specific state. It must be safe to call from multiple goroutines.
If the ConnPrepareFunc returns an error, then it will be called the next time the connection is about to be used. Once ConnPrepareFunc returns nil for a given connection, it will not be called on that connection again.
type MigrationOptions ¶ added in v0.7.0
type MigrationOptions struct {
// If DisableForeignKeys is true, then before starting the migration's
// transaction, "PRAGMA foreign_keys = off;" will be executed. After the
// migration's transaction completes, then the "PRAGMA foreign_keys" setting
// will be restored to the value it was before executing
// "PRAGMA foreign_keys = off;".
DisableForeignKeys bool
}
MigrationOptions holds optional parameters for a migration.
type Options ¶
type Options struct {
// Flags is interpreted the same way as the argument to [sqlitex.PoolOptions].
Flags sqlite.OpenFlags
// PoolSize sets an explicit size to the pool. If less than 1, a reasonable
// default is used.
PoolSize int
// OnStartMigrate is called after the pool has successfully opened a
// connection to the database but before any migrations have been run.
OnStartMigrate SignalFunc
// OnReady is called after the pool has connected to the database and run any
// necessary migrations.
OnReady SignalFunc
// OnError is called when the pool encounters errors while applying the
// migration. This is typically used for logging errors.
OnError ReportFunc
// PrepareConn is called for each connection in the pool to set up functions
// and other connection-specific state.
PrepareConn ConnPrepareFunc
}
Options specifies optional behaviors for the pool.
type Pool ¶
type Pool struct {
// contains filtered or unexported fields
}
Pool is a pool of SQLite connections.
func (*Pool) CheckHealth ¶
CheckHealth returns an error if the migration has not completed. Closed pools may report healthy.
func (*Pool) Close ¶
Close closes all connections in the Pool, potentially interrupting a migration.
func (*Pool) Get ¶
Get obtains an SQLite connection from the pool, waiting until the initial migration is complete. Get is identical to Pool.Take.
If no connection is available, Get will block until at least one connection is returned with Pool.Put, or until either the Pool is closed or the context is canceled. If no connection can be obtained or an error occurs while preparing the connection, an error is returned.
The provided context is also used to control the execution lifetime of the connection. See sqlite.Conn.SetInterrupt for details.
Applications must ensure that all non-nil Conns returned from Get are returned to the same Pool with Pool.Put.
func (*Pool) Take ¶ added in v1.3.0
Take obtains an SQLite connection from the pool, waiting until the initial migration is complete.
If no connection is available, Take will block until at least one connection is returned with Pool.Put, or until either the Pool is closed or the context is canceled. If no connection can be obtained or an error occurs while preparing the connection, an error is returned.
The provided context is also used to control the execution lifetime of the connection. See sqlite.Conn.SetInterrupt for details.
Applications must ensure that all non-nil Conns returned from Take are returned to the same Pool with Pool.Put.
type ReportFunc ¶
type ReportFunc func(error)
A ReportFunc is called for transient errors the pool encounters while running the migrations. It must be safe to call from multiple goroutines.
type Schema ¶
type Schema struct {
// Migrations is a list of SQL scripts to run.
// Each script is wrapped in a transaction which is rolled back on any error.
Migrations []string
// MigrationOptions specifies options for each migration.
// len(MigrationOptions) must not be greater than len(Migrations).
MigrationOptions []*MigrationOptions
// AppID is saved to the database file to identify the application.
// It's an optional measure to prevent opening database files for a different application.
// It must not change between runs of the same program.
//
// A common way of setting this is with a compile-time constant that was randomly generated.
// `head -c 4 /dev/urandom | xxd -p` can generate such an ID.
AppID int32
// RepeatableMigration is a SQL script to run if any migrations ran.
// The script is run as part of the final migration's transaction.
RepeatableMigration string
}
Schema defines the migrations for the application.
Example ¶
This example constructs a schema from a set of SQL files in a directory named schema01.sql, schema02.sql, etc.
package main
import (
"errors"
"fmt"
"os"
"zombiezen.com/go/sqlite/sqlitemigration"
)
func main() {
var schema sqlitemigration.Schema
for i := 1; ; i++ {
migration, err := os.ReadFile(fmt.Sprintf("schema%02d.sql", i))
if errors.Is(err, os.ErrNotExist) {
break
}
if err != nil {
// handle error...
}
schema.Migrations = append(schema.Migrations, string(migration))
}
}
Output:
type SignalFunc ¶
type SignalFunc func()
A SignalFunc is called at most once when a particular event in a Pool's lifecycle occurs.
Source Files