Accessing a PostgreSQL database

What we did in the SQLite database is similar to what we will be doing in the PostgreSQL database. This is because they are both based on the SQL language, but mostly because SQLite is designed to be similar to PostgreSQL. It may be harder to convert an application from PostgreSQL into SQLite because the former has many advanced features that are not available in the latter.

In this section, we are going to convert the example from the previous section so that it works with a PostgreSQL database instead of SQLite. So, we'll explain the differences.

The source code for this section can be found in the postgresql_example folder. To run it, open its folder and type in cargo run. This will carry out essentially the same operations that we saw for sqlite_example, and so after creating and populating the database, it will print the following:

At instant 1234567890, 7.439 Kg of pears were sold.

Implementation of the project

This project only uses the crate named postgres. Its name is a popular contraction of the postgresql name.

Creating a connection to a PostgreSQL database is very different from creating a connection to a SQLite database. As the latter is only a file, you do so in a similar way to opening a file, and you should write Connection::open(<pathname of the db file>). Instead, to connect to a PostgreSQL database, you need access to a computer where a server is running, then access to the TCP port where that server is listening, and then you need to specify your credentials on this server (your username and password). Optionally, you can then specify which of the databases managed by this server you want to use.

So, the general form of the call is Connection::connect(<URL>, <TlsMode>), where the URL can be, for example, postgres://postgres:post@localhost:5432/Rust2018. The general form of the URL is postgres://username[:password]@host[:port][/database], where the password, the port, and the database parts are optional. The TlsMode argument specifies whether the connection must be encrypted.

The port is optional because it has a value of 5432 by default. Another difference is that this crate does not use the params! macro. Instead, it allows us to specify a reference to a slice. In this case, it is an empty slice (&[]) because we don't need to specify parameters.

The table creation and population process is similar to the way it was done for sqlite_example. The query is different, however. This is the body of the print_db function:

for row in &conn.query(
    "SELECT p.name, s.unit, s.quantity, s.sale_date
    FROM Sales s
    LEFT JOIN Products p
    ON p.id = s.product_id
    ORDER BY s.sale_date",
    &[],
)? {
    let sale_with_product = SaleWithProduct {
        category: "".to_string(),
        name: row.get(0),
        quantity: row.get(2),
        unit: row.get(1),
        date: row.get(3),
    };
    println!(
        "At instant {}, {} {} of {} were sold.",
        sale_with_product.date,
        sale_with_product.quantity,
        sale_with_product.unit,
        sale_with_product.name
    );
}

With PostgreSQL, the query method of the connection class carries out parameter substitution, similarly to the execute method, but it does not map the row to a structure. Instead, it returns an iterator, which can be immediately used in a for statement. Then, in the body of the loop, the row variable can be used (as it is in the example) to fill a struct.

As we now know how to access data in the SQLite and PostgreSQL databases, let's see how to store and retrieve data from a Redis store.