How to Add Postgres to an Actix Web App
Connecting an Actix Web 4 app to PostgreSQL with Encore, SQLx, Diesel or deadpool-postgres, with migrations, transactions and a database that runs locally.
Actix Web gives you fast handlers and leaves the database to you. Adding Postgres means choosing a driver, a pool shared across worker threads, a migration tool, a local Postgres for each developer and a managed instance with credentials in production.
This guide adds Postgres to an Actix Web 4 todo API with Encore, SQLx, Diesel and deadpool-postgres. Each version creates and reads todos and marks one done in a transaction.
What you need to decide:
- Raw SQL or a query builder.
- How the pool is created and shared across Actix workers.
- How migrations run locally and on deploy.
- Where Postgres runs in each environment.
Postgres options for Actix Web at a glance
Encore: Postgres for your existing Actix Web app
Encore combines databases declared in your Actix Web code with the Postgres behind them, starting it locally under encore run, applying migrations and provisioning RDS on AWS or Cloud SQL on GCP in your own account on deploy. It is an infrastructure SDK, the encore-dev crate, added to an existing app, so App, extractors and handlers stay as they are.
The database is a static that points at a migrations directory:
use encore::storage::postgres::{Database, Row};
use serde::{Deserialize, Serialize};
// Encore provisions this database and applies ./migrations before the service starts.
static DB: Database = Database::new("todos").migrations("./migrations");
#[derive(Deserialize)]
struct NewTodo {
title: String,
}
#[derive(Serialize)]
struct Todo {
id: i64,
title: String,
done: bool,
}
impl From<Row> for Todo {
fn from(row: Row) -> Self {
Todo { id: row.get("id"), title: row.get("title"), done: row.get("done") }
}
}
The migration creates the todos table and an activity log:
CREATE TABLE todos (
id BIGSERIAL PRIMARY KEY,
title TEXT NOT NULL,
done BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE activity (
id BIGSERIAL PRIMARY KEY,
todo_id BIGINT NOT NULL REFERENCES todos (id),
action TEXT NOT NULL,
at TIMESTAMPTZ NOT NULL DEFAULT now()
);
Handlers are ordinary Actix Web handlers using query_one and query_opt:
use actix_web::{get, post, web, HttpResponse, Responder};
#[post("/todos")]
async fn create_todo(body: web::Json<NewTodo>) -> impl Responder {
match DB
.query_one(
"INSERT INTO todos (title) VALUES ($1) RETURNING id, title, done",
&[&body.title],
)
.await
{
Ok(row) => HttpResponse::Created().json(Todo::from(row)),
Err(_) => HttpResponse::InternalServerError().finish(),
}
}
#[get("/todos/{id}")]
async fn get_todo(path: web::Path<i64>) -> impl Responder {
let id = path.into_inner();
match DB
.query_opt("SELECT id, title, done FROM todos WHERE id = $1", &[&id])
.await
{
Ok(Some(row)) => HttpResponse::Ok().json(Todo::from(row)),
Ok(None) => HttpResponse::NotFound().finish(),
Err(_) => HttpResponse::InternalServerError().finish(),
}
}
Completing a todo updates the row and writes an activity entry in one transaction. The AND NOT done condition makes a repeated request a no-op:
// Marks a todo done and records it in one transaction.
async fn complete(id: i64) -> Result<bool, encore::Error> {
let tx = DB.transaction().await?;
let updated = tx
.execute("UPDATE todos SET done = true WHERE id = $1 AND NOT done", &[&id])
.await?;
if updated == 1 {
tx.execute(
"INSERT INTO activity (todo_id, action) VALUES ($1, 'completed')",
&[&id],
)
.await?;
}
tx.commit().await?;
Ok(updated == 1)
}
#[post("/todos/{id}/done")]
async fn complete_todo(path: web::Path<i64>) -> impl Responder {
match complete(path.into_inner()).await {
Ok(true) => HttpResponse::Ok().body("completed\n"),
Ok(false) => HttpResponse::Ok().body("already done or not found\n"),
Err(_) => HttpResponse::InternalServerError().finish(),
}
}
main calls encore::initialize() and binds to the address Encore assigns:
use actix_web::{App, HttpServer};
#[actix_web::main]
async fn main() -> std::io::Result<()> {
encore::initialize();
let addr = std::env::var("ENCORE_LISTEN_ADDR").unwrap_or("0.0.0.0:8080".into());
HttpServer::new(|| {
App::new()
.service(create_todo)
.service(get_todo)
.service(complete_todo)
})
.bind(addr)?
.run()
.await
}
encore run starts Postgres locally and applies the migration before the service starts:
Each request's trace includes the SQL statements and their timing. On deploy, Encore provisions RDS on AWS or Cloud SQL on GCP in your own account, applies the migrations and passes credentials to the service.
When an agent adds a table, it writes a migration file and runs it locally under encore run, and the same file is applied in the cloud on the next deploy.
Good to know
The transaction rolls back if complete returns early with an error, so the activity row never exists without the update. PostgreSQL's default isolation level is Read Committed; the isolation docs explain when to use a stricter level.
SQLx
SQLx is the most common choice for raw SQL in async Rust. Create the pool once and share it with web::Data:
use actix_web::{get, web, App, HttpResponse, HttpServer, Responder};
use sqlx::postgres::PgPoolOptions;
use sqlx::PgPool;
#[get("/todos/{id}")]
async fn get_todo(pool: web::Data<PgPool>, path: web::Path<i64>) -> impl Responder {
let row: Option<(i64, String, bool)> =
sqlx::query_as("SELECT id, title, done FROM todos WHERE id = $1")
.bind(path.into_inner())
.fetch_optional(pool.get_ref())
.await
.unwrap_or(None);
match row {
Some((id, title, done)) => HttpResponse::Ok().json((id, title, done)),
None => HttpResponse::NotFound().finish(),
}
}
#[actix_web::main]
async fn main() -> std::io::Result<()> {
let url = std::env::var("DATABASE_URL").expect("DATABASE_URL");
let pool = PgPoolOptions::new().max_connections(10).connect(&url).await.unwrap();
sqlx::migrate!().run(&pool).await.unwrap();
HttpServer::new(move || App::new().app_data(web::Data::new(pool.clone())).service(get_todo))
.bind("0.0.0.0:8080")?
.run()
.await
}
Good to know
The query! macros check SQL against a database at build time, which needs DATABASE_URL or an offline cache in CI. You provide Postgres locally and a managed instance with its URL in production.
Diesel
Diesel generates a schema module and builds queries with a typed DSL. In async handlers, use diesel-async or run blocking calls in web::block:
use diesel::prelude::*;
use diesel_async::RunQueryDsl;
#[derive(Queryable, Selectable, serde::Serialize)]
#[diesel(table_name = crate::schema::todos)]
struct Todo {
id: i64,
title: String,
done: bool,
}
async fn find(conn: &mut diesel_async::AsyncPgConnection, todo_id: i64) -> QueryResult<Option<Todo>> {
use crate::schema::todos::dsl::*;
todos.find(todo_id).select(Todo::as_select()).first(conn).await.optional()
}
Good to know
diesel migration run applies migrations and regenerates schema.rs. Compile errors catch wrong column types early, at the cost of longer builds and a DSL to learn.
deadpool-postgres
deadpool-postgres pools tokio-postgres connections with little else on top:
use deadpool_postgres::{Config, Pool, Runtime};
use tokio_postgres::NoTls;
fn make_pool() -> Pool {
let mut cfg = Config::new();
cfg.url = Some(std::env::var("DATABASE_URL").expect("DATABASE_URL"));
cfg.create_pool(Some(Runtime::Tokio1), NoTls).unwrap()
}
async fn count(pool: &Pool) -> i64 {
let client = pool.get().await.unwrap();
client.query_one("SELECT count(*) FROM todos", &[]).await.unwrap().get(0)
}
Good to know
It has no migration tool, so pair it with refinery or run SQL files yourself. Managed Postgres usually requires TLS, which means swapping NoTls for a TLS connector.
How to choose
- Encore if you want Postgres running locally with migrations applied, and RDS or Cloud SQL in your own account from the same code.
- SQLx for raw SQL with compile-time checks against an existing database.
- Diesel if you prefer a typed query DSL and generated schema.
- deadpool-postgres for a thin pool over tokio-postgres.
Rust support in Encore is in beta, and you can sign up for access to try it with your Actix Web app.
Related guides
Frequently asked questions
How do I connect Actix Web to PostgreSQL?
Create one connection pool at startup and use it from handlers. With Encore, declare static DB: Database = Database::new("todos").migrations("./migrations") and call DB.query_one, DB.query_opt or DB.query in handlers. encore run starts Postgres locally and applies the migrations, and on deploy the database becomes RDS on AWS or Cloud SQL on GCP.
How do I run a transaction in Actix Web with Postgres?
Begin a transaction, run the statements on it and commit. With Encore, let tx = DB.transaction().await?, call tx.execute for each statement and tx.commit().await?. If the function returns early with an error, the transaction is rolled back.
Should I use SQLx or Diesel with Actix Web?
SQLx runs raw SQL and can check queries against a live database at compile time. Diesel builds queries with a typed DSL from a generated schema and needs diesel-async or a blocking thread pool in async handlers.
Where should the database pool live in Actix Web?
Create it once before HttpServer::new and share it through web::Data, because the factory closure runs once per worker thread. A static database handle, as Encore uses, avoids passing it around at all.
Deploy to your cloud on AWS and Google Cloud
without managing infrastructure
Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.
