How to Use Vultr Managed Databases for PostgreSQL in Rust

Introduction

PostgreSQL is an open-source, ACID-compliant relational database. The Rust programming language is supported by PostgreSQL thanks to the rust-postgres project.

This article explains:

• How to create a managed database.
• How to connect to it securely using TLS.
• How to execute CRUD (Create, Read, Update, Delete) operations using the native Rust PostgreSQL client.

PostgreSQL Rust client

rust-postgres provides a way for Rust applications to communicate with PostgreSQL in a synchronous way. Other crates in the Rust-PostgreSQL ecosystem include:

• tokio-postgres - A native, asynchronous PostgreSQL client.
• postgres-types - Helps convert between Rust and Postgres types.
• postgres-native-tls - Provides TLS support for postgres (and tokio-postgres) via native-tls.
• postgres-openssl - Provides TLS support for postgres (and tokio-postgres) via openssl.

rust-postgres has additional features that can be enabled on-demand. Some of the interesting ones include:

• with-serde_json-1: Enables support for the serde_json crate.
• with-uuid-1: Enables support for the uuid crate.
• with-geo-types-0_7: Enable support for the 0.7 version of the geo-types crate.

For a full feature list, see the PostgreSQL documentation.

Although this article uses the synchronous client, here is an overview of the asynchronous client.

Asynchronous client

It is based on the tokio runtime. Since it's asynchronous, invoking methods such as Client::query does not do anything. The request is not sent to the database until the future returned by the method is first polled. Requests are executed in the order that they are first polled, not in the order that their futures are created.

Please note that the rust-postgres crate is a wrapper over tokio-postgres. The postgres::Client is a wrapper around a tokio_postgres::Client along with a tokio runtime. The client blocks on the future's provided by the asynchronous client.

Secure connectivity with TLS

When TLS is not required, the NoTls type can be used. TLS support is implemented by external libraries. Client::connect and Config::connect take a TLS implementation as an argument. There are two possible implementations that can be used:

1. postgres-openssl implementation which is based on openssl.
2. postgres-native-tls implementation is based on native-tls crate.

Prerequisites

To follow the instructions in this article, you will only need to install a recent version of Rust.

Create a Managed Database

1. Log into your Vultr account and navigate to Add Managed Database.
2. Choose the PostgreSQL database engine.
3. You can choose from several options in the Server Type. This includes Cloud Compute, Cloud Compute High Performance - AMD or Intel, Optimized Cloud Compute - General Purpose, and Storage or Memory Optimized.
4. Select zero or more replica nodes and the cluster location. A replica node is the same server type and plan as the primary node. In this article, we will opt for the Cloud Compute server type without a replica node for demonstration purposes.
5. Add a label for the database cluster.
6. Click Deploy Now to create the cluster. It will take a few minutes for the cluster to be available, and the Status should change to Running.

The PostgreSQL database is ready. Now you can connect to it using a Rust program.

Create a new Rust project

Create a new Rust project and change into the directory:

cargo new postgres-rust
cd postgres-rust

This creates a new Cargo package, including a Cargo.toml manifest and a src/main.rs file.

Replace the contents of Cargo.toml file with below:

[package]
name = "postgres-rust"
version = "0.1.0"
edition = "2018"

[dependencies]
postgres = "0.18.1"
openssl = "0.10.32"
postgres-openssl = "0.4.0"

Notice that the openssl and postgres-openssl crates are being used to connect to the Database over TLS.

Connect to Vultr PostgreSQL with Rust client

Replace the contents of the main function in src/main.rs file with the below code:

use openssl::ssl::{SslConnector, SslMethod};
use postgres::types::Type;
use postgres_openssl::MakeTlsConnector;

