I'm building a business dashboard with a Python backend and am migrating from MongoDB to PostgreSQL. The tables are already designed, and I'm comfortable writing SQL, but I'm deciding how the application should communicate with the database.
Should I use Psycopg 3 directly with handwritten SQL, or use SQLAlchemy? I'm especially considering SQLAlchemy Core rather than the full ORM. I like the idea of parameterized queries, connection pooling, and a query builder, but I don't want to create a large collection of models and repositories if they aren't necessary. I'm also wondering about performance, complexity, security, and how difficult it would be to change approaches later. What has worked well for others in a similar project?
5 Answers
These aren't quite alternatives at the same level. Psycopg 3 is the PostgreSQL driver, while SQLAlchemy is a toolkit that can use Psycopg underneath it. SQLAlchemy Core is a good middle ground: you get parameterized query construction, connection pooling, and transaction helpers without committing to the ORM's models and sessions. It also leaves the door open to adding the ORM later if the application starts doing lots of record-oriented CRUD.
For a dashboard with aggregations, joins, reports, and time-series queries, Core or handwritten SQL is often a better fit than a full ORM. The ORM is most convenient when you're constantly loading and modifying individual entities. Analytical queries don't always map naturally to object models, so forcing every result into a model can make the code harder to understand.
Don't choose based on assumed performance differences. In most applications, the time spent constructing a query is tiny compared with the database work and network round trip. The bigger decision is readability and control. SQLAlchemy Core can keep queries structured while still looking close to SQL, whereas Psycopg 3 gives you maximum direct control. Both can be used safely and asynchronously, depending on how you configure them.
Whichever query approach you choose, plan migrations separately. SQLAlchemy itself doesn't provide the complete migration workflow; Alembic is the commonly used companion when you want schema changes tracked in code. You can also manage migrations manually if that fits your process, but keeping them versioned and repeatable becomes increasingly useful as the application evolves.
Direct Psycopg 3 is perfectly reasonable if you prefer SQL and keep the database code organized. Put queries behind focused database or repository functions instead of scattering SQL throughout the business logic, and always use parameters rather than string concatenation. That gives you the important SQL-injection protection without requiring an ORM. The tradeoff is that you'll need to handle more mapping, transaction, and connection-management details yourself.
A repository layer can work well either way. Starting with raw SQL there doesn't prevent you from replacing individual queries with SQLAlchemy later if the need comes up.
That distinction helps. I'm mainly deciding between Psycopg 3 with plain SQL and SQLAlchemy Core for the query-building layer.