### Install Dbmate using Scoop on Windows

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Install Dbmate on Windows using Scoop. After installation, you can run the help command.

```pwsh
scoop install dbmate
dbmate --help
```

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

### Use dbmate as a Library in Go

Source: https://github.com/amacneil/dbmate/blob/main/README.md

A basic example of using dbmate as a library within a Go application. Ensure the necessary driver is imported.

```go
package main

import (
	"net/url"

	"github.com/amacneil/dbmate/v2/pkg/dbmate"
	_ "github.com/amacneil/dbmate/v2/pkg/driver/sqlite"
)

func main() {
	u, _ := url.Parse("sqlite:foo.sqlite3")
	db := dbmate.New(u)

	err := db.CreateAndMigrate()
	if err != nil {
		panic(err)
	}
}
```

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

### Install Dbmate on Linux

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Download and install the Dbmate binary on Linux. Ensure the binary is executable and accessible in your PATH.

```sh
sudo curl -fsSL -o /usr/local/bin/dbmate https://github.com/amacneil/dbmate/releases/latest/download/dbmate-linux-amd64
sudo chmod +x /usr/local/bin/dbmate
/usr/local/bin/dbmate --help
```

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

### SQL Migration File Example

Source: https://github.com/amacneil/dbmate/blob/main/README.md

A standard SQL migration file format for dbmate, including both up and down migration directives. Both directives are required.

```sql
-- migrate:up
create table users (
  id integer,
  name varchar(255)
);

-- migrate:down
drop table if exists users;
```

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

### Install Dbmate using NPM

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Install Dbmate as a development dependency using NPM. After installation, you can run the help command.

```sh
npm install --save-dev dbmate
npx dbmate --help
```

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

### Install Dbmate using Homebrew

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Install Dbmate on macOS using Homebrew. After installation, you can run the help command.

```sh
brew install dbmate
dbmate --help
```

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

### Dump Schema Command Example

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Demonstrates how to dump the schema.sql file. This command relies on external tools like pg_dump or mysqldump.

```sh
$ dbmate dump
exec: "pg_dump": executable file not found in $PATH
```

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

### Start Development Shell

Source: https://github.com/amacneil/dbmate/blob/main/README.md

This command launches a development shell within the Docker environment, allowing for interactive development and debugging.

```sh
make docker-sh
```

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

### Run Dbmate with Docker

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Run Dbmate using a Docker container. Ensure to set `--network=host` for proper networking. This example shows how to access the help command.

```sh
docker run --rm -it --network=host ghcr.io/amacneil/dbmate --help
```

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

### Run Docker Compose

Source: https://github.com/amacneil/dbmate/blob/main/fixtures/bigquery/README.md

Executes the Docker Compose setup for the development environment. This command should be run from the top-level directory of the project.

```sh
$ docker compose run --rm dev
```

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

### Load Schema File with dbmate load

Source: https://context7.com/amacneil/dbmate/llms.txt

Loads the schema.sql file directly into the database, bypassing individual migration files. Useful for test environment setup.

```bash
export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_test?sslmode=disable"

dbmate create
dbmate load
```

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

### Find and List Migrations (Go Library)

Source: https://context7.com/amacneil/dbmate/llms.txt

Use `db.FindMigrations` to get a slice of `Migration` structs, indicating their version, filename, and applied status. This is useful for inspecting migration states.

```go
package main

import (
    "fmt"
    "net/url"

    "github.com/amacneil/dbmate/v2/pkg/dbmate"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/postgres"
)

func main() {
    u, _ := url.Parse("postgres://postgres:password@127.0.0.1:5432/myapp?sslmode=disable")
    db := dbmate.New(u)

    migrations, err := db.FindMigrations()
    if err != nil {
        panic(err)
    }

    for _, m := range migrations {
        status := "pending"
        if m.Applied {
            status = "applied"
        }
        fmt.Printf("[%s] version=%s file=%s\n", status, m.Version, m.FileName)
    }
    // Output:
    // [applied] version=20240101000000 file=20240101000000_create_users_table.sql
    // [pending] version=20240315120000 file=20240315120000_create_posts_table.sql
}
```

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

### Embed Migrations in Go Application

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Example of embedding migration files into a Go binary using the embed functionality. The db.FS field must be set to the embedded filesystem.

```go
package main

import (
	"embed"
	"fmt"
	"net/url"

	"github.com/amacneil/dbmate/v2/pkg/dbmate"
	_ "github.com/amacneil/dbmate/v2/pkg/driver/sqlite"
)

//go:embed db/migrations/*.sql
var fs embed.FS

func main() {
	u, _ := url.Parse("sqlite:foo.sqlite3")
	db := dbmate.New(u)
	db.FS = fs

	fmt.Println("Migrations:")
	migrations, err := db.FindMigrations()
	if err != nil {
		panic(err)
	}
	for _, m := range migrations {
		fmt.Println(m.Version, m.FilePath)
	}

	fmt.Println("\nApplying...")
	err = db.CreateAndMigrate()
	if err != nil {
		panic(err)
	}
}
```

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

### Resolve and Run dbmate Binary in Node.js

