How to connect and query to Postgresql Golang vs Rust?

How to connect to Postgresql Golang vs Rust?

Programming Languages war

Hey, my friends, a new post about this war. I don't know which is better, but I'll try to figure it out. I hope y'all enjoy this series. Here we go!

It's regular in the job environment to have to connect to databases, in this case, we're going to connect with Postgresql.

You only need to have to install the Postgresql server (Docker is good too 😉), in my case I had to create a database called "test" and a table called pokemon, pokemon has 3 fields, id, name, type.

Golang/Go

First, you need to install a package called "github.com/lib/pq", how to install this package?

Init the package main with the name of the folder.

go mod init name_folder

Get the package from the internet.

go get -u github.com/lib/pq

This package is A pure Go/Golang Postgres driver for Go's database/sql package, later we will see how to use it.

All Go/Golang scripts are the package, so remember to tell it what package is, In this case, is the main.

package main

Now import all packages we need to write this code.

import (
    "database/sql" // Package sql provides a generic interface around SQL (or SQL-like) databases. 
    "fmt" // implements formatted I/O
    "log" // implements a simple logging package

    _ "github.com/lib/pq" // Why has underscore at first, that's because I never had to call this package directly.
)

In Go/Golang all you define you have to use it, this is the reason why I have to use underscore because it's the way to define but never I'm going to use it this declaration.

Define a variable of connection to the database.

const (
    host     = "localhost"
    port     = 5432
    user     = "user"
    password = "password"
    dbname   = "test"
)

Define struct of database table.

type Pokemon struct {
    ID   int
    Name string
    Type string
}

Remember it's the main function because is the entry point to run.

Now, I have to define the connection's string, I need all variables defined before.
Open the connection with the method Open() from the SQL package with the driver name that I need and the connection's string.
Validate if the connection has an error. If it doesn't error call with "defer" the method Close() from the SQL package.

  psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
    db, err := sql.Open("postgres", psqlInfo)
    if err != nil {
        log.Fatal(err)
    }

  defer db.Close()

If you wonder what does "defer" does? It's simple when all code run, the last command will be the one you say in "defer".

It's time to catch a new Pokemon, who is this Pokemon?

I use the "struct" to define the values that I need to create the register.
After, I create a value with the SQL script to insert, returning the id.
With the method QueryRow, It needs the query and the values necessary to insert after that I use the method Scan to add the id to a struct called Pokemon.
I check if it has an error and print the register.

  myNewPokemon := Pokemon{
        Name: "Metapod",
        Type: "Bug",
    }

    insertSql := "INSERT INTO pokemons (name, type) VALUES ($1, $2) RETURNING id"
    err = db.QueryRow(insertSql, myNewPokemon.Name, myNewPokemon.Type).Scan(&myNewPokemon.ID)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%+v\n", myNewPokemon)

Now, It's time to fight, Metapod I choose you.

In this case, I need to get one register from the database, the steps are similar:

I declare a variable with the type Struct. (This is only to show that each register is in different variables)
I create the SQL script to find it in the database.
With the method QueryRow, it executes the query with the value necessary, and with the method Scan, it inserts the register in the struct.
I check if it has an error and print the register.

  var myPokemon Pokemon
    selectSql := `SELECT * FROM pokemons WHERE id = $1`
    err = db.QueryRow(selectSql, myNewPokemon.ID).Scan(&myPokemon.ID, &myPokemon.Name, &myPokemon.Type)
    if err != nil {
        log.Fatal(err)
    }

    fmt.Printf("%+v\n", myPokemon)

What is that, is Metapod evolving?

Now, it's time to update:

I declare a variable with the type Struct. (This is only to show that each register is in different variables)
I create the SQL script to update the database.
With the method QueryRow, it executes the query with the value necessary, and with the method Scan, it inserts the register in the struct.
I check if it has an error and print the register.

    var evolution Pokemon
    updateSql := "UPDATE pokemons SET name = $1, type = $2 WHERE id = $3 RETURNING id, name, type"
    err = db.QueryRow(updateSql, "Butterfree", "Bug Flying", myPokemon.ID).Scan(&evolution.ID, &evolution.Name, &evolution.Type)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%+v\n", evolution)

Anyone wants this moment?, but I have to let free my Butterfree, He fell in love:

