DEV Community

Cover image for Rust web application: MySQL server, sqlx, actix-web and tera.
Be Hai Nguyen
Be Hai Nguyen

Posted on

Rust web application: MySQL server, sqlx, actix-web and tera.

We write a Rust web application using a MySQL database. We use the already familiar crate sqlx for database works. The web framework we're using is crate actix-web. For Cross-Origin Resource Sharing (CORS) controls, we use crate actix-cors. For HTML template processing, we use crate tera, which implements Jinja2 template syntax.

The test project built in this post will have the following routes:

  • JSON response route -- method: POST; content type: application/json; request body: {"last_name": "%chi", "first_name": "%ak"}.
  • JSON response route -- method GET.
  • HTML response route -- method: POST; content type: application/x-www-form-urlencoded; charset=UTF-8; request body: last_name=%chi&first_name=%ak.
  • HTML response route -- method: GET.

This post does not discuss authentication, i.e. login, as this's a learning journey for me, I'm deliberately avoiding this potentially complex subject, perhaps we'll look into it in the future.

Table of contents

The Database and MySQL Database Server

❶ We'll use the same
Oracle Corporation MySQL test database, the same employees table and the same get_employees stored procedure; which we've used in other Rust and none-Rust posts.

To recap, the employees table has the following structure:

