[Rust Diesel] Getting Started - Diesel CRUD in PostgreSQL
Diesel is a Safe, Extensible ORM and Query Builder for Rust.
Diesel is the most productive way to interact with databases in Rust because of its safe and composable abstractions over queries.
For this guide, we’re going to walk through some simple examples for each of the pieces of CRUD, which stands for “Create Read Update Delete”. Each step in this guide will build on the previous, and is meant to be followed along.
Make sure you have PostgreSQL installed and running.
PostgreSQL: Downloads - https://www.postgresql.org/download/
Diesel requires Rust 1.31 or later. If you’re following along with this guide, make sure you’re using at least
that version of Rust by running
rustup update stable.
Initializing a new project
The first thing we need to do is generate our project.
Generate a new project
Installing Diesel CLI
Diesel provides a separate CLI tool to help manage your project. Since it’s a standalone binary, and doesn’t affect your project’s code directly, we don’t add it to
Cargo.toml. Instead, we just install it on our system.
Install the CLI tool.
We need to create a database with username and password.
Setup Diesel for your project
We need to tell Diesel where to find our database. We do this by setting the
DATABASE_URL environment variable. On our development machines, we’ll likely have multiple projects going, and we don’t want to pollute our environment. We can put the url in a
.env file instead.
echo DATABASE_URL=postgres://diesel_demo:[email protected]/diesel_demo > .env
Now Diesel CLI can set everything up for us.
This will create our database (if it didn’t already exist), and create an empty
migrations directory that we can use to manage our schema (more on that later).
The first thing we’re going to need is a table to store our posts. Let’s create a migration.
Diesel CLI will create two empty files for us in the required structure. You’ll see output that looks something like this:
diesel migration generate create_posts
Migrations allow us to evolve the database schema over time. Each migration can be applied (
up.sql) or reverted (
down.sql). Applying and immediately reverting a migration should leave your database schema unchanged.
Next, we’ll write the SQL for migrations:
CREATE TABLE posts (
DROP TABLE posts;
We can apply our new migration:
diesel migration run
It’s a good idea to make sure that down.sql is correct. You can quickly confirm that your down.sql rolls back your migration correctly by redoing the migration:
diesel migration redo
A Note on Raw SQL in Migrations:
Since migrations are written in raw SQL, they can contain specific features of the database system you use. For example, the
CREATE TABLE statement above uses PostgreSQL’s
SERIAL type. If you want to use
SQLite instead, you need to use
A Note on Using Migrations on fly:
The diesel_migrations diesel_migrations 1.4.0 - Docs.rs - https://docs.rs/crate/diesel_migrations/1.4.0 crate provides the
embed_migrations! macro, allowing you to embed migration scripts in the final binary. Once your code uses it, you can simply include
embedded_migrations::run(&db_conn) at the start of your main function to run migrations every time the application starts.
We’ll start by writing some code to show the last five published posts. The first thing we need to do is establish a database connection.
Next we need to create the two modules that we just declared.
#[derive(Queryable)] will generate all of the code needed to load a
Post struct from a SQL query.
Typically the schema module isn’t created by hand, it gets generated by Diesel. When we ran
diesel setup, a file called
diesel.toml was created which tells Diesel to maintain a file at
src/schema.rs for us. The file should look like this:
The exact output might vary slightly depending on your database, but it should be equivalent.
table! macro creates a bunch of code based on the database schema to represent all of the tables and columns. We’ll see how exactly to use that in the next example.
Any time we run or revert a migration, this file will get automatically updated.
A Note on Field Order
Using #[derive(Queryable)] assumes that the order of fields on the Post struct matches the columns in the posts table, so make sure to define them in the order seen in the schema.rs file.
Let’s write the code to actually show us our posts.
diesel_demo::schema::posts::dsl::* line imports a bunch of aliases so that we can say posts instead of
posts::table, and published instead of
posts::published. It’s useful when we’re only dealing with a single table, but that’s not always what we want.
We can run our script with
cargo run --bin show_posts. Unfortunately, the results won’t be terribly interesting, as we don’t actually have any posts in the database. Still, we’ve written a decent amount of code, so let’s commit.
The full code for the demo at this point can be found here diesel/examples/postgres/getting_started_step_1 at v1.4.4 · diesel-rs/diesel - https://github.com/diesel-rs/diesel/tree/v1.4.4/examples/postgres/getting_started_step_1/.
Next, let’s write some code to create a new post.We’ll want a struct to use for inserting a new record.
Now let’s add a function to save a new post.
When we call
.get_result on an insert or update statement, it automatically adds
RETURNING * to the end of the query, and lets us load it into any struct that implements Queryable for the right types. Neat!
Diesel can insert more than one record in a single query. Just pass a Vec or slice to insert, and then call
get_results instead of
get_result. If you don’t actually want to do anything with the row that was just inserted, call
.execute instead. The compiler won’t complain at you, that way. :)
Now that we’ve got everything set up, we can create a little script to write a new post.
We can run our new script with
cargo run --bin write_post. Go ahead and write a blog post. Get creative! Here was mine:
cargo run --bin write_post
show_posts still won’t display our new post, because we saved it as a draft. If we look back to the code in
show_posts, we added
.filter(published.eq(true)), and we had published default to false in our migration. We need to publish it! But in order to do that, we’ll need to look at how to update an existing record. First, let’s commit. The code for this demo at this point can be found here.
Now that we’ve got create and read out of the way, update is actually relatively simple. Let’s jump right into the script:
That’s it! Let’s try it out with
cargo run --bin publish_post 1.
$ cargo run --bin publish_post 1
We’ve still only covered three of the four letters of CRUD though. Let’s show how to delete things. Sometimes we write something we really hate, and we don’t have time to look up the ID. So let’s delete based on the title, or even just some words in the title.
We can run the script with
cargo run --bin delete_post demo (at least with the title I chose). Your output should look something like:
cargo run --bin delete_post
When we try to run
cargo run --bin show_posts again, we can see that the post was in fact deleted. This barely scratches the surface of what you can do with Diesel, but hopefully this tutorial has given you a good foundation to build off of. We recommend exploring the API docs to see more. The final code for this tutorial can be found here diesel/examples/postgres/getting_started_step_3 at v1.4.4 · diesel-rs/diesel - https://github.com/diesel-rs/diesel/tree/v1.4.4/examples/postgres/getting_started_step_3/.
cannot find macro
table in this scope, cannot find derive macro
Queryable in this scope
 Getting Started - https://diesel.rs/guides/getting-started.html
 Diesel is a Safe, Extensible ORM and Query Builder for Rust - https://diesel.rs/
 diesel/examples/postgres/getting_started_step_3 at v1.4.4 · diesel-rs/diesel - https://github.com/diesel-rs/diesel/tree/v1.4.4/examples/postgres/getting_started_step_3/
 diesel - Rust - https://docs.diesel.rs/master/diesel/index.html
 rustup.rs - The Rust toolchain installer - https://rustup.rs/
 PostgreSQL: Downloads - https://www.postgresql.org/download/