Blog

Dolt runs on Diesel

James Cor
REFERENCE
14 min read

Hello Rustaceans, welcome to the world of Dolt and Diesel! Dolt is a version controlled database that is a drop-in MySQL replacement. We've demonstrated Dolt's compatibility with a plethora of tools and ORMs. Diesel is an ORM and query builder for the Rust programming language. This blog will guide you through setting up Dolt with Diesel, showcasing some Dolt features in the process.

dolt_rust_diesel.png

Initialize Workspace

This guide assumes you've installed Rust and Dolt. All the code used in here can be found in this repo. I recommend cloning the repo and following along, as the blog will be a walkthrough of the code.

In one terminal, start the dolt server:

$ cd workspace
$ dolt sql-server

This will start a Dolt server on port 3306 with default user root and no password. Just leave this running in the background.

In a separate terminal, initialize the Diesel project:

$ cd workspace
$ cargo new --lib diesel_demo
    Creating library `diesel_demo` package
note: see more `Cargo.toml` keys and their definitions at https://doc.rust-lang.org/cargo/reference/manifest.html

In Cargo.toml, add the following dependencies:

[dependencies]
diesel = { version = "2.2.0", features = ["mysql"] }
dotenvy = "0.15"

Since Dolt is a MySQL drop-in replacement, we need to install the mysql feature for Diesel, and nothing else. Diesel requires that we specify the MYSQLCLIENT_LIB_DIR and MYSQLCLIENT_VERSION environment variables. I'm on Windows, so my environment variables look like this:

$ export MYSQLCLIENT_LIB_DIR="C:\Program Files\MySQL\MySQL Server 8.0\lib"
$ export MYSQLCLIENT_VERSION=8.0.29
$ cargo install diesel_cli --no-default-features --features mysql

For Unix-based systems, the environment variables would look like this:

$ export MYSQLCLIENT_LIB_DIR="/usr/local/mysql/lib"
$ export MYSQLCLIENT_VERSION=8.0.29
$ cargo install diesel_cli --no-default-features --features mysql

Set the DATABASE_URL environment variable to connect to the Dolt server:

$ echo DATABASE_URL=mysql://root@localhost/dolt_demo > .env

Run Diesel setup:

$ diesel setup

Create a migration:

$ diesel migration generate create_movies

Edit up.sql:

-- Your SQL goes here
CREATE TABLE movies (
    title VARCHAR(255) PRIMARY KEY,
    genre VARCHAR(255) NOT NULL,
    year INT NOT NULL,
    rating INT
);

Edit down.sql:

-- This file should undo anything in `up.sql`
DROP TABLE movies;

Apply migration:

$ diesel migration run

That's pretty much it for setting up the project from scratch. From this point onward, I'll be highlighting snippets from the starter code, and explaining what they do.

Connecting to Database

First and foremost, we need to connect to the Dolt server.

fn establish_connection() -> MysqlConnection {
    dotenv().ok();

    let database_url = env::var("DATABASE_URL").expect("DATABASE_URL must be set");
    MysqlConnection::establish(&database_url)
        .unwrap_or_else(|_| panic!("Error connecting to {}", database_url))
}

This function will return a mutable reference to a MysqlConnection object, which we can use to interact with the database. It'll read the DATABASE_URL environment variable (which we set earlier in the .env file) to connect to the Dolt server.

Interacting with Database

Let's go over the basic CRUD operations: SELECT, INSERT, UPDATE, and DELETE.

SELECTs

Since Rust is a statically typed language, we need to define a schema for the movies table before we can read from it. Diesel should've automatically generated a schema.rs file in the src directory. This file contains the schema for the movies table, which serves as reference for the Diesel ORM.

// @generated automatically by Diesel CLI.

diesel::table! {
    movies (title) {
        #[max_length = 255]
        title -> Varchar,
        #[max_length = 255]
        genre -> Varchar,
        year -> Integer,
        rating -> Nullable<Integer>,
    }
}

If you want to learn more about schemas, you can read the Diesel documentation.

In addition to the schema.rs file, we need to create a model in the models.rs file.