Source: https://context7.com/amacneil/dbmate/llms.txt

Use the `dbmate` npm package to resolve the platform-specific binary and execute migrations. Ensure the appropriate `@dbmate/<platform>-<arch>` dependency is installed.

```typescript
import { resolveBinary } from "dbmate";
import { spawnSync } from "node:child_process";

// Resolve the binary for the current OS and architecture
const binary = resolveBinary();
// e.g. "/node_modules/@dbmate/linux-x64/bin/dbmate"

// Run a migration
const result = spawnSync(binary, ["up"], {
  env: { ...process.env, DATABASE_URL: "postgres://user:pass@localhost/myapp" },
  stdio: "inherit",
});

if (result.status !== 0) {
  process.exit(result.status ?? 1);
}
```

```sh
# Install via npm
npm install --save-dev dbmate

# Run directly via npx
DATABASE_URL="postgres://user:pass@localhost/myapp" npx dbmate up
```

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

### Initialize DB Instance (Go Library)

Source: https://context7.com/amacneil/dbmate/llms.txt

Use `dbmate.New` to initialize a `*DB` instance for programmatic use. Configure migrations directory, schema file, and auto-dump schema behavior. `CreateAndMigrate` applies pending migrations.

```go
package main

import (
    "fmt"
    "net/url"

    "github.com/amacneil/dbmate/v2/pkg/dbmate"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/postgres"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/sqlite"
)

func main() {
    u, err := url.Parse("postgres://postgres:password@127.0.0.1:5432/myapp?sslmode=disable")
    if err != nil {
        panic(err)
    }

    db := dbmate.New(u)
    db.MigrationsDir = []string{"./db/migrations"}
    db.SchemaFile = "./db/schema.sql"
    db.AutoDumpSchema = true
    db.Strict = false

    // Create database if missing, then apply all pending migrations
    if err := db.CreateAndMigrate(); err != nil {
        panic(err)
    }
    fmt.Println("Migrations applied successfully")
}
```

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

### Apply All Pending Migrations with dbmate up

Source: https://context7.com/amacneil/dbmate/llms.txt

Creates the database if it doesn't exist, applies all pending migrations, and writes the schema.sql file. Use --strict to fail on out-of-order migrations or --wait to wait for the DB to be ready.

```bash
export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_dev?sslmode=disable"

# Create database and run all pending migrations
dbmate up

# Fail if any migration would be applied out of order
dbmate up --strict

# Wait for DB to be ready before migrating (useful in Docker)
dbmate --wait up

# Print rows affected / last insert ID for each statement
dbmate up --verbose
```

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

### Create Database

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Create the database if it does not already exist.

```sh
dbmate create
```

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

### dbmate.New Go Library Function

Source: https://context7.com/amacneil/dbmate/llms.txt

Initializes a *DB instance for programmatic use. All CLI operations are available as methods on this instance. Configuration options like MigrationsDir, SchemaFile, AutoDumpSchema, and Strict can be set.

```APIDOC
## dbmate.New

### Description
Initializes a `*DB` instance for programmatic use. All CLI operations are available as methods.

### Usage
```go
package main

import (
    "fmt"
    "net/url"

    "github.com/amacneil/dbmate/v2/pkg/dbmate"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/postgres"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/sqlite"
)

func main() {
    u, err := url.Parse("postgres://postgres:password@127.0.0.1:5432/myapp?sslmode=disable")
    if err != nil {
        panic(err)
    }

    db := dbmate.New(u)
    db.MigrationsDir = []string{"./db/migrations"}
    db.SchemaFile = "./db/schema.sql"
    db.AutoDumpSchema = true
    db.Strict = false

    // Create database if missing, then apply all pending migrations
    if err := db.CreateAndMigrate(); err != nil {
        panic(err)
    }
    fmt.Println("Migrations applied successfully")
}
```
```

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

### Create or Drop Database with dbmate

Source: https://context7.com/amacneil/dbmate/llms.txt

Creates or drops the database specified in the DATABASE_URL environment variable.

```bash
export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_dev?sslmode=disable"

dbmate create

dbmate drop
```

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

### Mount local migrations directory and run migrations

Source: https://context7.com/amacneil/dbmate/llms.txt

Use this command to mount your local migrations directory and run all pending migrations. Ensure the DATABASE_URL environment variable is correctly set.

```bash
docker run --rm -it --network=host \
  -v "$(pwd)/db:/db" \
  -e DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp?sslmode=disable" \
  ghcr.io/amacneil/dbmate migrate
```

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

### Apply All Pending Migrations

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Create the database if it doesn't exist and then apply all pending schema migrations.

```sh
dbmate up
```

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

### Build Docker Image and Run Tests

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Use this command to build the Docker image and execute the project's tests against a real database.

```sh
make docker-all
```

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

### Create BigQuery Service Account and Permissions

Source: https://github.com/amacneil/dbmate/blob/main/fixtures/bigquery/README.md

These commands set up a Google Cloud project, create a service account, and grant it necessary BigQuery roles (dataEditor and jobUser). A credentials file is generated for authentication.