CREATE TABLE `employees` (
  `emp_no` int NOT NULL,
  `birth_date` date NOT NULL,
  `first_name` varchar(14) COLLATE utf8mb4_unicode_ci NOT NULL,
  `last_name` varchar(16) COLLATE utf8mb4_unicode_ci NOT NULL,
  `gender` enum('M','F') COLLATE utf8mb4_unicode_ci NOT NULL,
  `hire_date` date NOT NULL,
  PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Enter fullscreen mode Exit fullscreen mode

And the get_employees stored procedure is:

CREATE DEFINER=`root`@`%` PROCEDURE `get_employees`( pmLastName varchar(16), pmFirstName varchar(14) )
  select * from employees e where (e.last_name like pmLastName)
    and (e.first_name like pmFirstName) order by e.emp_no;
Enter fullscreen mode Exit fullscreen mode

❷ The MySQL server used is a Docker container discussed in the following posts:

Crates Used In This Post

Let's discuss the crates, to get them out of the way.

Crates Which We Have Not Covered Before

● Crate actix-web -- this's the web development framework that I choose to learn. I'm aware of several others. I choose this one due to the comparison presented by the following sites:

Crate actix-web ranks top based on popularity and supported features. For beginners, the official tutorial can be a bit daunting, at least for me. But after completing all tutorials, we should've an overall understanding of this crate. It's a good investment.

● The example code in this post applies Cross-Origin Resource Sharing (CORS) controls. This Amazon Web Services article What is Cross-Origin Resource Sharing? offers a very good explanation of what CORS is.

Crate actix-cors -- this's a middleware which implements CORS controls for actix-web. Take note of the given Example, the example code will copy this implementation as is, and we'll also call pub fn supports_credentials(self) -> Cors to make the implementation a bit more secured:

        let cors = Cors::default()
            .allowed_methods(vec!["GET", "POST"])
Enter fullscreen mode Exit fullscreen mode

● Crate tera -- this's a template processing engine middleware. It's based on the Jinja2 engine, which I'am familiar with. There're several crates which were Jinja2-based. I choose this one due to the comparison presented by the following site Compare askama and tera's popularity and activity, and tera seems to be more popular, and the documentation page offers a usage example, although it's a bit weak.

This page Day 15 - tera offers a more comprehensive example, whereby a Rust vector is passed to the template. Please note, the code is a bit outdated, but together with the official example, we can make it works with little effort.

This GitHub page Tera has an example of how a template renders data passed in.

● Crate dotenv -- supports .env file. We'll use the code presented in the Example page.

We've used the .env file before, in this post Rust SQLx CLI: database migration with MySQL and PostgreSQL.

Crates Which We Have Covered Before

In addition to the above new crates, we also use some of the crates which we have used before: time, sqlx, async-std, serde and serde_json.

Among other previous posts, we've covered these crates in the following posts in chronological order of most recent to least:

The Example Code

The complete source code for this post is on GitHub. The code for this post has been tagged with v0.1.0. To get the code at this tag, i.e. the code for this post, run the command:

git clone -b v0.1.0
Enter fullscreen mode Exit fullscreen mode

The layout of the project is shown the screenshot below:


To keep it simple, all modules live in the src/ directory.

The Cargo.toml File

The Cargo.toml file includes all crates we've discussed in the previous section. View the content of Cargo.toml on GitHub. We've covered crate features in some other previous posts.

The .env File

We store some configuration items in the .env file. Its content is reproduced below:


Enter fullscreen mode Exit fullscreen mode
  • MAX_CONNECTIONS: the maximum total number of database connections in the pool.
  • DATABASE_URL: MySQL database connection string. We've seen this connection string in other Rust posts.
  • ALLOWED_ORIGIN: CORS' Access-Control-Allow-Origin. This value can be a list, but to keep the example simple, we use only one.
  • MAX_AGE: CORS' Access-Control-Max-Age. This value specifies the duration, in seconds, that the preflight results can be cached in the browser. When this duration elapses, the browser'll need to send another preflight request.

The templates/employees.html File

The templates/employees.html on GitHub. This's a Jinja2 template. Rust code passes to it the employees vector, where each element is a JSON object.

If there is at least one (1) element in the vector, we'll display the list of employees in an HTML table. We first render the header row, then enter the for loop to display each employee. It's a simple template, there isn't any CSS.

The Rust Code

To recap, please note that: the code for this post has been tagged with v0.1.0.

Except for src/, all other module files should have sufficient documentation to explain the code. To view the documentation in a browser, on the terminal command line, just change to the project root directory, i.e. where Cargo.toml file is, and run the following command:

cargo doc --open
Enter fullscreen mode Exit fullscreen mode

But I think it's easier just to open the file and read it!


This's the run time representation of the .env file. We define a struct with fields that match the corresponding entries in the .env file, and a function to load field values from the file to the struct.


It has only a short, single mod australian_date_format, which serialises MySQL date into an Australian date format dd/mm/yyyy. It's also in Rust & MySQL: JSON serialisation of result sets retrieved using crate sqlx, where its name is mod my_date_format.


The intention is, this module is responsible for database connection. In a proper application, I imagine it would be a substantial module. But for this project, there's only a single method get_mysql_pool, it attempts to connect to the target MySQL database, if successful, it prints a success message to the console, and returns the pool. Otherwise, it prints some failure messages, and terminates the application.


This module is about the employees table. Please note, struct Employee as defined in this module, has also been used in the previously mentioned post Rust & MySQL: JSON serialisation of result sets retrieved using crate sqlx.

I imagine that, in a proper application, it would be a models/ sub-directory instead of a single file. And underneath this models/ directory, there would be the actual module among other modules for other tables. And each module would've all relevant functions that operate on the target database table.

For this project, we have only a single pub async fn get_employees(...) which attempts to retrieve data from the employees table based on partial last name and partial first name. This function is a refactored version of the function async fn do_run_stored_proc(), which is also in the previously mentioned post Rust & MySQL: JSON serialisation of result sets retrieved using crate sqlx, and also in some other earlier posts.


This's where all the HTTP request handler methods are. In a Model–View–Controller pattern, I think this's the controller? Again, in a proper application, there'd be several of these modules, and handlers/ or controllers/ would be a project sub-directory where these modules live. In Python, I've seen others use controllers/, and I follow, too.

We've four (4) methods to handle POST and GET requests. These methods demonstrate the most common and basic tasks any web application usually implements. ⓵ Accepting POST requests in JSON and x-www-form-urlencoded, and returning a response in either JSON or HTML. ⓶ Accepting GET requests where data are in request paths and returning a response in either JSON or HTML.

  1. pub async fn employees_json1(...): accepting POST requests in JSON, and returning responses in JSON. See also:
  2. pub async fn employees_json2(...): accepting GET requests where data are in request paths and returning responses in JSON. See also:
  3. pub async fn employees_html1(...): accepting POST requests in x-www-form-urlencoded, and returning responses in HTML. See also:
  4. pub async fn employees_html2(...): accepting GET requests where data are in request paths and returning responses in HTML. See also:

We have not covered query string requests, it's not much too different to others that we've covered above.

On the later two (2) HTML response methods, we could've written the final response as:

Enter fullscreen mode Exit fullscreen mode

Instead of:

        .content_type("text/html; charset=utf-8")
Enter fullscreen mode Exit fullscreen mode

The Content-Type header might get ignored. I have it in just to remember how we can set it in case we need to.

Finally, the private worker fn render_employees_template(...) is just a copy version of examples from other articles in the section where we discuss crate tera.


We use the application state to persist database connection across the application:

pub struct AppState {
    db: Pool<MySql>,
            .app_data(web::Data::new(AppState {
                db: pool.clone()
Enter fullscreen mode Exit fullscreen mode

This code is just about an exact copy of the code from the above official documentation 😂.

The CORS code's been discussed in section crate actix-cors.

In src/ module, the root route for all handler methods is /employees. We use route scoping to route methods which return JSON to /data/employees, and methods which return HTML to /ui/employees:

Enter fullscreen mode Exit fullscreen mode

I'd like to be able to visually test this project across my home network, so I bind it to Port 5000 just happens to be the port that I allocate to test work in progress projects.

Some Visual Test Runs

For some reasons, Postman reports 403 Forbidden for some of the routes... While these routes are okay on browsers. I use Testfully instead. is the address of my Ubuntu 22.10 machine, I run the tests from my Windows 10 Pro machine.






Concluding Remarks

We've not written any test for any of the modules in this project. It's my intention to do so in the near future, but I'm not too certain if I can actually see it through. I've written tests before, for example, in the first Rust post, and some later ones.

We've covered some only very basic features crate actix-web has. It's worth studying the tutorials provided by the official documentation.

I hope you find this post useful. Thank you for reading and stay safe as always.


Feature image source:

Top comments (0)