### Quick Start SQL Migration File

Source: https://pressly.github.io/goose/blog/2022/overview-sql-file

A simple SQL migration file demonstrating the Up and Down annotations. Use this as a starting point for your database migrations.

```sql
-- +goose Up
SELECT 'up SQL query';

-- +goose Down
SELECT 'down SQL query';

```

--------------------------------

### Basic Goose Migration Example

Source: https://pressly.github.io/goose/documentation/annotations

A standard Goose migration example showing 'up' and 'down' statements for creating and dropping a users table, including multiple inserts.

```sql
-- +goose up
CREATE TABLE users (
    id int NOT NULL PRIMARY KEY,
    username text
);


INSERT INTO "users" ("id", "name") VALUES (1, 'gallant_almeida7');
INSERT INTO "users" ("id", "name") VALUES (2, 'brave_spence8');
.
.
INSERT INTO "users" ("id", "name") VALUES (99999, 'jovial_chaum1');
INSERT INTO "users" ("id", "name") VALUES (100000, 'goofy_ptolemy0');

-- +goose down
DROP TABLE users;

```

--------------------------------

### Install Goose using Go on Windows

Source: https://pressly.github.io/goose/installation

Install Goose on Windows using the 'go install' command, ensuring you have Go version 1.16 or later. This command installs the latest version of the Goose command-line tool.

```bash
go install github.com/pressly/goose/v3/cmd/goose@latest

```

--------------------------------

### Run ClickHouse Docker Container

Source: https://pressly.github.io/goose/blog/2022/improving-clickhouse

Starts a ClickHouse server instance in a Docker container. Ensure Docker is installed and running.

```bash
docker run --rm -d \
    -e CLICKHOUSE_DB=clickdb \
    -e CLICKHOUSE_USER=clickuser \
    -e CLICKHOUSE_DEFAULT_ACCESS_MANAGEMENT=1 \
    -e CLICKHOUSE_PASSWORD=password1 \
    -p 9000:9000/tcp clickhouse/clickhouse-server:22-alpine

```

--------------------------------

### Run Migrations with Provider

Source: https://pressly.github.io/goose/blog/archive/2023

After initializing the provider, call methods like Up() to apply pending database migrations. This example runs all migrations that have not yet been applied.

```go
results, err := provider.Up(ctx)
```

--------------------------------

### Install Goose on Linux using install.sh

Source: https://pressly.github.io/goose/installation

Execute this command on Linux systems to download and install the Goose binary using the provided installation script. The default installation directory is /usr/local/bin.

```bash
curl -fsSL \
    https://raw.githubusercontent.com/pressly/goose/master/install.sh |
    sh 

```

--------------------------------

### Example Verbose Migration Output

Source: https://pressly.github.io/goose/documentation/provider

Illustrates the output when running migrations with verbose logging enabled, showing the status and duration of each migration script.

```text
goose: OK    up 00001_users_table.sql (1.32ms)
goose: OK    up 00002_add_users.go (638.96µs)
goose: OK    up 00003_count_users.go (561.58µs)
goose: successfully migrated database, current version: 3
```

--------------------------------

### SQL Query for Listing Users

Source: https://pressly.github.io/goose/blog/2024/goose-sqlc

An example SQL query file for sqlc to generate Go code. This query selects all users ordered by username.

```sql
-- name: ListUsers :many
SELECT * FROM users ORDER BY username;

```

--------------------------------

### Build Goose from Source

Source: https://pressly.github.io/goose/installation

Clone the Goose repository and build the binary from source. This method requires Git and Go (1.16+) to be installed. It includes all supported drivers and produces a larger binary.

```bash
git clone https://github.com/pressly/goose
cd goose
go mod tidy
go build -o goose ./cmd/goose

./goose --version
# goose version:(devel)

```

--------------------------------

### Build a Lite Goose Version for SQLite

Source: https://pressly.github.io/goose/installation

Create a smaller Goose binary by using build tags to exclude unnecessary drivers, targeting only SQLite in this example. This reduces the binary size significantly.

```bash
go build \
    -tags='no_postgres no_clickhouse no_mssql no_mysql' \
    -o goose ./cmd/goose

```

--------------------------------

### Goose Status Output Example

Source: https://pressly.github.io/goose/blog/2021/visualizing-up-down-commands

Displays the current migration status, showing applied and pending migrations.

```bash
Applied At                  Migration
=======================================
Sun Dec 19 20:09:48 2021 -- 00001_a.sql
Sun Dec 19 20:09:48 2021 -- 00002_b.sql
Sun Dec 19 20:09:48 2021 -- 00003_c.sql
Sun Dec 19 20:09:48 2021 -- 00004_d.sql
Sun Dec 19 20:09:48 2021 -- 00005_e.sql
Sun Dec 19 20:09:48 2021 -- 00006_f.sql
Sun Dec 19 20:09:48 2021 -- 00007_g.sql
Sun Dec 19 20:09:48 2021 -- 00008_h.sql
Pending                  -- 00009_i.sql
Pending                  -- 00010_j.sql
Pending                  -- 00011_k.sql


```