#[derive(Queryable, Selectable)]
#[diesel(table_name = movies)]
#[diesel(check_for_backend(diesel::mysql::Mysql))]
pub struct Movie {
    pub title:  String,
    pub genre:  String,
    pub year:   i32,
    pub rating: Option<i32>,
}

This defines a Rust struct that represents a row in the movies table that we can use to interact with the database. #[derive(Queryable)] generates code for Rust to load a SQL row into the Movie struct, allowing us to call .load(). #[derive(Selectable)] generates code to build a SELECT * ... query based off this struct.

Note that the Movie struct has a rating field of type Option<i32>, which corresponds with the Nullable<Integer> rating field described in the schema.rs file. This is how we handle NULL values in the database.

Lastly, let's look at the Rust code that does the actual selecting.

fn print_movies(conn: &mut MysqlConnection) {
    use self::schema::movies::dsl::*;
    ...
    let results = movies
        .select(Movie::as_select())
        .load(conn)
        .expect("Error loading movies");
    ...
}

This block uses the movies model, constructs a SELECT * FROM movies query, and loads the results into a Vec<Movie>. .expect(<err_msg>) is a Rust idiom that will panic with the provided error message if the load function returns an error.

Fortunately for those familiar with SQL, Diesel's query builder is very similar to SQL syntax.

INSERTs

Similarly to SELECT, we need to define a struct for inserting data into the movies table. In the models.rs file, we have the following struct:

#[derive(Insertable)]
#[diesel(table_name = movies)]
pub struct NewMovie<'a> {
    pub title:  &'a str,
    pub genre:  &'a str,
    pub year:   i32,
    pub rating: Option<i32>,
}

Next, we have the function that inserts a new movie into the movies table.

fn add_movie(conn: &mut MysqlConnection, new_title: &str, new_genre: &str, new_year: i32, new_rating: Option<i32>) {
    println!("Inserting '{new_title}'...");
    use self::schema::movies::dsl::*;
    let new_movie = NewMovie {
        title:  new_title,
        genre:  new_genre,
        year:   new_year,
        rating: new_rating,
    };

    let _ = diesel::insert_into(movies)
        .values(&new_movie)
        .execute(conn)
        .expect("Error inserting new movie");
}

This function uses diesel::insert_into to create an INSERT INTO movies ... query and passes in a reference to the NewMovie struct.

UPDATEs

UPDATE operations follow a very similar pattern to INSERT operations, except we're not required to define a new struct.

fn update_rating(conn: &mut MysqlConnection, update_title: &str, new_rating: Option<i32>) {
    use self::schema::movies::dsl::*;
    diesel::update(movies.filter(title.eq(&update_title)))
        .set(rating.eq(new_rating))
        .execute(conn)
        .expect("Error updating movie");
}

This block here is essentially running a UPDATE movies SET rating = <new_rating> WHERE title = <update_title> query.

DELETEs

Lastly, we have the DELETE operation.

fn remove_movie(conn: &mut MysqlConnection, delete_title: &str) {
    println!("Deleting '{delete_title}'...");
    use self::schema::movies::dsl::*;
    let _ = diesel::delete(movies.filter(title.eq(&delete_title)))
        .execute(conn)
        .expect("Error deleting movie");
}

This function is just a DELETE FROM movies WHERE title = <delete_title> query.

Dolt Operations

Dolt is often described as if Git and MySQL had a baby. We've gone over the typical database operations, but now let's go over some version control capabilities through Diesel.

Many Dolt operations are accessible within a SQL context through the use of system tables and procedures. Consequently, we'll have to rely on Diesel's sql_query (which allows us to run raw SQL queries) function to execute these operations.

Dolt Log

The Dolt equivalent of git log is dolt log; it shows the commit history of the database. This particular function is implemented in Dolt through a system table dolt_log.

Similar to interacting with plain tables like movies, we need to define a schema and model for the dolt_log table, so Diesel knows how to interact with our table.

schema.rs:

diesel::table! {
    dolt_log (commit_hash) {
        commit_hash -> Varchar,
        committer   -> Varchar,
        email       -> Varchar,
        date        -> Varchar,
        message     -> Varchar,
    }
}