I have to delete, remember the delete methods are so dangerous, be careful.

I declare a variable with the type Struct. (This is only to show that each register is in different variables)
I create the SQL script to delete the database.
With the method QueryRow, it executes the query with the value necessary, and with the method Scan, it inserts the register in the struct.
I check if it has an error and print the register.

    var byePokemon Pokemon
    deleteSql := "DELETE FROM pokemons WHERE id = $1 RETURNING id, name, type"
    err = db.QueryRow(deleteSql, evolution.ID).Scan(&byePokemon.ID, &byePokemon.Name, &byePokemon.Type)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%+v\n", byePokemon)

I have good pokemons yet.

In the end, how to show all registers in the database:

I create the SQL script to read in the database.
With the method QueryRow, it executes the query with the value necessary.
I check if it has an error.
I have to close the connection with the method close, and I use "defer", you know why, if you don't know why, you can read a little higher.
I declare a variable with the type Struct, in this case, is an array because I want to show all pokemons.
I read each row and associate it with other variables in each column, with the method Scan.
I check if it has an error and add the register to the array.
Print the registers.

  rows, err := db.Query("SELECT * FROM pokemons")
    if err != nil {
        log.Fatal(err)
    }

    defer rows.Close()
    var myPokemons []Pokemon
    for rows.Next() {
        var pokemon Pokemon
        err = rows.Scan(&pokemon.ID, &pokemon.Name, &pokemon.Type)
        if err != nil {
            log.Fatal(err)
        }

        myPokemons = append(myPokemons, pokemon)
    }

    fmt.Printf("%+v\n", myPokemons)

Here is all my code in Go/Golang. And now it's our code. 🤓

package main

import (
    "database/sql"
    "fmt"
    "log"

    _ "github.com/lib/pq"
)

const (
    host     = "localhost"
    port     = 5432
    user     = "user"
    password = "password"
    dbname   = "test"
)

type Pokemon struct {
    ID   int
    Name string
    Type string
}

func main() {
    psqlInfo := fmt.Sprintf("host=%s port=%d user=%s password=%s dbname=%s sslmode=disable", host, port, user, password, dbname)
    db, err := sql.Open("postgres", psqlInfo)
    if err != nil {
        log.Fatal(err)
    }
    defer db.Close()

    // Create
    myNewPokemon := Pokemon{
        Name: "Metapod",
        Type: "Bug",
    }

    insertSql := "INSERT INTO pokemons (name, type) VALUES ($1, $2) RETURNING id"
    err = db.QueryRow(insertSql, myNewPokemon.Name, myNewPokemon.Type).Scan(&myNewPokemon.ID)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%+v\n", myNewPokemon)

    // Read one register
    var myPokemon Pokemon
    selectSql := `SELECT * FROM pokemons WHERE id = $1`
    err = db.QueryRow(selectSql, myNewPokemon.ID).Scan(&myPokemon.ID, &myPokemon.Name, &myPokemon.Type)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%+v\n", myPokemon)

    // Update
    var evolution Pokemon
    updateSql := "UPDATE pokemons SET name = $1, type = $2 WHERE id = $3 RETURNING id, name, type"
    err = db.QueryRow(updateSql, "Butterfree", "Bug Flying", myPokemon.ID).Scan(&evolution.ID, &evolution.Name, &evolution.Type)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%+v\n", evolution)

    // Delete
    var byePokemon Pokemon
    deleteSql := "DELETE FROM pokemons WHERE id = $1 RETURNING id, name, type"
    err = db.QueryRow(deleteSql, evolution.ID).Scan(&byePokemon.ID, &byePokemon.Name, &byePokemon.Type)
    if err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%+v\n", byePokemon)

    // Read all
    rows, err := db.Query("SELECT * FROM pokemons")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    var myPokemons []Pokemon
    for rows.Next() {
        var pokemon Pokemon
        err = rows.Scan(&pokemon.ID, &pokemon.Name, &pokemon.Type)
        if err != nil {
            log.Fatal(err)
        }

        myPokemons = append(myPokemons, pokemon)
    }
    fmt.Printf("%+v\n", myPokemons)

}

Rust

The first part is to add all dependencies necessary to execute this code. Remember these dependencies should add in the file Cargo.toml

