Rust with PostgreSQL and SQLx: Pools, Queries, Migrations and Transactions

1. Add SQLx#

1. Add SQLx cargo add sqlx --features runtime-tokio-rustls,postgres,uuid,chrono,migrate cargo add tokio --features full cargo add dotenvy 2. Store the connection string outside code DATABASE_URL=postgres://app_user:password@localhost:5432/app_db Never commit production passwords. Use environment variables or a secrets manager. 3. Create a connection pool use sqlx::postgres::PgPoolOptions; let pool = PgPoolOptions::new() .max_connections(10) .connect(&std::env::var("DATABASE_URL")?) .await?; A pool reuses database connections instead of opening a new TCP/database session per request. 4. Define a model #[derive(sqlx::FromRow, Debug)] struct User { id: i64, name: String, } 5. Query rows let users = sqlx::query_as::<_, User>( "SELECT id, name FROM users ORDER BY id" ) .fetch_all(&pool) .await?; 6. Bind parameters let user = sqlx::query_as::<_, User>( "SELECT id, name FROM users WHERE id = $1" ) .bind(user_id) .fetch_optional(&pool) .await?; Bind values rather than interpolating untrusted input into SQL. 7. Insert data let row = sqlx::query_as::<_, User>( "INSERT INTO users (name) VALUES ($1) RETURNING id, name" ) .bind(&name) .fetch_one(&pool) .await?; 8. Migrations sqlx migrate add create_users A migration might contain: CREATE TABLE users ( id BIGSERIAL PRIMARY KEY, name TEXT NOT NULL, created_at TIMESTAMPTZ NOT NULL DEFAULT now() ); Run migrations: sqlx migrate run 9. Transactions let mut tx = pool.begin().await?; sqlx::query("UPDATE accounts SET balance = balance - $1 WHERE id = $2") .bind(amount) .bind(from_id) .execute(&mut *tx) .await?; sqlx::query("UPDATE accounts SET balance = balance + $1 WHERE id = $2") .bind(amount) .bind(to_id) .execute(&mut *tx) .await?; tx.commit().await?; Use transactions when a set of changes must succeed or fail together. 10. Pool sizing Bigger is not automatically better. Size pools according to application concurrency, database capacity and the number of service instances. A database can be overwhelmed by many application replicas each holding oversized pools. 11. Timeouts Add acquisition and statement timeouts appropriate to your application. A request should not wait forever for a database connection or slow query. 12. Repository pattern struct UserRepository { pool: sqlx::PgPool, } impl UserRepository { async fn find(&self, id: i64) -> Result, sqlx::Error> { sqlx::query_as("SELECT id, name FROM users WHERE id = $1") .bind(id) .fetch_optional(&self.pool) .await } } Repositories help keep SQL and persistence decisions out of HTTP route handlers. 13. Database error handling Map low-level errors into application errors. A unique-key violation might become a 409 Conflict, a missing row might become 404, and unexpected connection errors should become a generic server response while the detailed cause stays in logs. 14. Avoid N+1 query patterns If one request loads 100 rows and then issues an additional query for each row, latency and database load can explode. Use joins, batched queries or carefully designed repository methods.

15. Indexes#

15. Indexes Indexes should reflect actual query patterns. Do not index every column. Inspect slow queries and PostgreSQL query plans before guessing. 16. Production security • Use a dedicated least-privilege database role. • Require TLS where traffic crosses untrusted networks. • Do not log passwords or sensitive query parameters. • Keep database ports private whenever possible. • Back up and test restore procedures. 17. Mini lab Extend the Chapter 23 Notes API with PostgreSQL. Create a migration, a notes table, a SQLx pool and repository methods for create/list/get. Add one transaction and an integration test using a dedicated test database. Key takeaways Use a bounded connection pool, parameterized SQL, versioned migrations, short transactions and clear repository boundaries. Database performance is a system concern: the Rust service and PostgreSQL must be tuned together.