```sh
$ PROJECT_ID=your-google-cloud-project-id
$ LOCATION=us-east5
$ DATASET=test_dataset
$ SERVICE_ACCOUNT=dbmate-test-sa

$ gcloud auth login

$ gcloud iam service-accounts create $SERVICE_ACCOUNT

$ gcloud projects add-iam-policy-binding $PROJECT_ID \
  --role="roles/bigquery.dataEditor" \
  --member=serviceAccount:${SERVICE_ACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com

$ gcloud projects add-iam-policy-binding $PROJECT_ID \
  --role="roles/bigquery.jobUser" \
  --member=serviceAccount:${SERVICE_ACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com

$ gcloud iam service-accounts keys create \
  fixtures/bigquery/credentials.json \
  --iam-account=${SERVICE_ACCOUNT}@${PROJECT_ID}.iam.gserviceaccount.com
```

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

### Run Pending Migrations with dbmate

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Use `dbmate up` to apply all pending SQL migrations. The database will be created if it doesn't exist. Pending migrations are applied in numerical order.

```sh
$ dbmate up
Creating: myapp_development
Applying: 20151127184807_create_users_table.sql
Applied: 20151127184807_create_users_table.sql in 123µs
Writing: ./db/schema.sql
```

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

### Create a New Migration File

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Run this command to generate a new SQL migration file. The file will be named with a timestamp and the provided description.

```sh
dbmate new create_users_table
```

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

### Embedding Migrations with embed.FS

Source: https://context7.com/amacneil/dbmate/llms.txt

Demonstrates how to bundle migration files into the application binary using Go's `embed` package by assigning an embedded filesystem to `db.FS`. This approach is useful for creating self-contained applications.

```APIDOC
## Embedding Migrations with `embed.FS`

### Description
Migrations can be bundled into the application binary using Go's `embed` package by assigning a filesystem to `db.FS`.

### Usage
```go
package main

import (
    "embed"
    "fmt"
    "net/url"

    "github.com/amacneil/dbmate/v2/pkg/dbmate"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/sqlite"
)

//go:embed db/migrations/*.sql
var migrationFS embed.FS

func main() {
    u, _ := url.Parse("sqlite:./app.sqlite3")

    db := dbmate.New(u)
    db.FS = migrationFS
    db.AutoDumpSchema = false // disable schema dump for embedded use

    if err := db.CreateAndMigrate(); err != nil {
        panic(err)
    }

    migrations, _ := db.FindMigrations()
    for _, m := range migrations {
        fmt.Printf("version=%s applied=%v\n", m.Version, m.Applied)
    }
}
```
```

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

### Run dbmate Migrations using Docker

Source: https://context7.com/amacneil/dbmate/llms.txt

Execute database migrations using the official dbmate Docker image. Mount host volumes or use environment variables for database connection strings and configuration.

```sh
# Run migrations against host network database
docker run --rm -it --network=host \
  -e DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp?sslmode=disable" \
  ghcr.io/amacneil/dbmate up
```

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

### Create New Migration File with dbmate

Source: https://context7.com/amacneil/dbmate/llms.txt

Generates a new timestamp-versioned SQL migration file. Ensure the DATABASE_URL environment variable is set.

```bash
export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_dev?sslmode=disable"

dbmate new create_users_table
```

```sql
-- migrate:up


-- migrate:down
```

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

### Dbmate Command Line Help

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Display the main help message for the Dbmate command-line tool.

```sh
dbmate --help
```

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

### Generate a new migration file with Dbmate

Source: https://context7.com/amacneil/dbmate/llms.txt

This command generates a new, empty migration file in the specified directory. The filename will be a timestamp followed by the provided description.

```bash
docker run --rm -it --network=host \
  -v "$(pwd)/db:/db" \
  ghcr.io/amacneil/dbmate new create_users_table
```

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

### Generate Dbmate NPM Package

Source: https://github.com/amacneil/dbmate/blob/main/typescript/README.md

Run this command to generate the dbmate npm package. For local development, use the --skip-bin flag if dbmate binaries are not available.

```bash
npm run generate
```

```bash
npm run generate -- --skip-bin
```

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

### Basic Migration Structure

Source: https://github.com/amacneil/dbmate/blob/main/README.md

A new migration file contains `migrate:up` for schema changes and `migrate:down` for reverting them. Add your SQL statements within these sections.

```sql
-- migrate:up

-- migrate:down
```

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

### Register Custom Database Driver in Go

Source: https://context7.com/amacneil/dbmate/llms.txt

Implement and register a custom database driver for a specific URL scheme. Ensure the custom driver implements the `dbmate.Driver` interface.

