Connecting to the Database

Now that we have everything we need, let's start by doing a simple endpoint that will get the database version.

This will help us to get acquainted with SQLx.

Creating the endpoint

Can you create the endpoint yourself?

Don't worry about how to retrieve the information from the database, we will do that in a moment. Just focus on creating and endpoint that will return a string. The string can be anything you want and the route should be /version.

If you're not sure about how to do it, expand the section below to see the solution.

#![allow(unused)]
fn main() {
#[get("/version")]
async fn version() -> &'static str {
    "version 0.0.0"
}
}

Setting up the endpoint

You may have noticed that if you run the project and go to the /version route, you will get a 404 error.

curl -i http://localhost:8000/version # HTTP/1.1 404 Not Found

This is because we haven't set up the endpoint yet.

Can you guess how to do it?

#![allow(unused)]
fn main() {
let config = move |cfg: &mut ServiceConfig| {
    // NOTE: this is the modified line
    cfg.service(hello_world).service(version);
};

}

Now, let's try it again:

curl -i http://localhost:8000/version # HTTP/1.1 200 OK version 0.0.0

Did it work? If so, congratulations! You have just created your first endpoint.

Connecting to the database

Now that we have the endpoint, let's connect to the database and retrieve the version.

For that we will need to do a couple of things:

1. Pass the database connection pool to the endpoint.
2. Execute a query in the endpoint and return the result.

Passing the database connection pool to the endpoint

In order to pass the connection pool to the endpoints we're going to leverage the Application State Extractor from Actix Web.

Ok, so just after the line where we initialized our database, let's add the following code:

#![allow(unused)]
fn main() {
let pool = actix_web::web::Data::new(pool);
}

Now, we need to change again the line we changed before when we added a new endpoint and use the .app_data method like this:

#![allow(unused)]
fn main() {
let config = move |cfg: &mut ServiceConfig| {
    cfg.app_data(pool).service(hello_world).service(version);
};
}

Executing a query in the endpoint and returning the result

Now let's change our version endpoint so we can get the connection pool from the state and execute a query. If you have taken a look to the Application State Extractor documentation it should be pretty straightforward.

We have to add a parameter to the version function that will be our access to the database connection pool. We will call it db and it will be of type actix_web::web::Data<sqlx::PgPool>.

#![allow(unused)]
fn main() {
#[get("/version")]
async fn version(db: actix_web::web::Data<sqlx::PgPool>) -> &'static str {
    "version 0.0.0"
}
}

Now, we need to execute a query. For that, we will use the sqlx::query_scalar function.

Let's change the version function to this:

#![allow(unused)]
fn main() {
#[get("/version")]
async fn version(db: actix_web::web::Data<sqlx::PgPool>) -> String {
    let result: Result<String, sqlx::Error> = sqlx::query_scalar("SELECT version()")
        .fetch_one(db.get_ref())
        .await;

    match result {
        Ok(version) => version,
        Err(e) => format!("Error: {:?}", e),
    }
}
}

There are a couple of things going on here, so let's break it down.

First of all, it's worth noticing that we changed the return type of the function to String. This is for two different reasons:

1. We don't want our endpoint to fail. If the query fails, we will have to return an error message as a String.
2. We need that return to be a String because the version of the database will come to us as a String.

On the other hand, we have the sqlx::query_scalar function. This function will execute a query and return a single value. In our case, the version of the database.

As you can see, the query is pretty simple. We're just selecting the version of the database. The most interesting part in there is that we need to use the .get_ref() method to get a reference to the inner sqlx::PgPool from the actix_web::web::Data<sqlx::PgPool>.

Finally, we have the match expression. The sqlx::query_scalar function will return a Result with either the version of the database or an error. With the match expression we're covering both cases and we will make sure that we will always return a String.

Note that even if you introduce an error in the query, the endpoint will not fail or even return a 500 error. This is because we're handling the error in the match expression.

We will see later how to handle errors in a more elegant way.

For now, let's commit our changes:

git add .
git commit -m "add version endpoint"