Database Access Stack in Rust
Tags: rust, sql, programming, database
Published: 2023-10-23 12:43:44

I am learning to access databases (specifically Sqlite and Postgres) in Rust. Database access requires a stack of libraries
that consist of the following layers:
* **A database access API specification**. These specifications help library developers to provide a
  consistent API to access databases.
* **Low-level database access libraries** built on top of the specification.
* **Object-Relational-Mapper (ORM)** to map tables to classes.
* **Migration Assistants** Manage changes to the database schema

There are other types of libraries such as connection pools. However the layers mentioned above are the most common.

A project that uses a database will use libraries for each layer in the stack. An example stack from
3 popular programming languages are:


|Layer|Python|Java|Golang|
|-----|------|----|------|
|Specification|[Python DB API](https://peps.python.org/pep-0249/)|[JDBC](https://docs.oracle.com/javase/8/docs/technotes/guides/jdbc/)|[`database/sql`](https://pkg.go.dev/database/sql)
|DB Access Libraries|[sqlite3](https://docs.python.org/3/library/sqlite3.html)|[sqlite-jdbc](https://github.com/xerial/sqlite-jdbc)|[sqlite3](https://pkg.go.dev/github.com/mattn/go-sqlite3)|
|ORM|[SqlAlchemy](https://www.sqlalchemy.org)|[Hibernate](https://hibernate.org/orm/)|[GORM](https://gorm.io/index.html)|
|Migration|[Alembic](https://pypi.org/project/alembic/)|[Flyway](https://flywaydb.org)|[golang-migrate](https://pkg.go.dev/github.com/golang-migrate/migrate/v4)|



## Data Access Stack in Rust

### Database Access API Specification

Rust does not yet have an API specification. While not a specification, the closest
alternative is [SQLx](https://github.com/launchbadge/sqlx)
which has re-implemented database access to Sqlite, Postgres and MySQL with the same interface.

The lack of a specification adds a burden for libraries that have to work with different types of
databases. For example, in Java an ORM is initialized with a JDBC driver. The ORM can use classes
like Connection and Statement from the JDBC driver and assume that the APIs and behaviour are consistent
across JDBC drivers.

This is not the case in Rust. Consider the native drivers for [Postgres](https://docs.rs/postgres/0.19.7/postgres/)
and [MySQL](https://docs.rs/mysql/latest/mysql/). Both the drivers have a `Statement` class.

Struct [`mysql::Statement`](https://docs.rs/mysql/latest/mysql/struct.Statement.html) has the following functions:

```
pub fn columns(&self) -> &[Column]
pub fn params(&self) -> &[Column]
pub fn id(&self) -> u32
pub fn connection_id(&self) -> u32
pub fn num_params(&self) -> u16
pub fn num_columns(&self) -> u16
```
Struct [`postgres::Statement`](https://docs.rs/postgres/0.19.7/postgres/struct.Statement.html) has the following functions:

```
pub fn params(&self) -> &[Type]
pub fn columns(&self) -> &[Column]
```
`params` returns a different type. `mysql::Statement` has more member functions.

Therefore a library that needs to connect to both MySQL and Postgres has to implement custom code for each one.

### Database Access Libraries

There are access libraries for all popular databases across the spectrum of OLTP and OLAP databases.
The main concern is that some popular access libraries are of unknown quality.

The top open source OLTP databases: Postgres, MySQL and SQLite have native and well-documented drivers.

### Object-Relational-Mapper & Migration Assistants

There are two popular ORMs which also provide support for migrations:
* [Diesel](http://diesel.rs)
* [SeaORM](https://www.sea-ql.org/SeaORM/)

There is a [comparison](https://www.sea-ql.org/SeaORM/docs/internal-design/diesel/) of both the
projects by the maintainers of SeaORM.

For starter projects both of these look like good choices. I could not find any information on which
one scales better for web applications that have to scale.

## My Starter Stack

I am working on a command line application that has to access either Postgres or SQLite databases.
It runs SQL queries and does not need an ORM. I will be using SQLx as it provides the same interface
to access both databases.

The lack of a specification does not affect this project. However, it will be difficult if the
project has to be extended to databases such as Snowflake and Presto in the future.