7 ms·

ORMs are typically lowest common denominator for compatibility with many database engines. The best thing you can do is learn about the underlying engine (Postg

by ttfkam 3y ago

ORMs are typically lowest common denominator for compatibility with many database engines. The best thing you can do is learn about the underlying engine (Postgres, MySQL, MS SQL Server, SQLite, etc.) and its distinct feature set. Once you know that, you will often find yourself quite a ways away from the lowest common denominator. That may be built in temporal table support in MS SQL Server or MariaDB, so you don't need explicit audit tables in your schema. Or perhaps timestamp ranges with exclusion constraints in Postgres for enforcing valid schedules without hacks in the app layer. Eventually you notice that you're having to define everything twice: once for the DB and again for the ORM definition. This is why I prefer solutions like PostgREST, Postgraphile, and Hasura (for Postgres while other DBs have other similar solutions). Define it once in the DB and just let it bubble up. You may want to expose your storage API through views, but it still ensures optimal data structure at the foundation. DB -> data-generated API service -> client API codegen -> client app such as web or mobile. With a typical ORM, you're building from the middle out. It almost always results in a faulty foundation. Pour and set the foundation first. Always. https://postgrest.org/ https://postgrest.org/ https://postgraphile.org/ https://postgraphile.org/ https://hasura.io/ https://hasura.io/

KronisLV 3y ago
> This is why I prefer solutions like PostgREST, Postgraphile, and Hasura (for Postgres while other DBs have other similar solutions). Define it once in the DB and just let it bubble up. You may want to expose your storage API through views, but it still ensures optimal data structure at the foundation. > DB -> data-generated API service -> client API codegen -> client app such as web or mobile. This seems like a nice approach, albeit mine is even more basic. I pick a DB, create SQL migrations for the schema (sometimes even generate those from an ERD planning tool, with manual edits where needed), apply them to a DB with something like dbate: https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate After that, if I want to use an ORM, I generate entity mappings from the tables/views in a schema-first approach, for example, like https://blog.jetbrains.com/dotnet/2022/01/31/entity-framework-core-inside-rider-ui-way/#existing-database-scaffolding https://blog.jetbrains.com/dotnet/2022/01/31/entity-framewor... or https://www.jetbrains.com/help/idea/persistence-tool-window.html#generate_entities_and_mappings https://www.jetbrains.com/help/idea/persistence-tool-window.... I don't think that sort of codegen is as popular as it should be, but for the most popular frameworks in each language, the schema-first approach is usually doable with no additional software that would need to be deployed to prod.
- deleted 3y ago
  [deleted]
no_wizard 3y ago
That assumes that you should just expose your Postgres tables as your data. As someone who consumes more APIs than they write nowadays, I appreciate when data is scoped to task[0]. Employing window functions and stored procedures that can make queries into data fit for purpose is the ideal - databases are faster at manipulating data than any intermediate language, most of the time. Unfortunately, I don't see them employed enough. Backend developers seem content with throwing the schema over the wall way too often. [0]: As an aside, this is why I personally like GraphQL so much. The middleware lets me cleanup data that backend engineers simply refuse to do, most of the time.
- ttfkam 3y ago
  You may have missed this part of my comment: > You may want to expose your storage API through views Views are a great way to decouple on-disk storage structure from access patterns.
mrweasel 3y ago
> ORMs are typically lowest common denominator for compatibility with many database engines. That really depends on what you mean and the ORM. Typically the larger and more popular ORMs can and do take advantage of database specific features, if you let it. Using the database specific feature will prevent you from using the ORM as an abstraction for replacing you DBMS, but outside open source projects that support SQLite, MariaDB and Postgresql, I've never seen that used in professional settings. One system I've worked with had on paper support for DB2, Sybase and MS SQL. It ran solely on MS SQL, the others had not been tested in years and the developers had pulled in so many MS SQL specific features that it was never going to run on neither DB2 nor Sybase ever again.

by ttfkam 3y ago

KronisLV 3y ago

> This is why I prefer solutions like PostgREST, Postgraphile, and Hasura (for Postgres while other DBs have other similar solutions). Define it once in the DB and just let it bubble up. You may want to expose your storage API through views, but it still ensures optimal data structure at the foundation. > DB -> data-generated API service -> client API codegen -> client app such as web or mobile. This seems like a nice approach, albeit mine is even more basic. I pick a DB, create SQL migrations for the schema (sometimes even generate those from an ERD planning tool, with manual edits where needed), apply them to a DB with something like dbate: https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate After that, if I want to use an ORM, I generate entity mappings from the tables/views in a schema-first approach, for example, like https://blog.jetbrains.com/dotnet/2022/01/31/entity-framework-core-inside-rider-ui-way/#existing-database-scaffolding https://blog.jetbrains.com/dotnet/2022/01/31/entity-framewor... or https://www.jetbrains.com/help/idea/persistence-tool-window.html#generate_entities_and_mappings https://www.jetbrains.com/help/idea/persistence-tool-window.... I don't think that sort of codegen is as popular as it should be, but for the most popular frameworks in each language, the schema-first approach is usually doable with no additional software that would need to be deployed to prod.

deleted 3y ago
[deleted]

no_wizard 3y ago

That assumes that you should just expose your Postgres tables as your data. As someone who consumes more APIs than they write nowadays, I appreciate when data is scoped to task[0]. Employing window functions and stored procedures that can make queries into data fit for purpose is the ideal - databases are faster at manipulating data than any intermediate language, most of the time. Unfortunately, I don't see them employed enough. Backend developers seem content with throwing the schema over the wall way too often. [0]: As an aside, this is why I personally like GraphQL so much. The middleware lets me cleanup data that backend engineers simply refuse to do, most of the time.

ttfkam 3y ago
You may have missed this part of my comment: > You may want to expose your storage API through views Views are a great way to decouple on-disk storage structure from access patterns.

mrweasel 3y ago

> ORMs are typically lowest common denominator for compatibility with many database engines. That really depends on what you mean and the ORM. Typically the larger and more popular ORMs can and do take advantage of database specific features, if you let it. Using the database specific feature will prevent you from using the ORM as an abstraction for replacing you DBMS, but outside open source projects that support SQLite, MariaDB and Postgresql, I've never seen that used in professional settings. One system I've worked with had on paper support for DB2, Sybase and MS SQL. It ran solely on MS SQL, the others had not been tested in years and the developers had pulled in so many MS SQL specific features that it was never going to run on neither DB2 nor Sybase ever again.