Take our file-based CRUD operations and turn them into database-based CRUD operations
This is the second part in a multi-part series about writing web apps. For this series, we will be writing the web app in Rust, and I explain to you how to write it yourself.
If you’d rather not write out the code yourself, however, I have made a repository with all the code written throughout this series here. I made a commit into the repository at the end of each part of the series. In the previous part, we covered how to do CRUD operations via files. In this part, we’ll cover how to reduce the boilerplate code for that by making use of databases.
In this part, we are going to be removing the file-based saving system in our app and will replace it with a much more powerful system created specifically for tasks like ours: databases.
Most databases are programs that store tables (this definition is much more limited than the actual definition for databases, but just assume this to be true for our purposes). These tables record information for a variety of individuals in a certain category. For example, you might have a table called
Persons that stores data on a variety of people. In that example, each row would be a person, and each column would store a different piece of information on that person. The diagram below shows that example.
The table stores 4 people. For each person, we store their name, last name and age in the columns of our table. The first column, the id, works exactly like the id we used when defining tasks. The id is a simple and easy way to access a certain person.
Now, unlike our file example, databases make it very easy to perform CRUD operations on these tables. Creating, Reading, Updating and Deleting certain entries of the table are handled via simple commands, so we don’t have to worry about the overhead of writing, renaming and deleting files. It also comes with a variety of other advantages that will show up naturally as we continue to write our web app.
But now that we’re aware of what a database is, let’s try and use one.
For our purposes, PostgreSQL will serve just fine. There are many different databases out there, all with their own pros and cons, so if you end up working on your own software, keep that in mind and do your research to determine the best option. In any case, install Postgres. Have it listen on port
5432 and when it asks for a password for your database just use
password for now.
If you are having trouble installing or using postgres on Windows, you can check out the following video from Amigoscode: Getting Started with PostgreSQL for Windows | 2021. Sadly, I run a Windows machine, so I can’t vouch for any Mac or Linux resources for setting up postgres, but there are several online. For Mac, this article from Codementor seems to be good. I assume you know what you’re doing if you’re on Linux, but this microfocus article may be helpful if you are having trouble with it.
Now, let’s use our newly installed database! Run
psql, which is the SQL Shell you installed to use postgres. Log into your database with the following information:
This will connect you to the postgres database. Now, we’re going to use this connection to create a new database to test out the CRUD operations available to us. At this point, you may ask why we have to create a database. Didn’t I just call Postgres a database? Well, Postgres is an app that stores multiple databases. Thus, we need to create a database to use before we can do anything. Now, to do that, simply run the following command in the
CREATE DATABASE test;
Note that the semicolon is important, because postgres will not view the command as “ready to use” until a semicolon is found. With that, if you run
\l, you should get the following output.
As you can see, postgres came with 3 databases pre-packaged:
template1. The 4th database,
test, is the one we have created. Now, run
quit to leave the shell, and re-enter the shell. Login the same way as last time, but list
test as your database rather than
postgres. With that, you’ll log into your newly created database.
Now, to test out our CRUD operations, we have to create a table, so run the following command (by the way, since postgres doesn’t process a command until it reaches a semicolon, you can write commands out over multiple lines).
CREATE TABLE person (
id BIGSERIAL NOT NULL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
country VARCHAR(50) NOT NULL
With this, you’ll create a table. This table will take items that have a name and country listed, and the id is a number that will be incremented automatically whenever we create a new entry. If you want to see all the relations in your database (which includes your table and the auto incrementing id), you can run
\d. If you want to see all the tables in your database (which is just person currently) run
\dt. The output of
\d, if everything has been done correctly, should look like this:
List of relations
Schema | Name | Type | Owner
public | person | table | postgres
public | person_id_seq | sequence | postgres
Now, let’s do some CRUD operations! First off, let’s do the creation operation. Run the following commands to add a few people to your person table.
INSERT INTO person (name, country) VALUES ('Torvald', 'Norway');
INSERT INTO person (name, country) VALUES ('Abelarda', 'Germany');
INSERT INTO person (name, country) VALUES ('Melete', 'Italy');
Next, let’s do a read operation. Run the following command to look at all the new entries to your table.
SELECT * FROM person
The output should look like the following
id | name | country
1 | Torvald | Norway
2 | Abelarda | Germany
3 | Melete | Italy
Now, we can do our update operation by running the following command.
UPDATE person SET country = 'USA' WHERE id = 3;
This will make Melete’s country the US.
Finally, let’s do the delete operation. Just run the following command to remove Abelarda from our table
DELETE FROM person WHERE id = 2;
If you exit and re-enter the database at any point between these operations, and even if you turn off the computer, your database will still hold onto these values. Thus, we were able to achieve persistent CRUD operations via minimal effort. I’m sure you probably would agree that the commands and the database are easier to use and manage than a file, even if both are attempting to solve the same problem.
However, just because we have this database means nothing if we can’t use it with our app. That’s where database drivers come into play.
A database driver solves the exact problem we have. It allows you to connect and use a database via a program. Instead of logging in and running the operations on our database manually, like we did in the previous section, we instead use a database driver to connect to the database, and then write out our operations in our program. These database drivers are generally libraries that you install for your project, the same way we installed Rocket.
However, in our case, Rocket requires us to use one of their supported database drivers for our application. But, regardless of what we’re using, let’s setup the driver. By the way, most of the code here will be a little shoddy, because we are eventually going to replace this with something slightly more sophisticated that will result in less code.
In any case, modify your
Cargo.toml to have the following imports
Next, go into
main.rs and add the following code next to all your other structs.
Then, at the same level as the
Cargo.toml, create a file called
Rocket.toml and enter the following code
Then, go to the
rocket function in
main.rs and modify it to look like the following. This will initialize our database.
Now, when we implement the CRUD operations, we will assume that we have a table called
tasks in our database. The next step is to create our database (which we have named
todo based on our configuration) and the
tasks table in postgres. Simply head over to
psql and run the following command
CREATE DATABASE todo;
This will create a database called
todo. Now, connect to
todo and run this command
CREATE TABLE IF NOT EXISTS tasks (
id BIGSERIAL NOT NULL PRIMARY KEY,
item TEXT NOT NULL
This will create our
tasks table in our database.
Now that that is done, we can change our CRUD operations to use our database rather than a file. We get access to the database by just including an argument in our method, and then we can use that variable and various functions to run the commands that we were running earlier. So, let’s start modifying our functions. The first thing we are going to do is add the creation operation, and we also add in a little bit of code to make the errors work. I also made some slight adjustments to the
TaskId struct to make them play nice with the database driver. The code for those changes is as follows
If you’re wondering about the code for
DatabaseError, I’m just using a tuple struct to wrap
rocket_db_pools::sqlx::Error, so I can implement the
Responder trait for it. In Rust, a trait is a way to ensure some function is implemented on a struct or an enum. With Rocket, we have to implement the
Responder trait for any struct, enum or type that we want to return from a request. The functions implemented in
Responder tell Rocket how to turn that struct, enum or type into proper data that can be sent in a response.
However, Rust’s orphan rule states that you can’t implement external traits on external types, so since neither
Responder are defined in the file we are working in, we can’t implement the
Responder trait for our error. Thus, we use the newtype pattern, which has us wrap the external struct in our own struct, which allows us to implement whatever traits we want on it.
After that, we also implement the
From trait for
DatabaseError. We specifically do this to make it work nicely with Rust’s question mark operator. The question mark operator is used when you call a function that returns a
Result enum. This enumerator either stores a value, or an error. If a question mark operator is used on a
Result, it will return an error immediately if the
Result is storing one. Otherwise, it will use the value like normal if the
Result is holding a value. For example,
add_task uses the question mark operator when declaring
added_task. The statement
added_task is assigned to returns a
Result that either holds a
Task or a
rocket_db_pools::sqlx::Error. In the case that
Result is holding an error, the question mark operator forces
add_task to return a
Result holding a
added_task is given the
Task value, which we can use nicely in the next line.
Now, the question mark operator implicitly uses the
From trait. If the error that the
Result is holding is different from the one that the function returns, then the question mark operator uses the
From trait to convert the error from it’s current type to one the function can return before returning the error. In our previous example, the
added_task statement returns a
rocket_db_pools::sqlx::Error, yet the function returns a
DatabaseError. To make everything simple, the question mark operator implicitly uses the
From trait to convert the
rocket_db_pools::sqlx::Error to a
DatabaseError before returning the
Result. However, this requires that
DatabaseError implements the
From trait for
For application code, a lot of people use the
anyhow crate to implement errors (this link talks about it a bit). Generally, this results in less code than making a specific enum to handle our errors like we did here. Normally, you’d only put errors in enums or tuple structs if you were making a library, where you want returned errors to be clear and clean. However, in this case, we’d still have to implement the
Responder trait for the
anyhow error type, so we’d still need to declare a tuple struct, and it ends up not saving us any time! It would allow us to change the underlying errors we use, but, as we’ll see, that won’t be necessary, as we are going to change how we do error handling later on.
As for the actual content of the
add_task function, it just is using sqlx database driver that we just set up. It’s using it to run an
INSERT command on the database.
Despite the long explanation, you can see that avoiding files and using a database instead has greatly simplified the creation operation. Most of our code is just making the database driver send the proper request (the stuff after
add_task) and making sure our errors respond properly (everything above
add_task), which is pretty wonderful. With that out of the way, let’s now implement the read operation
The read operation is also simple, we just use that
SELECT command we used earlier to grab all the available results.
Of course, the update operation simply uses the
UPDATE command we discussed earlier, and, just like the creation operation, we use the
RETURNING statement to return our actual modified task, so we can return it to the user.
DELETE operation is the same story. Take the command we used before, shove it into the database driver and let it run
With this, we have persistent CRUD operations using MUCH LESS code. After deleting all the redundant/unneeded code, we have a code base that clocks in at less than 100 lines.
And that’s all for today. In this part, we were able to take our file-based CRUD operations and turn them into database-based CRUD operations. This reduced the complexity and the size of our operations by quite a bit. However, programming is iterative, there are still improvements that could be made to this codebase, and we are going to make them. In the next part of this series, we’ll be cutting down the boilerplate code even more via an ORM.
Thank you for reading this article. I hope this series will continue to help you improve your web development skills.