5 ms·

The basic problem is that: 1) Relational databases are the best abstraction we've found for storing data. Despite years of attempts (OODB, XML databases, vario

by blagie 3y ago

The basic problem is that: 1) Relational databases are the best abstraction we've found for storing data. Despite years of attempts (OODB, XML databases, various nosql stores, etc.), we have not been able to improve on it. postgresql, by adding native JSON support, became a better mongo than mongo virtually overnight. 2) Most people never learn databases, and most people who do are idiots working on enterprise three-tier architectures, so mostly it's misused. There is an attempt to hide them. 3) ORMs generally make easy stuff easy, and hard stuff painful. What I generally want is: 1) Something translating SQL syntax into my native language, but maintaining SQL full semantics and expressiveness. 2) This should allow me to be database-agnostic. 3) This should prevent things like injection attacks. 4) It should not map onto objects. It should maintain 100% of the capability of SQL. The only differences should be syntactic (e.g. instead of writing WHERE, I might write .where() or similar). 5) This may and ideally should have added functionality, for example, around managing and organizing database migrations. Stored procedures and virtual tables are also very important (but poorly implemented in most databases). These: 1) Allow proper abstraction at the SQL level. 2) Improve performance. What most programmers fail to understand -- since universities don't teach -- is how powerful and elegant the underlying theory of databases is.

brainlessdev 3y ago
Can it be database-agnostic and 100% expressive at the same time? Perhaps expressiveness is different depending on the engine. SQLx comes close to this btw.
- blagie 3y ago
  Yes. The basic data model was invented a half-century ago. Expressiveness of basic SQL is not different depending on the engine. Relational algebra is the same however it's implemented. Some databases have extensions, and those are okay to include. You should be able to either (1) choose to not use those or (2) lock yourself into a subset of databases which support the extension you need. It's good if those extension were namespaced. For example: * If I want to use a postgres JSON type, it should live under postgres.json (or if it cuts across multiple databases, perhaps extensions.json or similar) * Likewise, database built-in functions, except for common ones like min/max/mean, should be available under the namespace of those databases. * It's okay if some of those are abstracted out into a namespace like extensions.math, so I can use extensions.math.sin no matter whether the underlying datastore decided to call it SINE, SIN, SINE_RADIANS, SIN(x/180*PI), and if it doesn't have sine, an exception gets raised. The basic relational data model provides the best expressiveness for representing data and queries created to date, and doesn't differ. It's a good theoretical model, and it works well in practice. There's good reason it's survived this long, despite no lack of competition. The places expressiveness differs are relatively surface things like data types and functions. It's also okay to have compound types, where I am explicit about what the type is in different databases. e.g.: string_type = {mysql: 'TEXT', postgresql: ...
pjc50 3y ago
> It should not map onto objects ? To me, this is the main use of an ORM: don't let it do query building for you (unless it's fairly simple LINQ stuff), but just have something map the rows returned to typesafe objects for you.
- blagie 3y ago
  Mapping rows returned from a query onto typesafe objects is okay, but doesn't require an ORM. It's quite literally just making sure each row: 1) Has the correct native type 2) Can be accessed by name Many normal database APIs do that themselves. I guess there's a little bit more for writes. The point of ORMs is that they generally map objects onto tables. You create a class, and the ORM will create the database table for you based on what you have in the class. For example, for Django, you write: class Blog(models.Model): name = models.CharField(max_length=100) tagline = models.TextField() def __str__(self): return self.name (source: https://docs.djangoproject.com/en/5.0/topics/db/queries/ https://docs.djangoproject.com/en/5.0/topics/db/queries/) And it will make the table for you. If you change the class, it will make the migration for you. That's clean, simple, and easy if you're not doing anything complex. It saves a ton of work. However, this is a Very Bad Idea for complex systems, since exactly as you point out, there shouldn't be a 1:1 mapping between tables and classes. There may very well be a 1:1 or many:1 mapping between queries and classes. More sophisticated ORMs can do a bit of that, but at some point, you run into the equivalent of Greenspun's Tenth Rule, where the ORM turns into a more complex, buggy version of SQL.