models.rs:

#[derive(QueryableByName)]
#[diesel(table_name = dolt_log)]
pub struct DoltLog {
    pub commit_hash: String,
    pub committer:   String,
    pub email:       String,
    pub date:        String,
    pub message:     String,
}

Note the #[derive(QueryableByName)] instead of #[derive(Queryable)].

Then, we can define our print_dolt_log function in main.rs:

fn print_dolt_log(conn: &mut MysqlConnection) {
    println!("Retrieving Dolt log...");
    let query = "
        SELECT 
            commit_hash,
            committer,
            CAST(date as CHAR) as date,
            email,
            message
        FROM 
            dolt_log
        ";

    let results: Vec<DoltLog> = sql_query(query)
        .load(conn)
        .expect("Error loading log");

    for log in results {
        ...
    }
    ...
}

It's important to ensure that the projections/aliases in the query match the fields in the DoltLog struct.

Dolt Diff

The Dolt equivalent of git diff is dolt diff shows the changes between the working set and the last commit. dolt diff is also available in Dolt through a system table dolt_diff. As a result, accessing its contents is similar to dolt log.

schema.rs:

diesel::table! {
    dolt_diff_movies (to_commit) {
        #[max_length = 255]
        to_title  -> Nullable<Varchar>,
        #[max_length = 255]
        to_genre  -> Nullable<Varchar>,
        to_year   -> Nullable<Integer>,
        to_rating -> Nullable<Integer>,
        to_commit -> Nullable<Varchar>,

        #[max_length = 255]
        from_title  -> Nullable<Varchar>,
        #[max_length = 255]
        from_genre  -> Nullable<Varchar>,
        from_year   -> Nullable<Integer>,
        from_rating -> Nullable<Integer>,
        from_commit -> Nullable<Varchar>,

        diff_type -> Varchar,
    }
}

models.rs:

#[derive(QueryableByName)]
#[diesel(table_name = dolt_diff_movies)]
pub struct DoltDiffMovies {
    pub to_title:  Option<String>,
    pub to_genre:  Option<String>,
    pub to_year:   Option<i32>,
    pub to_rating: Option<i32>,
    pub to_commit: Option<String>,

    pub from_title:  Option<String>,
    pub from_genre:  Option<String>,
    pub from_year:   Option<i32>,
    pub from_rating: Option<i32>,
    pub from_commit: Option<String>,

    pub diff_type: String,
}

main.rs:

fn print_dolt_diff(conn: &mut MysqlConnection) {
    println!("Retrieving Dolt diff...");
    let query = "
        SELECT 
            to_title, 
            to_genre, 
            to_year, 
            to_rating,
            to_commit, 
            from_title, 
            from_genre, 
            from_year, 
            from_rating, 
            from_commit,
            diff_type 
        FROM 
            dolt_diff_movies
        WHERE
            to_commit = 'WORKING'
        ";

    let results: Vec<DoltDiffMovies> = sql_query(query)
        .load(conn)
        .expect("Error loading diff");

    for diff in results {
        ...
    }
    ...
}

Dolt Add and Commit

Dolt's add and commit operations, which stage and commit changes, are accessible through the dolt_add() and dolt_commit() procedures.

For these operations, we don't really care about the results, so long as they don't error. Therefore, we don't need to define a schema or model for these operations, and can just run the raw SQL queries.

main.rs:

fn dolt_add(conn: &mut MysqlConnection, tbl: &str) {
    println!("Staging changes to Dolt...");
    let query = format!("CALL dolt_add('{tbl}')");
    let _ = diesel::sql_query(query)
        .execute(conn)
        .expect("Error calling dolt_add");
}

fn dolt_commit(conn: &mut MysqlConnection, msg: &str) {
    println!("Committing changes to Dolt...");
    let query = format!("CALL dolt_commit('-m', '{msg}')");
    let _ = diesel::sql_query(query)
        .execute(conn)
        .expect("Error calling dolt_commit");
}

Dolt Branch and Merge