--------------------------------

### Install Goose with Homebrew on macOS

Source: https://pressly.github.io/goose/installation

Use this command to install Goose if you are using macOS and have Homebrew installed. This is the easiest method for Mac users.

```bash
brew install goose

```

--------------------------------

### Install a Specific Goose Version on Linux

Source: https://pressly.github.io/goose/installation

This command installs a specific version of Goose on Linux by setting the GOOSE_INSTALL environment variable to change the default output directory and specifying the version as an argument to the install script.

```bash
curl -fsSL \
    https://raw.githubusercontent.com/pressly/goose/master/install.sh |
    GOOSE_INSTALL=$HOME/.goose sh -s v3.5.0

```

--------------------------------

### Annotation Whitespace Rules

Source: https://pressly.github.io/goose/blog/2022/overview-sql-file

Annotations must not have leading whitespace. This example shows a valid annotation with a checkmark and an invalid one with leading spaces.

```sql
-- +goose Up ✅
    -- +goose Up ❌ (error because leading whitespace)

```

--------------------------------

### sqlc Generate Command Output: Relation Error

Source: https://pressly.github.io/goose/blog/2024/goose-sqlc

Example output from 'sqlc generate' indicating a 'relation "usres" does not exist' error, highlighting compile-time error detection.

```bash
$ sqlc generate
# package
data/sql/queries/users.sql:1:1: relation "usres" does not exist

```

--------------------------------

### Invalid Down Annotation Order

Source: https://pressly.github.io/goose/blog/2022/overview-sql-file

The -- +goose Down annotation must appear after the -- +goose Up annotation within the same file. This example shows an invalid ordering.

```sql
-- +goose Down
SELECT 'down SQL query';

-- +goose Up
SELECT 'up SQL query';

```

--------------------------------

### Build and Run Embedded Goose Binary

Source: https://pressly.github.io/goose/blog/2021/embed-sql-migrations

Demonstrates how to build a goose binary with embedded migrations and run it to create a database file. This proves the binary has no external file dependencies.

```bash
go build -o goosey internal/goose/main.go
mv goosey $HOME
cd $HOME
./goosey
```

--------------------------------

### Create a Goose Provider Instance

Source: https://pressly.github.io/goose/blog/2023/goose-provider

Instantiate a new Goose Provider with a specified dialect, database connection, and migration source. The migration source can be a directory on disk or embedded within the binary.

```go
provider, err := goose.NewProvider(
  goose.DialectPostgres, 
  db, 
  os.DirFS("migrations"), 
)
```

--------------------------------

### Create Goose Provider with Postgres Dialect

Source: https://pressly.github.io/goose/blog

Instantiate a Goose provider for PostgreSQL. Requires a database connection and a source for migration files, such as os.DirFS.

```go
provider, err := goose.NewProvider(
  goose.DialectPostgres, 
  db,
  os.DirFS("migrations"),
)
```

--------------------------------

### Initialize Goose Provider

Source: https://pressly.github.io/goose/blog/archive/2023

Use this to create a new Goose provider instance. Ensure you have Goose v3.16.0 or later. The database connection must implement the database/sql interface.

```go
provider, err := goose.NewProvider(
  goose.DialectPostgres, 
  db,
  os.DirFS("migrations"), 
)
```

--------------------------------

### sqlc Configuration: sqlc.yaml

Source: https://pressly.github.io/goose/blog/2024/goose-sqlc

Minimal sqlc configuration file specifying schema location, query directory, database engine, and Go output path.

```yaml
version: "2"
sql:
  - schema: "data/sql/migrations"
    queries: "data/sql/queries"
    engine: "sqlite"
    gen:
      go:
        out: "gen/dbstore"

```

--------------------------------

### Generate Go Code with sqlc

Source: https://pressly.github.io/goose/blog/2024/goose-sqlc

Run the `sqlc generate` command to produce Go code from your SQL schema. This command creates a `gen/dbstore` directory containing the generated Go files.

```bash
$ sqlc generate

```

--------------------------------

### Create Sequential Migration File Command

Source: https://pressly.github.io/goose/blog/2022/overview-sql-file

Use the `goose create -s` command to generate a new SQL migration file prefixed with a sequential number instead of a timestamp.

```bash
$ goose -dir migrations -s create add_users_table sql 
The -s flag instructs goose to create new migration files in **sequential** order. Timestamp
    is the default.

migrations/00001_add_users_table.sql

```

--------------------------------

### Initialize Goose Provider

Source: https://pressly.github.io/goose/documentation/provider

Initializes a new Goose provider with the specified dialect, database connection, filesystem, and optional configurations. Ensure the dialect matches the database driver.

```go
func NewProvider(
    dialect Dialect,
    db *sql.DB,
    fsys fs.FS,
    opts ...ProviderOption,
) (*Provider, error){
    // ...
}
```

--------------------------------

### NewProvider

Source: https://pressly.github.io/goose/documentation/provider