mike_hearn 3y ago
Sounds like jOOQ? https://www.jooq.org/ https://www.jooq.org/ Note that although advertised as for Java, it really supports any language that can run on the JVM which is many of them these days.
sureglymop 3y ago
I learned about database theory a few times (and even interned as a DBA before I started uni). But it wasn't until my discrete math class in university that I finally understood, "oh, that's why we call a table a relation, a row a tuple, etc", and from there - started understanding the whole relational model and why it makes sense. I think ORMs however, became a thing more so due to fear of having to write (correct) SQL. It was meant to reduce complexity for developers but of course that's not really how it panned out in reality.
random_kris 3y ago
https://orm.drizzle.team/ https://orm.drizzle.team/ Basically this ?
- iccananea 3y ago
  Drizzle does exactly what I described in the article: it re-implements SQL in the target language (in this case TypeScript).
  - random_kris 3y ago
    But you have raw SQL operator and all drizzle operations does is to build you the SQL query. Well other orms do that as well ofc. But I feel this is a low level as it can get
everforward 3y ago
https://GitHub.com/masterminds/squirrel https://GitHub.com/masterminds/squirrel sounds like what you want, in Go at least. They bill it as a SQL query builder instead of an ORM. It lets you build SQL queries using code and just returns the built SQL query as a string, the list of variables to pass into the query, and an error if your query was invalid. Actually executing the query and doing stuff with the result is explicitly outside their scope. I typically use it with sqlx with a struct per query, just to avoid writing tedious row iterators. Makes it super easy to eg define a function that adds paging to a query and returns the paged query. You can even write a function that takes an HTTP request, reads out standard paging parameters and adds them to a query.
PH95VuimJjqBqy 3y ago
> Most people never learn databases, and most people who do are idiots working on enterprise three-tier architectures Tell me how you really feel :) My biggest problem with ORM's is that it causes people to sprinkle the ORM code throughout their codebase but they'd never do that with SQL, they'd want to try and sequester it to a system whose responsibility is the retrieving and updating of data. It puts people into a poor mindset around their data.
- feoren 3y ago
  That's like saying: my problem with Functions is that people sprinkle them throughout their codebase, instead of having one single place where all their Functions are defined. For most applications, "Retrieving and updating data" is the entire point, or at least a deeply fundamental part of how they work. It shouldn't scare you that such a core function -- the entire reason your application exists -- is "sprinkled around" your codebase. It should scare you if it isn't! I think the reason it scares you is because you imagine the performance nightmare of every random function possibly hitting the database N times. That's using an ORM terribly. Good ORMs let you plan database actions, and compose those plans. Guys, you really, genuinely, don't have to choose between (a) writing SQL strings in code, and (b) using an ORM badly. You can truly do better!
  - PH95VuimJjqBqy 3y ago
    putting repeated data requests behind a function is what I'm suggesting... sprinkling your ORM code all throughout your codebase is the equivalent of NOT using functions but instead copy/pasting the implementation everywhere it's needed. Most ORM's recognize this is a problem, which is why they often try to bake in some sort of solution for re-use, only it's always done badly. They'll generally attach it to the model and it will turn into some sort of unobvious behavior or they'll attempt to be able to attach SQL fragments by name. What it ends up doing is effectively spreading an SQL query over several files. Often by convention so there's no real way to know if part of the query is going to be in file X without just checking or knowing ahead of time.
    - 3y ago
