A tool for automated migrations for PostgreSQL, SQLite and MySQL.

Overview

Models

Models is an implementation for a SQL migration management tool. It supports PostgreSQL, MySQL, and SQLite.

Quick Start

install the CLI by running the following command:

$ cargo install models-cli

Now run the following command to create an environment file with the DATABASE_URL variable set:

$ echo "DATABASE_URL=sqlite://database.db" > .env

Alternatively it can be set as a environment variable with the following command:

$ export DATABASE_URL=sqlite://database.db

We now can create the database running the following command:

$ models database create

This command will have created an SQLite file called database.db. You can now derive the Model trait on your structures, and models will manage the migrations for you. For example, write at src/main.rs:

#![allow(dead_code)]
use models::Model; 

#[derive(Model)]
struct User {
    #[primary_key]
    id: i32,
    #[unique]
    email: String,
    password: String,
    #[default(0)]
    is_admin: bool,
}

#[derive(Model)]
struct Post {
    #[primary_key]
    id: i32,
    #[foreign_key(User.id)]
    author: String,
    #[default("<Untitled Post>")]
    title: String,
    content: String,
}

#[derive(Model)]
struct PostLike {
    #[foreign_key(User.id, on_delete="cascade")]
    #[primary_key(post_id)]
    user_id: i32,
    #[foreign_key(Post.id, on_delete="cascade")]
    post_id: i32,
}

#[derive(Model)]
struct CommentLike {
    #[foreign_key(User.id)]
    #[primary_key(comment_id)]
    user_id: i32,
    #[foreign_key(Comment.id)]
    comment_id: i32,
    #[default(0)]
    is_dislike: bool,
}

#[derive(Model)]
struct Comment {
    #[primary_key]
    id: i32,
    #[foreign_key(User.id)]
    author: i32,
    #[foreign_key(Post.id)]
    post: i32,
}
fn main() {}

If you now run the following command, your migrations should be automatically created.

$ models generate

The output should look like this:

Generated: migrations/1632280793452 user
Generated: migrations/1632280793459 post
Generated: migrations/1632280793465 postlike
Generated: migrations/1632280793471 comment
Generated: migrations/1632280793476 commentlike

You can check out the generated migrations at the migrations/ folder. To execute these migrations you can execute the following command:

models migrate run

The output should look like this:

Applied 1631716729974/migrate user (342.208µs)
Applied 1631716729980/migrate post (255.958µs)
Applied 1631716729986/migrate comment (287.792µs)
Applied 1631716729993/migrate postlike (349.834µs)
Applied 1631716729998/migrate commentlike (374.625µs)

If we later modify those structures in our application, we can generate new migrations to update the tables.

Reverting migration

Models can generate down migrations with the -r flag. Note that simple and reversible migrations cannot be mixed:

$ models generate -r

In order to revert the last migration executed you can run:

$ models migrate revert

If you later want to see which migrations are yet to be applied you can also excecute:

$ models migrate info

Avaibale Attributes

primary_key

It's used to mark the primary key fo the table.

    #[primary_key]
    id: i32, 

for tables with multicolumn primary keys, the following syntax is used:

    #[primary_key(second_id)]
    first_id: i32, 
    second_id: i32, 

This is equivalent to

    PRIMARY KEY (first_id, second_id),

foreign_key

It is used to mark a foreign key constraint.

    #[foreign_key(User.id)]
    user: i32, 

It can also specify on_delete and on_update constraints:

    #[foreign_key(User.id, on_delete="cascade")]
    user_id: i32, 

This is equivalent to

    FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE,

default

It can be used to set a default value for a column.

    #[default(false)] // when using SQLite use 0 or 1
    is_admin: bool, 
    #[default("")]
    text: String, 
    #[default(0)]
    number: i32, 

unique

It is used to mark a unique constraint.

    #[unique]
    email: String, 

For multicolumn unique constraints the following syntax is used:

    #[unique(post_id)]
    user_id: String,
    post_id: i32,

This is equivalent to

    UNIQUE (user_id, post_id),
