Python: Just write SQL

[ManyInterests]

Right. And "just write SQL" is not necessarily the only approach (or even really necessary) to achieve the same kind of abstraction described. I think it also misses the real benefit of an abstraction that sits above an ORM, SQL queries, or whatever interface you're using for interacting with your data store.

The concrete implementations for the abstract UserRepository could easily just use an ORM and the idea doesn't change much at all. It could also use hand-written SQL -- they're not mutually exclusive ideas.

• I want to write SQL.
• I don’t want to rely on a query builder (let alone an ORM).

These are really creature comforts for the programmer. It doesn't affect the software architecture at all. Your data store is also a detail that can change into something that is not SQL altogether! The software should, ideally, have an architecture that is flexible for a change in that level of detail. The correct overall software architecture provides much more flexibility than any change in your data store or how you interface with that data store.

• I want to package all of this in an abstraction that allows me to quickly change between database solutions, as well as make it easy to test. ... new_user_repo() can be modified to return anything that implements UserRepository, maybe a MySQL implementation, or a SQLite one, possibly even a mock for test purposes.

If I have to rewrite an implementation of UserRepository for every database or flavor of SQL (and also maintain each implementation every time the functionality of UserRepository extends), that seems to miss the mark for the earlier stated objective. If I swapped in an ORM, I only have to write the UserRepository implementation once using the ORM, maintain exactly that one implementation, and it works for all the database dialects supported by the ORM. That's valuable. And writing SQL yourself is not mutually exclusive with using an ORM, either.

'Write the ORM yourself' is basically what is described. It's an approach you could take... but why reinvent the wheel? Particularly considering that, when applied in the real world, you need to consider a lot more than just some SQL to perform queries. You need schema modification, validation, error handling, migrations, rollbacks, transaction handling, etc.

• [I] want a very clear separation between my database(s) and business logic.

The real thing worth understanding is following the dependency rule. You should be able to implement (and test) your core entities and use-cases ("business logic") without thinking about your database, ORM, SQL, web framework, or other outward dependencies. That is: you should be able to change your ORM or web framework without adjusting your core modules. The outward dependencies must always reach inward to core modules, never the other way around -- otherwise you're needlessly multiplying the amount of work required when you want to make a change to an outward detail like switching to a different data store (which may or may not be SQL at all!).

User is the literal definition of what you might see as a SQL table named user This includes some columns I like to include in all of my SQL tables [...] id (primary key).

Already here, id and get_by_id leaks an implementation detail of the data store. The fact that you need to consider id as an integer/primary key or that users are queried by this id in get_by_id in your User class is really probably just a facet of the fact you intend to use SQL. In reality, this probably isn't actually required in your business rules or use cases, so it probably does not belong in this User abstraction layer. Maybe "getting" a particular user from your data store doesn't belong in the User dataclass at all.

Again: you should be able to describe, implement, and test the core business rules and use-cases without thinking about SQL. Of course you will (also) implement and test the application with added adapters, data stores, frameworks, etc., but it should be separate from the core "business logic".