snorremd 3y ago
My best experience integrating with a Postgres database was using Clojure with the Honey SQL library [1]. Essentially Honey SQL operated solely as a query builder library allowing me to express queries as regular Clojure EDN maps and vectors (comparable to objects and arrays in JavaScript), or nested function calls returning the same kind of data. In essence SQL queries were expressed in a Clojure native data format which finally could be transformed into SQL syntax. The huge win was that I could use the Clojure REPL to run my SQL-generating functions and see that the SQL actually matched what I was aiming for. Caching the query-building was as simple as memoizing the query building functions as queries are parameterised in any case, so can be reused. There is something really nice about having the full power of SQL at your fingertips. The only drawback compared to ORMs of course is that it outputs the resulting query result in a flat structure. So any aggregation you'd need to handle in code or use something like Postgres JSON aggregation. [1] https://github.com/seancorfield/honeysql https://github.com/seancorfield/honeysql
feoren 3y ago
> What I generally want is: > 1) Something translating SQL syntax into my native language, but maintaining SQL full semantics and expressiveness. You have just described a good ORM used well. > 2) This should allow me to be database-agnostic. Meh, you sacrifice some powerful features if you demand total database agnosticism, and how often do you actually switch databases? Being database-agnostic is a side benefit of writing your logic simply against a good abstraction. The biggest benefit is composability. You can write (and optimize) one query against abstractions, and re-use that query in lots of different ways. > 3) This should prevent things like injection attacks. As all ORMs automatically already do. > 4) It should not map onto objects. It should maintain 100% of the capability of SQL. If it's not mapping onto objects, what is it doing? The problem is that your mental model of what "objects" means includes awful design decisions like deep inheritance trees, mutability, and lots of reference cycles (this.parent.child[0] == this, etc.) If your object model is already clean and following relational principles, then mapping into that model is exactly what you want. It should not strive to maintain the capability of some bastardized pseudo-language which is despised by the progenitors of relational logic. It should strive to support the relational model. That's not the same thing. > 5) This may and ideally should have added functionality, for example, around managing and organizing database migrations. No, because your code versions and your database schemas advance together. To reliably run a database migration in code, you'd need to run it with the code version that exactly matches each step in the schema. That means for each step in the migration, you'd need to checkout the correct commit in git, compile, and run the ORM code in that version. Either that, or you're maintaining a code model that is compatible with every historical database schema, which is way worse. But what ORMs should be able to do (and I haven't found one that does this well) is generate SQL migration scripts for you, which you store. Those would be frozen relative to the database schema version, so all the above problems go away. > What most programmers fail to understand -- since universities don't teach -- is how powerful and elegant the underlying theory of databases is. The underlying relational model is powerful and elegant. SQL itself is not. SQL is disliked by the founders of the relational model. Good ORMs let you incorporate the relational model into your code.
- blagie 3y ago
  > You have just described a good ORM used well. No. I did not. ORM is an "object–relational mapping." It maps data relations onto objects. https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapp... > how often do you actually switch databases? Very often. Virtually all of the systems I write have at least two back-ends to maintain that flexibility. At the very least, I'd like my systems to run well locally for development but also to scale. The easiest way to do that is to e.g. support both SQLite and Postgres, but there are other ways which make sense. In proprietary settings, I like having a BATNA. The architectural flexibility means I get better prices on hosted services and aren't liable to turning into a cash cow through lock-in. That's savings even if I'm not switching. > If your object model is already clean and following relational principles, then mapping into that model is exactly what you want. This is where your thinking broke. A good object model is NOT a relational model, and a good relational model is NOT an object model. Learn the theory of both. They're both good theories, but they're different. An ORM makes sense if you want to use an object model, but want the backing store to be an RDBMS. > But what ORMs should be able to do (and I haven't found one that does this well) is generate SQL migration scripts for you, which you store. Those would be frozen relative to the database schema version, so all the above problems go away. I believe that's one instantiation of what I wrote: "This may and ideally should have added functionality, for example, around managing and organizing database migrations." It's actually exactly what I was thinking. Some ORMs do this not badly, actually. Don't let the perfect be the enemy of the good. A simple system which does 90% of the work of generating a migration (with manual verification and tweaks) is often better than a complex one which tries to do 100% of the work. > The underlying relational model is powerful and elegant. SQL itself is not. SQL is disliked by the founders of the relational model. Citation required. In either case, ORMs aren't translating just syntax, but also semantics. That's where the problem lies. If you're not doing that, you're not an ORM. > Good ORMs let you incorporate the relational model into your code. You're confused about what an ORM is. ORMs essentially map an RDBMS onto an object model (which an OODBMS does natively). The two models are fundamentally different. It's a translation layer. Any good database library will let me "incorporate the relational model into my code." That's not an ORM.

by blagie 3y ago

brainlessdev 3y ago

Can it be database-agnostic and 100% expressive at the same time? Perhaps expressiveness is different depending on the engine. SQLx comes close to this btw.

blagie 3y ago
Yes. The basic data model was invented a half-century ago. Expressiveness of basic SQL is not different depending on the engine. Relational algebra is the same however it's implemented. Some databases have extensions, and those are okay to include. You should be able to either (1) choose to not use those or (2) lock yourself into a subset of databases which support the extension you need. It's good if those extension were namespaced. For example: * If I want to use a postgres JSON type, it should live under postgres.json (or if it cuts across multiple databases, perhaps extensions.json or similar) * Likewise, database built-in functions, except for common ones like min/max/mean, should be available under the namespace of those databases. * It's okay if some of those are abstracted out into a namespace like extensions.math, so I can use extensions.math.sin no matter whether the underlying datastore decided to call it SINE, SIN, SINE_RADIANS, SIN(x/180*PI), and if it doesn't have sine, an exception gets raised. The basic relational data model provides the best expressiveness for representing data and queries created to date, and doesn't differ. It's a good theoretical model, and it works well in practice. There's good reason it's survived this long, despite no lack of competition. The places expressiveness differs are relatively surface things like data types and functions. It's also okay to have compound types, where I am explicit about what the type is in different databases. e.g.: string_type = {mysql: 'TEXT', postgresql: ...

pjc50 3y ago

> It should not map onto objects ? To me, this is the main use of an ORM: don't let it do query building for you (unless it's fairly simple LINQ stuff), but just have something map the rows returned to typesafe objects for you.