```go
package mydriver

import (
    "database/sql"
    "net/url"

    "github.com/amacneil/dbmate/v2/pkg/dbmate"
    "github.com/amacneil/dbmate/v2/pkg/dbutil"
)

type Driver struct {
    databaseURL         *url.URL
    migrationsTableName string
}

func NewDriver(config dbmate.DriverConfig) dbmate.Driver {
    return &Driver{
        databaseURL:         config.DatabaseURL,
        migrationsTableName: config.MigrationsTableName,
    }
}

func init() {
    // Register for the "mydb://" URL scheme
    dbmate.RegisterDriver(NewDriver, "mydb")
}

// Driver must implement the dbmate.Driver interface:
// Open, DatabaseExists, CreateDatabase, DropDatabase, DumpSchema,
// MigrationsTableExists, CreateMigrationsTable, SelectMigrations,
// InsertMigration, DeleteMigration, Ping, QueryError
func (d *Driver) Open() (*sql.DB, error)                                    { /* ... */ return nil, nil }
func (d *Driver) DatabaseExists() (bool, error)                             { return false, nil }
func (d *Driver) CreateDatabase() error                                     { return nil }
func (d *Driver) DropDatabase() error                                       { return nil }
func (d *Driver) DumpSchema(*sql.DB, ...string) ([]byte, error)             { return nil, nil }
func (d *Driver) MigrationsTableExists(*sql.DB) (bool, error)               { return false, nil }
func (d *Driver) CreateMigrationsTable(*sql.DB) error                       { return nil }
func (d *Driver) SelectMigrations(*sql.DB, int) (map[string]bool, error)    { return nil, nil }
func (d *Driver) InsertMigration(dbutil.Transaction, string) error          { return nil }
func (d *Driver) DeleteMigration(dbutil.Transaction, string) error          { return nil }
func (d *Driver) Ping() error                                               { return nil }
func (d *Driver) QueryError(string, error) error                            { return nil }
```

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

### Load Schema File

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Load the 'schema.sql' file into the database. This is typically used to set up a new database or reset an existing one.

```sh
dbmate load
```

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

### Create migration file with Docker

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Use Docker to create a new migration file. This command mounts the local './db' directory into the container and generates a migration file named 'create_users_table'.

```sh
docker run --rm -it --network=host -v "$(pwd)/db:/db" ghcr.io/amacneil/dbmate new create_users_table
```

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

### Specify Database URL Directly on Command Line

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Provide the database URL directly using the -u flag on the command line. This method bypasses environment variable loading but is less convenient for complex URLs or frequent use.

```sh
$ dbmate -u "postgres://postgres://postgres@127.0.0.1:5432/myapp_test?sslmode=disable" up
```

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

### Configure dbmate with Environment Variables and .env File

Source: https://context7.com/amacneil/dbmate/llms.txt

Configure dbmate settings such as database URLs, migration directories, and table names using environment variables or a `.env` file. Supports overriding settings via command-line flags.

```sh
# .env
DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_dev?sslmode=disable"
TEST_DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_test?sslmode=disable"

# DBMATE_MIGRATIONS_DIR="./db/migrations"
# DBMATE_SCHEMA_FILE="./db/schema.sql"
# DBMATE_MIGRATIONS_TABLE="schema_migrations"
# DBMATE_NO_DUMP_SCHEMA=false
# DBMATE_STRICT=false
# DBMATE_WAIT=false
# DBMATE_WAIT_TIMEOUT=60s
# DBMATE_DRIVER=postgres
# DBMATE_VERBOSE=false
```

```sh
# Use a custom env file
dbmate --env-file .env.production up

# Use a different environment variable for the connection URL
dbmate -e TEST_DATABASE_URL drop
dbmate -e TEST_DATABASE_URL --no-dump-schema up

# Override URL directly on command line (passwords are redacted in error output)
dbmate --url "postgres://postgres:password@127.0.0.1:5432/myapp_test?sslmode=disable" migrate

# Multiple migration directories
dbmate --migrations-dir ./db/migrations --migrations-dir ./plugins/migrations up
```

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

### Specify Database URL in .env File

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Configure your database connection by adding the DATABASE_URL to your .env file. This is the default method dbmate uses to locate your database.

```sh
$ cat .env
DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_development?sslmode=disable"
```

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

### Build and Test with BigQuery Driver

Source: https://github.com/amacneil/dbmate/blob/main/fixtures/bigquery/README.md

These commands are executed inside the Docker container. They build the project and run tests, specifying the BigQuery credentials path, the BigQuery test URL, and additional flags for the test execution.

```sh
$ make build

$ make test \
  GOOGLE_APPLICATION_CREDENTIALS=/src/fixtures/bigquery/credentials.json \
  GOOGLE_BIGQUERY_TEST_URL=bigquery://$PROJECT_ID/$LOCATION/$DATASET \
  FLAGS+="-count 1 -v ./pkg/driver/bigquery #"
```

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

### Connect to BigQuery Emulator

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Connect to a custom endpoint like the BigQuery Emulator by specifying the host and port. Use `disable_auth=true` to skip authentication for testing.

```sh
bigquery://host:port/projectid/location/dataset?disable_auth=true
```

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

### Define SQL Migration with Up and Down Sections

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Implement `migrate:up` for creating database structures and `migrate:down` for reverting them. This allows for rolling back migrations.

```sql
-- migrate:up
create table users (
  id integer,
  name varchar(255),
  email varchar(255) not null
);

-- migrate:down
drop table users;
```

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

### Check Migration Status with dbmate status

Source: https://context7.com/amacneil/dbmate/llms.txt

