There's a lot of benefit in these systems, though there's rough edges and I agree about the basics like PK's and uniqueness.

I've been using Ormin [1] in Nim which works by parsing the SQL tables and uses it to compile time check queries:

    # Multiple joins with pagination
    let page = query:
      select Post(title)
      join Person(name) on author == id
      join Category(title) on category == id
      orderby desc(post.creation)
      limit 5 offset 10

I think that's better since defining SQL should be the source-of-truth for the DB and the code. ORM's always ended up causing trouble in my experience.

Things like indexes, defaults, partitions, etc generally aren't expressible in code without a lot of kludges. Then each DB engine have pretty different rules, syntax, etc for tables.

However having the queries compile time checked, type conversions handled, and the nuances between SQL query syntax handled is rather nice. As you mention it's a much easier subset.

1: https://github.com/Araq/ormin