[dependencies]
serde = { version = "1.0", features = ["derive"] } # framework for serializing and deserializing Rust data structures efficiently and generically.
postgres = "0.19.2" # A synchronous client for the PostgreSQL database.

Now I'm going to the file main.rs

First, call the modules necessary to run this code.

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

Define struct of database table

#[derive] This is able to implement a trait, which means that, how you can see, after this line, define a structure, I know that I need to get this information from a database, so, to achieve to serializing this information, I have to say how to structure with serializing or debug.

So, #[derive(Debug, Serialize)], Serialize, I used this to can change the name "type" to "ty" and Debug allows me to show variable with this operator {:?}

If you wonder why did I have to use rename? The reason is that type is a reserved keyword, then, exist two options.

using rename ( I like this more).
using r#type can escape reserved keywords.

#[derive(Debug, Serialize)]
struct Pokemon {
    id: i32,
    name: String,
    #[serde(rename = "type")]
    ty: String,
}

Remember it's the main function because is the entry point to run.

Now, I have to define the connection's string.
Open the connection with the method connect() from the SQL package with the string of connection.
To “unwrap” something in Rust is to say, “Give me the result of the computation, and if there was an error, panic and stop the program.”

    let mut client = Client::connect("postgresql://user:password@localhost:5432/test", NoTls).unwrap();

It's time to catch a new pokemon, who is this pokemon?

I use the "struct" to define the values that I need to create the register.
After, I create a value with the SQL script to insert, returning the id.
With the method query_one need the query and the values necessary to insert after that I use the method "get" to add the id to the struct called Pokemon.
Print the register.

    let mut my_new_pokemon = Pokemon {
        id: 0,
        name:  "Squirtle".to_string(),
        ty:  "Water".to_string(),
    };
    let insert_sql = "INSERT INTO pokemons (name, type) VALUES ($1, $2) RETURNING id";
    let row = client.query_one(insert_sql, &[&my_new_pokemon.name, &my_new_pokemon.ty])?;
    my_new_pokemon.id = row.get(0);
    println!("{:#?}", my_new_pokemon);

Now, It's time to fight, Squirtle I choose you. In this case, I need to get one register from the database, the steps are similar:

I create the SQL script to find it in the database.
With the method query_one execute the query with the value necessary.
I declare a variable with the type Struct and with the method "get", I insert the register in the struct. (This is only to show that each register is in different variables)
Print the register.

    let select_sql = "SELECT * FROM pokemons WHERE id = $1";
    let row = client.query_one(select_sql, &[&my_new_pokemon.id])?;
    let my_pokemon = Pokemon {
        id: row.get(0),
        name:  row.get(1),
        ty:  row.get(2),
    };
    println!("{:#?}", my_pokemon);

What is that, is Squirtle evolving? Now, it's time to update:

I create two variables with the name and type of this pokemon.
I create the SQL script to update the database.
With the method query_one execute the query with the value necessary.
I declare a variable with the type Struct and with the method "get", I insert the register in the struct. (This is only to show that each register is in different variables)
Print the register.

    let pokemon_name =  "Wartortle".to_string();
    let pokemon_type =  "Water".to_string();
    let update_sql = "UPDATE pokemons SET name = $1, type = $2 WHERE id = $3 RETURNING id, name, type";
    let row = client.query_one(update_sql, &[&pokemon_name, &pokemon_type, &my_pokemon.id])?;
    let evolution = Pokemon {
        id: row.get(0),
        name:  row.get(1),
        ty:  row.get(2),
    };
    println!("{:#?}", evolution);

Anyone wants this moment?, but I have to let free my Wartortle because is a great leader and the Squirtle squad need one, I know the story is not original.

I have to delete, remember the delete methods are so dangerous, be careful.

I create the SQL script to delete the database.
With the method query_one execute the query with the value necessary.
I declare a variable with the type Struct and with the method "get", I insert the register in the struct. (This is only to show that each register is in different variables)
Print the register.

    let delete_sql = "DELETE FROM pokemons WHERE id = $1 RETURNING id, name, type";
    let row = client.query_one(delete_sql, &[&evolution.id])?;
    let bye_pokemon = Pokemon {
        id: row.get(0),
        name:  row.get(1),
        ty:  row.get(2),
    };
    println!("{:#?}", bye_pokemon);

