There is a middle-ground between writing SQL statement strings in your code, and a full-blown ORM: query builders. At least in my experience with small to medium projects, these have far fewer footguns while keeping the code composable and readable. Here's one for Python: https://github.com/kayak/pypika

In SQLAlchemy, you can choose to use only the query builder without using the ORM part at all. The query builder in SQLAlchemy is very well documented[1] and its use without ORM has the same level of support as its ORM counterpart. SQLAlchemy call their query builder a SQL Expression Language[2], e.g.

    # Boilerplate
    from sqlalchemy import create_engine, MetaData, Table
    engine = create_engine('sqlite:///:memory:', echo=True)
    metadata = MetaData()

    customers = Table('customers', metadata, autoload_with=engine)
    query = customers.select([customers.c.id, customers.c.fname, customers.c.lname, customers.c.phone])
    # => SELECT customers.id, customers.fname, customers.lname, customers.phone FROM customers

    conn = engine.connect()
    conn.execute(query)
    # => [(1, "Foo", "Bar", "12345678"), ...]

ActiveRecord also has Arel which can be use as a standalone. Documentation is a bit more sparse compared to SQLAlchemy Core or its ActiveRecord ORM counterpart, though.

[1]: https://docs.sqlalchemy.org/en/13/index.html

[1]: https://docs.sqlalchemy.org/en/13/core/tutorial.html

This is where I tend to gravitate. Even using ActiveRecord, what people consider the “ultimate” ORM, I still find it gets in my way. None of my colleagues write SQL queries, it’s all completely abstracted away behind ActiveRecord, and we are constantly running into issues where someone forgets an `includes` and we end up with an N+1 error. N+1 errors are so easy to slip into with an ORM, it’s pretty much one of our biggest performance issues.

This seems like the biggest trend the more I use RAILs: it’s great to iterate and prototype, but as soon as you hit scale the maintainability becomes a big issue. I’m not saying other web frameworks are immune or that this is a problem that cannot be solved, it’s just all of these abstractions and cascade of configuration objects to make RAILs do what you want end up getting in the way.

A query builder is the best of both worlds: semantics that resemble raw SQL with some ability for composition.

I'll just say that SQLAlchemy can also be used effectively as just a query builder in places that you need it.

Thanks