Lists all migration files, indicating which are applied and which are pending. Use --exit-code to return 1 if pending migrations exist, or --quiet for silent exit code only.

```bash
export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_dev?sslmode=disable"

dbmate status

# Return exit code 1 if there are pending migrations (useful in CI)
dbmate status --exit-code

# Silent check — returns exit code only, no output
dbmate status --quiet
```

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

### Using Alternate Environment Variables for Database URL

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Specify a different environment variable for the database connection URL, such as TEST_DATABASE_URL, for specific tasks like managing test databases. This allows for distinct configurations for different environments.

```sh
$ cat .env
DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_dev?sslmode=disable"
TEST_DATABASE_URL="postgres://postgres@127.0.0.1:5432/myapp_test?sslmode=disable"
```

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

### PostgreSQL Connection with Search Path

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Specify the current schema using the `search_path` parameter. If multiple schemas are provided, the first is used for the `schema_migrations` table.

```sh
DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?search_path=myschema"
```

```sh
DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?search_path=myschema,public"
```

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

### Pass Arguments to pg_dump/mysqldump

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Shows how to pass additional arguments to pg_dump or mysqldump when using the dbmate dump command.

```sh
$ dbmate dump -- --flag1 --flag2
```

```sh
$ dbmate --url="..." dump -- --restrict-key=restrict_key
```

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

### Publish Dbmate NPM Package (CI)

Source: https://github.com/amacneil/dbmate/blob/main/typescript/README.md

This command is intended for use in Continuous Integration environments to publish the dbmate npm package.

```bash
npm run publish
```

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

### Dump Database Schema with dbmate dump

Source: https://context7.com/amacneil/dbmate/llms.txt

Writes the current database schema to ./db/schema.sql using the native dump tool. Extra flags can be passed to the underlying dump tool using --.

```bash
export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_dev?sslmode=disable"

dbmate dump

# Pass extra flags directly to pg_dump / mysqldump
dbmate dump -- --exclude-table=legacy_table

# Custom schema file location
dbmate --schema-file ./infra/schema.sql dump
```

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

### Run Pending Migrations

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Apply any pending schema migrations to the database.

```sh
dbmate migrate
```

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

### Embed Migrations with `embed.FS` (Go Library)

Source: https://context7.com/amacneil/dbmate/llms.txt

Bundle migrations into the application binary using Go's `embed` package by assigning an `embed.FS` to `db.FS`. Set `db.AutoDumpSchema = false` when using embedded migrations.

```go
package main

import (
    "embed"
    "fmt"
    "net/url"

    "github.com/amacneil/dbmate/v2/pkg/dbmate"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/sqlite"
)

//go:embed db/migrations/*.sql
var migrationFS embed.FS

func main() {
    u, _ := url.Parse("sqlite:./app.sqlite3")

    db := dbmate.New(u)
    db.FS = migrationFS
    db.AutoDumpSchema = false // disable schema dump for embedded use

    if err := db.CreateAndMigrate(); err != nil {
        panic(err)
    }

    migrations, _ := db.FindMigrations()
    for _, m := range migrations {
        fmt.Printf("version=%s applied=%v\n", m.Version, m.Applied)
    }
}
```

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

### Wait for Database with Other dbmate Commands

Source: https://github.com/amacneil/dbmate/blob/main/README.md

The `--wait` flag can be combined with other dbmate commands like `up` to ensure the database is ready before proceeding. The default timeout is 60 seconds.

```sh
$ dbmate --wait up
Waiting for database....
Creating: myapp_development
```

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

### Wait for Database Availability

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Use the `dbmate wait` command to pause execution until the database server is available. This is particularly useful in Dockerized development environments where the database might not be ready immediately on startup.

```sh
$ dbmate wait
```

```sh
$ dbmate wait
Waiting for database....
```

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

### Connect to Spanner PostgreSQL Dialect

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Connect to Spanner databases using the PostgreSQL Dialect via PGAdapter. Authentication is handled by PGAdapter and credentials are ignored.

```sh
DATABASE_URL="spanner-postgres://127.0.0.1:5432/database_name?sslmode=disable"
```

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

### Database URL Format

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Understand the required format for specifying the database connection URL. Ensure all components, including protocol, credentials, host, port, database name, and options, are correctly formatted.

```text
protocol://username:password@host:port/database_name?options
```

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

### Manage Test Database with Alternate Environment Variable

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Use the -e flag to specify an environment variable containing the test database URL for commands like 'drop' and 'up'. This is useful for isolated testing scenarios.

```sh
$ dbmate -e TEST_DATABASE_URL drop
Dropping: myapp_test
```

```sh
$ dbmate -e TEST_DATABASE_URL --no-dump-schema up
Creating: myapp_test
Applying: 20151127184807_create_users_table.sql
Applied: 20151127184807_create_users_table.sql in 123µs
```

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

### Generate New Migration File

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Generate a new SQL migration file. The filename will be prefixed with a timestamp.

```sh
dbmate new
```

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

### Make Credentials World-Readable

Source: https://github.com/amacneil/dbmate/blob/main/fixtures/bigquery/README.md

