Introduction
In nowdays there are a lot of programming languages, some of them used to do specific tasks and the same concept applies to Databases where its always useful to know how to perfom actions to an existing database.
In this post we will be using Rust and ill explain you how to use it to do a basic CRUD using Postgresql and hoping that you can merge your ideas with what ill show you here.
Prerequisites
For this tutorial ill be using a Linux Dist (Ubuntu 22.04 LTS) but if you are in Windows or MacOS ill suggest you to install Postgresql first and come back.
If you have already installed Postgres, create a user and grant all permissions so we can login later and manage the Database with Rust.
Create a table in the database you just created with the next structure:
Also install the Rust Programming Language from the official docs which is straight forward.
Lets go
Once we have installed Rust, with the installation comes Cargo which is the Rust Package Manager (like npm for Node), to create a new Rust Project we will type this in our CLI:
cargo new rust_psql
This will create a new Rust project ready to run.
Lets take a look at the files in the project:
First we will use the file named Cargo.toml, here we will write the necessary dependencies for the project, for now we will only use postgres 0.19.3 (remember to write them like in the image otherwise the project will fail at compile time)
Creating a client
We are going to use the file main.rs to create our client, for this we will pass a string that follow the next syntaxis:
"postgresql://userCreated:userPassword@localhost/databaseName"
use postgres::{ Client, Error, NoTls };
// use std::num::ParseIntError;
mod actions; // this will be used in the next steps
fn main() -> Result<(), Error> {
let mut client = Client::connect(
"postgresql://postgresuser:beautypassword123@localhost/company",
NoTls
)?;
// Using the "?" operator that works like the unwrap() method
// This will panic an error of type postgres and will make the function return, thats because we have to use that kind of Error here
// If we were parsing something, the kind of error could use is ParseIntError
Ok(())
}
Great, we have a client ready to start making querys but if you got an error check the string you passed in the Client::connect
Project Tree
This is optional because you can keep everything in the main file but it will eventually become so large and hard to read.
Instead we will split the code in a module, like this:
As you can see, we have created a folder called actions and a file inside called mod.rs we will use that file to save our functions related to do the CRUD operations.
Select
For now we are located in the /actions/mod.rs file, this is a special file to "export functions" and invoke them in our main.rs later.
In the file /actions/mod.rs write the following code:
// This is like any another rust file
// every function we write here in the "pub mod create{}" and declared as public, can be invoked later by using "mod actions;"
pub mod create {
use postgres::{ Client, Error, NoTls };
use postgres::types::Type; // Used to map rust types to postgres types
#[derive(Debug)] // This macro allows struct to be debugged
struct User {
username: String,
user_id: i32
}
// Important to deserialize the data retrieved from the Database table
}
The next part of the code have to be inside of the "pub mod create {}"
pub mod create {
//...
pub fn Select(client: &mut Client) -> Result<(), Error> {
for row in client.query("SELECT * FROM users", &[])? {
let user = User {
// row.get(column id) to get the value of the current row
username: row.get(0), // Deserialize the value into a field that is an instace of the User struct
user_id: row.get(1)
};
println!("Record: {:?}", &user);
}
Ok(())
}
}
One thing to note in the above code is the use of ? as client.query("SELECT * FROM users", &[]) returns a Result, using ? we are unwraping and getting the real value
Also inside of for loop we are using the method row.get(), this method asks for one argument, which is are the values of the record you want to take.
The index starts from 0 and they come in the same way that you created your table:
0 -> username Column
1 -> user_id Column
Insert
Its time to the Insert operation, this works the same like the one before but with some extra things.
As we are now inserting data, postgres has his own Data Types, so lets not get in trouble with them and we will map our Data Types from Rust to Postgres
pub mod create {
//...
pub fn Insert(client: &mut Client, username: String, user_id: i32) -> Result<(), Error> {
let statement = client.prepare_typed(
"INSERT INTO users (username, user_id) VALUES ($1, $2)",
&[Type::VARCHAR, Type::INT4],
)?;
let res = client.execute(
&statement,
&[&username, &user_id]
)?;
print!("Result while INSERT -> {}", &res);
Ok(())
}
}
One thing about the above code is the following:
let statement = client.prepare_typed(
"INSERT INTO users (username, user_id) VALUES ($1, $2)",
&[Type::VARCHAR, Type::INT4],
)?;
// ($1, $2) -> Those are placeholders for the statement above, think of them as if they were indexed
let res = client.execute(
&statement,
&[&username, &user_id]
)?;
The list passed as second argument in client.prepare_typed and client.execute should be in the same order with the placeholders of the query statement.
$1 -> Postgres Type::VARCHAR -> Rust String Type
$2 -> Postgres Type::INT4 -> Rust i32 Type
Note: If you are using integers in the query because you created your table like that, the only way i found to make it work was by using i32
Delete
The only thing that change here is the statement
pub mod create {
//...
pub fn Drop(client: &mut Client, username: String, user_id: i32) -> Result<(), Error> {
let statement = client.prepare_typed(
"DELETE FROM users WHERE user_id = $1",
&[Type::INT4],
)?;
let res = client.execute(
&statement,
&[&user_id]
)?;
print!("Result while INSERT -> {}", &res);
Ok(())
}
}
Update
Like in the past code, we only change the statement
pub mod create {
//...
pub fn Update(client: &mut Client, username: String, user_id: i32) -> Result<(), Error> {
let statement = client.prepare_typed(
"UPDATE users SET username = 'Bearz' WHERE user_id = $1",
&[Type::INT4],
)?;
let res = client.execute(
&statement,
&[&user_id]
)?;
print!("Result while INSERT -> {}", &res);
Ok(())
}
}
Calling CRUD actions
We almost done the only thing we have to do is test the functions, for that we will complete our code in main.rs
use postgres::{ Client, Error, NoTls };
// use std::num::ParseIntError;
mod actions;
fn main() -> Result<(), Error> {
let mut client = Client::connect(
"postgresql://postgresuser:beautypassword123@localhost/company",
NoTls
)?;
// This will panic an error of type postgres, thats because we have to use that kind of Error here
// If we are parsing, the kind of error will be ParseIntError
let username = String::from("Jess");
let user_id: i32 = 11;
// CRUD ACTIONS
// try out each one of them :)
actions::create::Select(&mut client);
actions::create::Insert(&mut client, username, user_id);
actions::create::Drop(&mut client, username, user_id);
actions::create::Update(&mut client, username, user_id);
Ok(())
}
And thats all, we have created a CRUD in Rust using Postgresql.
At this point you should be able to do your own projects with this basics that i gave you, maybe a REST API idk ;)
Thank you so much for reading, the git repo is here:
** https://github.com/AlonsoCrag/rust-postgres **
Top comments (2)
Thanks for the article!
What do you think of typesafe solutins such as sqlx?
Oh i didnt knew about that, but looks like sqlx is easier and has more cool features. I will definitely take a deeper look to use it in the next projects , thanks :D