Initializes a new Goose provider. This is the entry point for using the Goose library to manage database migrations.

```APIDOC
## NewProvider

### Description
Initializes a new Goose provider with the specified dialect, database connection, filesystem, and optional configurations.

### Signature
```go
func NewProvider(
    dialect Dialect,
    db *sql.DB,
    fsys fs.FS,
    opts ...ProviderOption,
) (*Provider, error)
```

### Parameters

*   **dialect** (Dialect) - The SQL dialect for the database.
*   **db** (*sql.DB) - The database connection to use for running migrations.
*   **fsys** (fs.FS) - The filesystem abstraction for reading migration files.
*   **opts** (...ProviderOption) - Optional functional options to configure the provider.
```

--------------------------------

### Create users table and insert data (SQL)

Source: https://pressly.github.io/goose/blog/2022/overview-sql-file

This SQL snippet defines the 'users' table and includes a series of INSERT statements. It demonstrates a migration that sends each statement individually, leading to potential performance issues with a large number of inserts.

```sql
-- +goose Up
CREATE TABLE users (
    id int NOT NULL PRIMARY KEY,
    username text,
    name text,
    surname text
);




This is a contrived example. Normally this would be a set of batched INSERT's with multiple
    column values, each enclosed with parentheses and separated by commas, like so:

INSERT INTO "users" ("id", "username", "name", "surname")
VALUES
  (1, 'gallant_almeida7', 'Gallant', 'Almeida7'),
  (2, 'brave_spence8', 'Brave', 'Spence8');

INSERT INTO "users" ("id", "username", "name", "surname") VALUES (1, 'gallant_almeida7', 'Gallant', 'Almeida7'); INSERT INTO "users" ("id", "username", "name", "surname") VALUES (2, 'brave_spence8', 'Brave', 'Spence8'); . . INSERT INTO "users" ("id", "username", "name", "surname") VALUES (99999, 'jovial_chaum1', 'Jovial', 'Chaum1'); INSERT INTO "users" ("id", "username", "name", "surname") VALUES (100000, 'goofy_ptolemy0', 'Goofy', 'Ptolemy0'); -- +goose Down
DROP TABLE users; 
```

--------------------------------

### Show Tables in ClickHouse Database

Source: https://pressly.github.io/goose/blog/2022/improving-clickhouse

Uses the `clickhouse-client` to list all tables in the specified ClickHouse database. Requires database credentials and connection details.

```bash
clickhouse-client --vertical \
    --database clickdb --password password1 -u clickuser \
    -q 'SHOW TABLES'

```

--------------------------------

### Enable Verbose Logging

Source: https://pressly.github.io/goose/documentation/provider

Configures the provider to log detailed information during the migration process. This is useful for debugging and monitoring migration execution.

```go
func WithVerbose(b bool)
```

--------------------------------

### Basic Goose Annotations

Source: https://pressly.github.io/goose/documentation/annotations

Illustrates the fundamental -- +goose up and -- +goose down annotations for defining migration scripts. Ensure annotations are on their own lines without leading whitespace.

```sql
-- +goose up
SELECT 'up SQL query';

-- +goose down
SELECT 'down SQL query';
```

--------------------------------

### Using a Custom Store with Goose Provider

Source: https://pressly.github.io/goose/documentation/custom-store

Pass a custom store implementation to `goose.NewProvider` using the `goose.WithStore` option. Ensure the first argument to `goose.NewProvider` is an empty string when using a custom store.

```go
provider, err := goose.NewProvider(
    "",
    db,
    migrations.Embed,
    // Use custom store implementation.
    goose.WithStore(memory.New("goose_migrations")),
)
if err != nil {
    return err
}

```

--------------------------------

### Batching Inserts with Goose Annotations

Source: https://pressly.github.io/goose/documentation/annotations

Demonstrates using '-- +goose statementbegin' and '-- +goose statementend' to batch multiple INSERT statements into a single database command for improved performance.

```sql
-- +goose up
CREATE TABLE users (
    id int NOT NULL PRIMARY KEY,
    username text,
    name text,
    surname text
);

-- +goose statementbegin
INSERT INTO "users" ("id", "username") VALUES (1, 'gallant_almeida7');
INSERT INTO "users" ("id", "username") VALUES (2, 'brave_spence8');
.
.
INSERT INTO "users" ("id", "username") VALUES (99999, 'jovial_chaum1');
INSERT INTO "users" ("id", "username") VALUES (100000, 'goofy_ptolemy0');
-- +goose statementend

-- +goose down
DROP TABLE users;

```

--------------------------------

### Use Sub-directory for Migrations

Source: https://pressly.github.io/goose/documentation/provider

Creates a filesystem that points to a sub-directory within an embedded filesystem, typically used for organizing migration files.

```go
fsys, err := fs.Sub(embeddedFS, "migrations")
```

--------------------------------

### Create Timestamp Migration File Command

Source: https://pressly.github.io/goose/blog/2022/overview-sql-file

Use the `goose create` command to generate a new SQL migration file prefixed with a timestamp. This is the default behavior.