Dolt also supports all of Git's branching and merging features: dolt branch, dolt checkout, and dolt merge.

main.rs:

fn print_dolt_branches(conn: &mut MysqlConnection) {
    println!("Retrieving Dolt branches...");
    let query = "select name from dolt_branches";

    let results: Vec<DoltBranches> = sql_query(query)
        .load(conn)
        .expect("Error loading branches");
    ...
}

fn create_branch(conn: &mut MysqlConnection, branch_name: &str) {
    println!("Creating branch '{branch_name}'...");
    let query = format!("CALL dolt_branch('{branch_name}')");
    let _ = diesel::sql_query(query)
        .execute(conn)
        .expect("Error creating branch");
}

fn checkout_branch(conn: &mut MysqlConnection, branch_name: &str) {
    println!("Switching to branch '{branch_name}'...");
    let query = format!("CALL dolt_checkout('{branch_name}')");
    let _ = diesel::sql_query(query)
        .execute(conn)
        .expect("Error switching branch");
}

fn merge_branch(conn: &mut MysqlConnection, branch_name: &str) {
    println!("Merging branch '{branch_name}'...");
    let query = format!("CALL dolt_merge('{branch_name}')");
    let _ = diesel::sql_query(query)
        .execute(conn)
        .expect("Error merging branch");
}

Putting it All Together

Now that we've gone over the Diesel and Dolt operations, let's see how they all work together. We are going to go over the final version of main.rs piece by piece. This will demonstrate a typical workflow someone might have when working with Dolt and Diesel.

After initial setup, our newly created dolt_demo database will have an empty movies table and a __diesel_schema_migrations table. Here's a look from within the dolt sql shell:

dolt_demo/main> show tables;
+----------------------------+
| Tables_in_dolt_demo        |
+----------------------------+
| __diesel_schema_migrations |
| movies                     |
+----------------------------+
2 rows in set (0.00 sec)

First, we stage and commit these changes, and then print the state of our database.

// Initialize repo
dolt_add(conn, ".");
dolt_commit(conn, "Diesel migrate and initialize movies table");
print_movies(conn);
print_dolt_diff(conn);
print_dolt_log(conn);

Here's the output:

Staging changes to Dolt...
Committing changes to Dolt...
Retrieving movies...
-----------

Retrieving Dolt diff...
-----------

Retrieving Dolt log...
-----------
commit_hash: 1o72cabo4r89m0bjhakcf71v1tdq274b
author:      root <root@%>
date:        2024-08-28 11:07:07.979
message:     Diesel migrate and initialize movies table
-----------
commit_hash: kg1pvvemmi0b5p8n8tiin1no91arnvkc
author:      jcor <james@dolthub.com>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

As expected from a fresh database, there are no movies in the movies table, no diffs, and only the initial commit and the Diesel migration commit in the log.

Next, we insert some movies into the movies table.

// Insert some movies
add_movie(conn, "The Shawshank Redemption", "Prison Drama", 1994, Some(93));
add_movie(conn, "The Godfather", "Mafia", 1972, Some(92));
add_movie(conn, "The Dark Knight", "Action", 2008, None);
print_dolt_log(conn);

Output:

Inserting 'The Shawshank Redemption'...
Inserting 'The Godfather'...
Inserting 'The Dark Knight'...

Now, we can see the movies we just added:

print_movies(conn);

Output:

Retrieving movies...
-----------
Title:  The Dark Knight
Genre:  Action
Year:   2008
Rating: NULL
-----------
Title:  The Godfather
Genre:  Mafia
Year:   1972
Rating: 92
-----------
Title:  The Shawshank Redemption
Genre:  Prison Drama
Year:   1994
Rating: 93
-----------

We can see the diff, indicates that we've added three movies:

print_dolt_diff(conn);

Output:

Retrieving Dolt diff...
-----------
Added movie:
Title:  The Dark Knight
Genre:  Action
Year:   2008
Rating: NULL
-----------
Added movie:
Title:  The Godfather
Genre:  Mafia
Year:   1972
Rating: 92
-----------
Added movie:
Title:  The Shawshank Redemption
Genre:  Prison Drama
Year:   1994
Rating: 93
-----------

