Dolt runs on Diesel | DoltHub blog

Hello Rustaceans, welcome to the world of Dolt and Diesel! Dolt is a version-controlled database that is a drop-in MySQL replacement. We have 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.

This guide assumes you have Rust and Dolt installed. All code used 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.

Start the dolt server in a terminal:

$ cd workspace
$ dolt sql-server

This starts a Dolt server on port 3306 with standard user root and no password. Just let this run in the background.

Initialize the Diesel project in a separate terminal:

$ 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.tomladd 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 mysql function for Diesel, and nothing else. Diesel requires that we 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 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 Dolt server:

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

Perform Diesel setting:

To create a migration:

$ diesel migration generate create_movies

Editing up.sql:


CREATE TABLE movies (
    title VARCHAR(255) PRIMARY KEY,
    genre VARCHAR(255) NOT NULL,
    year INT NOT NULL,
    rating INT
);

Editing down.sql:

Apply migration:

That’s pretty much it for setting up the project from scratch. From this point on I’ll highlight snippets of starter code and explain what they do.

First of all, 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 returns a variable reference to a MysqlConnection object, which we can use to interact with the database. It will DATABASE_URL environment variable (which we discussed earlier in the .env file) to connect to the Dolt server.

Let’s go over basic CRUD operations: SELECT, INSERT, UPDATEAnd DELETE.

`SELECT`S

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



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

If you want to know more about schematics, read the Diesel documentation.

Besides 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 structure that contains a row in the movies table that we can use to communicate with the database.
#(derive(Queryable)) generates code for Rust to load a SQL row into the Movie structure, which allows us to call .load().
#(derive(Selectable)) generates code to a SELECT * ... query based on this struct.

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

Finally, let’s look at the Rust code that actually performs the selection.

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 panics with the provided error message when the load function returns an error.

Fortunately for those familiar with SQL, Diesel’s query builder closely resembles SQL syntax.

`INSERT`S

Similar to SELECTwe need to define a structure for inserting data into the movies table. In the models.rs file, we have the following structure:

#(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 a INSERT INTO movies ... query and passes a reference to the NewMovie structure.

`UPDATE`S

UPDATE The operations follow a very similar pattern as INSERT edits, except that we don’t need to define a new structure.

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 actually running a UPDATE movies SET rating = <new_rating> WHERE title = <update_title> ask.

`DELETE`S

Finally, 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 feature is just a DELETE FROM movies WHERE title = <delete_title> ask.

Dolt is often described as if Git and MySQL had a baby. We’ve covered typical database operations, but now let’s look at some version control capabilities via Diesel.

Many Dolt operations are accessible within an SQL context through system tables and procedures. As a result, we will have to rely on Diesel’s sql_query (which allows us to execute raw SQL queries) to perform these operations.

Crazy Log

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

Similar to the interaction with regular tables such as movieswe need to define a schema and model for the dolt_log table, so Diesel knows how to handle 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,
}

Please note the #(derive(QueryableByName)) instead of #(derive(Queryable)).

Then we can 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 is important to ensure that the projections/aliases in the query match the fields in the DoltLog structure.

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 via a system table dolt_diffAs a result, access to the content 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 {
        ...
    }
    ...
}

Add and commit Dolt

Dolt’s add And commit edits, which prepare and record changes, are accessible via the dolt_add() And dolt_commit() method.

For these operations, we don’t really care about the results, as long as they don’t produce any errors. Therefore, we don’t need to define any schema or model for these operations and can just execute 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 checkoutAnd 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");
}

Now that we’ve gone over the Diesel and Dolt operations, let’s take a look at how they all work together. We’re going to be reviewing the final version of main.rs piece by piece. This will demonstrate a typical workflow that someone might have when working with Dolt and Diesel.

After the initial installation, our newly created dolt_demo database will be empty movies table and a __diesel_schema_migrations table. Here is a view from 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 execute and commit these changes, then we print the status of our database.


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

This is the result:

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 <[email protected]>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

As expected from a new database, there are no movies in the movies table, no differences and only the first commit and the Diesel migration commit in the log.

Next we add some movies to the movies table.


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:

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 difference, it indicates that we added three films:

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:

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 <[email protected]>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

So let’s make and record these changes:


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 <[email protected]>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

Now let’s create a new 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 out and make some changes:


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 a INSERT and a DELTEwe have replaced The Godfather of The Godfather Part II; we can see this change via the diff:

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


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 <[email protected]>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

In the same way we can go back to the main branch, and make some changes there:


checkout_branch(conn, "main");
update_rating(conn, "The Dark Knight", Some(90));
print_movies(conn);
print_dolt_diff(conn);


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 <[email protected]>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

We can the main And other branches:


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

Finally, we can other branch out in the main branch:


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 <[email protected]>
date:        2024-08-28 11:06:26.451
message:     Initialize data repository
-----------

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.