SQLX migrator

Migrator library for writing sqlx migration using Rust instead of SQL

License	Crates Version	Docs

Supported Databases:

• PostgreSQL
• SQLite
• MySql
• Any

Installation

To use sqlx migrator you can configure Cargo.toml as shown below according to your requirements

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

OR

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

OR

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

OR

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

Usage

To use sqlx_migrator first you need to implement Operation trait to write your sqlx operation below are examples for using postgres based migration

use sqlx_migrator::error::Error;
use sqlx_migrator::operation::Operation;
// Its better to use sqlx imported from sqlx_migrator
use sqlx_migrator::sqlx;

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)")
            .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 creation of operation you can implement Migration struct to create single migration

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

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
    fn name(&self) -> &str {
        "first_migration"
    }

    // use parents function to add parents of a migrations.
    fn parents(&self) -> Vec<Box<dyn Migration<DB>>> {
        vec![]
    }

    // 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
}

Now at last you need to create migrator for your database to run migrations

use sqlx_migrator::migrator::{Info, Migrate, Migrator};
use sqlx_migrator::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();
    migrator.add_migration(Box::new(FirstMigration));
}

Now you can use two ways to run migrator either directly running migration or creating cli from migrator For directly run

// use apply all to apply all pending migration
migrator.apply_all(&pool).await.unwrap();
// or use revert all to revert all applied migrations
migrator.revert_all(&pool).await.unwrap();

Or you can create cli

sqlx_migrator::cli::run(Box::new(migrator), &pool).await.unwrap();

Migrate from sqlx default sql based migration

To migrate from sqlx sql based migration you have two alternative:

1. Write all sql migration as rust operation
2. Write single rust based operation which apply and revert all sqlx sql based migration

Option: 1

Can be easily applied by following above usage docs where you only need to write your sql based migration as sqlx query Then you can create cli for migrator

sqlx_migrator::cli::run(Box::new(migrator), &pool).await.unwrap();

and run fake apply cli command <COMMAND_NAME> apply --fake which actually doesn't apply migration query but only update migration table

Option: 2

To run all sqlx sql based migration as single command create new operation

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

pub(crate) struct SqlxOperation;

#[async_trait::async_trait]
impl Operation<sqlx::Postgres> for SqlxOperation {
    async fn up(&self, connection: &mut sqlx::PgConnection) -> Result<(), Error> {
        sqlx::migrate!("migrations").run(connection).await?;
        Ok(())
    }

    async fn down(&self, connection: &mut sqlx::PgConnection) -> Result<(), Error> {
        sqlx::migrate!("migrations").undo(connection, 0).await?;
        Ok(())
    }
}