However, the logs are unchanged, as these changes are still in our working set:

print_dolt_log(conn);

Output:

Retrieving Dolt log...
-----------
commit_hash: 1o72cabo4r89m0bjhakcf71v1tdq274b
author:      root <root@%>
date:        2024-08-28 11:07:07.979
message:     Diesel migrate and initialize movies table
-----------
commit_hash: kg1pvvemmi0b5p8n8tiin1no91arnvkc
author:      jcor <james@dolthub.com>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

So let's stage and commit these changes:

// Add, Commit, and Log
dolt_add(conn, "movies");
dolt_commit(conn, "Added 3 movies");
print_dolt_log(conn);

Output:

Staging changes to Dolt...
Committing changes to Dolt...
Retrieving Dolt log...
-----------
commit_hash: 99ejpt58k757p2ok7gu6e9nuuq6o23hh
author:      root <root@%>
date:        2024-08-28 11:07:08.024
message:     Added 3 movies
-----------
commit_hash: 1o72cabo4r89m0bjhakcf71v1tdq274b
author:      root <root@%>
date:        2024-08-28 11:07:07.979
message:     Diesel migrate and initialize movies table
-----------
commit_hash: kg1pvvemmi0b5p8n8tiin1no91arnvkc
author:      jcor <james@dolthub.com>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

Next, let's create a new branch

// Make other branch 
create_branch(conn, "other");
print_dolt_branches(conn);

Output:

Creating branch 'other'...
Retrieving Dolt branches...
main
other

Now, let's switch to the other branch and make some changes:

// Make changes changes to other branch
checkout_branch(conn, "other");
remove_movie(conn, "The Godfather");
add_movie(conn, "The Godfather Part II", "Mafia", 1974, Some(90));
print_movies(conn);

Output:

Switching to branch 'other'...
Deleting 'The Godfather'...
Inserting 'The Godfather Part II'...
Retrieving movies...
-----------
Title:  The Dark Knight
Genre:  Action
Year:   2008
Rating: NULL
-----------
Title:  The Godfather Part II
Genre:  Mafia
Year:   1974
Rating: 90
-----------
Title:  The Shawshank Redemption
Genre:  Prison Drama
Year:   1994
Rating: 93
-----------

Through an INSERT and a DELTE, we've replaced The Godfather with The Godfather Part II; we can see this change through the diff:

print_dolt_diff(conn);

Output:

Retrieving Dolt diff...
-----------
Removed movie:
Title:  The Godfather
Genre:  Mafia
Year:   1972
Rating: 92
-----------
Added movie:
Title:  The Godfather Part II
Genre:  Mafia
Year:   1974
Rating: 90
-----------
// Commit and display log on other branch
dolt_add(conn, "movies");
dolt_commit(conn, "Replaced Godfather with Godfather Part II");
print_dolt_log(conn);

Output:

Staging changes to Dolt...
Committing changes to Dolt...
Retrieving Dolt log...
-----------
commit_hash: qekjshcif4fhrsjufas90f33qa4npse0
author:      root <root@%>
date:        2024-08-28 11:07:08.057
message:     Replaced Godfather with Godfather Part II
-----------
commit_hash: 99ejpt58k757p2ok7gu6e9nuuq6o23hh
author:      root <root@%>
date:        2024-08-28 11:07:08.024
message:     Added 3 movies
-----------
commit_hash: 1o72cabo4r89m0bjhakcf71v1tdq274b
author:      root <root@%>
date:        2024-08-28 11:07:07.979
message:     Diesel migrate and initialize movies table
-----------
commit_hash: kg1pvvemmi0b5p8n8tiin1no91arnvkc
author:      jcor <james@dolthub.com>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

Similarly, we can switch back to the main branch, and commit some changes there:

// Make changes to main branch
checkout_branch(conn, "main");
update_rating(conn, "The Dark Knight", Some(90));
print_movies(conn);
print_dolt_diff(conn);