fn main() {
    let pg_host =
        std::env::var("POSTGRES_HOST").expect("missing environment variable POSTGRES_HOST");
    let pg_port = std::env::var("POSTGRES_PORT").unwrap_or("5432".to_string());
    let pg_user =
        std::env::var("POSTGRES_USER").expect("missing environment variable POSTGRES_USER");
    let pg_password =
        std::env::var("POSTGRES_PASSWORD").expect("missing environment variable POSTGRES_PASSWORD");
    let pg_dbname = std::env::var("POSTGRES_DBNAME").unwrap_or("postgres".to_string());
    let pg_cert_loc =
        std::env::var("POSTGRES_CERT_LOCATION").expect("missing environment variable POSTGRES_CERT_LOCATION");

    let mut builder = SslConnector::builder(SslMethod::tls()).unwrap();
    builder.set_ca_file(pg_cert_loc).expect("failed to load certificate");

    let tls_connector = MakeTlsConnector::new(builder.build());

    let url = format!(
        "host={} port={} user={} password={} dbname={} sslmode=require",
        pg_host,pg_port, pg_user, pg_password, pg_dbname
    );

    let mut pg_client = postgres::Client::connect(&url, tls_connector).expect("failed to connect to postgres");

    println!("successfully connected to postgresql");
}

• To start with, we read PostgreSQL connectivity information from environment variables. This includes the managed database host, port, username, password, database, and the signed certificate location on the local machine.
• For TLS, an instance of SslConnectorBuilder is configured to use the signed certificate file (using set_ca_file) and used to create an instance of MakeTlsConnector.
• Finally, Client::connect uses the url (which is created using the connectivity parameters) and TLS connector to establish a connection with the PostgreSQL instance.

Verify connectivity with the managed database

Build the program:

cargo build

The program will be compiled and built. You will see an output similar to this (part of the output has been redacted):

Compiling futures v0.3.25
Compiling tokio-postgres v0.6.0
Compiling tokio-openssl v0.5.0
Compiling postgres v0.18.1
Compiling postgres-openssl v0.4.0
Compiling postgres-rust v0.1.0 (/Users/demo/postgres-rust)

For now, you can ignore the compiler warnings about unused imports. These will be resolved later.

Before you continue, you need to get the connection details for the managed database in order to connect to it and download the TLS certificate.

1. Click the Manage icon to open the Overview tab.

2. From Connection Details section, copy the following attributes:

   • Username
   • Password
   • Host
   • Port
   • Database

3. Click Download Signed Certificate and save it to a location on your local machine.

To run the program, run:

$ export POSTGRES_HOST=[the database host]
$ export POSTGRES_PORT=[the database port]
$ export POSTGRES_USER=[the database user]
$ export POSTGRES_PASSWORD=[the database password]
$ export POSTGRES_DBNAME=[the database name]
$ export POSTGRES_CERT_LOCATION=[the location of the certificate you downloaded]

$ cargo run

If the connectivity was established, you should see the following output:

successfully connected to postgresql

There might be multiple reasons why connectivity might fail.

Failure scenarios

• Incorrect host: You will see failed to connect to postgres log, and the program will exit with an error message similar to this - failed to lookup address information: nodename nor servname provided, or not known.
• Incorrect username or password: You will see failed to connect to postgres log, and the program will exit with error message similar to this - password authentication failed for user.
• Incorrect TLS certificate path: You will see failed to connect to postgres log, and the program will exit with error message similar to this - No such file or directory.

Initialize table in PostgreSQL database

The init_table function deletes a table (called users) and re-creates it.

Add the code below to src/main.rs file (init_table function along with the constants):

const CREATE_QUERY: &str =
    "CREATE TABLE users (id serial PRIMARY KEY, name VARCHAR(50), email VARCHAR(100));";

const DROP_TABLE: &str = "DROP TABLE users";

fn init_table(pg_client: &mut postgres::Client) {
    let res = pg_client.execute(DROP_TABLE, &[]);
    match res {
        Ok(_) => println!("dropped table"),
        Err(e) => println!("failed to drop table {}", e),
    }
    pg_client
        .execute(CREATE_QUERY, &[])
        .expect("failed to create table");

    println!("successfully created table");
}

• Table attributes consists of id (primary key), name, and email.
• Separate queries are defined to create and delete the table.
• execute function is used to invoke DROP TABLE first and then execute CREATE TABLE.

Add data to the users table in PostgreSQL

The add_user function is used to demonstrate how to add rows to a table using prepared statements.

Add the code below to src/main.rs file (add_user function along with the constant):

const INSERT_QUERY: &str = "INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id;";

