SQLX migrator

A Rust library for writing SQLX migrations using Rust instead of SQL.

License	Crates Version	Docs

Supported Databases:

• PostgreSQL
• SQLite
• MySql
• Any

Installation

Add sqlx_migrator to your Cargo.toml with the appropriate database feature:

sqlx_migrator = { version = "0.18.0", features=["postgres"] }

OR

sqlx_migrator = { version = "0.18.0", features=["mysql"] }

OR

sqlx_migrator = { version = "0.18.0", features=["sqlite"] }

OR

sqlx_migrator = { version = "0.18.0", features=[
    "any",
    # Plus any one of above database driver
    ] }

Usage

To use sqlx_migrator, implement the Operation trait to define your migration logic. Here's an example using PostgreSQL:

use sqlx_migrator::error::Error;
use sqlx_migrator::operation::Operation;

pub(crate) struct FirstOperation;

#[async_trait::async_trait]
impl Operation<sqlx::Postgres> for FirstOperation {
    // Up function runs apply migration
    async fn up(&self, connection: &mut sqlx::PgConnection) -> Result<(), Error> {
        sqlx::query("CREATE TABLE sample (id INTEGER PRIMARY KEY, name TEXT)")
            // NOTE: if you want to use connection multiple times pass `&mut *connection`
            // as a parameter instead of `connection`
            .execute(connection)
            .await?;
        Ok(())
    }

    // down migration runs down migration
    async fn down(&self, connection: &mut sqlx::PgConnection) -> Result<(), Error> {
        sqlx::query("DROP TABLE sample").execute(connection).await?;
        Ok(())
    }
}

After defining your operations, you can create a migration:

use sqlx_migrator::error::Error;
use sqlx_migrator::migration::Migration;
use sqlx_migrator::operation::Operation;

pub(crate) struct FirstMigration;

impl Migration<sqlx::Postgres> for FirstMigration {
    // app where migration lies can be any value
    fn app(&self) -> &str {
        "main"
    }

    // name of migration
    // Combination of migration app and name must be unique to work properly expects for virtual migration
    fn name(&self) -> &str {
        "first_migration"
    }

    // Use the parent function to add parents of a migration.
    // If you cannot access or create the parent migration easily, you can also use
    // `(A,N) where A: AsRef<str>, N: AsRef<str>` where A is the app name
    // and N is the name of the migration.
    fn parents(&self) -> Vec<Box<dyn Migration<sqlx::Postgres>>> {
        vec![]
        // vec![("main", "initial_migration"), AnotherInitialMigration]
    }

    // use operations function to add operation part of migration
    fn operations(&self) -> Vec<Box<dyn Operation<sqlx::Postgres>>> {
        vec![Box::new(FirstOperation)]
    }

    // Migration trait also have multiple other function see docs for usage
}

This migration can be represented in a simpler form using macros:

use sqlx_migrator::vec_box;
sqlx_migrator::migration!(
    sqlx::Postgres,
    FirstMigration,
    "main",
    "first_migration",
    vec_box![], 
    vec_box![FirstOperation]
);
// OR
sqlx_migrator::postgres_migration!(
    FirstMigration,
    "main",
    "first_migration",
    vec_box![], 
    vec_box![FirstOperation]
);

If your up and down queries are simple strings, you can simplify the implementation:

sqlx_migrator::postgres_migration!(
    FirstMigration,
    "main",
    "first_migration",
    sqlx_migrator::vec_box![],
    sqlx_migrator::vec_box![
        (
            "CREATE TABLE sample (id INTEGER PRIMARY KEY, name TEXT)",
            "DROP TABLE sample"
        )
    ]
);

Finally, create a migrator to run your migrations:

use sqlx_migrator::migrator::{Info, Migrate, Migrator};
use sqlx::Postgres;

#[tokio::main]
async fn main() {
    let uri = std::env::var("DATABASE_URL").unwrap();
    let pool = sqlx::Pool::<Postgres>::connect(&uri).await.unwrap();
    let mut migrator = Migrator::default();
    // Adding migration can fail if another migration with same app and name and different values gets added
    // Adding migrations add its parents, replaces and not before as well
    migrator.add_migration(Box::new(FirstMigration)).unwrap();
}

Running Migrations

You can run migrations directly or integrate them into a CLI:

Programmatic Execution

use sqlx_migrator::migrator::Plan;
let mut conn = pool.acquire().await?;
// use apply all to apply all pending migration
migrator.run(&mut *conn, Plan::apply_all()).await.unwrap();
// or use revert all to revert all applied migrations
migrator.run(&mut *conn, Plan::revert_all()).await.unwrap();
// If you need to apply or revert to certain stage than see `Plan` docs

CLI Integration

To integrate sqlx_migrator into your CLI, you can either use the built-in MigrationCommand or extend your own CLI with migrator support. Below are examples of both approaches:

Built-in Migration Command

use sqlx_migrator::cli::MigrationCommand;

MigrationCommand::parse_and_run(&mut *conn, Box::new(migrator)).await.unwrap();

Extending Your Own CLI with Migrator Support

#[derive(clap::Parser)]
struct Cli {
    #[command(subcommand)]
    sub_command: CliSubcommand
}

#[derive(clap::Subcommand)]
enum CliSubcommand {
    #[command()]
    Migrator(sqlx_migrator::cli::MigrationCommand)
}

impl Cli {
    async fn run() {
        let cli = Self::parse();
        // create connection
        match cli.sub_command {
            Migrator(m) => {
                m.run(&mut conn, Box::new(migrator)).await.unwrap()
            }
        }
    }
}

Migrate from old migrator migration to sqlx_migrator migration

To transition from your old migration system to sqlx_migrator, follow these steps:

1. Create Corresponding Migrations

For each migration in your old system, create an equivalent migration in sqlx_migrator format. This ensures all historical migrations are properly represented in the new system.

2. Implement the OldMigrator Trait

The OldMigrator trait allows you to bridge between your old migration system and sqlx_migrator. Here's a implementation example for sqlx sql:

use sqlx_migrator::sync::OldMigator;
use sqlx::Database

struct SqlxMigrator;

#[async_trait::async_trait]
impl<DB, T> OldMigrator<DB> for SqlxMigrator
    DB: Database,
{
    async fn applied_migrations(
        &self,
        connection: &mut <DB as Database>::Connection,
    ) -> Result<Vec<Box<dyn Migration<DB>>>, Error> {
        let old_migrations = sqlx::query!(
            "SELECT version, description FROM _sqlx_migrations ORDER BY version"
        )
        .fetch_all(connection)
        .await?;
        let mut migrations = vec![];
        // your custom logic here for example for sqlx you may use a migration name as combination of both version
        // and description or only one between them it is your choice and sqlx migrator have no preference between them
        Ok(migrations)
    }
}

3. Run sync

Before running your main migration code, execute the sync operation

use sqlx_migrator::Synchronize;

let sqlx_sql_migrator = SqlxMigrator;
migrator.sync(&mut *conn, &sqlx_sql_migrator).await?;

You can also implement above functionality for other migrator such as diesel, seaorm etc as well as for renaming table name of sqlx_migrator