// Commit and display log on main branch
dolt_add(conn, "movies");
dolt_commit(conn, "Updated The Dark Knight rating");
print_dolt_log(conn);

Output:

Switching to branch 'main'...
Retrieving movies...
-----------
Title:  The Dark Knight
Genre:  Action
Year:   2008
Rating: 90
-----------
Title:  The Godfather
Genre:  Mafia
Year:   1972
Rating: 92
-----------
Title:  The Shawshank Redemption
Genre:  Prison Drama
Year:   1994
Rating: 93
-----------

Retrieving Dolt diff...
-----------
Updated movie rating:
Title:  The Dark Knight
Genre:  Action
Year:   2008
Rating: NULL -> 90
-----------

Staging changes to Dolt...
Committing changes to Dolt...
Retrieving Dolt log...
-----------
commit_hash: 2ghjekmqk4h8kmc95u0arm7447thb0rt
author:      root <root@%>
date:        2024-08-28 11:07:08.078
message:     Updated The Dark Knight rating
-----------
commit_hash: 99ejpt58k757p2ok7gu6e9nuuq6o23hh
author:      root <root@%>
date:        2024-08-28 11:07:08.024
message:     Added 3 movies
-----------
commit_hash: 1o72cabo4r89m0bjhakcf71v1tdq274b
author:      root <root@%>
date:        2024-08-28 11:07:07.979
message:     Diesel migrate and initialize movies table
-----------
commit_hash: kg1pvvemmi0b5p8n8tiin1no91arnvkc
author:      jcor <james@dolthub.com>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

We can compare the main and other branches:

// View diff from main to other
print_dolt_branch_diff(conn, "main", "other");

Output:

Comparing diff from main to other...
-----------
Updated movie rating:
Title:  The Dark Knight
Genre:  Action
Year:   2008
Rating: 90 -> NULL
-----------
Removed movie:
Title:  The Godfather
Genre:  Mafia
Year:   1972
Rating: 92
-----------
Added movie:
Title:  The Godfather Part II
Genre:  Mafia
Year:   1974
Rating: 90
-----------

Lastly, we can merge the other branch into the main branch:

// Merge changes
merge_branch(conn, "other");
print_movies(conn);
print_dolt_log(conn);

Output:

Merging branch 'other'...
Retrieving movies...
-----------
Title:  The Dark Knight
Genre:  Action
Year:   2008
Rating: 90
-----------
Title:  The Godfather Part II
Genre:  Mafia
Year:   1974
Rating: 90
-----------
Title:  The Shawshank Redemption
Genre:  Prison Drama
Year:   1994
Rating: 93
-----------

Retrieving Dolt log...
-----------
commit_hash: l29o6rj2ajt9a3np7b4j5qca4cnahkga
author:      root <root@%>
date:        2024-08-28 11:07:08.092
message:     Merge branch 'other' into main
-----------
commit_hash: 2ghjekmqk4h8kmc95u0arm7447thb0rt
author:      root <root@%>
date:        2024-08-28 11:07:08.078
message:     Updated The Dark Knight rating
-----------
commit_hash: qekjshcif4fhrsjufas90f33qa4npse0
author:      root <root@%>
date:        2024-08-28 11:07:08.057
message:     Replaced Godfather with Godfather Part II
-----------
commit_hash: 99ejpt58k757p2ok7gu6e9nuuq6o23hh
author:      root <root@%>
date:        2024-08-28 11:07:08.024
message:     Added 3 movies
-----------
commit_hash: 1o72cabo4r89m0bjhakcf71v1tdq274b
author:      root <root@%>
date:        2024-08-28 11:07:07.979
message:     Diesel migrate and initialize movies table
-----------
commit_hash: kg1pvvemmi0b5p8n8tiin1no91arnvkc
author:      jcor <james@dolthub.com>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

Conclusion

And that's it! Hopefully, this guide has given you a good understanding of how to use Diesel with Dolt. If you have any questions or need help, feel free to reach out to us on Discord.

SHARE

Blog
Dolt is open source
JOIN THE DATA EVOLUTION

Get started with Dolt

Or join our mailing list to get product updates.