[Avg. reading time: 14 minutes]

Database

Rust is a strong choice for database-related development because it gives you low-level control, good performance, and memory safety without a garbage collector.

It works well for:

• database clients and applications
• ETL and data engineering tools
• backend services that talk to databases
• custom storage engines or query systems
• high-performance concurrent systems

Key Features

• Safety
• Performance
• Concurrency
• Ecosystem Support
• Good for System-Level tools

Free Cloud-based Databases (MySQL, PostgreSQL, CouchDB, RabbitMQ)

https://www.alwaysdata.com/en/register/?d

Read Environment Variables

SET Environment Variables

Mac/Linux/Windows (GitBash)

export MY_POSTGRESQL_USERID=value

fn main() {
    println!("{}",std::env::var("MY_POSTGRESQL_USERID").unwrap());
}

cargo.toml

postgres="0.19.13"

Create Table

use postgres::{Client, Error, NoTls};

fn main() -> Result<(), Error> {

    // clearscreen::clear().expect("failed to clear screen");

    let postgresql_userid = std::env::var("MY_POSTGRESQL_USERID").unwrap();
    let postgresql_pwd = std::env::var("MY_POSTGRESQL_PWD").unwrap();

    let conn_str = format!("postgresql://{postgresql_userid}:{postgresql_pwd}@postgresql-dbworldgc.alwaysdata.net:5432/dbworldgc_pg");

    let mut client = Client::connect(&conn_str, NoTls)?;

    client.batch_execute(
        "
    CREATE TABLE if not exists sitcoms (
        id      SERIAL PRIMARY KEY,
        name    TEXT NOT NULL,
        genre    TEXT NULL
    )
",
    )?;

    Ok(())
}

NoTls is a type provided by the postgres crate in Rust, which specifies that the connection to the PostgreSQL database should not use TLS (Transport Layer Security) encryption. Essentially, it indicates that the connection should be made without any encryption.

Unencrypted Connection: Non prod or Used in Trusted environment. Performance: Faster due to absence of encryption overhead.

use postgres::{Client, Error, NoTls};

fn main() -> Result<(), Error> {

    clearscreen::clear().expect("failed to clear screen");

    let postgresql_userid = std::env::var("MY_POSTGRESQL_USERID").unwrap();
    let postgresql_pwd = std::env::var("MY_POSTGRESQL_PWD").unwrap();

    let conn_str = format!("postgresql://{postgresql_userid}:{postgresql_pwd}@postgresql-dbworldgc.alwaysdata.net:5432/dbworldgc_pg");

    let mut client = Client::connect(&conn_str, NoTls)?;
        
    //INSERT

    let name = "Friends";
    let genre = "RomCom";

    client.execute(
        "INSERT INTO sitcoms (name, genre) VALUES ($1, $2)", &[&name, &genre],
    )?;

    //UPDATE

    let genre = "Comedy";
    let id = 2;

    client.execute(
         "UPDATE sitcoms SET genre = $2 WHERE id = $1", &[&id, &genre],
    )?;

    //DELETE

    let id = 1;
    client.execute(
        "DELETE FROM sitcoms WHERE id = $1", &[&id],
    )?;


    // Multiple Rows Insert

    let tup_arr = [("Seinfeld","Standup"),("Charmed","Drama")];

    for row in tup_arr{
        client.execute(
            "INSERT INTO sitcoms (name, genre) VALUES ($1, $2)", &[&row.0, &row.1],
        )?;
        println!("Inserting --- {},{}",row.0,row.1);
    }

    // Read the Value

    for row in client.query("SELECT id, name, genre FROM sitcoms", &[])? {
        let id: i32 = row.get(0);
        let name: &str = row.get(1);
        let genre: &str = row.get(2);
        println!("---------------------------------");
        println!("{} | {} | {:?}", id, name, genre);
    }


    #[derive(Debug)]
    struct Sitcom {
        id: i32,
        name: String,
        genre: Option<String>, // Use Option for nullable fields
    }


    // Read the Value and store in a vector
    let mut sitcoms: Vec<Sitcom> = vec![];
    
    for row in client.query("SELECT id, name, genre FROM sitcoms", &[])? {
         let sitcom = Sitcom {
             id: row.get(0),
             name: row.get(1),
             genre: row.get(2),
         };
         sitcoms.push(sitcom);
     }

    // // Print the sitcoms vector
     for sitcom in sitcoms {
         println!("{:?}", sitcom);
     }

    Ok(())
}

Transactions

use postgres::{Client, Error, NoTls, Transaction};

fn main() -> Result<(), Error> {
    let postgresql_userid = std::env::var("MY_POSTGRESQL_USERID").unwrap();
    let postgresql_pwd = std::env::var("MY_POSTGRESQL_PWD").unwrap();
    let conn_str = format!("postgresql://{postgresql_userid}:{postgresql_pwd}@postgresql-dbworldgc.alwaysdata.net:5432/dbworldgc_pg");

    let mut client = Client::connect(&conn_str, NoTls)?;

    // Create table
    client.batch_execute(
        "
        CREATE TABLE IF NOT EXISTS public.sitcoms_tran (
            id      SERIAL PRIMARY KEY,
            name    TEXT UNIQUE NOT NULL,
            genre   TEXT NULL
        )
        ",
    )?;

    // Start a transaction
    let mut transaction = client.transaction()?;

    // Insert rows within the transaction
    match insert_sitcoms(&mut transaction) {
        Ok(_) => {
            // If no error, commit the transaction
            transaction.commit()?;
            println!("Transaction committed.");
        },
        Err(e) => {
            // If there is an error, rollback the transaction
            transaction.rollback()?;
            eprintln!("Transaction rolled back due to error: {}", e);
        }
    }

    Ok(())
}

fn insert_sitcoms(transaction: &mut Transaction) -> Result<(), Error> {
    transaction.execute("INSERT INTO sitcoms_tran (name, genre) VALUES ($1, $2)", &[&"Seinfeld", &"RomCom"])?;

    Ok(())
}

Error Handling

use postgres::{Client, Error, NoTls};

fn main() -> Result<(), Error> {
    let postgresql_userid = std::env::var("MY_POSTGRESQL_USERID").unwrap();
    let postgresql_pwd = std::env::var("MY_POSTGRESQL_PWD").unwrap();
    let conn_str = format!("postgresql://{postgresql_userid}:{postgresql_pwd}@postgresql-dbworldgc.alwaysdata.net:5432/dbworldgc_pg");

    let mut client = Client::connect(&conn_str, NoTls)?;

    client.batch_execute(
        "
        CREATE TABLE IF NOT EXISTS public.sitcoms (
            id      SERIAL PRIMARY KEY,
            name    TEXT NOT NULL,
            genre   TEXT NULL
        )
        ",
    )?;

    let result = client.execute("INSERT INTO sitcoms (name, genre) VALUES ($1, $2)", &[&"Friends", &"RomCom"]);

    match result {
        Ok(rows) => println!("Inserted {} row(s)", rows),
        Err(err) => eprintln!("Error inserting row: {}", err),
    }

    Ok(())
}

#database #postgresqlVer 2.2.1