```bash
$ goose -dir migrations create add_users_table sql
Created new file: migrations/20230201093158_add_users_table.sql

```

--------------------------------

### Provider Up Method Signature

Source: https://pressly.github.io/goose/blog/2023/goose-provider

The Up method of the Provider now returns a slice of MigrationResult pointers and an error, allowing for detailed handling of migration outcomes.

```go
func (p *Provider) Up(ctx context.Context) ([]*MigrationResult, error) {
```

--------------------------------

### Add Go Migrations

Source: https://pressly.github.io/goose/documentation/provider

Registers custom Go migrations with the provider. Migrations must be created using the `NewGoMigration` constructor.

```go
func WithGoMigrations(migrations ...*Migration)
```

--------------------------------

### Create a Go Migration

Source: https://pressly.github.io/goose/documentation/provider

Constructs a new Go migration object. Both up and down functions can be nil, allowing for version recording without executing code.

```go
func NewGoMigration(version int64, up, down *GoFunc) *Migration
```

--------------------------------

### Embed SQL Migrations using embed.FS

Source: https://pressly.github.io/goose/blog/2021/embed-sql-migrations

Use the `//go:embed` directive to embed SQL files from a specified directory into an `embed.FS` variable. This variable can then be passed to `goose.SetBaseFS` to instruct goose to use the embedded filesystem instead of the OS filesystem.

```go
package main

import (
    "database/sql"
    "embed"
    "log"

    _ "github.com/mattn/go-sqlite3"
    "github.com/pressly/goose/v3"
)

//go:embed migrations/*.sql
var embedMigrations embed.FS // 


This //go:embed is a special directive that tells the Go tooling to read files from the package
   directory or subdirectories at compile time and stores them in the a variable of type
   embed.FS.  
  
The embed.FS will store a read-only collection of *.sql files.




func main() {
    log.SetFlags(0)
    db, err := sql.Open("sqlite3", "embed_example.sql")
    if err != nil {
        log.Fatal(err)
    }
    goose.SetDialect("sqlite3")
    goose.SetBaseFS(embedMigrations) // 


Pass the embed.FS variable to goose. This instructs goose to use the embedded filesystem
   instead of opening files from the underlying os.




    if err := goose.Up(db, "migrations"); err != nil { // 


You still have to tell goose which directory contains the .sql files. This implementation
   allowed us to keep existing functions without having to change the function signature or add new
   functions.  
  
It is a drop-in feature that enables the caller to either use the os (as
   before) or use embedded filesystem without changing parts of their existing programs.



        panic(err)
    }
    if err := goose.Version(db, "migrations"); err != nil {
        log.Fatal(err)
    }
    rows, err := db.Query(`SELECT * FROM users`)
    if err != nil {
        log.Fatal(err)
    }
    var user struct {
        ID       int
        Username string
    }
    for rows.Next() {
        if err := rows.Scan(&user.ID, &user.Username); err != nil {
            log.Fatal(err)
        }
        log.Println(user.ID, user.Username)
    }
}
```

--------------------------------

### Goose Provider Migration Methods

Source: https://pressly.github.io/goose/blog/2023/goose-provider

Lists available methods for managing database migrations using a Goose Provider instance, including applying versions, rolling back, and checking status.

```go
(p *Provider) ApplyVersion
  (p *Provider) Close
  (p *Provider) Down
  (p *Provider) DownTo
  (p *Provider) GetDBVersion
  (p *Provider) ListSources
  (p *Provider) Ping
  (p *Provider) Status
  (p *Provider) Up
  (p *Provider) UpByOne
  (p *Provider) UpTo
```

--------------------------------

### Build a Stripped-Down Lite Goose Version

Source: https://pressly.github.io/goose/installation

Further reduce the binary size by stripping debugging information using linker flags (-s -w) in addition to excluding drivers. This results in a very small binary, optimized for size.

```bash
go build \
    -ldflags="-s -w" \
    -tags='no_postgres no_clickhouse no_mssql no_mysql' \
    -o goose ./cmd/goose

```

--------------------------------

### Combine multiple inserts into a single command (SQL)

Source: https://pressly.github.io/goose/blog/2022/overview-sql-file

This SQL snippet uses `-- +goose StatementBegin` and `-- +goose StatementEnd` to group multiple INSERT statements into a single command. This optimization significantly improves migration performance by reducing database round trips.

```sql
-- +goose Up
CREATE TABLE users (
    id int NOT NULL PRIMARY KEY,
    username text,
    name text,
    surname text
);

-- +goose StatementBegin
INSERT INTO "users" ("id", "username", "name", "surname") VALUES (1, 'gallant_almeida7', 'Gallant', 'Almeida7');
INSERT INTO "users" ("id", "username", "name", "surname") VALUES (2, 'brave_spence8', 'Brave', 'Spence8');
.
.
INSERT INTO "users" ("id", "username", "name", "surname") VALUES (99999, 'jovial_chaum1', 'Jovial', 'Chaum1');
INSERT INTO "users" ("id", "username", "name", "surname") VALUES (100000, 'goofy_ptolemy0', 'Goofy', 'Ptolemy0');
-- +goose StatementEnd

-- +goose Down
DROP TABLE users;

```

