Don't use your ORM entities for everything – embrace the SQL

My simple rule for databases is that if you are not actively querying on it, it doesn't need to be a separate table or column. Use the YAGNI rule here and you'll be better off. The classic example here is persons that have addresses and phone numbers. Most applications have no requirements to query on most of that: street name, postal code, phone number, etc. Less tables and columns mean simpler joins (or better, no joins at all). Any structured data that you don't query, just store it in json form in a blob and simplify your schema. Anything that actually requires complex querying, consider using a proper search engine. Or extract it to a dedicated field with some index on it that actually helps you querying effectively. The golden rule here is that if that query is connected to user input you probably need some notion of ranking, fuzzy searches, etc.

Either way, everything gets easier with a simple schema. Faster query and insert performance, easier to reason about when doing transactions, easier to maintain, etc.

> Will you actually need to change between fundamentally different database technologies?

Yes, use sqlite locally for development and run PG in prod. Unit tests can now use the db and finish in milliseconds. You get unit tests that have the power of integration tests and don't have to ever stub out your db. I use Redislite for the same thing.

I'm of the opinion that SQLite is the musl of the SQL world. By deciding that you'll support it first-class you'll avoid the sharp edges of database specific behavior and extension hell and write better more maintainable code.

"Sorry we can't actually put that logic the database, SQLite doesn't support spooky action at a distance."

As someone who always prefers native SQL over ORM queries, I'll add a couple of counterpoints.

Most IDEs provide intellisense/validation of ORM entities, vs treating SQL like a raw string.

ORM entities also make refactoring and impact analysis slightly easier.

Despite those benefits, I generally find ORMs a pain for anything besides the most basic queries.

Kysely is excellent! Another library that complements it is pgTyped[0]. It compiles .sql files into functions with strongly typed parameters and return values

[0] https://pgtyped.dev/

This has to be one of the long lasting topics on HN. I remember commenting on "vietnam of cs" blogpost like two decades ago.