blagie 3y ago
Mapping rows returned from a query onto typesafe objects is okay, but doesn't require an ORM. It's quite literally just making sure each row: 1) Has the correct native type 2) Can be accessed by name Many normal database APIs do that themselves. I guess there's a little bit more for writes. The point of ORMs is that they generally map objects onto tables. You create a class, and the ORM will create the database table for you based on what you have in the class. For example, for Django, you write: class Blog(models.Model): name = models.CharField(max_length=100) tagline = models.TextField() def __str__(self): return self.name (source: https://docs.djangoproject.com/en/5.0/topics/db/queries/ https://docs.djangoproject.com/en/5.0/topics/db/queries/) And it will make the table for you. If you change the class, it will make the migration for you. That's clean, simple, and easy if you're not doing anything complex. It saves a ton of work. However, this is a Very Bad Idea for complex systems, since exactly as you point out, there shouldn't be a 1:1 mapping between tables and classes. There may very well be a 1:1 or many:1 mapping between queries and classes. More sophisticated ORMs can do a bit of that, but at some point, you run into the equivalent of Greenspun's Tenth Rule, where the ORM turns into a more complex, buggy version of SQL.

mike_hearn 3y ago

Sounds like jOOQ? https://www.jooq.org/ https://www.jooq.org/ Note that although advertised as for Java, it really supports any language that can run on the JVM which is many of them these days.

sureglymop 3y ago

I learned about database theory a few times (and even interned as a DBA before I started uni). But it wasn't until my discrete math class in university that I finally understood, "oh, that's why we call a table a relation, a row a tuple, etc", and from there - started understanding the whole relational model and why it makes sense. I think ORMs however, became a thing more so due to fear of having to write (correct) SQL. It was meant to reduce complexity for developers but of course that's not really how it panned out in reality.