--------------------------------

### Initialize Postgres Session Locker with Goose Provider

Source: https://pressly.github.io/goose/blog/2023/goose-provider

Configure a Postgres-specific session locker with custom timeout and retry settings. This locker is then passed to the Goose Provider using the WithSessionLocker option.

```go
sessionLocker, err := lock.NewPostgresSessionLocker(
  // Timeout after 30min. Try every 15s up to 120 times.
    lock.WithLockTimeout(15, 120),
)

provider, err := goose.NewProvider(
    goose.DialectPostgres,
    db,
    os.DirFS("migrations"),
    goose.WithSessionLocker(sessionLocker), // Use session-level advisory lock.

)
```

--------------------------------

### Apply versioned migrations with Goose

Source: https://pressly.github.io/goose/blog/2021/no-version-migrations

Use this command to apply standard versioned migrations from a specified directory.

```bash
goose -dir ./schema/migrations up

```

--------------------------------

### Multiple SQL Statements in Migration

Source: https://pressly.github.io/goose/documentation/annotations

Shows how to include multiple SQL statements within the -- +goose up and -- +goose down sections. Each statement should be separated by a semicolon.

```sql
-- +goose up
SELECT 'up SQL query 1';
SELECT 'up SQL query 2';
SELECT 'up SQL query 3';

-- +goose down 
SELECT 'down SQL query 1';
SELECT 'down SQL query 2';
```

--------------------------------

### Apply Migrations with Goose for ClickHouse

Source: https://pressly.github.io/goose/blog/2022/improving-clickhouse

Applies database migrations to a ClickHouse instance using Goose. Sets environment variables for driver, database string, and migration directory.

```bash
GOOSE_DRIVER=clickhouse \
    GOOSE_DBSTRING="tcp://clickuser:password1@localhost:9000/clickdb" \
    GOOSE_MIGRATION_DIR="tests/clickhouse/testdata/migrations" \
    goose up

```

--------------------------------

### Environment Variable Substitution in Goose Migrations

Source: https://pressly.github.io/goose/documentation/annotations

Enables environment variable substitution in SQL migration files using '-- +goose envsub on'. Variables are substituted until '-- +goose envsub off' or end of file.

```sql
-- +goose envsub on

-- +goose up
SELECT * FROM regions WHERE name = '${REGION}';

```

--------------------------------

### Create Goose Provider

Source: https://pressly.github.io/goose/blog/category/blog

Use this to create a new Goose provider for database migrations. Ensure you have Goose version v3.16.0 or above. Requires a context, database connection, and a file system for migrations.

```go
provider, err := goose.NewProvider(
  goose.DialectPostgres, 
  db, 
  os.DirFS("migrations"),
)

results, err := provider.Up(ctx) 

```

--------------------------------

### Goose CLI Usage

Source: https://pressly.github.io/goose/documentation/cli-commands

The basic usage of the Goose CLI. Flags must precede commands. DRIVER and DBSTRING can be set via environment variables.

```bash
Usage: goose [flags] DRIVER DBSTRING <command>

```

--------------------------------

### Available Goose Provider Migration Methods

Source: https://pressly.github.io/goose/blog/archive/2023

A list of methods available on the Goose Provider for managing database migrations. These include applying versions, closing the connection, and checking status.

```go
(p *Provider)
  ApplyVersion
  (p *Provider)
  Close
  (p *Provider)
  Down
  (p *Provider)
  DownTo
  (p *Provider)
  GetDBVersion
  (p *Provider)
  ListSources
  (p *Provider)
  Ping
  (p *Provider)
  Status
  (p *Provider)
  Up
  (p *Provider)
  UpByOne
  (p *Provider)
  UpTo
```

--------------------------------

### Apply unversioned migrations with Goose

Source: https://pressly.github.io/goose/blog/2021/no-version-migrations

Apply migrations from a specified directory without tracking their versions. Useful for seeding data or running tests.

```bash
goose -dir ./schema/seed -no-versioning up

```

--------------------------------

### NewGoMigration

Source: https://pressly.github.io/goose/documentation/provider

Creates a new Go migration that can be registered with a provider or globally.

```APIDOC
## NewGoMigration

### Description
Creates a new Go migration with a version number and optional up and down functions. These functions can either run within a transaction or directly on the database connection.

### Signature
```go
func NewGoMigration(version int64, up, down *GoFunc) *Migration
```

### Parameters

*   **version** (int64) - The unique version number for the migration.
*   **up** (*GoFunc) - The function to execute for the up migration. Can be nil.
*   **down** (*GoFunc) - The function to execute for the down migration. Can be nil.

### GoFunc Details

A `GoFunc` can be defined in one of two ways:

*   **Within a transaction:**
    ```go
    RunTx func(ctx context.Context, tx *sql.Tx) error
    ```
*   **Directly on the database connection:**
    ```go
    RunDB func(ctx context.Context, db *sql.DB) error
    ```

Only one of `RunTx` or `RunDB` can be set for a `GoFunc`. If both are set, an error will be returned.
```

