I've ended up writing a small database layer for nearly every Python project because I don't enjoy using full ORMs. I do want models that map cleanly to database rows, and I already use Pydantic for that, but for anything beyond straightforward CRUD I prefer writing SQL directly so the behavior stays simple and visible.
I'm looking for something modern, async-first, well typed, easy to inspect operationally, and friendly to AI-assisted development, while still providing conveniences such as model-to-row mapping, iteration over result sets, and pagination. Does a library like this already exist, or is building a small custom layer still the practical option?
I've open-sourced my own implementation, called etchdb, after getting tired of recreating the same pieces. I'd also appreciate advice on how to discuss a project like this constructively without the conversation turning into a simple project showcase.
3 Answers
A lightweight approach based on dataclasses or Pydantic models can work well for this. Keeping schema definitions in Python gives you a useful source of documentation, while explicit SQL preserves control and avoids the complexity of a full ORM. The main tradeoff is that mapping query results into models can feel repetitive, particularly when handling lists, pagination, and partial result shapes.
The built-in sqlite3 module is a perfectly reasonable option when SQLite fits the project. Just keep in mind that it’s a database driver and standard-library interface, not a model or mapping layer. You would still need to add the row-to-model conversion and convenience features yourself.
Right, I was wondering whether sqlite3 was being suggested as a database layer or as the actual SQL engine. It handles the engine connection, but not the higher-level model mapping.
SQLAlchemy is worth considering even if you avoid its ORM features. Its Core layer lets you work close to SQL while still providing connection handling, composable queries, typing support, and other useful infrastructure. For analytical work, DuckDB is also excellent, especially when querying dataframes with SQL.
SQLAlchemy is extremely capable, but I’m looking for something smaller and async-first, with stronger typing and less abstraction so the operational behavior is easier to inspect and control.
That model-to-row mapping is really the main feature I want. Beyond that, I mostly need small conveniences such as iterating over multiple rows and straightforward paging. A full ORM adds far more machinery than I need, which is why I built a small reusable implementation instead of repeating it in every project.