random_kris 3y ago

https://orm.drizzle.team/ https://orm.drizzle.team/ Basically this ?

iccananea 3y ago
Drizzle does exactly what I described in the article: it re-implements SQL in the target language (in this case TypeScript).
- random_kris 3y ago
  But you have raw SQL operator and all drizzle operations does is to build you the SQL query. Well other orms do that as well ofc. But I feel this is a low level as it can get

everforward 3y ago

https://GitHub.com/masterminds/squirrel https://GitHub.com/masterminds/squirrel sounds like what you want, in Go at least. They bill it as a SQL query builder instead of an ORM. It lets you build SQL queries using code and just returns the built SQL query as a string, the list of variables to pass into the query, and an error if your query was invalid. Actually executing the query and doing stuff with the result is explicitly outside their scope. I typically use it with sqlx with a struct per query, just to avoid writing tedious row iterators. Makes it super easy to eg define a function that adds paging to a query and returns the paged query. You can even write a function that takes an HTTP request, reads out standard paging parameters and adds them to a query.

PH95VuimJjqBqy 3y ago

> Most people never learn databases, and most people who do are idiots working on enterprise three-tier architectures Tell me how you really feel :) My biggest problem with ORM's is that it causes people to sprinkle the ORM code throughout their codebase but they'd never do that with SQL, they'd want to try and sequester it to a system whose responsibility is the retrieving and updating of data. It puts people into a poor mindset around their data.

feoren 3y ago
That's like saying: my problem with Functions is that people sprinkle them throughout their codebase, instead of having one single place where all their Functions are defined. For most applications, "Retrieving and updating data" is the entire point, or at least a deeply fundamental part of how they work. It shouldn't scare you that such a core function -- the entire reason your application exists -- is "sprinkled around" your codebase. It should scare you if it isn't! I think the reason it scares you is because you imagine the performance nightmare of every random function possibly hitting the database N times. That's using an ORM terribly. Good ORMs let you plan database actions, and compose those plans. Guys, you really, genuinely, don't have to choose between (a) writing SQL strings in code, and (b) using an ORM badly. You can truly do better!
- PH95VuimJjqBqy 3y ago
  putting repeated data requests behind a function is what I'm suggesting... sprinkling your ORM code all throughout your codebase is the equivalent of NOT using functions but instead copy/pasting the implementation everywhere it's needed. Most ORM's recognize this is a problem, which is why they often try to bake in some sort of solution for re-use, only it's always done badly. They'll generally attach it to the model and it will turn into some sort of unobvious behavior or they'll attempt to be able to attach SQL fragments by name. What it ends up doing is effectively spreading an SQL query over several files. Often by convention so there's no real way to know if part of the query is going to be in file X without just checking or knowing ahead of time.
  - 3y ago

snorremd 3y ago

My best experience integrating with a Postgres database was using Clojure with the Honey SQL library [1]. Essentially Honey SQL operated solely as a query builder library allowing me to express queries as regular Clojure EDN maps and vectors (comparable to objects and arrays in JavaScript), or nested function calls returning the same kind of data. In essence SQL queries were expressed in a Clojure native data format which finally could be transformed into SQL syntax. The huge win was that I could use the Clojure REPL to run my SQL-generating functions and see that the SQL actually matched what I was aiming for. Caching the query-building was as simple as memoizing the query building functions as queries are parameterised in any case, so can be reused. There is something really nice about having the full power of SQL at your fingertips. The only drawback compared to ORMs of course is that it outputs the resulting query result in a flat structure. So any aggregation you'd need to handle in code or use something like Postgres JSON aggregation. [1] https://github.com/seancorfield/honeysql https://github.com/seancorfield/honeysql

feoren 3y ago