--------------------------------

### Goose Database Store Interface Definition

Source: https://pressly.github.io/goose/documentation/custom-store

The `database.Store` interface defines the contract for custom store implementations. It includes methods for managing the version table, inserting, deleting, and retrieving migration records.

```go
type Store interface {
    // Tablename is the name of the version table. This table is used to record applied migrations
    // and must not be an empty string.
    Tablename() string

    // CreateVersionTable creates the version table, which is used to track migrations. When
    // creating this table, the implementation MUST also insert a row for the initial version (0).
    CreateVersionTable(ctx context.Context, db DBTxConn) error

    // Insert a version id into the version table.
    Insert(ctx context.Context, db DBTxConn, req InsertRequest) error

    // Delete a version id from the version table.
    Delete(ctx context.Context, db DBTxConn, version int64) error

    // GetMigration retrieves a single migration by version id. If the query succeeds, but the
    // version is not found, this method must return [ErrVersionNotFound].
    GetMigration(ctx context.Context, db DBTxConn, version int64) (*GetMigrationResult, error)

    // GetLatestVersion retrieves the last applied migration version. If no migrations exist,
    // this method must return [ErrVersionNotFound].
    GetLatestVersion(ctx context.Context, db DBTxConn) (int64, error)

    // ListMigrations retrieves all migrations sorted in descending order by id or timestamp. If
    // there are no migrations, return empty slice with no error. Typically this method will return
    // at least one migration, because the initial version (0) is always inserted into the version
    // table when it is created.
    ListMigrations(ctx context.Context, db DBTxConn) ([]*ListMigrationsResult, error)
}

```

--------------------------------

### Cleanup Docker Container in Go Tests

Source: https://pressly.github.io/goose/blog/2021/better-tests

This code demonstrates how to schedule the cleanup of a Docker container after a test completes using t.Cleanup. It ensures that resources are released, preventing leaks.

```go
t.Cleanup(func() {
    if err := pool.Purge(container); err != nil {
        log.Printf("failed to purge resource: %v", err)
    }
})
```

--------------------------------

### Verify Goose Migration Output

Source: https://pressly.github.io/goose/blog/2022/improving-clickhouse

Expected output after successfully applying migrations with Goose. Indicates which migration files were processed and the current database version.

```bash
2022/06/19 20:19:04 OK    00001_a.sql
2022/06/19 20:19:04 OK    00002_b.sql
2022/06/19 20:19:04 OK    00003_c.sql
2022/06/19 20:19:04 goose: no migrations to run. current version: 3

```

--------------------------------

### SQL Migration with Multiple Up Statements

Source: https://pressly.github.io/goose/blog/2022/overview-sql-file

A SQL migration file can contain multiple statements for the Up migration. The Down annotation is optional but recommended.

```sql
-- +goose Up
SELECT 'up SQL query 1';
SELECT 'up SQL query 2';
SELECT 'up SQL query 3';

-- +goose Down


SELECT 'down SQL query 1'; SELECT 'down SQL query 2'; 

```

--------------------------------

### Register Go Migrations with Goose Provider

Source: https://pressly.github.io/goose/blog/2023/goose-provider

Define and register Go migrations directly with the Goose Provider using goose.NewGoMigration. This allows for multiple, independent sets of Go migrations within an application.

```go
register := []*goose.Migration{
    goose.NewGoMigration(
        1,
        &goose.GoFunc{RunTx: newTxFn("CREATE TABLE users (id INTEGER)")},
        &goose.GoFunc{RunTx: newTxFn("DROP TABLE users")},
    ),
}
provider, err := goose.NewProvider(goose.DialectSQLite3, db, nil,
    goose.WithGoMigrations(register...),
)


```

--------------------------------

### Goose Migration: Create and Drop Users Table

Source: https://pressly.github.io/goose/blog/2024/goose-sqlc

Defines the Up and Down migrations for creating and dropping the 'users' table. This SQL is managed by goose.

```sql
-- +goose Up
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username text NOT NULL
);

-- +goose Down
DROP TABLE users;

```

--------------------------------

### Valid and Invalid Annotation Placement

Source: https://pressly.github.io/goose/documentation/annotations

Demonstrates correct and incorrect usage of the -- +goose up annotation. Annotations must not have leading whitespace to be recognized by Goose.

```sql
-- +goose up ✅
```

```sql
    -- +goose up ❌ (invalid, because leading whitespace)
```

--------------------------------

### Goose Status Output After All Migrations

Source: https://pressly.github.io/goose/blog/2021/visualizing-up-down-commands

Shows the status when all migrations have been successfully applied.

