5 ms·

> If I ever see another ORM-generated DB schema it'll be too soon. I'd argue that most ORMs generate the schema you ask it to. Using an ORM isn't going to crea

> If I ever see another ORM-generated DB schema it'll be too soon. I'd argue that most ORMs generate the schema you ask it to. Using an ORM isn't going to create a worse database layout that you could do by hand. The issue is that some/many developers don't know SQL, nor have the required knowledge of their databases to use an ORM. The ORM requires you to know what lies underneath, it is a fairly leaky abstraction. Understanding that, you can get most ORMs to create nice schemas.

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.
dkarl 3y ago
If you are sophisticated enough to design a good relational model and use an ORM to generate it, what does an ORM give you? Serious question. The answers I've seen typically stress the advantages for developers who are uncomfortable with SQL (who I think are either going to get comfortable or fail, and the ORM will only delay them getting comfortable or enable them to create a more expensive failure) or more easily generate lots of different complex queries (which sounds plausible, but I've never seen it.)
- mrweasel 3y ago
  The ORM, in my mind, is just there to help you translate data into objects, that's it really. You could do that manually and I have worked on projects where we did just that, but it's a lot of repetitive work that brings little value. I have seen and done complex queries using a DSL for an ORM, in my case Django, but now you're just learning a different query language, so you're right that the ORM doesn't bring much to the table. Realistically, those who are uncomfortable with SQL are going to create poor queries with the ORM as well. For quick prototyping and systems with limited amounts of data ORMs can speed up development quite a bit. Technically there's a cost, but computers are fast enough that it doesn't matter on the small scale.
  - dkarl 3y ago
    I guess the way you describe it is the way I like to work, keeping my object model in code close to the relational model to minimize the mental and performance cost of mapping back and forth, and using a SQL library to minimize boilerplate. I don't think of it as using ORM because my favorite tools for working that way don't bill themselves as ORMs, but I'm not sure what I'd use in Python other than SQLAlchemy. Even projects that seem to be stripped down non-ORMs like SQLModel turn out to be built on top of SQLAlchemy.
- skydhash 3y ago
  Before it was about “What if we need to change the database DBMS?” But now it’s more readable code and easy conversion to the language data structures. But it’s the first thing that is looked at when improving performance

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.

dkarl 3y ago

If you are sophisticated enough to design a good relational model and use an ORM to generate it, what does an ORM give you? Serious question. The answers I've seen typically stress the advantages for developers who are uncomfortable with SQL (who I think are either going to get comfortable or fail, and the ORM will only delay them getting comfortable or enable them to create a more expensive failure) or more easily generate lots of different complex queries (which sounds plausible, but I've never seen it.)

mrweasel 3y ago
The ORM, in my mind, is just there to help you translate data into objects, that's it really. You could do that manually and I have worked on projects where we did just that, but it's a lot of repetitive work that brings little value. I have seen and done complex queries using a DSL for an ORM, in my case Django, but now you're just learning a different query language, so you're right that the ORM doesn't bring much to the table. Realistically, those who are uncomfortable with SQL are going to create poor queries with the ORM as well. For quick prototyping and systems with limited amounts of data ORMs can speed up development quite a bit. Technically there's a cost, but computers are fast enough that it doesn't matter on the small scale.
- dkarl 3y ago
  I guess the way you describe it is the way I like to work, keeping my object model in code close to the relational model to minimize the mental and performance cost of mapping back and forth, and using a SQL library to minimize boilerplate. I don't think of it as using ORM because my favorite tools for working that way don't bill themselves as ORMs, but I'm not sure what I'd use in Python other than SQLAlchemy. Even projects that seem to be stripped down non-ORMs like SQLModel turn out to be built on top of SQLAlchemy.
skydhash 3y ago
Before it was about “What if we need to change the database DBMS?” But now it’s more readable code and easy conversion to the language data structures. But it’s the first thing that is looked at when improving performance