> What I generally want is: > 1) Something translating SQL syntax into my native language, but maintaining SQL full semantics and expressiveness. You have just described a good ORM used well. > 2) This should allow me to be database-agnostic. Meh, you sacrifice some powerful features if you demand total database agnosticism, and how often do you actually switch databases? Being database-agnostic is a side benefit of writing your logic simply against a good abstraction. The biggest benefit is composability. You can write (and optimize) one query against abstractions, and re-use that query in lots of different ways. > 3) This should prevent things like injection attacks. As all ORMs automatically already do. > 4) It should not map onto objects. It should maintain 100% of the capability of SQL. If it's not mapping onto objects, what is it doing? The problem is that your mental model of what "objects" means includes awful design decisions like deep inheritance trees, mutability, and lots of reference cycles (this.parent.child[0] == this, etc.) If your object model is already clean and following relational principles, then mapping into that model is exactly what you want. It should not strive to maintain the capability of some bastardized pseudo-language which is despised by the progenitors of relational logic. It should strive to support the relational model. That's not the same thing. > 5) This may and ideally should have added functionality, for example, around managing and organizing database migrations. No, because your code versions and your database schemas advance together. To reliably run a database migration in code, you'd need to run it with the code version that exactly matches each step in the schema. That means for each step in the migration, you'd need to checkout the correct commit in git, compile, and run the ORM code in that version. Either that, or you're maintaining a code model that is compatible with every historical database schema, which is way worse. But what ORMs should be able to do (and I haven't found one that does this well) is generate SQL migration scripts for you, which you store. Those would be frozen relative to the database schema version, so all the above problems go away. > What most programmers fail to understand -- since universities don't teach -- is how powerful and elegant the underlying theory of databases is. The underlying relational model is powerful and elegant. SQL itself is not. SQL is disliked by the founders of the relational model. Good ORMs let you incorporate the relational model into your code.

blagie 3y ago
> You have just described a good ORM used well. No. I did not. ORM is an "object–relational mapping." It maps data relations onto objects. https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapping https://en.wikipedia.org/wiki/Object%E2%80%93relational_mapp... > how often do you actually switch databases? Very often. Virtually all of the systems I write have at least two back-ends to maintain that flexibility. At the very least, I'd like my systems to run well locally for development but also to scale. The easiest way to do that is to e.g. support both SQLite and Postgres, but there are other ways which make sense. In proprietary settings, I like having a BATNA. The architectural flexibility means I get better prices on hosted services and aren't liable to turning into a cash cow through lock-in. That's savings even if I'm not switching. > If your object model is already clean and following relational principles, then mapping into that model is exactly what you want. This is where your thinking broke. A good object model is NOT a relational model, and a good relational model is NOT an object model. Learn the theory of both. They're both good theories, but they're different. An ORM makes sense if you want to use an object model, but want the backing store to be an RDBMS. > But what ORMs should be able to do (and I haven't found one that does this well) is generate SQL migration scripts for you, which you store. Those would be frozen relative to the database schema version, so all the above problems go away. I believe that's one instantiation of what I wrote: "This may and ideally should have added functionality, for example, around managing and organizing database migrations." It's actually exactly what I was thinking. Some ORMs do this not badly, actually. Don't let the perfect be the enemy of the good. A simple system which does 90% of the work of generating a migration (with manual verification and tweaks) is often better than a complex one which tries to do 100% of the work. > The underlying relational model is powerful and elegant. SQL itself is not. SQL is disliked by the founders of the relational model. Citation required. In either case, ORMs aren't translating just syntax, but also semantics. That's where the problem lies. If you're not doing that, you're not an ORM. > Good ORMs let you incorporate the relational model into your code. You're confused about what an ORM is. ORMs essentially map an RDBMS onto an object model (which an OODBMS does natively). The two models are fundamentally different. It's a translation layer. Any good database library will let me "incorporate the relational model into my code." That's not an ORM.