This command makes the generated credentials file world-readable. This is a security risk and should only be done on private machines. It is required for dbmate running as root inside a Docker container to access the file.

```sh
$ chmod a+r fixtures/bigquery/credentials.json
```

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

### SQLite Absolute Path Connection

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Connect to a SQLite database using an absolute path. Prepend a forward slash to specify an absolute path.

```sh
DATABASE_URL="sqlite:/tmp/database.sqlite3"
```

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

### ClickHouse Driver with Generic URL

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Use the ClickHouse driver with a standard http, https, or tcp URL by providing the `--driver` flag.

```sh
# Connect via HTTP using generic URL syntax
dbmate --driver clickhouse --url "http://username:password@127.0.0.1:8123/database_name" status
```

```sh
dbmate --driver clickhouse --url "https://username:password@127.0.0.1:8443/database_name" status
```

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

### Multiple Migrations in One File

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Include multiple `migrate:up` and `migrate:down` sections within a single file to group related changes. The entire file is treated as a single atomic migration.

```sql
-- migrate:up
CREATE TABLE users (id SERIAL PRIMARY KEY);

-- migrate:down
DROP TABLE users;

-- migrate:up
ALTER TABLE users ADD COLUMN email VARCHAR;

-- migrate:down
ALTER TABLE users DROP COLUMN email;
```

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

### Write Up Migration SQL

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Add your SQL statements for creating or altering tables to the `migrate:up` section of a migration file.

```sql
-- migrate:up
create table users (
  id integer,
  name varchar(255),
  email varchar(255) not null
);

-- migrate:down
```

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

### Wait for Database Availability (CLI)

Source: https://context7.com/amacneil/dbmate/llms.txt

Use `dbmate wait` to block until the database server is reachable. It does not verify the database exists, only that the server accepts connections. A custom timeout can be specified with `--wait-timeout`.

```sh
export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_dev?sslmode=disable"

dbmate wait
# (no output if already available)
# Waiting for database....  (printed while retrying)

# Custom timeout
dbmate --wait-timeout=30s wait

# Combine --wait with another command
dbmate --wait migrate
```

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

### Wait for Database Availability

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Wait until the database server becomes available. This is useful in CI/CD pipelines or during application startup.

```sh
dbmate wait
```

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

### Drop Database

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Drop the database.

```sh
dbmate drop
```

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

### Dump Database Schema

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Write the current database schema to a 'schema.sql' file. This is useful for version control.

```sh
dbmate dump
```

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

### Connect to BigQuery in GCP

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Use this format for `DATABASE_URL` when connecting to BigQuery in GCP. Ensure `GOOGLE_APPLICATION_CREDENTIALS` is set for authentication.

```sh
bigquery://projectid/location/dataset
```

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

### Check Migration Status (Go Library)

Source: https://context7.com/amacneil/dbmate/llms.txt

The `db.Status` method prints migration status to `db.Log` and returns the count of pending migrations. It can be used to exit with an error if migrations are pending.

```go
package main

import (
    "fmt"
    "net/url"
    "os"

    "github.com/amacneil/dbmate/v2/pkg/dbmate"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/postgres"
)

func main() {
    u, _ := url.Parse("postgres://postgres:password@127.0.0.1:5432/myapp?sslmode=disable")
    db := dbmate.New(u)
    db.Log = os.Stdout

    pending, err := db.Status(false /* quiet */)
    if err != nil {
        panic(err)
    }

    if pending > 0 {
        fmt.Fprintf(os.Stderr, "ERROR: %d pending migration(s)\n", pending)
        os.Exit(1)
    }
}
```

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

### SQLite Relative Path Connection

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Connect to a SQLite database using a relative path. The database file will be created relative to the current directory.

```sh
DATABASE_URL="sqlite:db/database.sqlite3"
```

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

### MySQL Standard Connection

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Standard connection string for MySQL databases.

```sh
DATABASE_URL="mysql://username:password@127.0.0.1:3306/database_name"
```

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

### dbmate wait CLI command

Source: https://context7.com/amacneil/dbmate/llms.txt

Waits until the database server is reachable. It does not verify that the named database exists, only that the server accepts connections. A custom timeout can be specified.

```APIDOC
## dbmate wait

### Description
Blocks until the database server is reachable (up to `--wait-timeout`, default 60s). Does not verify that the named database exists, only that the server accepts connections.

### Usage
```sh
export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_dev?sslmode=disable"

dbmate wait
# (no output if already available)
# Waiting for database....  (printed while retrying)

# Custom timeout
dbmate --wait-timeout=30s wait

# Combine --wait with another command
dbmate --wait migrate
```
```

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

### SQL Migration File Format

Source: https://context7.com/amacneil/dbmate/llms.txt

SQL migration files use `-- migrate:up` and `-- migrate:down` directives to define schema changes. Multiple up/down sections are supported within a single file.

```sql
-- db/migrations/20240315120000_create_users_and_posts.sql

-- migrate:up
CREATE TABLE users (
  id   SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE
);

-- migrate:down
DROP TABLE users;

-- migrate:up
CREATE TABLE posts (
  id      SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title   VARCHAR(255) NOT NULL,
  body    TEXT
);

-- migrate:down
DROP TABLE posts;
```

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

