Introduction Hello, world! Good news! It’s easy to create a simple to-do list using and for the data layer side. bb8 tokio_postgres : . Feel free to add a library like or , or to setup a REST API and a web front-end. Disclaimer This example doesn't use any user interface egui iced I won't show you how to set up a TLS session for the database connections in this article, however, it isn’t challenging. is a full-featured pool connection designed for asynchronous connections. The name of this crate can be explained easily because it's originally based on . bb8 r2d2 Step 1:Dependencies In order to perform asynchronous statements to the database, I use the crate. tokio_postgres I found an interesting crate called which provides both crates ( ). bb8_postgres bb8 and tokio_postgres To make our lives easier we’ll use the following naming structure: . PostgresConnectionManager Let's get started by installing some dependencies in your . Cargo.toml [dependencies] bb8-postgres = "0.7.0" once_cell = "1.9.0" snafu = "0.6" [dependencies.postgres-types] version = "0.2.2" features = ["derive"] [dependencies.uuid] version = "0.8.2" features = ["v4", "serde"] [dependencies.tokio] version = "1" features = ["full"] [dependencies.chrono] version = "0.4.19" features = ["serde"] [dependencies.tokio-postgres] version = "0.7.5" features = ['with-chrono-0_4', 'with-uuid-0_8', 'with-serde_json-1'] Chrono is used to manage creation and , SQL serialization, and deserialization through . datetime timestamptz serde I have decided to identify a to-do list using UUIDs because they are unique and useful to ensure collision avoidance if I have to merge databases of todos. I added , a rusty way to implement a thread-safe singleton pattern to get a unique instance of the bb8 pool during the execution of our program. once_cell Snafu will help us to handle errors using context. Step 2:Implement the DBManager use bb8_postgres::bb8::{Pool, PooledConnection, RunError}; use bb8_postgres::PostgresConnectionManager; use chrono::{DateTime, Utc}; use once_cell::sync::OnceCell; use snafu::{ResultExt, Snafu}; use tokio_postgres::types::{FromSql, ToSql}; use tokio_postgres::{NoTls, Row, ToStatement}; use uuid::Uuid; // Thread-safe instance of DBManager static DB_MANAGER_INSTANCE: OnceCell<DBManager> = OnceCell::new(); // Alias to represent a postgres database connection pub type DBConnection<'a> = PooledConnection<'a, PostgresConnectionManager<NoTls>>; // Alias to represent a database pool connections pub type DBPool = Pool<PostgresConnectionManager<NoTls>>; // It can occur when your not able to get a connection from the pool pub type PostgresConnectionError = RunError<tokio_postgres::error::Error>; // Provide a contexts for better error handling #[derive(Debug, Snafu)] pub enum Error { #[snafu(display("ConnectionError: {}", source))] ConnectionError { source: PostgresConnectionError }, #[snafu(display("PostgresError: {}", source))] PostgresError { source: tokio_postgres::Error }, } We start with some basics. Import the stuff we need and create a type alias to work with. I also define here the main error struct. As you see, we will create a struct called . DBManager The idea is to set up a to manage the connections and call functions to prepare statements and perform queries to the database. struct bb8 pool tokio_postgres This must be a singleton because we don't want to create several pools of connections -- that doesn't make sense. is a kind of service pattern. DBManager pub struct DBOptions { // see https://docs.rs/tokio-postgres/latest/tokio_postgres/config/struct.Config.html" pub pg_params: String, pub pool_max_size: u32, } // We call the DBManager when required // like a kind of singleton pub struct DBManager { pool: DBPool, } impl DBManager { // Get an instance of DBManager pub async fn get() -> &'static DBManager { DB_MANAGER_INSTANCE.get().unwrap() } // Create the DBManager instance using DBOptions async fn new(config: DBOptions) -> Result<Self, Error> { let DBOptions { pg_params, pool_max_size, } = config; let manager = PostgresConnectionManager::new_from_stringlike(pg_params, NoTls) .expect("unable build PostgresConnectionManager"); let pool = Pool::builder() .max_size(pool_max_size) .build(manager) .await .context(PostgresError)?; Ok(Self { pool }) } // Helper to get a connection from the bb8 pool pub async fn connection(&self) -> Result<DBConnection<'_>, Error> { let conn = self.pool.get().await.context(ConnectionError)?; Ok(conn) } // Perform a query from a fetched bb8 connection pub async fn query<T>( &self, statement: &T, params: &[&(dyn ToSql + Sync)], ) -> Result<Vec<Row>, Error> where T: ?Sized + ToStatement, { let conn = self.connection().await?; let rows = conn.query(statement, params).await.context(PostgresError)?; Ok(rows) } // Perform a query_one from a fetched bb8 connection pub async fn query_one<T>( &self, statement: &T, params: &[&(dyn ToSql + Sync)], ) -> Result<Row, Error> where T: ?Sized + ToStatement, { let conn = self.connection().await?; let row = conn .query_one(statement, params) .await .context(PostgresError)?; Ok(row) } } DBOptions represent the required parameters to initialize the DBManager. I've added a method to perform a secure SQL statement based on query and query_one method from tokio_postgres. The code will rely on this struct to do any operations with the postgres database. Another quite interesting thing, DBManager can be a mockup to interact with a fake database for unit testing. Step 3:Add the To-Do Entity and Operations The database contains a to-do table and a priority level enumeration.  create extension if not exists "uuid-ossp"; create type priority_level as enum ( 'Low', 'Medium', 'High' ); create table todo ( id uuid primary key default uuid_generate_v4(), task text, priority priority_level, created_at timestamptz not null, expired_at timestamptz, completed_at timestamptz ); A simple database for a simple example. I'm thinking about writing a second article on this blog using a more complex database. But this example is enough to understand the basics. #[derive(Debug, ToSql, FromSql)] #[postgres(name = "priority_level")] pub enum PriorityLevel { Low, Medium, High, } #[derive(Debug)] pub struct Todo { id: uuid::Uuid, task: String, priority: PriorityLevel, created_at: DateTime<Utc>, expired_at: Option<DateTime<Utc>>, completed_at: Option<DateTime<Utc>>, } I used the ‘entity pattern’ here. A SQL table is represented by a rust struct, a SQL enumeration is represented by a rust enumeration. Quite simple. We deal with objects firstly. These structs can derivate serde struct for JSON serialization. impl Todo { pub fn new(task: String, priority: PriorityLevel, expired_at: Option<DateTime<Utc>>) -> Self { Self { id: Uuid::new_v4(), task, priority, created_at: chrono::offset::Utc::now(), expired_at, completed_at: None, } } // Get all todo from database pub async fn get_all() -> Result<Vec<Self>, Error> { let select_one_todo = " select id as todo_id, task as todo_task, priority as todo_priority, created_at as todo_created_at, expired_at as todo_expired_at, completed_at as todo_completed_at from todo;"; let rows = DBManager::get().await.query(select_one_todo, &[]).await?; let todo_list: Vec<Self> = rows .iter() .map(|row| Self::try_from(row).unwrap()) .collect(); Ok(todo_list) } // get a todo by id from database pub async fn get_by_id(id: &Uuid) -> Result<Self, Error> { let select_one_todo = " select id as todo_id, task as todo_task, priority as todo_priority, created_at as todo_created_at, expired_at as todo_expired_at, completed_at as todo_completed_at from todo where id = $1;"; let row = DBManager::get() .await .query_one(select_one_todo, &[id]) .await?; Ok(Self::try_from(&row)?) } // Toggle completed_at, if None the todo is not completed, pub fn toggle_complete(&mut self) { self.completed_at = match self.completed_at { Some(_) => None, None => Some(chrono::offset::Utc::now()), } } // Method to persist the object in database // can be calls to create or update an existing object in database pub async fn save(&self) -> Result<&Self, Error> { let insert_new_todo = " insert into todo (id, task, priority, created_at, expired_at, completed_at) values ($1, $2, $3, $4, $5, $6) ON CONFLICT (id) DO UPDATE SET task = EXCLUDED.task, priority = EXCLUDED.priority, created_at = EXCLUDED.created_at, expired_at = EXCLUDED.expired_at, completed_at = EXCLUDED.completed_at;"; let _ = DBManager::get() .await .query( insert_new_todo, &[ &self.id, &self.task, &self.priority, &self.created_at, &self.expired_at, &self.completed_at, ], ) .await?; Ok(self) } // Be carefull, it's not a soft-delete. // this will remove the data of the object from the database. // But the object himself is not dropped. So you can continue to // interact with it. async fn delete(&self) -> Result<&Self, Error> { let delete_todo = "delete from todo where id = $1;"; let _ = DBManager::get() .await .query(delete_todo, &[&self.id]) .await?; Ok(self) } } The methods and are quite special -- I was inspired by ORM like Doctrine or Eloquent. save delete You can create and manipulate the object and save it in the database when you're ready to store it. I provided static methods (get_by_id and get_all) to fetch the to-do tasks from the database. These act like methods from a repository. But you should have seen something special, I call try_from method to convert an into an instance of . So as you imagine, we have to implement the trait. &Row Todo TryFrom<T> Step 4:The Final Touch! impl<'a> TryFrom<&'a Row> for Todo { type Error = Error; fn try_from(row: &'a Row) -> Result<Self, Self::Error> { let id = row.try_get("todo_id").context(PostgresError)?; let task = row.try_get("todo_task").context(PostgresError)?; let created_at = row.try_get("todo_created_at").context(PostgresError)?; let expired_at = row.try_get("todo_expired_at").context(PostgresError)?; let completed_at = row.try_get("todo_completed_at").context(PostgresError)?; let priority = row.try_get("todo_priority").context(PostgresError)?; Ok(Self { id, task, created_at, expired_at, completed_at, priority, }) } } Have you noticed the names of the columns? The column name starts with the name of the table (todo_) and finishes with the name of the field. That's the trick. When you select data from PostgreSQL, each field must start with the name of the entity. Like this: select id as todo_id, task as todo_task, priority as todo_priority, created_at as todo_created_at, expired_at as todo_expired_at, completed_at as todo_completed_at from todo; This naming system prevents you from getting the wrong fields if you join some tables because many tables can have common named fields. #[tokio::main] async fn main() -> Result<(), Error> { // TODO: You can improve this by using clap to // get database settings from CLI or ENV VAR let options = DBOptions { pg_params: String::from( "postgres://postgres:test@localhost:5432/postgres?connect_timeout=10", ), pool_max_size: 8u32, }; // Create the unique instance of DBManager let _ = DB_MANAGER_INSTANCE.set(DBManager::new(options).await?); // Create a new todo let mut todo_finish_this_draft = Todo::new(String::from("Publish this draft"), PriorityLevel::High, None); // Persist this todo in database, // this insert the data of the object // into the todo table todo_finish_this_draft.save().await?; // Show the todo object println!("{:?}", todo_finish_this_draft); // Mutate the state of this todo make it completed! todo_finish_this_draft.toggle_complete(); // Then, persist the object again. // This update the object in database because // the id of this object already exist. todo_finish_this_draft.save().await?; // Display the updated todo println!("{:?}", todo_finish_this_draft); // Fetch all todo from the database let todo_list = Todo::get_all().await?; // As you see, there is only 1 todo in the database // That's normal, we persist 2 times the same object. println!("{:?}", todo_list); // Remote the object data from the database // but it does not drop the rust object. todo_finish_this_draft.delete().await?; // As you see, there is no more todo in database let new_todo_list = Todo::get_all().await?; println!("{:?}", new_todo_list); Ok(()) } Honestly, it would be better to write unit tests, but I want to keep things simple for now. The main function calls all the implemented functions to test them. The complete code is available on this repository: https://github.com/prx0/todolist-bb8-postgres/tree/main/src
