Developing a web app with rust, part 4: SQLx, data validation, deployment, final remarks

5 min readJun 10, 2024

1. SQLx

SQLx is “an async, pure Rust SQL crate featuring compile-time checked queries without a DSL.” SQLx is fully compatible with actix_web.

SQLx supports PostgreSQL, MySQL, MariaDB and SQLite. In my Rust character dictionary, I use SQLite.

SQLx accepts queries written in SQL using each DB system’s indiosyncrasies — in my case SQLite’s SQL syntax.

For each query you may choose between unprepared queries and precompiled queries:

the macros sqlx::query!() and sqlx::query_as!() perform compile-time validation of SQL,
while the functions sqlx::query() and sql::query_as() provide no SQL pre-compilation.

I chose no to perform compile-time validation.

The documentation is good and I did not need any other resource.

Here are two examples from dbase.rs:

pub async fn getsize(data: Data<AppState>) -> i64 {
    let result: (i64,) = sqlx::query_as("SELECT COUNT(*) FROM pyhz")
        .fetch_one(&data.db)
        .await
        .unwrap();

    result.0
}

The getsize function returns the size (row count) of the `pyhz` table. The SQL query “SELECT COUNT(*) FROM pyhz” is standard sql. It is not validated at compilation time since I use the function query_as() and not the macro query_as!().

Here is another example, which does not need any comment:

pub async fn delete_db(id: i64, data: Data<AppState>) -> String {
    let query = format!("DELETE FROM pyhz WHERE id= {} ", id);

    let result = sqlx::query(&query).execute(&data.db).await;
    match result {
        Ok(_) => return format!("Successful deleted entry Id = {}", id),
        Err(_) => return String::from("Internal server error"),
    }
}

As a conclusion, SQLx is easy to use, implements SQL queries with no incidental complexity, and plays well with actix_web (and most other web frameworks, but I did not check on that).

2. Data validation

Data validation for any data introduced in the program is important :

for security: avoiding XSS attacks,
and for data quality control: limiting the risk of introducing garbage into the database.

In the Rust character dictionary, I have essentially two types of data:

data with a specific format, such as single chinese characters, their phonetic transcription (pinyin and tone), their unicode value; for these I use HTML5 validation;
data with no specific format, such as the range of meanings of each character; for these I use ammonia sanitization.

HTML5 data validation:

I use validation by string length, validation by data type and validation according to regex pattern. The following examples are from forms.rs:

Validation by length:

<input id="carac" name="carac" type="text" required minlength="1" maxlength="1">

In this example the string to be input must be exactly one character length.

Validation by data type:

<input id="id" name="id" required type="number" autofocus>

In this example, you can input only a number (in decimal representation)

Validation by pattern:

<label for="unicode">Unicode (hexa, using pattern ^[0-9,A-F]{4}):</label>
<input id="unicode" name="unicode" type="text" required pattern="^[0-9,A-F]{4}">

The pattern “^[0–9,A-F]{4}” means that the input string must contain exactly 4 characters, either numerical or in the range A-F. If you enter incorrect data this is what you get after clicking ‘submit’:

Try it using the ‘Add zi’ button in https://ruzdman.fly.dev/ (no need to fear to damage the database, since it will automatically be reloaded when you leave the program for more than a few minutes).

ammonia sanitization:

ammonia is “a whitelist-based HTML sanitization library. It is designed to prevent cross-site scripting, layout breaking, and clickjacking caused by untrusted user-provided HTML being mixed into a larger web page”. I apply ammonia not in the forms themselves but when commiting the data to the database, as in the following (from dbase.rs):

let query = format!(
    "INSERT INTO pyhz (pinyin_ton, unicode, sens) VALUES ('{}', '{}', '{}')",
    zi.pinyin_ton,
    zi.unicode,
    ammonia::clean(&zi.sens) // sanitize zi.sens, which has no specific format
);

3. Deployment

I chose to deploy to fly.io, which has a hobby plan that used to be free and is now billed at 5 $/month, which is still quite affordable. The deployment is based on a docker container. I prepared the following dockerfile, derived from the one I used for the golang version, after reading S.Kerkour’s post:

# syntax=docker/dockerfile:1

##
## Build the application from source
##

FROM rust AS build-stage
WORKDIR /app
COPY . .
RUN cargo build --release

##
## Deploy the application binary into a lean image
##

FROM gcr.io/distroless/cc
ENV DATABASE_URL=sqlite://vol/zidian.db
ENV RUST_LOG=ruzdman=inf
WORKDIR /
COPY --from=build-stage /app/target/release/ruzdman /ruzdman
COPY ./vol ./vol
EXPOSE 8090
ENTRYPOINT ["/ruzdman"]

Using this dockerfile, the size of the docker image I get on fly.io is 38 MB, more than for the image I get for the Golang version, which is only 16MB. I understand I can further reduce the size by using the musl library.

The /vol directory contains all the files I need for execution: the templates, various assets, and the database. The database is reloaded from the docker image each time the program is launched, which is the reason why database changes are only effective during one fly.io session.

4. Final remarks

If I try to sum up my experience from starting a web app with golang and then porting it to rust, I find that:

the golang version was really easy to develop (which was also thanks to the fact that I had built my own extensive tutorial for it in https://github.com/francisstephan/gin-html-tutorial);
porting it to rust was initially quite demanding; but finally it obliged me to write a better program; for instance, since rust is much less tolerant than golangabout string management, I had to make a much better use of the templating system than I did for the golang version, where I had used it only minimally; the same goes for logging.

All I want to say in conclusion is: thank you to the brilliant developers in the golang and rust communities who make those great tools available!

For those who might wish to read the former instalments, here they are: