21 releases (12 breaking)

new 0.14.0 Mar 28, 2024
0.12.0 Feb 21, 2024
0.10.0 Nov 24, 2023
0.9.1 Jul 18, 2023
0.1.0 May 28, 2022

#186 in Database interfaces

Download history 5/week @ 2024-01-03 6/week @ 2024-01-17 9/week @ 2024-01-24 2/week @ 2024-02-14 436/week @ 2024-02-21 47/week @ 2024-02-28 5/week @ 2024-03-06 10/week @ 2024-03-13 401/week @ 2024-03-20

466 downloads per month

MIT license

115KB
2.5K SLoC

SQLX migrator

Migrator library for writing sqlx migration using Rust instead of SQL

License Crates Version Docs
License: MIT Crate 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.14.0", features=["postgres"] }

OR

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

OR

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

OR

sqlx_migrator = { version = "0.14.0", 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,
        state: &(),
    ) -> 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(
        connection: &mut sqlx::PgConnection,
        state: &(),
    ) -> 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 expects for virtual migration
    fn name(&self) -> &str {
        "first_migration"
    }

    // Use the parent function to add parents of a migration.
    // If you cannot access the parent migration, 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
}

Which can be represented in simple form as

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

If your up and down query are simple string only than you can directly uses (U,D) where U: AsRef<str>, D: AsRef<str> to implement Operation trait So above example can be further simplified as

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

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 direct run

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();

Or you can create cli using

use sqlx_migrator::cli::MigrationCommand;

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

If you want to extend your own clap based cli then you can add migrator to sub command enum and then run migrator

#[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();
        match cli.sub_command {
            Migrator(m) => {
                m.run(Box::new(migrator), &mut conn).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

use sqlx_migrator::cli::MigrationCommand;
MigrationCommand::parse_and_run(Box::new(migrator), &mut conn).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(())
    }
}

Dependencies

~11–27MB
~436K SLoC