Accessing Firebird With Diesel and Rust

How to use Firebird with Rust

We recently launched the rsfbclient-diesel adapter, which enabled the diesel to work with the Firebird database.

This post works as a continuation of my previous post, where I showed how to use Firebird with Rust. Now let’s learn how to improve this usage with an ORM layer.

To facilitate our understanding, we will implement a CRUD CLI tool with the job table from the example database employee.fdb. This database comes with your Firebird installation, but it can also be found in the examples/empbuild folder extracted from the official site.

Starting our project

We need to start including the diesel and rsfbclient-diesel dependencies in our Cargo.toml file:

Now in main.rs we import everything and connect in the employee.fdb database:

Listing rows

To manipulate some table with diesel, we need to declare the fields and the struct of this table. In a new module schema.rs we will declare our job table:

The Job struct will be our record representation in the job table. Because the table use the job_ prefix in some fields, we need to use the #[diesel(column_name = …)]. Below we declare our table schema, providing the types of columns.

Our struct declares the #[derive(Queryable)]. With this derivation, we are able to perform read operations.

Back to the main.rs again. We import the new module and use the diesel load function to get our records list:

Improving our list of records

We have a list, now let’s improve the view and the method we use to call this. To do this, we need to add the tabled and argopt in Cargo.toml file.

We are building a CRUD, let’s move the list operation to a command called list with help of argopt.

In the main.rs create the list function, moving the entire content of current main function and marking it with #[subcmd]:

In order for the command to be available, we need to add the #[cmd_group] derivation on the main function.

When we test, we will see that our project lists all the available commands:

‹dieselexample› cargo run
……
SUBCOMMANDS:
 help Print this message or the help of the given subcommand(s)
 list List all available jobs

Now we just need to format our list using the tabled. In this project, we will use the schema struct directly with the tabled just to simplify our implementation.

In our Job struct definition, we added the Tabled derive:

In our list command, we changed the way to print the records by calling the tabled:

When we test, we will see our formatted list:

‹dieselexample› cargo run list
+-------+--------------------------+-------------+
| code  | title                    | country     |
+-------+--------------------------+-------------+
| CEO   | Chief Executive Officer  | USA         |
+-------+--------------------------+-------------+
| CFO   | Chief Financial Officer  | USA         |
+-------+--------------------------+-------------+
| VP    | Vice President           | USA         |
+-------+--------------------------+-------------+
| Dir   | Director                 | USA         |
+-------+--------------------------+-------------+
| Mngr  | Manager                  | USA         |

Inserting new records

Before we can insert a new record, we need to finish our columns mapping. Some non-null columns still remain:

With the new columns, we create the add function and place it in the available command list with #[cmd_groud]:

When we test, we will see our new command:

‹dieselexample› cargo run add 
USAGE:
 employee add —code <CODE> —title <TITLE> —grade <GRADE> —country <COUNTRY> —min-salary <MIN_SALARY> —max-salary <MAX_SALARY>

Now let’s go to the insert. To enable some struct for insert operations, we need to add the Insertable derive:

With the struct ready, we just need to call the insert_into diesel function:

Let’s test:

‹dieselexample› cargo run add --code TE --title "Tech solver stuffs" --grade 1 --country USA --min-salary 2000 --max-salary 5000 
‹dieselexample› cargo run list
+-------+-------------+---------+-------+------------+------------+
| code  | title       | country | grade | min_salary | max_salary |
+-------+-------------+---------+-------+------------+------------+
....
| TE    | Tech solver | USA     | 1     | 2000       | 5000       |
+-------+-------------+---------+-------+------------+------------+

It’s update time

We already have the list and the add operation, still remains the update. We start by creating the update command:

We will use the Option type on the editable fields. With this approach the user only needs to provide what he really wants to update:

<dieselexample› cargo run update —help
USAGE:
    employee update [OPTIONS] <CODE>
ARGS:
    <CODE>    Job code
OPTIONS:
    --max-salary <MAX_SALARY>    New max salary
    --min-salary <MIN_SALARY>    New min salary
    --title <TITLE>              New title

Since our intention is just to update some fields, let’s create a new struct with the AsChangeset derive that will represent these fields:

In our command again, now we just need to call the update diesel function:

Let’s test:

‹dieselexample› cargo run update TE —title=”New job title”
‹dieselexample› cargo run list
+-------+--------------+---------+-------+------------+------------+
| code | title         | country | grade | min_salary | max_salary |
+------+---------------+---------+-------+------------+------------+
....
| TE   | New job title | USA     | 1     | 2000       | 5000       |
+------+---------------+---------+-------+------------+------------+

Removing records

Finally, now let’s delete our records. To make this, we create the remove command:

As expected, the deletion is performed by calling the delete diesel function:

Let’s test:

‹dieselexample› cargo run remove TE
‹dieselexample› cargo run list
+------+---------------+---------+-------+-----------+-------------+
| code | title         | country | grade | min_salary | max_salary |
+------+---------------+---------+-------+-----------+-------------+
....
| SRep | Sales Represe | France  | 4     | 20000     | 100000      |
+------+---------------+---------+-------+-----------+-------------+

Improving the connection

As you can see, the string connection is hard coded. We can solve this with a simple approach, using environment variables.

To manage environment variables in rust, we use the std:env module:

With the imported module, we just need to get the string connection from the DIESELFDB_CONN env:

Let’s test:

‹dieselexample› export DIESELFDB_CONN=”firebird://SYA:ma@localhost/employee.fdb”
‹dieselexample› cargo run list
+------+---------------+---------+-------+-----------+-------------+
| code | title         | country | grade | min_salary | max_salary |
+------+---------------+---------+-------+-----------+-------------+
....
| SRep | Sales Represe | France  | 4     | 20000     | 100000      |
+------+---------------+---------+-------+-----------+-------------+

The source code of the example project is available below:

rsfbclient/rsfbclient-diesel/examples/employee at master · fernandobatels/rsfbclient

To follow the rsfbclient and rsfbclient_diesel development, you can access our repository: https://github.com/fernandobatels/rsfbclient

Thanks for your attention and see you next time!