```bash
Applied At                  Migration
=======================================
Sun Dec 19 21:31:11 2021 -- 00001_a.sql
Sun Dec 19 21:31:11 2021 -- 00002_b.sql
Sun Dec 19 21:31:11 2021 -- 00003_c.sql
Sun Dec 19 21:31:11 2021 -- 00004_d.sql
Sun Dec 19 21:31:11 2021 -- 00005_e.sql
Sun Dec 19 21:31:11 2021 -- 00006_f.sql
Sun Dec 19 21:31:11 2021 -- 00007_g.sql
Sun Dec 19 21:31:11 2021 -- 00008_h.sql
Sun Dec 19 21:31:11 2021 -- 00009_i.sql
Sun Dec 19 21:31:11 2021 -- 00010_j.sql
Sun Dec 19 21:31:11 2021 -- 00011_k.sql


```

--------------------------------

### Generated Go Code for Listing Users

Source: https://pressly.github.io/goose/blog/2024/goose-sqlc

This Go code is automatically generated by sqlc. It defines a `ListUsers` function that queries the database for all users, handles row scanning, and manages errors. sqlc abstracts away the boilerplate for database interactions.

```go
const listUsers = `-- name: ListUsers :many
SELECT id, username FROM users ORDER BY username
`

func (q *Queries) ListUsers(ctx context.Context) ([]User, error) {
    rows, err := q.db.QueryContext(ctx, listUsers)
    if err != nil {
        return nil, err
    }
    defer rows.Close()
    var items []User
    for rows.Next() {
        var i User
        if err := rows.Scan(&i.ID, &i.Username); err != nil {
            return nil, err
        }
        items = append(items, i)
    }
    if err := rows.Close(); err != nil {
        return nil, err
    }
    if err := rows.Err(); err != nil {
        return nil, err
    }
    return items, nil
}

```

--------------------------------

### Query Data from ClickStream Table

Source: https://pressly.github.io/goose/blog/2022/improving-clickhouse

Retrieves all data from the `clickstream` table in ClickHouse using the `clickhouse-client`. Requires database credentials.

```bash
clickhouse-client --vertical \
    --database clickdb --password password1 -u clickuser \
    -q 'SELECT * FROM clickstream'

```

--------------------------------

### Goose SQL Migration Annotations

Source: https://pressly.github.io/goose/blog

Define SQL migration files using Goose annotations to control migration execution. Supports Up, Down, statement boundaries, and transaction control.

```sql
-- +goose Up
-- +goose Down
-- +goose StatementBegin
-- +goose StatementEnd
-- +goose NO TRANSACTION
```

--------------------------------

### Add Goose Provider

Source: https://pressly.github.io/goose/blog/category/package

Use this to add a new database migration provider to your Go application. Requires Goose v3.16.0 or above. Ensure you have the necessary database connection and migration files.

```go
provider, err := goose.NewProvider(
  goose.DialectPostgres, 
  db, 
  os.DirFS("migrations"), 
)

results, err := provider.Up(ctx) 

```

--------------------------------

### Exclude Migration File Names

Source: https://pressly.github.io/goose/documentation/provider

Configures the provider to ignore specific migration file names. This can be used to skip certain migrations during the process.

```go
func WithExcludeNames(excludes []string)
```

--------------------------------

### Reset unversioned migrations with Goose

Source: https://pressly.github.io/goose/blog/2021/no-version-migrations

Apply down migrations for unversioned files in reverse order, effectively resetting the seeded data without affecting versioned migrations.

```bash
goose -dir ./schema/seed -no-versioning down-to 0

```

```bash
# or
goose -dir ./schema/seed -no-versioning reset

```

--------------------------------

### Configure Session Locking

Source: https://pressly.github.io/goose/documentation/provider

Enables database locking during migrations to prevent concurrent execution by multiple application instances. This is crucial in distributed or clustered environments.

```go
func WithSessionLocker(locker lock.SessionLocker)
```

--------------------------------

### Define Go Migration Execution within Transaction

Source: https://pressly.github.io/goose/documentation/provider

Specifies that a Go migration's up or down function should be executed within a database transaction.

```go
RunTx func(ctx context.Context, tx *sql.Tx) error
```

--------------------------------

### Run migration statements outside transaction (SQL)

Source: https://pressly.github.io/goose/blog/2022/overview-sql-file

This SQL snippet demonstrates the use of the `-- +goose NO TRANSACTION` annotation. This is necessary for statements like `CREATE INDEX CONCURRENTLY` which cannot be executed within a transaction block.

```sql
-- +goose NO TRANSACTION

-- +goose Up
CREATE INDEX CONCURRENTLY ON users (user_id);

-- +goose Down
DROP INDEX IF EXISTS users_user_id_idx;

```

--------------------------------

### Provider Options

Source: https://pressly.github.io/goose/documentation/provider

Customizes the behavior of the Goose provider using functional options.