fn add_user(pg_client: &mut postgres::Client) {
    let prep_stmt = pg_client
        .prepare(&INSERT_QUERY)
        .expect("failed to create prepared statement");

    let row = pg_client
        .query_one(&prep_stmt, &[&"user1", &"user1@foo.com"])
        .expect("failed to insert user");

    let id: i32 = row.get(0);
    println!("added user with id {}", id);

    let typed_prep_stmt = pg_client
        .prepare_typed(&INSERT_QUERY, &[Type::VARCHAR, Type::VARCHAR])
        .expect("failed to create prepared statement");

    let row = pg_client
        .query_one(&typed_prep_stmt, &[&"user2", &"user2@foo.com"])
        .expect("failed to insert user");

    let id: i32 = row.get(0);
    println!("added user with id {}", id);

    for n in 3..=5 {
        let row = pg_client
            .query_one(
                &typed_prep_stmt,
                &[
                    &("user".to_owned() + &n.to_string()),
                    &("user".to_owned() + &n.to_string() + &"@foo.com".to_owned()),
                ],
            )
            .expect("add user failed");

        let id: i32 = row.get(0);
        println!("added user with id {} ", id);
    }
}

• A prepared Statement is created using prepare function.
• The Statement is executed using query_one function - this creates a new user row to the table and returns the auto-generated ID.
• A typed prepared Statement is created using prepare_typed, which allows query parameters to be explicitly specified.
• As before, query_one function is used to execute the typed prepared Statement and insert another row into the table.
• Finally, to add more data to the table, query_one is executed in a for loop.
• At the end of this function execution, there should be five rows in the users table.

Query data in the users table

The get_users function is used to demonstrate how to query table data using prepared statements.

Add the code below to src/main.rs file (get_users function along with the constants):

const SELECT_ALL_USERS_QUERY: &str = "SELECT * FROM users;";
const SELECT_USER_BY_ID: &str = "SELECT name, email FROM users where id=$1;";

fn get_users(pg_client: &mut postgres::Client) {
    let prep_stmt = pg_client
        .prepare_typed(&SELECT_USER_BY_ID, &[Type::INT4])
        .expect("failed to create prepared statement");

    let user_id = 1;
    let c = pg_client
        .query_one(&prep_stmt, &[&user_id])
        .expect("failed to query user");

    let name: String = c.get(0);
    let email: String = c.get(1);
    println!("email for user {} = {}", name, email);

    let users = pg_client
        .query(SELECT_ALL_USERS_QUERY, &[])
        .expect("select all users failed");

    println!("listing users...");
    for user in users {
        let id: i32 = user.get("id");
        let name: String = user.get("name");
        let email: String = user.get("email");
        println!(
            "user info: id = {}, name = {}, email = {} ",
            id, name, email
        );
    }
}

• A typed prepared Statement is created using the prepare function.
• The Statement is executed using the query_one function - this fetches a user with a specific user ID.
• query function is used to retrieve all the rows in the users table - it executes a SELECT * FROM users query.
• The resulting data is iterated, and all the user info (id, email, name) is printed out.

Update data in the users table

The update_user function is used to demonstrate how to update table data using prepared statements.

Add the code below to src/main.rs file (update_user function along with the constant):

const UPDATE_USER_QUERY: &str = "UPDATE users SET name = $1 WHERE email = $2 RETURNING name;";

fn update_user(pg_client: &mut postgres::Client) {
    let stmt = pg_client
        .prepare_typed(&UPDATE_USER_QUERY, &[Type::VARCHAR, Type::VARCHAR])
        .expect("failed to create prepared statement");

    for id in 1..=5 {
        let row = pg_client
            .query_one(
                &stmt,
                &[
                    &("new_user".to_owned() + &id.to_string()),                    
                    &("user".to_owned() + &id.to_string() + &"@foo.com".to_owned()),                ],
            )
            .expect("update failed");

        let new_name: String = row.get("name");
        println!("updated user id {} to name = {}", id, new_name);
    }
}

• A typed prepared Statement is created using the prepare function.
• Using a for loop, all the users (with user ID 1 to 5) are updated - _new is appended to their name.
• Since the new name is returned as a result of the UPDATE query, we can verify that name was indeed updated.

Delete data from the users table

The delete_user function is used to demonstrate how to delete table data using prepared statements.

