Python: Just Write SQL

This page summarizes the projects mentioned and recommended in the original post on

Our great sponsors
  • InfluxDB - Collect and Analyze Billions of Data Points in Real Time
  • Onboard AI - Learn any GitHub repo in 59 seconds
  • SaaSHub - Software Alternatives and Reviews
  • migrate

    Database migrations. CLI and Golang library.

    First of all, thank you for SQLAlchemy! If I ever had to make a final choice in how I would interact with a database for a very large project that involves a considerable dev team, I would always bet on SQLAlchemy. Not that I would necessarily like all aspects of it, but when it comes to Python and SQL - “Nobody ever got fired for picking SQLAlchemy.”.

    With that out of the way, despite ORMs doing much more than "just writing SQL", it is exactly on that point that I flinch: Most devs should be exposed to SQL. And if your project allows you to build around simple enough abstractions so that you aren't reinventing the wheel, you should definitely be writing SQL. Especially if you don't know SQL yet - which is the growing case of new devs coming into the job market.

    You can achieve a lot with SQlAlchemy Core, a tool that I absolutely recommend, but my post is just a simple alternative to get developers to think about their approach. If that results in some devs reconsidering using "full fat" SQLAlchemy and to try SQLAlchemy Core, that's a win for me!

    Your gist tries to highlight the difficulty of doing certain things without an ORM. Migrations (as just 1 example) doesn't need to be hard, simple tools like flyway, or migrate ( achieve a similar result (while also keeping you on the path of writing SQL!). Deep and complex relationships between objects also don't need to be hard - typically people approach this subject with a requirement to be very flexible in the way they want to build queries and objects, but that to me in a sign that maybe they should reconsider their business logic AND reconsider that, just maybe, their project doesn't require all that flexibility, it is fairly straightforward to extend objects and introduce some more complex representations as and when it is needed - will all of this make me write code faster? Absolutely not. That is why you have spent so much time perfecting SQLAlchemy, but then again, I am not advocating for devs to go and replace their usage of ORMs, just presenting an alternative that may or may not fit their needs for a new project + give devs the chance to learn something that the ORM might have taken away.

  • sqlc

    Generate type-safe code from SQL

    Seems like there's 3 groups of opinions on ORMs:

    Firstly (1); "I want to use the ORM for everything (table definitions, indexes, and queries)"

    Then second (2), on the other extreme: "I don't want an ORM, I want to do everything myself, all the SQL and reading the data into objects".

    Then thirdly (3) the middle ground: "I want the ORM to do the boring reading/writing data between the database and the code's objects".

    The problem with ORMs is that they are often designed to do number 1, and are used to do number 3. This means there's often 'magic' in the ORM, when really all someone wanted to do was generate the code to read/write data from the database. In my experience this pushes engineers to adopt number 2.

    I'm a big fan of projects like sqlc[1] which will take SQL that you write, and generate the code for reading/writing that data/objects into and out of the database. It gives you number 3 without any of the magic from number 1.


  • InfluxDB

    Collect and Analyze Billions of Data Points in Real Time. Manage all types of time series data in a single, purpose-built database. Run at any scale in any environment in the cloud, on-premises, or at the edge.

  • pugsql

    A HugSQL-inspired database library for Python

    I've had good experience using PugSQL to write SQL in Python [1]. You write the SQL inside SQL files so you can actually benefit from syntax highlighting, static analysis, etc. At the different of writing strings of SQL inside Python files.


  • mini_sql

    a minimal, fast, safe sql executor

    For those looking for a rubyish approach to this see:

  • sqlz

    Simple struct/slice/chan mapper for SQL rows. (by semrekkers)

    Shameless plug, with channel support:

  • Sequel

    Sequel: The Database Toolkit for Ruby

    Thea answer to your prayers already exists:

    By far the best database toolkit (ORM, query builder, migration engine) I have seen for any programming language.

  • sql-template-tag

    ES2015 tagged template string for preparing SQL statements, works with `pg`, `mysql`, and `sqlite`

    You can avoid this entirely with JavaScript's tagged template literals. Here is an example library:

  • Onboard AI

    Learn any GitHub repo in 59 seconds. Onboard AI learns any GitHub repo in minutes and lets you chat with it to locate functionality, understand different parts, and generate new code. Use it for free at

  • sqlx

    general purpose extensions to golang's database/sql

    We've always used which is just the standard package + mapping to/from structs.

  • powderkeg

    Live-coding the cluster!

    As a data engineer, the pattern the OP shares is very familiar. I find it much preferable to use of ORMs for wide variety of reasons. However, I view implementing with SQL as an antiquated problem rather than a pragmatic feature. The evolution of this pattern would be to integrate database querying into languages more directly and eliminate SQL entirely. While this could be achieved in Python, I find that a language like Clojure, via functional programming (FP) primitives and transducers, is a natural candidate, particularly for JVM implemented databases. Rather than encapsulating SQL via query building or ORM based APIs, an FP core could be integrated into database engines to allow, via transducers, complex native forms to be executed directly across database clusters. Apache Spark is an analog of this. In particular the Clojure project, powderkeg ( demonstrates the potential of utilizing transducers in a database cluster context.

  • py-mysql-type-plugin

    Mypy plugin to type sql

    It's fine advice - if you can type check your queries. My colleague wrote a mypy plugin for parsing SQL statements and doing type checking against a database schema file, which helps to identify typos and type errors early:

  • squiller

    Generate boilerplate from annotated SQL queries

    One challenge working with SQL from statically typed languages (including Python + Mypy) is that you have to convert the query inputs/outputs to/from types and it's a lot of boilerplate. I started an experiment to generate this from annotated queries. [1] Python support is still incomplete, but I'm using it somewhat successfully for using SQLite from Rust so far.


  • typed-sql

    similar project that generates types for queries into an intermediate representation that can be consumed by, say TypeScript, to get static Types:

  • papers-we-love

    Papers from the computer science community to read and discuss.

    I'm in a 4th camp: we should be writing our applications against a relational data model and _not_ marshaling query results into and out of Objects at all.

    Elaborations on this approach:



  • dbt-core

    dbt enables data analysts and engineers to transform their data using the same practices that software engineers use to build applications.

    I really dislike SQL, but recognize its importance for many organizations. I also understand that SQL is definitely testable, particularly if managed by environments such as DBT ( Those who arrived here with preference to python will note that dbt is largely implemented in python, adds Jinja macros and iterative forms to SQL, and adds code testing capabilities.

  • Django

    The Web framework for perfectionists with deadlines.

    Can you explain a bit more about the Django ORM being very thin and easy to read? It does seem like the Django ORM is thin (from an architecture perspective), but it doesn't seem to be small, it seems to be pretty big. Maybe I'm not understanding it though, so here's what I see:

    The "ORM" part of Django seems to be everything in `django.db.models.Model`, which seems to require you to declare your Models as subclasses of the aforementioned class. Looking into that code though, it seems like the implementation supporting all this is around ~20,000 lines of Python:

    That doesn't strike me as a super lightweight. For comparison, all of Flask (a Python WSGI web app framework, but mostly a 10+ year old project to compare to, and excluding tests) is ~4,000 lines of Python.

    Is there a small subsection of the code in `django/db/models/` that is all that's necessary to use the ORM part? Or maybe I'm missing something about the "core" of the ORM?

  • sqlc-gen-python

    If you want to try out something cool, check out

    It's written in Go and used to convert sql migrations and queries into a typesafe code that you use access your database.

    It currently has a plugin for Python that's in Beta, but what essentially does something similar to what this post is saying.

    You write your migrations, and queries and a config file and it does the rest.

  • yesql

    A Clojure library for using SQL.

    There's a whole family of libraries like that. Yesql is the first I became aware of. The repo has an (incomplete) list of ports to other languages:

  • SQLAlchemy

    The Database Toolkit for Python

    That above pattern is one I've seen people do even recently, using the "select().c" attribute which from very early versions of SQLAlchemy is defined as "the columns from a subquery of the SELECT" ; this usage began raising deprecation warnings in 1.4 and is fully removed in 2.0 as it was a remnant of a much earlier version of SQLAlchemy. it will do exactly as you say, "make a subquery for each filter condition".

    the moment you see SQLAlchemy doing something you see that seems "asinine", send an example to and I will clarify what's going on, correct the usage so that the query you have is what you expect, and quite often we will add new warnings or documentation when we see people doing things we didn't anticipate.

  • SaaSHub

    SaaSHub - Software Alternatives and Reviews. SaaSHub helps you find the best software and product alternatives

NOTE: The number of mentions on this list indicates mentions on common posts plus user suggested alternatives. Hence, a higher number means a more popular project.

Suggest a related project

Related posts