This is the 3rd article in the series of learning more about Postgres and applying it to a Rust backend.
Here is a index of them all.
Starting from this article (in the series of Postgres) we now abstract from the database, move one level up and start looking at how to interact with Postgres using Rust. Previous entries of the series focused on the Postgres JSON fields and custom data types.
In this article we will focus on how to quickly whip up some code to use the tokio-postgres crate to query a Postgres database. Most of this code can be found in the documentation, so tokio-postgres users won't find great insights here. Except perhaps in the second part, where I briefly touch how to work with enumerated data types which IIRC took me a bit to figure out.
As far as I could see, if you're writing a Rust application and want to use a relational database, you have these options:
- diesel, the only mature full ORM in this ecosystem (not async)
- tokio-postgres: more barebone, easier to use and fully async (sits on top of the tokio runtime)
- sqlx: another middleware similar to tokio-postgres but a bit more rich, supports either tokio and async-std async runtimes
- SeaORM: the new entry in the "light-ORM" panorama, looks interesting, I think it will gain traction
After a first experience with diesel in another toy(~ish) project in 2019, I decided that after all I didn't need an ORM, especially one that is lacking good ergonomics (diesel works great, but errors are inscrutable and there was a bit of "tribal knowledge" to be acquired from their issue tracker before feeling at ease), so the new project started with tokio-postgres: I wanted the lightest DB middleware possible and I wanted it fully async from the start. I knew that if I started with a sync DB middleware, than it would have been a nightmare to move to an async one (a bit more about this later). SeaORM looks interesting but haven't yet had a chance to try it.
§ Basic tokio-postgres
Here is one way to use tokio-postgres, I'll omit for brevity all error handling. First let's create our model, both on the DB and the Rust struct:
CREATE TABLE test_users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT
);
#[derive(Debug)]
struct User {
id: i32,
name: String,
// optional field in the DB
age: Option<u32>
}
Then let's implement the code to talk to the database. I'll copy and slightly modify a basic usage example from the tokio-postgres README:
use tokio_postgres::NoTls;
#[tokio::main]
async fn main() {
// connect to the database
let (client, connection) =
tokio_postgres::connect("host=127.0.0.1 user=xxx password=yyy", NoTls)
.await
.unwrap();
tokio::spawn(async move {
if let Err(e) = connection.await {
eprintln!("connection error: {}", e);
}
});
let name = "Max";
let age = 42;
// INSERT a new record
// take advantage of Postgres `RETURNING` to get the new record back
let row : postgres::Row = client
.query_one(
"INSERT INTO test_users (name, age) VALUES ($1, $2) RETURNING *",
&[&name, &age])
.await
.unwrap();
// prints: "Created id=1, name=Max"
println!(
"Created id={}, name={}",
row.get::<_, i32>(0),
row.get::<_, &str>(1)
);
}
The code should be self-explanatory. The &[¶m1, ¶m2, ...] parameters when building the query and the getters on the row object are a bit awkward.
Let's implement a core::convert::From and remove some awkwardness:
impl From<postgres::Row> for User {
fn from(row: postgres::Row) -> Self {
Self {
id: row.get("id"),
name: row.get("name"),
age: row.get("age"),
}
}
}
let u = User::from(row);
println!("Created {:?}", u);
// prints: Created User { id: 1, name: "Max", age: 42 }
§ Using Postgres Enumerated type
In this article we gave a glance at using enums with Postgres. Now let's see how to write code in Rust to use them.
First let's update the previous code by including a new field to express the user status:
CREATE TYPE USER_STATUS AS ENUM (
'INVITED',
'ACTIVE',
'DELETED'
);
CREATE TABLE test_users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
age INT,
status USER_STATUS NOT NULL DEFAULT 'ACTIVE'
);
Now let's derive the FromSql and ToSql traits and have the crate postgres_types (which we already have installed), figure out how to translate this Rust enum for Postgres. Postgres types and Rust types have a very strict mapping, so plan your models ahead.
// Note: map the exact name - case sensitive - of the Postgres ENUM
#[derive(FromSql, ToSql, Debug)]
#[postgres(name = "USER_STATUS")]
enum UserStatus {
INACTIVE,
ACTIVE,
DELETED,
}
struct User {
id: u32,
name: String,
age: Option<u32>,
status: UserStatus
}
Notice how I have decorated the enum with ToSql and FromSql: these will allow translating my Rust enum to the corresponding Postgres type without additional legwork. This is briefly explained in the documentation.
What the documentation doesn't really clarify is how to use this field when using the DB. So let's insert a new record with this new field:
use std::fmt::Debug;
use postgres_types::{FromSql, ToSql};
use tokio_postgres::NoTls;
use tokio_postgres::Row;
#[tokio::main]
async fn main() {
// connect to the database
// ...
let name = "Max";
let age = 42;
let status = UserStatus::ACTIVE;
let row = client
.query_one(
"INSERT INTO test_users (name, age, status)
VALUES ($1, $2, $3::USER_STATUS) RETURNING *",
&[&"john", &42, &status],
)
.await
.unwrap();
let u = User::from(row);
println!("Created {:?}", u);
// prints: Created User { id: 1, name: "Max", age: 42, status: ACTIVE }
}
§ Working with JSONB fields
Since last time we saw how to use JSON/JSONB Postgres fields, let's complement that knowledge and see the Rust counterpart.
Now, one of the cool things about Rust is the strong typing. JSON objects are not so "strong typed" so if you use serde (a serialization/deserialization library), you have it figure that out for you:
use serde::{Deserialize, Serialize};
#[derive(Debug, Deserialize, Serialize)]
struct User {
id: i32,
name: String,
age: i32,
status: UserStatus,
}
#[derive(FromSql, ToSql, Debug, Deserialize, Serialize)]
#[postgres(name = "user_status")]
enum UserStatus {
INACTIVE,
ACTIVE,
DELETED,
}
fn main() {
// create a JSON object
let j = json!({
"id": 1,
"name": "John Doe",
"age": 43,
"status": "ACTIVE"
});
// Convert to a type <T>
let u: User = serde_json::from_value(j).unwrap();
println!("Object: {:?}", u);
// Object: User { id: 1, name: "John Doe", age: 43, status: ACTIVE }
// and viceversa, convert our type <T> back to JSON
let j = serde_json::to_value(u).unwrap();
println!("JSON: {}", j);
// JSON: {"age":43,"id":1,"name":"John Doe","status":"ACTIVE"}}
What happens when we have a JSON/JSONB field in the DB? Let's find out! First we will update the schema adding yet another field, let's say each user owns a list of books:
> ALTER TABLE test_users ADD COLUMN owned_books JSONB DEFAULT '[]'::jsonb;
> \d test_users
+-------------+-------------+----------------------------------------------------------+
| Column | Type | Modifiers |
|-------------+-------------+----------------------------------------------------------|
| id | integer | not null default nextval('test_users_id_seq'::regclass) |
| name | text | not null |
| age | integer | |
| status | user_status | not null default 'ACTIVE'::user_status |
| owned_books | jsonb | default '[]'::jsonb |
+-------------+-------------+----------------------------------------------------------+
And the Rust model:
#[derive(Debug, Deserialize, Serialize)]
struct Book {
author: String,
title: String,
year: u32
}
#[derive(Debug, Deserialize, Serialize)]
struct User {
id: i32,
name: String,
age: i32,
status: UserStatus,
owned_books: Vec<Book>
}
And our From when we retrieve a record:
use tokio_postgres::types::Json;
impl From<postgres::Row> for User {
fn from(row: postgres::Row) -> Self {
Self {
id: row.get("id"),
name: row.get("name"),
age: row.get("age"),
status: row.get("status"),
owned_books: serde_json::from_value(row.get("owned_books")).unwrap(),
// also this one works
// owned_books: row.get::<_, Json<Vec<Book>>>("owned_books").0,
}
}
}
I've put two solutions to convert the JSON, they both work but maybe the first one is less awkward 1.
Let's add this field in the query and get the result:
let books = json!([
{"title":"A deepness in the sky", "author":"vinge", "year": 1999}
]);
let q = format!("INSERT INTO test_users (name, age, status, owned_books)
VALUES ($1, $2, $3::USER_STATUS, '{}') RETURNING *",
books
);
let row: tokio_postgres::Row = client
.query_one(
&q,
&[&name, &age, &status],
)
.await
.unwrap();
let u = User::from(row);
println!("{} owns: {:?}", u.name, u.owned_books);
// Max owns: [Book { author: "vinge", title: "A deepness in the sky", year: 1999 }]
§ One final small refinement
Reading the DB error returned was not really easy and I wanted to extract as much information as possible from that, so I wrote a simple utility to log in detail why a query failed:
fn log_full_db_err(err: &tokio_postgres::error::Error, msg: &str) {
let dberr = match err.as_db_error() {
None => {
log::error!("Error unwrapping tokio_postgres DbError: {:?}", &err);
return;
}
Some(err) => err,
};
log::error!(
"DB error: {} {}",
dberr.message(),
dberr
.detail()
.expect("cannot retrieve detail error from postgres")
);
log::error!("{}", msg);
}
fn main() {
let maybe_row: Result<tokio_postgres::Row, tokio_postgres::error::Error> = client
.query_one(
&q,
&[&name, &age, &status],
)
.await;
let row = match maybe_row {
Err(db_err) => {
log::error!("{}", log_full_db_err(db_err, "Query failed"));
}
Ok(row) => row
}
}
§ Conclusions
In this brief journey we have seen a basic example of usage of the tokio-postgres crate. I have only touched the surface hoping to set enough foundations, either for me as a future reference and for the occasional visitor of this page. Interesting that while writing the code for this article and focusing the code snippets, I've found some mistakes I did at the time in the implementation in my project :-)
As always, comments and suggestions are greatly appreciated.
In the next chapter we will have a look at another frequently asked question: how to use a threadpool with tokio-postgres.
Obviously, I've been using the second solution for months before discovering just now a better one