Learn how to work with databases using Rust.
shuttle init
.
sqlx
crate to an initialised Rust program. We will also want to add the shuttle-shared-db
crate which allows us to use the macro that provisions the database instance to us.
sqlx migrate add <name>
in our project to add a migrations folder that will contain an empty SQL file, with the naming convention <timestamp>_<name>.sql
. We can then create our tables using the power of raw SQL:
sqlx migrate run --database-url <database-url>
. We can also just run the migrate macro programmatically after connecting to the database:
CREATE TABLE IF NOT EXISTS
, you will probably want to make sure you either comment this statement out or just delete it once the migrations have run.
To get started with querying using SQLx, we’ll need to connect to our database. Normally you’d have to set up your connection pool manually - however, with Shuttle you can just set the macro up and you’ll receive the connection pool immediately:
&pool
in your queries as the database connection you want to execute your query against. The query uses the referenced version of the variable to be able to keep the connection alive; if it doesn’t, then the variable will get consumed and you’d lose your connection pool.
At a basic level, we can use sqlx::query
to query something and then chain the .bind()
method to insert our own variables into the query:
sqlx::FromRow
derive macro on a struct with the data type we want from the database. Let’s have a look at what that might look like below:
macros
feature flag to SQLx in our Cargo.toml file, though if following this guide from the start you’ll probably have it already. If not, you can run this command:
query!()
and query_as!()
macros, which like the above allows us to execute general queries as well as queries that can directly return structs. The main difference however is that these macros do compile-time syntactic and semantic verification of the SQL, meaning you’ll get errors at compile time if the queries are invalid. Let’s have a look at how we can use them below: