4 ms·

> Rule 5. Data dominates. If you've chosen the right data structures and organized things well, the algorithms will almost always be self-evident. Data structur

by ttfkam 3y ago

> Rule 5. Data dominates. If you've chosen the right data structures and organized things well, the algorithms will almost always be self-evident. Data structures, not algorithms, are central to programming. This goes double for databases. Folks who use a DB as a dumb bit bucket or a simple 1:1 reflection of their object definitions are often surprised when the DB takes it personal and dooms performance. If I ever see another ORM-generated DB schema it'll be too soon.

mrweasel 3y ago
> 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.
    - 3y ago
- 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
nabla9 3y ago
Let's add Conway's law to that: "Any organization that designs a system will produce a design whose structure is a copy of the organization's communication structure." (Melvin E. Conway( "The structure of any system designed by an organization is isomorphic to the structure of the organization" (Yourdon and Constantine) Coming back to your point. How do you ensure that data structures are organized well and stay that way as design changes? You separate data and code in organizational level. You keep the design of database schema, use cases, and mapping between them is separate from the implementation of the rest. This group also writes all integrity checks and etc. Data and code organizations are separate. IF you don't do it this way, it's hard to separate code and data because the structure of the organization does not separate them.
- ttfkam 3y ago
  > Coming back to your point. How do you ensure that data structures are organized well and stay that way as design changes? It's a hard problem if not THE hard problem. Using an ORM or not has no bearing on this. Conway's Law extends to the lowest levels. If an organization changes substantially, no UI, data structure, or database schema will be left unscathed. Solve the problems you know about. Tomorrow always delivers problems you couldn't (and shouldn't) anticipate. You'll drive yourself and your coworkers crazy by trying to cover all possibilities. It's a disease called Flexibility Syndrome.
  - nabla9 3y ago
    Your viewpoint is from someone who writes code for others. My viewpoint is hiring others to write code. My business is tomorrow and keeping contractors in check. Planning and maintaining data and their schemas in-house and contracting out writing code has been incredibly successful so far.
    - ttfkam 3y ago
      For what it's worth, most of my time is spent inside the DB, not app code. I learned long ago that once bad data gets in, it's far harder to get out and trust the system again. Best defense against bad data is a well-designed, strict schema. In other words, proper data structures.
pjmlp 3y ago
Stored procedures for the win.

by ttfkam 3y ago

mrweasel 3y ago

> 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.
  - 3y ago
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

nabla9 3y ago

Let's add Conway's law to that: "Any organization that designs a system will produce a design whose structure is a copy of the organization's communication structure." (Melvin E. Conway( "The structure of any system designed by an organization is isomorphic to the structure of the organization" (Yourdon and Constantine) Coming back to your point. How do you ensure that data structures are organized well and stay that way as design changes? You separate data and code in organizational level. You keep the design of database schema, use cases, and mapping between them is separate from the implementation of the rest. This group also writes all integrity checks and etc. Data and code organizations are separate. IF you don't do it this way, it's hard to separate code and data because the structure of the organization does not separate them.

ttfkam 3y ago
> Coming back to your point. How do you ensure that data structures are organized well and stay that way as design changes? It's a hard problem if not THE hard problem. Using an ORM or not has no bearing on this. Conway's Law extends to the lowest levels. If an organization changes substantially, no UI, data structure, or database schema will be left unscathed. Solve the problems you know about. Tomorrow always delivers problems you couldn't (and shouldn't) anticipate. You'll drive yourself and your coworkers crazy by trying to cover all possibilities. It's a disease called Flexibility Syndrome.
- nabla9 3y ago
  Your viewpoint is from someone who writes code for others. My viewpoint is hiring others to write code. My business is tomorrow and keeping contractors in check. Planning and maintaining data and their schemas in-house and contracting out writing code has been incredibly successful so far.
  - ttfkam 3y ago
    For what it's worth, most of my time is spent inside the DB, not app code. I learned long ago that once bad data gets in, it's far harder to get out and trust the system again. Best defense against bad data is a well-designed, strict schema. In other words, proper data structures.

pjmlp 3y ago

Stored procedures for the win.