DEV Community 👩‍💻👨‍💻

Cover image for Rust and Postgresql
Bearz
Bearz

Posted on • Updated on

Rust and Postgresql

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:
Database table example

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:
Project source tree
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)
Dependecies in cargo.toml

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(())
}

Enter fullscreen mode Exit fullscreen mode

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:
Creating a module
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

}
Enter fullscreen mode Exit fullscreen mode

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(())

    }

}

Enter fullscreen mode Exit fullscreen mode

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(())
    }

}
Enter fullscreen mode Exit fullscreen mode

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]
)?;
Enter fullscreen mode Exit fullscreen mode

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(())
    }

}
Enter fullscreen mode Exit fullscreen mode

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(())
    }

}
Enter fullscreen mode Exit fullscreen mode

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(())
}
Enter fullscreen mode Exit fullscreen mode

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)

Collapse
 
peteole profile image
Ole Petersen

Thanks for the article!
What do you think of typesafe solutins such as sqlx?

Collapse
 
bearz profile image
Bearz Author

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

🌚 Browsing with dark mode makes you a better developer by a factor of exactly 40.

It's a scientific fact.