4_1_db

Task 4.1: Databases, connection pools and ORMs

The current situation with databases integration in Rust ecosystem is illustrated quite well in this "Awesome Rust" section and in "Database" topic of "Are we web yet?": the majority of the drivers are implemented fully in Rust, and only few wrap existing libraries, and of course, most of them use async I/O.

Connection pool

The important concept to understand is a connection pool pattern. It's widely adopted in situations where a program represents a long-running application (like daemons or servers). The key point is that instead of creating a new connection to database every time we need to interact with, we'd rather pre-create a pool of such connections and reuse them. As connection creation is quite an expensive operation, applying this pattern leads to huge performance improvements.

Fortunately, Rust ecosystem provides generic implementations of database-agnostic connection pool in both flavours: synchronous and asynchronous.

For better understanding connection pooling, read through the following articles:

• Charlie Custer: What is Connection Pooling, and Why Should You Care

Synchronous

For synchronous connections there is the r2d2 crate (the pioneer among such crates, existed far before async I/O has landed in Rust). You can easily adopt it for your specific use-case (or database) just by implementing its traits. Obviously, there are implementations for common drivers already.

For more details, read through the following articles:

• Official r2d2 crate docs

Asynchronous

For asynchronous connections there are much more options in Rust ecosystem, due to historical reasons and bigger competitiveness (as the result of bigger async I/O popularity).

The very first one, historically, was the bb8 crate. It mirrors the r2d2 crate for asynchronous connections (tokio only), and originally was based on it. Similarly, there are implemented bridges for common drivers already.

deadpool is an alternative and very mature implementation of the connection pool pattern, supporting both tokio and async-std, provided with its own large ecosystem.

Another alternative implementation is the mobc crate, yet inspired by deadpool and r2d2 crates. Similarly, supports both tokio and async-std and provides some bridges for common drivers.

qp (Quick Pool) is a very simple and limited implementation of the connection pool pattern, utilizing lock-free primitives and focused on being performant.

For more details, read through the following articles:

• Official bb8 crate docs
• Official deadpool crate docs
• Official mobc crate docs
• Official qp crate docs

Query builder

Query builder is effectively a builder pattern applied for building SQL (or other data query languages) queries, and allowing to write them as a regular Rust code (and so, using an embedded DSL instead of external DSL).

The canonical implementation of this pattern in Rust ecosystem is represented by sea-query and sql_query_builder crates.

barrel crate, on the other hand, allows to write schema migrations, rather than querying data.

For more details, read through the following articles:

• Official sea-query crate docs
• Official sql_query_builder crate docs
• Official barrel crate docs

Non-DSL toolkit

sqlx crate, while being a feature-rich toolkit for SQL, takes a completely opposite approach here: it focuses on writing pure SQL queries (no custom DSL, no query building), which are statically checked to be correct at compile-time.

For better understanding sqlx design, concepts, usage, and features, read through the following articles:

• Official sqlx crate docs

ORM

Regarding the ORM pattern, there are multiple feature-rich and mature implementation in Rust ecosystem at the moment. Every one has its own unique design, advantages and disadvantages.

The very first ORM created in Rust was the diesel crate. Even now, it supports only synchronous connections (as was created before async I/O has landed in Rust). However, still may be used with asynchronous connections, thankfully to the diesel-async extension.

sea-orm (built on top of sea-query) is an alternative feature-rich and mature implementation of the [ORM] pattern in Rust, focused on dynamic querying to avoid complexity of static checks ("fighting the ORM").

ormx is a lightweight extension of the sqlx crate, aimed to provide it with ORM-like features.

rustorm is a very simple and SQL-centered ORM, focused on easing conversions of database types to their appropriate Rust types.

For better understanding ORMs design, concepts, usage, and features, read through the following articles:

• Official diesel crate docs
• Official diesel crate guides
• Official sea-orm crate docs
• Official sea-orm crate guides
• Official ormx crate docs
• Official rustorm crate docs

Migrations

For database migrations there are multiple tools in Rust ecosystem.

For diesel users, the obvious choice is the diesel_migrations crate (which may be used directly via diesel_cli). Though, doesn't require the diesel itself to be used, and may be used as a fully separate tool.

For sqlx users, similarly, the sqlx-cli tool provides migrations out-of-the-box, while also may be used directly in the application code.

refinery and migrant are another standalone Rust tools for migrations, allowing both CLI and "in-application-code" usage. The interesting part about the refinery crate is that it also allows to write "in-application-code" migrations via the barrel schema migration builder.

For being familiar with migrations tools, their similarities and differences, read through the following articles:

• Official diesel_migrations crate docs
• Official diesel_cli crate docs
• Official diesel crate guides: Getting Started
• Official sqlx crate docs: Macro sqlx::migrate
• Official refinery crate docs
• Official migrant crate docs

Task

Estimated time: 1 day

Create an SQL database (PostgreSQL, MySQL or SQLite, on your choice) consisting of the following tables:

• users: id, name and any other fields on your choice;
• roles: slug as a primary key, name and permissions (the concrete format on your choice) fields;
• users_roles: users.id to roles.slug many-to-many relationship.

Write a simple CLI application which allows to CRUD data in your database tables in the following ways:

• create and delete users and roles (a user must always have an assigned role);
• update fields of a single user or a role;
• assign or unassign a role to/from a user;
• list all roles or a single role by its slug;
• list all users or a single user by its id (a user should be displayed with all the roles assigned to him).

Consider to ensure data consistency in your database as much as possible.

Questions

After completing everything above, you should be able to answer (and understand why) the following questions:

• What is connection pool pattern? How does it work? Which problems does it solve?
• What is ORM pattern? How does it differ from query building? What benefits do they give?
• Why writing raw SQL queries could be meaningful? Which are use-cases for it and when is it preferred over ORMs?
• What are migrations? Why should we use them? How do they work?
• Which kinds of migrations do exist? What are their advantages and disadvantages? When and which kind is preferred?