DOCSEARCHbyAlgolia
Submit Docs

Refinery SQL Migration Toolkit

repository·main·Indexed 23 days ago

https://github.com/rust-db/refinery

A SQL migration toolkit for Rust that manages database schema changes via embedded migrations in Rust code or a dedicated CLI tool. It supports drivers including postgres, tokio-postgres, mysql, mysql_async, rusqlite, and tiberius. Refinery supports both SQL and Rust-based migrations with strictly versioned (V) and non-contiguous (U) naming conventions. Key features include the embed_migrations! macro for binary embedding, async connection pool support for Deadpool and bb8, and a CLI for managing migrations through configuration files or environment variables.

Tokens
7.9K
Snippets
19
Records
48
Agent score
81%

What's inside refinery

  1. Understand how refinery manages migrations

    main

    Refinery tracks migration state by creating a internal table in your database that stores the versions and metadata of all applied migrations.

    When a Runner is executed:

    1. It compares the applied migrations in the database with the available migrations in your code.
    2. It checks for divergent migrations (where checksums don't match) or missing migrations.
    3. It executes any unapplied migrations.

    Transaction Behavior

    • Default: Each migration is run in its own single transaction.
    • Grouped: You can configure the runner to wrap the entire execution of all migrations in a single transaction by calling .set_grouped(true) on the Runner.

  2. Define migrations using SQL or Rust

    main

    Refinery supports two types of migrations:

    1. SQL Migrations: Files ending in .sql.
    2. Rust Migrations: Modules containing a function named migration that returns a String.

    Naming Convention

    All migration files (both .sql and .rs) must follow the pattern [U|V]{1}__{2}.[sql|rs], where {1} is the version and {2} is the name.

    Versioning Types

    • Strictly Versioned (V): Use the V prefix (e.g., V1__initial.sql) for contiguous migrations where the next version is always greater than the previous. Use this if migrations are deployed in sequential order.
    • Non-contiguous (U): Use the U prefix (e.g., U11__update.sql) for migrations that might be merged or deployed out of order. This provides flexibility for teams where multiple developers might create migrations simultaneously.

    Version Number Types

    • By default, version numbers are i32 (signed 32-bit integers).
    • To use i64 versions, enable the int8-versions feature. Warning: Enabling this on an existing database will break checksums for all previously applied migrations.

  3. Rollback migrations in refinery

    main
    Refinery does not support an automatic undo or rollback command. Following the philosophy of early Flyway, to undo a migration, you must generate a new migration file that explicitly contains the logic required to reverse the changes made by the previous migration.

  4. Run migrations using a configuration file

    main

    To execute migrations, use the migrate command. You must specify the path to your migrations directory using the -p flag and the path to your configuration file using the -c flag.

    $ refinery migrate -c sqlite_refinery.toml -p ./sql_migrations

  5. Install refinery and configure database drivers

    main

    To use refinery, add it to your Cargo.toml dependencies and enable the feature corresponding to your database driver.

    Supported drivers include:

    • postgres
    • tokio-postgres
    • mysql
    • mysql_async
    • rusqlite
    • tiberius

    If you are using a driver not explicitly listed (like SQLx), you can provide a Config object instead of a connection type, as Config implements Migrate. However, you must still enable the appropriate driver feature (e.g., postgres, mysql, etc.) for Runner::run or tokio-postgres/mysql_async for Runner::run_async to function.

  6. Run migrations using a database URI environment variable

    main

    If your database connection string is stored in an environment variable (e.g., DB_URI), you can run migrations by passing the variable name to the -e flag. This is particularly useful when running Refinery inside Docker containers.

    $ refinery migrate -e DB_URI -p ./sql_migrations

  7. Install the Refinery CLI

    main

    The refinery_cli binary is named refinery. You can install it using several methods depending on your operating system:

    • Cargo (Rust programmers): Use cargo install refinery_cli.
    • Debian/Ubuntu: Download the .deb file from the releases page and install it using dpkg -i.
    • Arch Linux: Install via AUR using yay refinery_cli.
    • NixOS: Install via nix-env -iA refinery-cli.
    • Precompiled Binaries: Static executables for Windows, macOS, and Linux are available on the GitHub releases page.
    $ cargo install refinery_cli

  10. Understanding Migration Verification and Error States

    main

    Refinery performs verification to ensure the integrity of the migration history between the database and the filesystem. During verification, the following error conditions can occur:

    • Divergent Version (Kind::DivergentVersion): Occurs when a migration exists in the database with the same version number as a migration on the filesystem, but the name or checksum differs. This indicates a migration file was modified after it was applied.
    • Missing Version (Kind::MissingVersion):
      • If abort_missing is true: Occurs if a migration recorded in the database is missing from the filesystem, or if a versioned migration on the filesystem has a version lower than the current database version but was not applied.
      • If abort_missing is false: The system logs an error but continues.
    • Repeated Version (Kind::RepeatedVersion): Occurs if multiple migrations with the same version are found in the migration set to be applied.

    Verification behavior can be toggled using abort_divergent and abort_missing flags to decide whether to treat these discrepancies as hard errors or mere log warnings.

  11. Parse database configuration from URLs

    main

    Refinery supports parsing database connection URLs into a Config object. The supported schemes are:

    • mysql -> ConfigDbType::Mysql
    • postgres or postgresql -> ConfigDbType::Postgres
    • sqlite -> ConfigDbType::Sqlite
    • mssql -> ConfigDbType::Mssql

    Special Query Parameters:

    • Postgres: Use ?sslmode=require to enable TLS or ?sslmode=disable to disable it.
    • Mssql (Tiberius): Use ?trust_cert=true or ?trust_cert=false to configure certificate trust.

  12. Initialize a Config instance

    main

    You can create a Config instance in several ways depending on your source of truth:

    1. Manually: Use Config::new(db_type) to start with a specific database type.
    2. From a Connection String: Use Config::from_str(url_str) to parse a standard database URL.
    3. From an Environment Variable: Use Config::from_env_var(name) to read a URL from a specified environment variable.
    4. From a TOML File: If the toml feature is enabled, use Config::from_file_location(path) to load configuration from a file. For Sqlite, relative paths in the config file are automatically canonicalized relative to the config file's location.