```APIDOC
## Provider Options

### WithVerbose

#### Description
Enables verbose logging of the migration process.

#### Signature
```go
func WithVerbose(b bool)
```

### WithSessionLocker

#### Description
Configures a session locker to ensure only one instance of the application runs migrations at a time, preventing concurrent migration issues.

#### Signature
```go
func WithSessionLocker(locker lock.SessionLocker)
```

### WithExcludeNames

#### Description
Excludes migration files with the specified names from the migration process. If called multiple times, the list of excludes is merged.

#### Signature
```go
func WithExcludeNames(excludes []string)
```

### WithExcludeVersions

#### Description
Excludes migration versions with the specified numbers from the migration process. If called multiple times, the list of excludes is merged.

#### Signature
```go
func WithExcludeVersions(versions []int64)
```

### WithGoMigrations

#### Description
Adds the given Go migrations to the list of migrations. Migrations must be constructed using the `NewGoMigration` constructor.

#### Signature
```go
func WithGoMigrations(migrations ...*Migration)
```

### WithDisableGlobalRegistry

#### Description
Disables the global registry for migrations. Useful for running migrations in multiple processes where migrations should be registered with the provider directly.

#### Signature
```go
func WithDisableGlobalRegistry(b bool)
```
```

--------------------------------

### Goose SQL Migration Annotations

Source: https://pressly.github.io/goose/blog/archive/2022

These are the available annotations for Goose SQL migrations. They control how SQL statements are parsed and executed.

```sql
-- +goose Up
-- +goose Down
-- +goose StatementBegin
-- +goose StatementEnd
-- +goose NO TRANSACTION

```

--------------------------------

### Define Go Migration Execution outside Transaction

Source: https://pressly.github.io/goose/documentation/provider

Specifies that a Go migration's up or down function should be executed directly against the database connection, outside of a transaction.

```go
// or

RunDB func(ctx context.Context, db *sql.DB) error
```

--------------------------------

### Exclude Migration Versions

Source: https://pressly.github.io/goose/documentation/provider

Configures the provider to ignore migrations based on their version numbers. This allows selective application of migration scripts.

```go
func WithExcludeVersions(versions []int64)
```

--------------------------------

### Goose Migration Annotations

Source: https://pressly.github.io/goose/blog/category/sql-migrations

These annotations are used within SQL migration files to parse SQL statements and modify how migrations are executed by the Goose tool. Use them to define migration direction, statement boundaries, and transaction behavior.

```sql
-- +goose Up

```

```sql
-- +goose Down

```

```sql
-- +goose StatementBegin

```

```sql
-- +goose StatementEnd

```

```sql
-- +goose NO TRANSACTION

```

--------------------------------

### Complex SQL Statement with Goose Annotations

Source: https://pressly.github.io/goose/blog/2022/overview-sql-file

Use -- +goose StatementBegin and -- +goose StatementEnd to delimit complex SQL statements, such as PL/pgSQL functions, that contain semicolons.

```sql
-- +goose Up

-- +goose StatementBegin
CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE )
returns void AS $$
DECLARE
  create_query text;
BEGIN
-- This comment will be preserved.
  -- And so will this one.
  FOR create_query IN SELECT
      'CREATE TABLE IF NOT EXISTS histories_'
      || TO_CHAR( d, 'YYYY_MM' )
      || ' ( CHECK( created_at >= timestamp '''
      || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' )
      || ''' AND created_at < timestamp '''
      || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' )
      || ''' ) ) inherits ( histories );'
    FROM generate_series( $1, $2, '1 month' ) AS d
  LOOP
    EXECUTE create_query;
  END LOOP;  -- LOOP END
END;         -- FUNCTION END
$$
language plpgsql;
-- +goose StatementEnd

```

--------------------------------

### Grouping Complex SQL Statements

Source: https://pressly.github.io/goose/documentation/annotations

Utilizes -- +goose statementbegin and -- +goose statementend to treat a block of SQL, including functions with internal semicolons, as a single statement. Comments and empty lines within the block are preserved.

```sql
-- +goose up

-- +goose statementbegin
CREATE OR REPLACE FUNCTION histories_partition_creation( DATE, DATE )
returns void AS $$
DECLARE
  create_query text;
BEGIN
-- This comment will be preserved.
  -- And so will this one.
  FOR create_query IN SELECT
      'CREATE TABLE IF NOT EXISTS histories_'
      || TO_CHAR( d, 'YYYY_MM' )
      || ' ( CHECK( created_at >= timestamp '''
      || TO_CHAR( d, 'YYYY-MM-DD 00:00:00' )
      || ''' AND created_at < timestamp '''
      || TO_CHAR( d + INTERVAL '1 month', 'YYYY-MM-DD 00:00:00' )
      || ''' ) ) inherits ( histories );'
    FROM generate_series( $1, $2, '1 month' ) AS d
  LOOP
    EXECUTE create_query;
  END LOOP;  -- LOOP END
END;         -- FUNCTION END
$$
language plpgsql;
-- +goose statementend
```

--------------------------------

### Disable Global Migration Registry

Source: https://pressly.github.io/goose/documentation/provider

Disables the default global registry for migrations. This is recommended when running migrations across multiple processes to avoid conflicts.

```go
func WithDisableGlobalRegistry(b bool)
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.