Just Write SQL

I think that in 2025, there's no good arguments left for using abstraction layers on top of SQL in your application's programming language. The programming model is easy to reason about, fast to iterate with, & more powerful. SQL has also never been easier to write!

Programming Model

(introduce the following points somehow)

Examples will assume we're using Postgres.

Driver

The ideal SQL driver for your app language is mostly:

• an async interface for running queries & returning their results
• an automatic, extensible mapping from database type to app language type

In Rust, I prefer the sqlx crate over others, because it aligns well with the points above. We'll use it for examples.

Iteration

At Dora, most of our API routes have a SQL query or two at their core, and then the service transforms the returned rows into the response shape we want.

Getting the right results with this setup is mostly about getting the query right, in my experience. Luckily, you can iterate queries very quickly using your preferred SQL interface, such as psql or PgAdmin. The iteration loop of run query -> observe any error -> alter query -> is very fast, with no compile step needed.

This speed of iteration extends to optimizing queries as well!

Optimization

Optimization is straightforward in most queries. When you see it being slow, try running EXPLAIN to look at the query plan. This shows you what the database thinks is the best way to find your data, and a cost estimate for how much time each part could take.

• observe where the query is doing Seq Scan on any sizable dataset, and probably create a new index to change that to Index Scan.
• look for opportunities to batch reads & writes
• move a LIMIT from an outer query to a subquery