### Apply Pending Migrations with dbmate migrate

Source: https://context7.com/amacneil/dbmate/llms.txt

Applies only pending migrations without creating the database first. Use --no-dump-schema to skip updating schema.sql.

```bash
export DATABASE_URL="mysql://root:password@127.0.0.1:3306/myapp"

dbmate migrate

# Skip auto-updating schema.sql
dbmate --no-dump-schema migrate
```

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

### db.Status Go Library Function

Source: https://context7.com/amacneil/dbmate/llms.txt

Prints the migration status to the configured logger (`db.Log`) and returns the count of pending migrations. Exits with an error if there are pending migrations and `quiet` is false.

```APIDOC
## db.Status

### Description
Prints migration status to `db.Log` and returns the number of pending migrations.

### Usage
```go
package main

import (
    "fmt"
    "net/url"
    "os"

    "github.com/amacneil/dbmate/v2/pkg/dbmate"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/postgres"
)

func main() {
    u, _ := url.Parse("postgres://postgres:password@127.0.0.1:5432/myapp?sslmode=disable")
    db := dbmate.New(u)
    db.Log = os.Stdout

    pending, err := db.Status(false /* quiet */)
    if err != nil {
        panic(err)
    }

    if pending > 0 {
        fmt.Fprintf(os.Stderr, "ERROR: %d pending migration(s)\n", pending)
        os.Exit(1)
    }
}
```
```

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

### Dump Schema with Custom Arguments

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Dump the database schema and pass additional arguments directly to the underlying database dump utility (e.g., mysqldump or pg_dump).

```sh
dbmate dump -- [...]
```

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

### Configure ClickHouse ZooKeeper Path

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Specify a custom path for the migration table in ClickHouse/ZooKeeper using the `zoo_path` parameter.

```sh
DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_cluster&zoo_path=/zk/path/tables"
```

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

### Configure ClickHouse Replica Macro

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Set a custom replica macro for the replica name in the replicated migration table engine using the `replica_macro` parameter.

```sh
DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_cluster&replica_macro={my_replica}"
```

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

### Configure ClickHouse Cluster Connection

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Configure ClickHouse connections to work with a cluster by setting the `on_cluster` query parameter. This enables cluster statements and replicated migration tables.

```sh
DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_cluster"
```

```sh
DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_cluster=true"
```

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

### Create Schema Migrations Table SQL

Source: https://github.com/amacneil/dbmate/blob/main/README.md

This SQL statement creates the `schema_migrations` table if it does not already exist. It is used by dbmate to track applied migrations.

```sql
CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(255) PRIMARY KEY
)
```

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

### Custom Wait Timeout for Database

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Specify a custom timeout for the database wait using the `--wait-timeout` flag. If the database is not available within the specified time, an error will be returned.

```sh
$ dbmate --wait-timeout=5s wait
Waiting for database.....
Error: unable to connect to database: dial tcp 127.0.0.1:5432: connect: connection refused
```

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

### Roll Back Most Recent Migration with dbmate

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Execute `dbmate rollback` to revert the last applied migration. This requires the `migrate:down` section to be implemented in the SQL file.

```sh
$ dbmate rollback
Rolling back: 20151127184807_create_users_table.sql
Rolled back: 20151127184807_create_users_table.sql in 123µs
Writing: ./db/schema.sql
```

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

### MySQL Connection via Unix Socket

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Connect to MySQL using a Unix socket by specifying the `socket` parameter with the file path.

```sh
DATABASE_URL="mysql://username:password@/database_name?socket=/var/run/mysqld/mysqld.sock"
```

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

### db.FindMigrations Go Library Function

Source: https://context7.com/amacneil/dbmate/llms.txt

Returns a slice of Migration structs representing all discovered migration files along with their applied status. This is useful for inspecting the current state of migrations.

```APIDOC
## db.FindMigrations

### Description
Returns a slice of `Migration` structs representing all discovered migration files with their applied status.

### Usage
```go
package main

import (
    "fmt"
    "net/url"

    "github.com/amacneil/dbmate/v2/pkg/dbmate"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/postgres"
)

func main() {
    u, _ := url.Parse("postgres://postgres:password@127.0.0.1:5432/myapp?sslmode=disable")
    db := dbmate.New(u)

    migrations, err := db.FindMigrations()
    if err != nil {
        panic(err)
    }

    for _, m := range migrations {
        status := "pending"
        if m.Applied {
            status = "applied"
        }
        fmt.Printf("[%s] version=%s file=%s\n", status, m.Version, m.FileName)
    }
    // Output:
    // [applied] version=20240101000000 file=20240101000000_create_users_table.sql
    // [pending] version=20240315120000 file=20240315120000_create_posts_table.sql
}
```
```

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

### Check Migration Status

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Display the status of all migrations. Supports options like --exit-code and --quiet for scripting.

```sh
dbmate status
```

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

### Configure ClickHouse Cluster Macro

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Specify a custom cluster macro for ON CLUSTER statements and the replicated migration table engine's ZooKeeper path using the `cluster_macro` parameter.

