> ## Documentation Index > Fetch the complete documentation index at: https://docs.shuttle.dev/llms.txt > Use this file to discover all available pages before exploring further. # Working with Databases in Rust > Learn how to work with databases using Rust. In this guide we'll be looking at working with PostgreSQL using Rust. By the end of this guide you'll have more of an idea of how to use both and when each one would be better for your use case. It will be assumed you already have a project initialised - if not, you can always initialise a new project with `shuttle init`. ## SQL Relational databases are the classical way to store data in the backend of web applications when it comes to storing records and persisted data. Shuttle currently offers free provisioned SQL instances for your applications and currently provides it through an SQLx connection pool. It should be noted that although we're using Postgres for this guide, the same concepts works equally well with both MySQL and MariaDB. To get started with SQLx (and using our new database), we'll want to add the `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. ```bash theme={null} cargo add sqlx shuttle-shared-db --features sqlx/macros,shuttle-shared-db/postgres,shuttle-shared-db/sqlx cargo install sqlx-cli ``` We can then run `sqlx migrate add ` in our project to add a migrations folder that will contain an empty SQL file, with the naming convention `_.sql`. We can then create our tables using the power of raw SQL: ```sql theme={null} CREATE TABLE IF NOT EXISTS users ( id SERIAL PRIMARY KEY NOT NULL, name VARCHAR NOT NULL, email VARCHAR NOT NULL, password VARCHAR NOT NULL, created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP ); ``` When we run our migrations, this folder will be the default that will be used - when we're ready to run our migrations, we can run `sqlx migrate run --database-url `. We can also just run the migrate macro programmatically after connecting to the database: ```rust theme={null} sqlx::migrate!().run(&pool).await.expect("Migrations failed :("); ``` Be aware however that this will run every time the program starts, so if you only want your migrations to run once or you're not using `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: ```rust theme={null} #[shuttle_runtime::main] async fn axum( #[shuttle_shared_db::Postgres] pool: PgPool ) -> shuttle_axum::ShuttleAxum { ... some code } ``` Now when you need to make a query, you can use `&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: ```rust theme={null} // fetch one row let query = sqlx::query("SELECT * FROM users WHERE id = $1") .bind(id) .fetch_one(&pool) .await; let number: i32 = query.get("id"); ``` While this is pretty cool, what we probably want to do if we know what we want to extract from the database is to use SQLx's `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: ```rust theme={null} #[derive(sqlx::FromRow)] struct Message { id: i32, message: String } let query = sqlx::query::_as<_, Message>("SELECT id, message FROM messages") .fetch_all(&pool) .await; ``` This is much more convenient for us for gathering information since we already know what we want the outputted data type from the query to be, so when we carry out the query it'll now automatically be converted into a vector of structs. One of SQLx's main strengths in addition to the above is being able to create data for compile-time queries. To do this, we'll want to enable the macros flag for SQLx by adding the `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: ```rust theme={null} cargo add sqlx --features macros ``` This will allow us to be able to use the `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: ```rust theme={null} // as you can see this is pretty much the same let messages = sqlx::query!("SELECT * FROM messages") .fetch_all(&pool) .await; #[derive(sqlx::FromRow)] struct Message { id: i32, message: String } // as you can see we input the struct type in-macro, then the query & bound variables let messages_to_struct = sqlx::query_as!(Message, "SELECT id, message FROM messages WHERE id = (1)", 1i32) .fetch_one(&pool) .await; ``` Once we've built all of our queries that we want to use, all we need to do is run the following command which will create query metadata at the current working directory: ```bash theme={null} cargo sqlx prepare --database-url // use the command below if you're using a workspace cargo sqlx prepare --workspace ``` Now we can build and run our program as usual, and if there's no errors then it should work! If you're using SQLx in conjunction with a backend web development framework like Axum, bear in mind that you will want to use Serde to serialize your data to JSON - this can be enabled by installing Serde with the "derive" feature: ```bash theme={null} cargo add serde --features derive ``` Then you need to add the Serialize derive macro to your structs as required, and then when you return the JSON-serialized data it'll automatically be accepted as a HTTP-compatible response.