Comments
  • Supported Databases

    Supported Databases

    What databases are currently supported? I was about to play around with it and was wondering if it currently is even possible to create migrations other than sqlite that are supported by sqlx right now. I see some occurrences of postgresql in the code and dialect sections but i don't really now how to navigate the code yet to answer that myself. It is unfortunately not mentioned in the readme etc. but some places in the code made it look like it can determine the database from the connection string DATABASE_URL=sqlite:// ... DATABASE_URL=postgres:// but it looks like it is only generating sqlite compatible migrations atm.

    opened by pythoneer 5
  • Postgresql serial type

    Postgresql serial type

    in postgresql there is a handy serial type would be very convenient to mark some fields (e.g. id) to have this type instead of the regular i32 -> integer conversion or in general more power to choose types. Is there any other way to have auto inc ids?

    opened by pythoneer 2
  • Moves with constraints cause conflicts

    Moves with constraints cause conflicts

    constraint names are global, so they should be dropped before a move in PostgreSQL and MySQL, as they otherwise conflict with constraints on the tempopary table.

    bug 
    opened by tvallotton 0
  • False positive dependecy cycles

    False positive dependecy cycles

    False positive errors are triggered when checking for dependency cycles if one of the dependencies is not a model. This could be fixed either by adjusting the error message of by changing the mechanism to allow this kind of behavior. This kind of permissiveness could be desirable when the structure belongs to another crate. Example:

    struct A {
       id: i32, 
    }
    
    #[derive(Models)]
    struct B {
       #[foreign_key(A.id)]
       a: i32
    }
    
    bug 
    opened by tvallotton 0
  • How to combine this with rocket_auth

    How to combine this with rocket_auth

    Is there an example of how to use this together with rocket_auth?

    In the rocket_auth example, I see that the Users table is being created, but I want that to be part of a migration: https://github.com/tvallotton/rocket_auth/blob/4046459ffec839d6f2cf8884c30a6d8cc4f8f52c/examples/sqlite.rs#L65

    opened by yzernik 1
  • Default literals should be compatible with all dialects.

    Default literals should be compatible with all dialects.

    Currently default SQLite boolean defaults require to use a 0 and 1, whereas PostgreSQL and MySQL use boolean literals. It would be better if true and false were replaced with 0 and 1 when using SQLite automatically.

    opened by tvallotton 0
  • Error when using `sqlx migrate generate` on existing project

    Error when using `sqlx migrate generate` on existing project

    I already had a code base with handwritten (up and down) migrations. I changed my Rust struct and annotated it properly. For some reason I get the following error:

    $ sqlx migrate generate
        Finished dev [unoptimized + debuginfo] target(s) in 16.92s
    thread 'main' panicked at 'called `Result::unwrap()` on an `Err` value: Error("expected value", line: 1, column: 33)', /home/user/.cargo/registry/src/github.com-1ecc6299db9ec823/sqlx-models-cli-0.1.1/src/generate.rs:74:43
    note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace
    

    I tried to reproduce it inside a clean environment but that failed. In a new, clean project everything works fine. Sadly I can't link you the code.

    opened by mainrs 2
  • add a Json<T> and Blob<T> type for serializable objects

    add a Json and Blob type for serializable objects

    create a Json type can hold any type T that implements Serialize and Deserialize. Json probably using serde_json and Blob or Bytes using bincode. they should also implement Deref and DerefMut to T.

    opened by tvallotton 0
Owner
null
🧰 The Rust SQL Toolkit. An async, pure Rust SQL crate featuring compile-time checked queries without a DSL. Supports PostgreSQL, MySQL, SQLite, and MSSQL.

SQLx ?? The Rust SQL Toolkit Install | Usage | Docs Built with ❤️ by The LaunchBadge team SQLx is an async, pure Rust† SQL crate featuring compile-tim

launchbadge 7.6k Dec 31, 2022
cogo rust coroutine database driver (Mysql,Postgres,Sqlite)

cdbc Coroutine Database driver Connectivity.based on cogo High concurrency,based on coroutine No Future<'q,Output=*>,No async fn, No .await , no Poll*

co-rs 10 Nov 13, 2022
postgres-ical - a PostgreSQL extension that adds features related to parsing RFC-5545 « iCalendar » data from within a PostgreSQL database

postgres-ical - a PostgreSQL extension that adds features related to parsing RFC-5545 « iCalendar » data from within a PostgreSQL database

Edgar Onghena 1 Feb 23, 2022
Effortless database migrations for SeaORM!

Sea Migrations Effortless database migrations for SeaORM! This crate aims to provide a simple solution to doing database migrations with SeaORM. Featu

Oscar Beaumont 6 Sep 12, 2022
Mycelite is a SQLite extension that allows you to synchronize changes from one instance of SQLite to another.

Mycelite What is Mycelite? Mycelite is a SQLite extension that allows you to synchronize changes from one instance of SQLite to another. Currently, it

Mycelial 16 Jan 2, 2023
Skybase is an extremely fast, secure and reliable real-time NoSQL database with automated snapshots and SSL

Skybase The next-generation NoSQL database What is Skybase? Skybase (or SkybaseDB/SDB) is an effort to provide the best of key/value stores, document

Skybase 1.4k Dec 29, 2022
Skytable is an extremely fast, secure and reliable real-time NoSQL database with automated snapshots and TLS

Skytable is an effort to provide the best of key/value stores, document stores and columnar databases, that is, simplicity, flexibility and queryability at scale. The name 'Skytable' exemplifies our vision to create a database that has limitless possibilities. Skytable was previously known as TerrabaseDB (and then Skybase) and is also nicknamed "STable", "Sky" and "SDB" by the community.

Skytable 1.4k Dec 29, 2022
High-performance, lock-free local and concurrent object memory pool with automated allocation, cleanup, and verification.

Opool: Fast lock-free concurrent and local object pool Opool is a high-performance Rust library that offers a concurrent and local object pool impleme

Khashayar Fereidani 8 Jun 3, 2023
CRUD example with Rocket, Toql and MySQL

Todo (Rocket, Toql, MySQL) This is a little REST server built with Rocket, Toql and MySQL. The server allows to call CRUD functions on a Todo item. It

Artos 7 Jan 5, 2022
fast & easy CLI and vscode extension specialized to format MySQL INSERT queries.

insertfmt fast & easy CLI specialized to format MySQL INSERT queries. format queries so that they look like a table. NOTE: If you wanna use the VSCode

canalun 7 May 2, 2023
Simple and handy btrfs snapshoting tool. Supports unattended snapshots, tracking, restoring, automatic cleanup and more. Backed with SQLite.

Description Simple and handy btrfs snapshoting tool. Supports unattended snapshots, tracking, restoring, automatic cleanup and more. Backed with SQLit

Eduard Tolosa 27 Nov 22, 2022
A highly scalable MySQL Proxy framework written in Rust

mysql-proxy-rs An implementation of a MySQL proxy server built on top of tokio-core. Overview This crate provides a MySQL proxy server that you can ex

AgilData 175 Dec 19, 2022
Asyncronous Rust Mysql driver based on Tokio.

mysql-async Tokio based asynchronous MySql client library for rust programming language. Installation Library hosted on crates.io. [dependencies] mysq

Anatoly I 292 Dec 30, 2022
Mysql client library implemented in rust.

mysql This crate offers: MySql database driver in pure rust; connection pool. Features: macOS, Windows and Linux support; TLS support via nativetls cr

Anatoly I 548 Dec 31, 2022
A user crud written in Rust, designed to connect to a MySQL database with full integration test coverage.

SQLX User CRUD Purpose This application demonstrates the how to implement a common design for CRUDs in, potentially, a system of microservices. The de

null 78 Nov 27, 2022
Gibbs MySQL Spyglass

Gibbs MySQL Spyglass Ahoy Matey! The Gibbs MySQL Spyglass is a application used to capture application traffic into a MySQL database. It is designed t

AgilData 82 Nov 14, 2021
rust-mysql-simple support library for the r2d2 connection pool

r2d2-mysql rust-mysql-simple support library for the r2d2 connection pool.

outersky 44 Nov 1, 2022
This project provides a Rust-based solution for migrating MSSQL databases to MySQL.

MSSQL to MySQL Database Migration A Rust project to migrate MSSQL databases to MySQL, including table structures, column data types, and table data ro

Bitalizer 2 Jul 10, 2023
Grsql is a great tool to allow you set up your remote sqlite database as service and CRUD(create/read/update/delete) it using gRPC.

Grsql is a great tool to allow you set up your remote sqlite database as service and CRUD (create/ read/ update/ delete) it using gRPC. Why Create Thi

Bruce Yuan 33 Dec 16, 2022