```sh
DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name?on_cluster&cluster_macro={my_cluster}"
```

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

### PostgreSQL Connection via Unix Socket

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Connect to PostgreSQL using a Unix socket by specifying the `socket` parameter with the directory path.

```sh
DATABASE_URL="postgres://username:password@/database_name?socket=/var/run/postgresql"
```

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

### PostgreSQL Connection with SSL Disabled

Source: https://github.com/amacneil/dbmate/blob/main/README.md

When connecting to PostgreSQL, disable SSL by adding `sslmode=disable` to the connection string if TLS is not required.

```sh
DATABASE_URL="postgres://username:password@127.0.0.1:5432/database_name?sslmode=disable"
```

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

### PostgreSQL Default User Connection

Source: https://github.com/amacneil/dbmate/blob/main/README.md

If the username is omitted, it defaults to the `PGUSER` environment variable or the OS username.

```sh
DATABASE_URL="postgres:///database_name?socket=/var/run/postgresql"
```

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

### Check ClickHouse Database Status

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Use this command to check the status of your ClickHouse database migrations. Ensure the clickhouse:// scheme is preferred for connections.

```sh
dbmate --driver clickhouse --url "tcp://username:password@127.0.0.1:9000/database_name" status
```

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

### Rollback Last Migration in Go

Source: https://context7.com/amacneil/dbmate/llms.txt

Programmatically roll back the most recently applied migration using the `dbmate.Rollback` function. Handles the `dbmate.ErrNoRollback` error if no migrations are available to roll back.

```go
package main

import (
    "fmt"
    "net/url"

    "github.com/amacneil/dbmate/v2/pkg/dbmate"
    _ "github.com/amacneil/dbmate/v2/pkg/driver/postgres"
)

func main() {
    u, _ := url.Parse("postgres://postgres:password@127.0.0.1:5432/myapp?sslmode=disable")
    db := dbmate.New(u)

    if err := db.Rollback(); err != nil {
        if err == dbmate.ErrNoRollback {
            fmt.Println("Nothing to roll back")
            return
        }
        panic(err)
    }
    fmt.Println("Rollback complete")
}
```

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

### PostgreSQL Passwordless Authentication

Source: https://github.com/amacneil/dbmate/blob/main/README.md

For passwordless authentication methods like peer auth, omit the password from the connection string. The `@` symbol is still required.

```sh
DATABASE_URL="postgres://username@/database_name?socket=/var/run/postgresql"
```

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

### Rollback Last Migration (Alias)

Source: https://github.com/amacneil/dbmate/blob/main/README.md

An alias for the 'rollback' command to roll back the most recent migration.

```sh
dbmate down
```

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

### Rollback Last Migration with dbmate rollback

Source: https://context7.com/amacneil/dbmate/llms.txt

Rolls back the most recently applied migration by executing its -- migrate:down block. The --no-dump-schema flag can be used to skip updating schema.sql.

```bash
export DATABASE_URL="postgres://postgres:password@127.0.0.1:5432/myapp_dev?sslmode=disable"

dbmate rollback

# Alias
dbmate down
```

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

### ClickHouse Native TCP Connection

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Connect to ClickHouse using the native protocol, which defaults to port 9000.

```sh
DATABASE_URL="clickhouse://username:password@127.0.0.1:9000/database_name"
```

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

### SQLite Migration Transaction Setting

Source: https://github.com/amacneil/dbmate/blob/main/README.md

For certain SQLite settings like `journal_mode`, transactions must be disabled for the migration file. Otherwise, the migration will fail.

```sql
-- migrate:up transaction:false
PRAGMA journal_mode = WAL;
```

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

### ClickHouse HTTP/HTTPS Connection

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Connect to ClickHouse via HTTP (default port 8123) or HTTPS (default port 8443) using the respective URL schemes.

```sh
# HTTP (Defaults to port 8123)
DATABASE_URL="clickhouse+http://username:password@127.0.0.1:8123/database_name"
```

```sh
# HTTPS (Defaults to port 8443)
DATABASE_URL="clickhouse+https://username:password@127.0.0.1:8443/database_name"
```

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

### Rollback Last Migration

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Roll back the most recently applied migration.

```sh
dbmate rollback
```

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

### Spanner Migration DDL Transaction Control

Source: https://github.com/amacneil/dbmate/blob/main/README.md

Spanner does not allow DDL within explicit transactions. Use `transaction:false` on migrations containing DDL statements.

```sql
-- migrate:up transaction:false
CREATE TABLE ...

-- migrate:down transaction:false
DROP TABLE ...
```

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

### Migration Option: Disable Transaction

Source: https://context7.com/amacneil/dbmate/llms.txt

Use `transaction:false` directive for migrations that cannot run within a transaction, such as certain DDL statements in PostgreSQL or Spanner.

```sql
-- db/migrations/20240316000000_add_enum_value.sql

-- migrate:up transaction:false
ALTER TYPE user_status ADD VALUE 'suspended' AFTER 'active';

-- migrate:down transaction:false
-- Note: removing enum values is not supported in PostgreSQL
```

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