I have good pokemons yet.

In the end, how to show all registers in the database:

I declare a variable with the type Struct, in this case, is a vector because I want to show all pokemons.
I create the SQL script to read in the database, with the method query execute the query with the value necessary and I go through the rows that get the query.
I declare a variable with the type Struct and with the method "get", I insert the register in the struct. (This is only to show that each register is in different variables).
With the method push, I add to the vector each row.
Print the registers.

    let mut my_pokemons:Vec<Pokemon> = vec![];
    for row in client.query("SELECT * FROM pokemons", &[]).unwrap() {
        let pokemon = Pokemon {
            id: row.get(0),
            name: row.get(1),
            ty: row.get(2),
        };
        my_pokemons.push(pokemon);
    }
    println!("{:#?}", my_pokemons);

Here is all my code in Rust. And now it's our code. 🤓

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

#[derive(Debug, Serialize)]
struct Pokemon {
    id: i32,
    name: String,
    #[serde(rename = "type")]
    ty: String,
}

fn main() -> Result<(), Error>{
    let mut client = Client::connect("postgresql://user:password@localhost:5432/test", NoTls).unwrap();

    // Create
    let mut my_new_pokemon = Pokemon {
        id: 0,
        name:  "Squirtle".to_string(),
        ty:  "Water".to_string(),
    };
    let insert_sql = "INSERT INTO pokemons (name, type) VALUES ($1, $2) RETURNING id";
    let row = client.query_one(insert_sql, &[&my_new_pokemon.name, &my_new_pokemon.ty])?;
    my_new_pokemon.id = row.get(0);
    println!("{:#?}", my_new_pokemon);

    // Read
    let select_sql = "SELECT * FROM pokemons WHERE id = $1";
    let row = client.query_one(select_sql, &[&my_new_pokemon.id])?;
    let my_pokemon = Pokemon {
        id: row.get(0),
        name:  row.get(1),
        ty:  row.get(2),
    };
    println!("{:#?}", my_pokemon);

    // Update
    let pokemon_name =  "Wartortle".to_string();
    let pokemon_type =  "Water".to_string();
    let update_sql = "UPDATE pokemons SET name = $1, type = $2 WHERE id = $3 RETURNING id, name, type";
    let row = client.query_one(update_sql, &[&pokemon_name, &pokemon_type, &my_pokemon.id])?;
    let evolution = Pokemon {
        id: row.get(0),
        name:  row.get(1),
        ty:  row.get(2),
    };
    println!("{:#?}", evolution);

    // Delete
    let delete_sql = "DELETE FROM pokemons WHERE id = $1 RETURNING id, name, type";
    let row = client.query_one(delete_sql, &[&evolution.id])?;
    let bye_pokemon = Pokemon {
        id: row.get(0),
        name:  row.get(1),
        ty:  row.get(2),
    };
    println!("{:#?}", bye_pokemon);

    // Read all
    let mut my_pokemons:Vec<Pokemon> = vec![];
    for row in client.query("SELECT * FROM pokemons", &[]).unwrap() {
        let pokemon = Pokemon {
            id: row.get(0),
            name: row.get(1),
            ty: row.get(2),
        };
        my_pokemons.push(pokemon);
    }
    println!("{:#?}", my_pokemons);
    Ok(())
}

Conclusion

Structure: It's similar, Maybe the principal difference between them, it's that Go/Golang uses "if" in each iteration to validate if has an error, this is because Go/Golang doesn't have exceptions. Lines: Golang: 91, Rust: 69 Easy to get information: For me It was easier to get information from Go/Golang than Rust. Execution time: In this example, the difference is not decisive.

Would you like that I write more info about the database? What would be your first pokemon?

I hope you enjoy my post and remember that I am just a Dev like you!

How to connect and query to Postgresql Golang vs Rust?

Comments (1)

More from this blog

How to create a rest API client Golang vs Rust?

Programming Languages war Golang vs Rust Define Variables

Programming Languages war Golang vs Rust

Programming Games Python Hangman

How to connect to Postgresql Golang vs Rust?

Programming Languages war

Golang/Go

Rust

Command Palette

Comments (1)

More from this blog

How to connect to Postgresql Golang vs Rust?

Programming Languages war

Golang/Go

Rust