Add the code below to src/main.rs file (delete_user function along with the constant):

const DELETE_QUERY: &str = "DELETE FROM users WHERE id = $1 RETURNING id,name,email;";

fn delete_user(pg_client: &mut postgres::Client) {
    let stmt = pg_client
        .prepare_typed(&DELETE_QUERY, &[Type::INT4])
        .expect("failed to create prepared statement");

    let user = pg_client
        .query_one(&stmt, &[&1])
        .expect("delete failed");

    let id: i32 = user.get(0);
    let name: String = user.get(1);
    let email: String = user.get(2);
    println!(
        "deleted user info: id = {}, name = {}, email = {} ",
        id, name, email
    );
}

• A typed prepared Statement is created using prepare function.
• The Statement is executed using query_one function - this deletes a user with a specific user ID.
• Since the id, name, and email are returned as a result of the DELETE query, we can verify the user info which was deleted.

So far, we have added code to achieve the following:

• Connect to the PostgreSQL database using TLS.
• Add data to the table using a prepared statement.
• Query table data using a prepared statement.
• Update table data using a prepared statement.
• Delete data from the table using a prepared statement.

Now, let's put all of this together before we execute the program.

Update the main function

Update the main function in src/main.rs file. Add the below code:

init_table(&mut pg_client);
add_user(&mut pg_client);
get_users(&mut pg_client);
update_user(&mut pg_client);
delete_user(&mut pg_client);

pg_client.close().expect("failed to close client");
println!("end of program");

We add all the function calls to the main function and close the Client.

Run the program

Re-build the program:

$ cargo build

You should see output similar to this:

Finished dev [unoptimized + debuginfo] target(s) in 0.53s

Use the connectivity information and signed certificate location from the section Connect to Vultr PostgreSQL with Rust client.

To run the program:

$ export POSTGRES_HOST=[the database host]
$ export POSTGRES_PORT=[the database port]
$ export POSTGRES_USER=[the database user]
$ export POSTGRES_PASSWORD=[the database password]
$ export POSTGRES_DBNAME=[the database name]
$ export POSTGRES_CERT_LOCATION=[the location of the certificate you downloaded]

$ cargo run

All the functions will be executed in sequence, and you should see an output similar to this:

successfully connected to postgresql
failed to drop table db error: ERROR: table "users" does not exist
successfully created table
added user with id 1
added user with id 2
added user with id 3 
added user with id 4 
added user with id 5 
email for user user1 = user1@foo.com
listing users...
user info: id = 1, name = user1, email = user1@foo.com 
user info: id = 2, name = user2, email = user2@foo.com 
user info: id = 3, name = user3, email = user3@foo.com 
user info: id = 4, name = user4, email = user4@foo.com 
user info: id = 5, name = user5, email = user5@foo.com 
updated user id 1 to name = new_user1
updated user id 2 to name = new_user2
updated user id 3 to name = new_user3
updated user id 4 to name = new_user4
updated user id 5 to name = new_user5
deleted user info: id = 1, name = new_user1, email = user1@foo.com 
end of program

Note: Don't worry about the log message failed to drop table db error: ERROR: table "users" does not exist. This happens when you run the program for the first time. If you re-run the program again, this will not occur since the users table will already exist.

After you have completed the tutorial in this article, you can delete the database.

Delete the Managed Database

Log into your Vultr account and navigate to Managed Databases.

1. For the database you just created, choose the delete icon.
2. In the Destroy Managed Database? pop-up window, select the checkbox Yes, destroy this Managed Database. and click on Destroy Managed Database

Conclusion

In this article, you created a Vultr Managed Database for PostgreSQL, connected to it using TLS, and executed basic SQL queries using the rust-postgres client library.

To learn more about Vultr Managed Databases, you can refer to the following documentation:

• Vultr Managed Databases Quickstart
• Vultr Managed Databases for PostgreSQL Reference Guide

If you are interested in learning more about Rust or PostgreSQL, refer to some of these interesting articles from the documentation:

• Building REST APIs in Rust with Actix Web
• Setup a Rust Game Server on CentOS
• How to Install PostgreSQL in Vultr Kubernetes Engine (VKE) with Postgres Operator
• How to Perform Full-Text Search on a PostgreSQL Database