Show HN: Write universally accessible SQL, not library-specific ORM wrapper APIs(github.com)
github.com
Show HN: Write universally accessible SQL, not library-specific ORM wrapper APIs
https://github.com/craigmichaelmartin/pure-orm
129 コメント
I feel like one of the few JS devs who is happy to just write SQL. Inline or as a stored procedure I just don’t see how learning an ORM and all it’s issues and bugs is harder than learning SQL.
Yay another non sql fearing js dev. I feel as though we're rare
Reporting for duty. I never feared SQL after https://www.sqlteaching.com/
Granted, there's much more to learn than that, but so is the case with ORMs. I've had more issues writing good ORM queries than SQL queries.
Granted, there's much more to learn than that, but so is the case with ORMs. I've had more issues writing good ORM queries than SQL queries.
Great (and fun!) resource. https://pgexercises.com/ for more in depth on aggregates + recursion.
I thought we were rare while reading through the comments on this article about ORMs, but then I realised that the SQL community is huge and anyone fine with SQL would more likely be posting over there.
https://blog.logrocket.com/why-you-should-avoid-orms-with-ex...
https://blog.logrocket.com/why-you-should-avoid-orms-with-ex...
At the risk of sounding arrogant,
why do people fear SQL? If you can program in any modern language, you can pick up SQL.
There is no good JS ORM, so it's for the best.
There's no good ORM.
Entity Framework (in .NET) is good. Looks like this package takes a lot of the feel of Entity Framework transferred over to node.js
EF is slow, IMX. But I think it mainly has to do with the fact that it's an ORM, and since the application un-marshals to objects and marshals back to the data store more or less one object at a time, you lose set-based operations. If you're interacting with a lot of discrete, unrelated objects, that gets really inefficient. You can easily end up with the dreaded "row by agonizing row" pattern:
UPDATE Student SET Grade = '04' WHERE StudentId = '12345'
UPDATE Student SET Grade = '04' WHERE StudentId = '12346'
UPDATE Student SET Grade = '04' WHERE StudentId = '12347'
UPDATE Student SET Grade = '04' WHERE StudentId = '12348'
.
.
.
.
UPDATE Student SET Grade = '04' WHERE StudentId = '12349'
UPDATE Student SET Grade = '04' WHERE StudentId IN ('12345','12346','12347','12348', ...., '12349')Oh it can be slow, not all of the entity framework adaptors support bulk operations. I ended up extending it a bit on my object model to include bulk operations which do pretty much what you have there (I stuck to delete and insert though). However, I have used a pile of ORM products since the early C++ / smalltalk days back in the 1990s and it's easily the best ORM I've ever used.
Oh interesting! I'll have to check it out!
ActiveRrcord is pretty good. So is Sequel.
prisma is amazing, takes 5 minutes to setup, and lets you make just about any query with minimal typing, unlike making raw sql.
Best practice, in the case of JS, is to use MongoDB, which has a native JS query API, and then use microservice patterns to provide joins, two-phase commit, and (eventual) consistency.
Why would your database choice have anything to do with your language choice?
Because your programmers don't know SQL and don't want to. In an ideal world of perfectly spherical programmers, choosing JavaScript would be completely orthogonal to database choice, but in reality one common symptom of JavaScriptitis is considering SQL an old relic that's too much of a pain in the ass to learn. Thankfully, MongoDB's query language is JavaScript and its result sets are JSON objects.
To be fair, this sort of feeling toward SQL is not found uniquely among JavaScript programmers; one major reason why we have ORM hell is because a previous generation of programmers did not want to leave the warm cocoon of treating data as objects in Java.
To be fair, this sort of feeling toward SQL is not found uniquely among JavaScript programmers; one major reason why we have ORM hell is because a previous generation of programmers did not want to leave the warm cocoon of treating data as objects in Java.
Assuming you're not being facetious (which I'm not sure of)...
You have described why people make such decisions, but you have not yet made an argument for whether it's wise or not. Can you imagine why it would be an unwise decision?
Even if you have engineers who stubbornly refuse to learn SQL (a problem with your engineering management if there ever was one), there's always options like Hasura [1].
[1] https://hasura.io/
You have described why people make such decisions, but you have not yet made an argument for whether it's wise or not. Can you imagine why it would be an unwise decision?
Even if you have engineers who stubbornly refuse to learn SQL (a problem with your engineering management if there ever was one), there's always options like Hasura [1].
[1] https://hasura.io/
I know youre joking but why do I feel so hurt?
You should spend more time here. I'm fairly certain that, if HN is representative, nobody likes ORMs. (Also: advertisement works on nobody)
Personally, I love solving fancy puzzles with SQL but honestly prefer Rails' ActiveRecord ORM because it drastically cuts down on typing.
Personally, I love solving fancy puzzles with SQL but honestly prefer Rails' ActiveRecord ORM because it drastically cuts down on typing.
I don't really like ORM's (or even java) but if I'd have to write code to model a typical user/product/subscription system I'd rather use hibernate with some annotations than dealing with all the add/update sql and projections between domains.
That's too simple and efficient and too easy to debug. Need more layers.
Developer here frim the Ruby world (although I wrote extensively Elixir and now Go).
I can't even begin to describe the amount of damage ORMs repeatedly do (ActiveRecord being particularly magical). Our team writes sql rather than ORM and we have way less problems, not to mention we get better at SQL, so our team is also the one you refer to when you need to optimize things.
We do use query builders when we need to, usually the scenario is: a form with incremental filtering (multiple conditions), but not a full fledged ORM.
We do use query builders when we need to, usually the scenario is: a form with incremental filtering (multiple conditions), but not a full fledged ORM.
Opt for the stored procedure whenever you can. Your database will be able to yield much better performance - even if you have average SQL skills. If you have a SQL query giving you performance fits then you can bring in someone with greater SQL expertise to optimize it for you and your code isn't impacted.
In the readme you say:
>This contrasts against traditional ("stateful") ORMs which use query builders (rather than raw SQL) to return database-aware (rather than pure) objects.
>The name pureORM reflects both that it is pure ORM (there is no query builder dimension) as well as the purity of the mapped Objects.
I don't know what you mean by "pure" or "purity" here, and i think an explantion would help.
Also, in the code:
>This contrasts against traditional ("stateful") ORMs which use query builders (rather than raw SQL) to return database-aware (rather than pure) objects.
>The name pureORM reflects both that it is pure ORM (there is no query builder dimension) as well as the purity of the mapped Objects.
I don't know what you mean by "pure" or "purity" here, and i think an explantion would help.
Also, in the code:
db.one(query)
"single" might be a better name. The Kotlin standard library uses that name for a variant of the "filter" method (on collections) that returns a single matching element or throws an exception if there are more or fewer.
I thought it was first or firstOrNull. Or is there a new Kotlin collection method I need to learn about?
Aside, Kotlin collection extensions might be my favorite thing I learned in the last two years.
Aside, Kotlin collection extensions might be my favorite thing I learned in the last two years.
They are similar but slightly different. "single" checks that there is exactly one matching element:
>>> listOf(1, 2, 3).single { it > 2 }
res0: kotlin.Int = 3
>>> listOf(1, 2, 3).single { it > 1 }
java.lang.IllegalArgumentException: Collection contains more than one matching element.
>>> listOf(1, 2, 3).single { it > 100 }
java.util.NoSuchElementException: Collection contains no element matching the predicate.
>>> listOf(1, 2, 3).firstOrNull { it > 2 }
res0: kotlin.Int? = 3
>>> listOf(1, 2, 3).firstOrNull { it > 1 }
res1: kotlin.Int? = 2
>>> listOf(1, 2, 3).firstOrNull { it > 100 }
res2: kotlin.Int? = null
I'm not familiar with it, but at first glance, "one()" stands in contrast to "multiple()". Either running a single query or running multiple queries in a request.
Ah, that makes more sense now.
It is used for the amount of objects returned (not the amount of raw rows returned, to be clear). Many orms have `findOne` or `findAll` methods that return an object or an array respectively. Just helps to make things more ergonomic and usually appends a LIMIT clause.
Thanks for the correction. I guess that validates the OP's point after all.
SQLAlchemy author here.
I would just note that these two statements are contradictory:
> The name pureORM reflects both that it is pure ORM (there is no query builder dimension)
and then
> Specifying all the columns is tedious; lets use BaseBo.getSQLSelectClause() to get them for free.
the "getSQLSelectClause()" is absolutely a query builder function. Building out the columns to select from is in fact where things get very complicated if you are for example using SQL aliases, selecting the entity from subqueries, etc. I would predict this method would have to be very complicated to truly be useful in such real world scenarios, so you'd end up with a "pure" ORM that still has a significant query builder, just one that has its own particular brand of awkwardness in that the textual SQL you write has to match up with the assumptions of getSQLSelectClause().
I would just note that these two statements are contradictory:
> The name pureORM reflects both that it is pure ORM (there is no query builder dimension)
and then
> Specifying all the columns is tedious; lets use BaseBo.getSQLSelectClause() to get them for free.
the "getSQLSelectClause()" is absolutely a query builder function. Building out the columns to select from is in fact where things get very complicated if you are for example using SQL aliases, selecting the entity from subqueries, etc. I would predict this method would have to be very complicated to truly be useful in such real world scenarios, so you'd end up with a "pure" ORM that still has a significant query builder, just one that has its own particular brand of awkwardness in that the textual SQL you write has to match up with the assumptions of getSQLSelectClause().
I was handed maintenance of a database with horrid schemas, like table_1 joined table_2 on "substr(table_1.foo, 4, 3) = upper(table_2.bar) and table_1.type = table_2.first_name", and crazy stuff like that. One table had an index on "upper(substr(purchase_id,1,1))". Another was indexed on "substring(table_3.xmlvars,'<KEY>(.*)</KEY>')". (I just looked up the old codebase and copied and pasted. These aren't exaggerations.) I had zero chance of fixing the schema because a giant legacy internal application was built with it, and I had to make my (new) application adapt to its schema instead.
I spent a couple of months exhaustively documenting these relationships in the form of SQLAlchemy models, so that I could eventually write table_1.join(table_2) and have it do the right thing without having to remember (and implement!) that complexity everywhere those two tables touched. It was sanity saving, and for that I’m hugely grateful. Thank you.
If you’re starting with a brand new DB schema, shiny and pure, perhaps you can get away with hardcoding a bunch of SQL. I’ve rarely had that luxury, so query builders are one of my favorite things.
I spent a couple of months exhaustively documenting these relationships in the form of SQLAlchemy models, so that I could eventually write table_1.join(table_2) and have it do the right thing without having to remember (and implement!) that complexity everywhere those two tables touched. It was sanity saving, and for that I’m hugely grateful. Thank you.
If you’re starting with a brand new DB schema, shiny and pure, perhaps you can get away with hardcoding a bunch of SQL. I’ve rarely had that luxury, so query builders are one of my favorite things.
Seems to me if there's a bunch of apps dependent on that legacy data, that cleanup could have been done more usefully and elegantly in the database with some views anyone could use.
Or, if for some reason views couldn't be added (lack of access) a set of generic CTE includes?
Or, if for some reason views couldn't be added (lack of access) a set of generic CTE includes?
Sigh, if only. OK, so the rest of the story was that the original app was written in Visual FoxPro. I was hired to build a web frontend for it. Well, turns out 1) there is, or at least was, no native VFP client for Unix, and 2) the Windows client was single threaded per host, so you couldn't even farm the connectivity out to a Windows process. After struggling with this for a while -- a cluster of Win XP hosts running a single-threaded VFP client and serving results to a Linux webserver via XMLRPC, as was the custom at the time -- I finally said "screw this, let's brute force it." That turned into https://github.com/kstrauser/pgdbf, which is an app to convert a VFP table into a PostgreSQL table. We had it running on a cron job, which worked fine because the website was read-only and it was the VFP app that was actually writing to the tables. It was alright to have up to a few hours of latency between the VFP view of the data and the PostgreSQL view of it.
When the company later committed to rewriting the VFP app in a sane language, they wrote it to run directly against PostgreSQL. That was quite a few years after I'd started there, though.
When the company later committed to rewriting the VFP app in a sane language, they wrote it to run directly against PostgreSQL. That was quite a few years after I'd started there, though.
You should receive a medal!
I feel so dirty after just reading this, that I have to take a shower! ;)
I feel so dirty after just reading this, that I have to take a shower! ;)
Sometimes ya just do what ya’ve gotta do. :)
At the time, I was so heads-down with the day to day challenges that I didn’t really think about it. It wasn’t until later that the enormity of it really sunk in.
At the time, I was so heads-down with the day to day challenges that I didn’t really think about it. It wasn’t until later that the enormity of it really sunk in.
If you have to interact with a system that's so poorly designed that you need an adapter to make sense of it, then sure, use an adapter.
The thing is, the more you use an ORM, the less and less thought you put into designing your database to have a sensible interface of its own, so of course your database isn't going to have a sensible interface. Probably not as horrific as what you're describing, but as much as an ORM can solve that kind of problem, it can also perpetuate it. I'm almost terrified to ask how your DB schema got that fucked, but I'm going to guess it's because it's very tightly coupled to that giant legacy internal application and no one thought to take it seriously as its own service component with a consciously designed interface.
The thing is, the more you use an ORM, the less and less thought you put into designing your database to have a sensible interface of its own, so of course your database isn't going to have a sensible interface. Probably not as horrific as what you're describing, but as much as an ORM can solve that kind of problem, it can also perpetuate it. I'm almost terrified to ask how your DB schema got that fucked, but I'm going to guess it's because it's very tightly coupled to that giant legacy internal application and no one thought to take it seriously as its own service component with a consciously designed interface.
Yeah, when you're doing the 1% of super complicated stuff, definitely don't use this (and opt for the underlying database driver directly). But when you're doing the 99%, it's great! Unlike with stateful ORMs, there is no temptation to do the 1% using some obscure orm methods and never heard of option params. Just write the sql.
It's always the same with the anti orm crowd, they either avoid any wrapper and prevent having a standard api to build uppon and inspect, or they create a light wrapper that ends up being a poor badly tested and documented implementation of 1% of SQLA.
It's not really an either or thing. There are plenty of reasons to avoid an ORM. When I talk to python devs who don't like ORMs I recommend SQLAlchemy Core. It's all the most important parts without the ORM. In my experience most people against ORMs are not dead set on writing SQL by hand, they just don't like OOP. Of course there are some DBAs disguised as programmers running around, but they're more of an exception than the rule :-).
Took the words right out of my mouth. As someone who has been adamant about using orm’s for the last 15 years, it shocks me to no end that an engineer (or group of engineers) think they can roll a DAO better than a battle hardened orm like SQLAlchemy, Hibernate, Entity Framework. It’s hubris in the least and straight arrogance at best.
What might actually qualify as hubris, is thinking you've discovered the best solution for everyone else's future problems.
What do you do when what you want/need is not representable in the ORM?
For example: https://stackoverflow.com/questions/65596920/use-django-subq...
Composite types & triggers, for some other examples, are only (at least they are!) available via third-party plugin/'installed app's too. You cannot defer a Django unique 'constraint', because it happens to implement it as a unique index instead.
That's just the few that come to mind.
For example: https://stackoverflow.com/questions/65596920/use-django-subq...
Composite types & triggers, for some other examples, are only (at least they are!) available via third-party plugin/'installed app's too. You cannot defer a Django unique 'constraint', because it happens to implement it as a unique index instead.
That's just the few that come to mind.
For the SO question, why do you need to represent that in the ORM? It seems like you've got the SQL, and most ORMs have an escape hatch to just run SQL. Does Django not?
I responded to the same point in a comment there at the time iirc. No reason other than to be friendly/idiomatic to a project and colleagues that's largely Django.
We've used more and more SQL though, for things Django doesn't support, such as (materialised) views. (You can use models with managed=False, but not have it manage their DDL & schema migrations.)
I still try to use Django where possible and especially if it's auxiliary to a Django-managed table though.
We've used more and more SQL though, for things Django doesn't support, such as (materialised) views. (You can use models with managed=False, but not have it manage their DDL & schema migrations.)
I still try to use Django where possible and especially if it's auxiliary to a Django-managed table though.
Bingo. This. If you’ve broken your relational model then go ahead and use raw sql with your orm. They all support a way of executing raw sql against your data. This is the exception, not the norm.
I just don't see the value in using one for the trivial stuff (select from join where); the intermediate stuff is longer, more verbose, more DSLy (functions, aggregates, groups); and anything rarer/more 'advanced' is just missing.
The useful bit is de/serialisation from 'model' <-> query/record, IMO. Everything else is just limitation.
The useful bit is de/serialisation from 'model' <-> query/record, IMO. Everything else is just limitation.
If you are putting business logic in your database, you are doing it wrong. I get your point. Which is why I said it’s sometimes ok to run with raw sql with your orm. The problem is your database is really great at storing and retrieving data. Keep it doing what it’s good at and do your aggregate work on a separate machine where you have cpu cycles to spare. This is the point. I’ve told people before about hibernate and it’s foreign key constraints on models - “sometimes is best to not define the relationship at the model and just make another trip to the database to load”. Heavily utilized databases are a bottleneck to your systems, stop making them do the work. Do it on the edge.
I think I feel the opposite to you. I want the ORM to handle the trivial stuff like SELECT * FROM table WHERE id=? and UPDATE table SET thing=? WHERE id=?. That's the lion's share of what I'm doing in an app, and it's pointless to type it out with slight variations repeatedly.
On the other hand, for complex stuff, I'd rather write the raw SQL, since it's in a real language and not some half-baked, under-documented DSL that everyone onboarding onto the project will be unfamiliar with.
On the other hand, for complex stuff, I'd rather write the raw SQL, since it's in a real language and not some half-baked, under-documented DSL that everyone onboarding onto the project will be unfamiliar with.
> I want the ORM to handle the trivial stuff like SELECT * FROM table WHERE id=? and UPDATE table SET thing=? WHERE id=?.
Why are list(Table.objects.filter(id=?)) and Table.objects.filter(id=?).update(thing=?) better?
That's what I mean by the trivial, it's 'handled', sure, with some pointless (IMO) thin wrapper that's Django-specific to learn. (Sure it's not loads to learn, but it has its quirks. I always have to check the meaning of the 'defaults' kwarg to update it create for example.)
> On the other hand, for complex stuff, I'd rather write the raw SQL, since it's in a real language and not some half-baked, under-documented DSL that everyone onboarding onto the project will be unfamiliar with.
Yes, I agree! I just think the trivial wrapper stuff isn't valuable, and especially if I'm using raw SQL for less trivial stuff, I may as well always do so.
I think my comment above might've sounded ambiguously like maybe I wanted DSL for more complex stuff. That's not what I meant - I meant that it tends to be more complex in the DSL than in SQL, up to the point where it's just impossible in the DSL, and you either use SQL or don't do it.
Why are list(Table.objects.filter(id=?)) and Table.objects.filter(id=?).update(thing=?) better?
That's what I mean by the trivial, it's 'handled', sure, with some pointless (IMO) thin wrapper that's Django-specific to learn. (Sure it's not loads to learn, but it has its quirks. I always have to check the meaning of the 'defaults' kwarg to update it create for example.)
> On the other hand, for complex stuff, I'd rather write the raw SQL, since it's in a real language and not some half-baked, under-documented DSL that everyone onboarding onto the project will be unfamiliar with.
Yes, I agree! I just think the trivial wrapper stuff isn't valuable, and especially if I'm using raw SQL for less trivial stuff, I may as well always do so.
I think my comment above might've sounded ambiguously like maybe I wanted DSL for more complex stuff. That's not what I meant - I meant that it tends to be more complex in the DSL than in SQL, up to the point where it's just impossible in the DSL, and you either use SQL or don't do it.
> Why are list(Table.objects.filter(id=?)) and Table.objects.filter(id=?).update(thing=?) better?
I'm not familiar with SQLAlchemy, but with other ORMs, this isn't how you'd write this.
It would be more like Widgets.findOne(id) and then widget.property = bla; widget.save();
For me this is much more natural than writing SQL, especially for the update query.
There are usually also query helpers for simple queries by fields, like Table.findWhere({property: 'value', property2: 'value2'}).
To me this syntax is much less awkward than writing SQL in strings, and it saves you from repeatedly writing out the SELECT statements and where clauses.
I'm not familiar with SQLAlchemy, but with other ORMs, this isn't how you'd write this.
It would be more like Widgets.findOne(id) and then widget.property = bla; widget.save();
For me this is much more natural than writing SQL, especially for the update query.
There are usually also query helpers for simple queries by fields, like Table.findWhere({property: 'value', property2: 'value2'}).
To me this syntax is much less awkward than writing SQL in strings, and it saves you from repeatedly writing out the SELECT statements and where clauses.
That was Django, not SQLAlchemy. You can also do it more as you describe, `get(pk=id)` instead of `findOne`.
I just find it pointlessly different to SQL, not really saying it's worse.
The SQL would be `update table set thing = ? where id = ?`.
I just find it pointlessly different to SQL, not really saying it's worse.
The SQL would be `update table set thing = ? where id = ?`.
The problem is not the orm, it’s how you are treating data. The ORM has features for doing “out of the box” sql if you happen to fuck up your relational model. Which you clearly have in your s/o post. It’s ok, we all make mistakes. SQL modeling has been screwed up many many many times. For these rare occasions where the orm doesn’t match the object model or the desired action you are trying to achieve, there are ways to use raw sql, when necessary. Your case, it’s necessary as the relational model is broken.
So, is anything that is representable in SQL but not in Django's tiny subset a 'fuck up'?
Like many 9s of development are, it's brownfield anyway, so you're saying the ORM should only be used if one's prepared to make large schema changes?
Like many 9s of development are, it's brownfield anyway, so you're saying the ORM should only be used if one's prepared to make large schema changes?
Django's default subset is enough to do 99.9% of what you'd need to do (I know from experience) and there are various packages you can install to extend the ORM if necessary or if it just makes your life easier.
Of course, as the other person said, you can always always always descend into raw SQL if you must or if it just saves you time.
Of course, as the other person said, you can always always always descend into raw SQL if you must or if it just saves you time.
YOLO
It depends what you're doing with the data. If you're trying to map into an object based view of your data, then yes ORMs have at least thought of most of the issues there.
However if you'd like to view your relational data gasp relationally, then the ORM is a giant anchor around your neck.
However if you'd like to view your relational data gasp relationally, then the ORM is a giant anchor around your neck.
Good orms like sqla comes in layers, and the object relationships mapping is the highest one. You can use lower level layers as you need, and retain the benefit of a strong battle tested abstraction that gives you introspection and a standard API.
Sometimes I use sqla just for the connection pool and session handling and dump raw sql in it.
It's not raw sql vs orm, as usual it's use a spectrum with good use cases for each part of it, and you usually benefit from all of it. But orm have pareto value in my xp.
Sometimes I use sqla just for the connection pool and session handling and dump raw sql in it.
It's not raw sql vs orm, as usual it's use a spectrum with good use cases for each part of it, and you usually benefit from all of it. But orm have pareto value in my xp.
ORMs only have value IF there's an object model you're mapping to. When working in a language that has good support for relational data, ie support for sets of heterogeneus maps, you can skip the whole object model and avoid the whole need for mapping in the first place.
Ruby and Python in particular really missed the boat on this one. I feel like they cargo culted practices from Java rather than embrace the implications of their own langauge's flexibility.
Ruby and Python in particular really missed the boat on this one. I feel like they cargo culted practices from Java rather than embrace the implications of their own langauge's flexibility.
Isn't that what pandas does kind of? You can execute sql queries and get the results in a dataframe which is a dictionary with some convenience functions on top.
Do you feel python could have gone further to take advantage of specific language features to improve that workflow?
I was more taking shots at Django. pandas is a great example of doing things right imo and me ripping on python at large was more my web-dev myopia shining through.
Right, OOP is sometimes not the best paradigm for the given problem. If you're using C# or Java, sure, ORMs fit right in. Ruby and Python, not to mention Lisp etc., offer a multi-paradigm palette where forcing objects in, just to access relational data, just obscures things.
I guess a lot of people haven't seen the middle-ground. For me that's always been where I've sat with ORMs. It takes care of some tedious parts of getting data from SQL into my application without pretending that the underlying data isn't relational. SQLAlchemy and Dapper (.NET) have been great for this middle-ground usage.
I personally prefer something like sqlx, where you just scan rows into structs. That's the tedious part of object-relational mapping, and the part that actually makes sense to automate.
Generally, I think what people call ORMs try to do too much, and there is an impedance mismatch on many levels. Objects and relations don't map; objects are statically-defined at program compilation time, and relations are the result of a dynamic query. I think the reason that ORMs work in dynamic languages is that there isn't a rigid set of objects defined by the program; rather they can come and go during execution. So the problem becomes a problem later -- static analysis, editor tooling, etc.
Lifecycles are also complicated. You can have your ORM return dumb objects that you modify and re-store, or they can be "smart" and updated the database automatically. The "dumb object" approach neglects database transactions (multiple writers could have already modified your cached copy), and the "smart" approach neglects the networked nature of your SQL server (you may need to retry, you may need to time out if the server goes down, etc.) by pretending that some server run by another company is actually your CPU's cache.
Finally, I think it's very optimistic to write an application to support more than one database backend, which seems to be everyone's dream. Many have tried. Few have succeeded. I chalk it up as actually being O(n) work to support n databases. MySQL has some quirk that breaks Postgres's assumptions. Even the humble SQLite is different enough that is hard work to support as a secondary / test-only implementation. (I realized this, I think, when I debugged some code that worked in tests, but not in production. The tests used SQLite and did something like "insert into foo (boolean_value=1); select * from foo where boolean_value='t'". In Postgres, the inserted object is selected. In SQLite, it's not! That, to me, was the end of ever supporting two databases without the explicit requirement to do so. I'd still run my Postgres tests against Postgres and my SQLite tests against SQLite, though.)
All in all, I don't get it. Start a transaction, scan into a struct, mutate the object, write it back to the database. Not much code is required. If your mutation operation is too long to run in a transaction (i.e., it reaches out to the network for something), then you really need a state machine, which you can easily implement in your relational data model.
Generally, I think what people call ORMs try to do too much, and there is an impedance mismatch on many levels. Objects and relations don't map; objects are statically-defined at program compilation time, and relations are the result of a dynamic query. I think the reason that ORMs work in dynamic languages is that there isn't a rigid set of objects defined by the program; rather they can come and go during execution. So the problem becomes a problem later -- static analysis, editor tooling, etc.
Lifecycles are also complicated. You can have your ORM return dumb objects that you modify and re-store, or they can be "smart" and updated the database automatically. The "dumb object" approach neglects database transactions (multiple writers could have already modified your cached copy), and the "smart" approach neglects the networked nature of your SQL server (you may need to retry, you may need to time out if the server goes down, etc.) by pretending that some server run by another company is actually your CPU's cache.
Finally, I think it's very optimistic to write an application to support more than one database backend, which seems to be everyone's dream. Many have tried. Few have succeeded. I chalk it up as actually being O(n) work to support n databases. MySQL has some quirk that breaks Postgres's assumptions. Even the humble SQLite is different enough that is hard work to support as a secondary / test-only implementation. (I realized this, I think, when I debugged some code that worked in tests, but not in production. The tests used SQLite and did something like "insert into foo (boolean_value=1); select * from foo where boolean_value='t'". In Postgres, the inserted object is selected. In SQLite, it's not! That, to me, was the end of ever supporting two databases without the explicit requirement to do so. I'd still run my Postgres tests against Postgres and my SQLite tests against SQLite, though.)
All in all, I don't get it. Start a transaction, scan into a struct, mutate the object, write it back to the database. Not much code is required. If your mutation operation is too long to run in a transaction (i.e., it reaches out to the network for something), then you really need a state machine, which you can easily implement in your relational data model.
What is wrong with viewing the data relationally?
Nothing at all, I'd argue working with relational data relationally is ideal. It was in response to the statement that anybody try to do their own data access layer is guilty of hubris.
I'd say the bigger hubris is trying to shove relational data into object graphs and the challenges and pain of ORMs is proof of that.
I'd say the bigger hubris is trying to shove relational data into object graphs and the challenges and pain of ORMs is proof of that.
It’s important to know the difference between a DAO and a DAL in my comment. Many are confusing the two.
> However if you'd like to view your relational data gasp relationally, then the ORM is a giant anchor around your neck.
How so? I've never found this to be the case.
How so? I've never found this to be the case.
I'm quite anti-ORM, though I've never not used one professionally (just because it's already been there / not my decision), so that might be different if I did for a while. I do think something's necessary though, and it maybe is a bit arbitrary where we draw the line for what's an 'ORM'?
For example, I really enjoyed using rusqlite for a little side project/personal thing, but found apart from anything else just the repetition of `(?,?,?,?,?,?)` any time I wanted to insert something was annoying, for example. I started working a bit on [0] .. I don't know if I'd call it an ORM, just so I didn't have to do that, and could write `Model { ... }.insert(&conn)` instead. But still write plain SQL queries, I just want easy mapping between db table and language 'model' struct/class; column and field/attr.
[0] - https://github.com/OJFord/rusqlite-model
For example, I really enjoyed using rusqlite for a little side project/personal thing, but found apart from anything else just the repetition of `(?,?,?,?,?,?)` any time I wanted to insert something was annoying, for example. I started working a bit on [0] .. I don't know if I'd call it an ORM, just so I didn't have to do that, and could write `Model { ... }.insert(&conn)` instead. But still write plain SQL queries, I just want easy mapping between db table and language 'model' struct/class; column and field/attr.
[0] - https://github.com/OJFord/rusqlite-model
I'm personally not that much against query builders, but I dislike things that try to hide the database. Any datastructure that transparently performs database queries as you access items in it goes swiftly in my "nope" pile. A reasonable exception can be made for streaming iterators in a statically typed context, though that has its own issues.
I especially dislike "load" methods on objects since they hide what is potentially a very expensive operation behind something that looks cheap and is easy to call in a loop; what I want is to query data first and then load it into objects if needed rather than create objects for querying data.
I especially dislike "load" methods on objects since they hide what is potentially a very expensive operation behind something that looks cheap and is easy to call in a loop; what I want is to query data first and then load it into objects if needed rather than create objects for querying data.
This may sound odd, but I never understood using heavy classes with relational databases.
I use javascript too, and at most I need a couple of HOF (higher order functions) to do most of the work I need.
Then again, I do use typescript and its type system, so maybe I am not the target audience.
I use javascript too, and at most I need a couple of HOF (higher order functions) to do most of the work I need.
Then again, I do use typescript and its type system, so maybe I am not the target audience.
Same, I usually run away when an ORM is forcing classes on me, sequelize ftw
IMO ORMs are an abstraction too far. I’d rather use a query builder. It gives you better control over the query if you must use such an abstraction. Of course I would much prefer raw SQL and then doing the mapping to objects and serialization myself but that’s not for everyone and yadda yadda move fast … startups etc etc
I tried to do something similar with https://github.com/justinvanwinkle/Norm about 10 years ago. It hasn't generated a lot of interest, but I find it quite useful to construct queries without having to learn the minutia of an ORM library, or even a SQL generation library.
Probably one of the best parts of Norm, and a big part of why I wrote it, is that it doesn't require you to have a hardcoded copy of the databases schema in your code, it just works like SQL.
I put quite a bit of effort into making bulk inserts efficient, as well as making sure rows could be streamed from the database while buffering as few as possible in memory on the client.
I still maintain and update for my own use. Feel free to make suggestions or request features.
Probably one of the best parts of Norm, and a big part of why I wrote it, is that it doesn't require you to have a hardcoded copy of the databases schema in your code, it just works like SQL.
I put quite a bit of effort into making bulk inserts efficient, as well as making sure rows could be streamed from the database while buffering as few as possible in memory on the client.
I still maintain and update for my own use. Feel free to make suggestions or request features.
Nice work!
I mean, more power to you if this is something you want, but writing raw SQL is something I will never miss.
Why? Just like learning Emacs/vim, the time investment is well worth it.
You get much more elaborate the more time you invest in learning, and the skills gained will still be valuable in 25 years - as opposed to whatever subset of SQL the query builder du jour would give you.
You get much more elaborate the more time you invest in learning, and the skills gained will still be valuable in 25 years - as opposed to whatever subset of SQL the query builder du jour would give you.
It sounds like the commenter knows SQL, so it’s not like they want to avoid learning it. I can only guess, but more likely they dislike the fact that you have to represent your raw queries etc safely as a string in your application’s language - which isn’t hard but it does look a little ungainly and tends not to scale too well. As for the “du jour” part - is there that much churn and fanboyism in ORMs?
Strings do give you the ability to copy them right into your code from the SQL console after testing.
While that is true, I think most queries would involve parameters of some sort, so those would need to be fiddled with at the very least after pasting to your code, plus you'd also want to format them so they don't look that out of place. And at that point you've likely got a single maybe-weirdly indented query that appears as a plain string, which is fine if you have just a couple but can get a little tricky if you have a larger application.
I'm not super religious about this - a colleague and I were discussing Hangfire.io's SQL Server code which has inline SQL[0] and we ended up agreeing that it's fine - but if I'm writing an application with a SQL backend I'm definitely leaning towards using something like EF in .NET
[0] = https://github.com/HangfireIO/Hangfire/blob/master/src/Hangf...
I'm not super religious about this - a colleague and I were discussing Hangfire.io's SQL Server code which has inline SQL[0] and we ended up agreeing that it's fine - but if I'm writing an application with a SQL backend I'm definitely leaning towards using something like EF in .NET
[0] = https://github.com/HangfireIO/Hangfire/blob/master/src/Hangf...
True about the params.
I guess I've always put the queries in as "here" strings, so they do look out of place in the sense that the structure is different. In most cases the sql was in a file with a bunch of other "lowest" level db calls, so I kind of got used to that being the look of those particular source files.
Looked at the Hangfire code and the last two methods there look a lot like what I used to do in the old C# days. Except I'd have whole files of db stuff like that, and other files for the C# code that called them.
Looked at the Hangfire code and the last two methods there look a lot like what I used to do in the old C# days. Except I'd have whole files of db stuff like that, and other files for the C# code that called them.
Ah neat so there's a very real separation between the code itself and the queries, that I think addresses the weirdness of seeing a block of SQL in a string literal that I find a bit jarring. Out of curiousity, is this Perl by any chance? I haven't really seen here docs in use outside shell or Perl scripts
Haven't used Perl but they're in a lot of languages, Lisps, Python ("""), Ruby, PHP etc. but C# does at least have the verbatim-type of strings which would be good enough. Don't use the interpolation features though, use the @params and let the SQL driver do the replacements.
I've learned vim. It was not worth it. I'm more productive with vscode and nano.
I know SQL, 99% of the time, an ORM is more productive, for the rest, I can still write SQL manually. It's not an XOR.
I know SQL, 99% of the time, an ORM is more productive, for the rest, I can still write SQL manually. It's not an XOR.
I got pretty proficient at using vim in vscode, and personally for me the investment was worth it. It's great for working in HTML with chords like `va"p` (replace selection around quote with clipboard) and `dip` (cut out the contents inside this xml tag). YMMV depending on the type of work you do.
I'm completely hopeless in nano because I can't for the life of me use the arrow keys for moving around, so I just end up typing random strings of hjkl/bw or whatever in the buffer until I realize I have the wrong editor and switch to some variant of vi so I don't fight with my fingers.
One major advantage of vi is that it's always there (except on Windows :().
One major advantage of vi is that it's always there (except on Windows :().
I'm fluent in SQL, and even with ActiveRecord it's not always possible to escape having to write it directly, but I still don't like it.
Writing raw SQL comes with all kinds of risks you can abstract away through nice ORM APIs, and with a good ORM API I find that it's much easier to clearly communicate intent in the code.
YMMV.
Writing raw SQL comes with all kinds of risks you can abstract away through nice ORM APIs, and with a good ORM API I find that it's much easier to clearly communicate intent in the code.
YMMV.
Even sticking to "just" SQL without a true ORM, using a well-designed query builder instead of writing raw strings will save you from inevitable "oops, forgot a comma" bugs.
With the library I'm using (Quill), the DSL is mostly a copy of the standard library's collections, so other than a little boilerplate, it's mostly already familiar.
In my experience, writing "raw" SQL only sucks if your schema sucks and/or you don't have much patience for abstract thinking and building views to make common use cases easier.
I much prefer being able to write a simple SQL query to deliver a piece of business functionality, especially if this is part of some configuration-time setup and not a code-time thing.
It's also 1000x easier to discuss the implications of a particular SQL query with other non-wizards. Emailing my project managers C# method snippets is just going to return more questions to my inbox.
I much prefer being able to write a simple SQL query to deliver a piece of business functionality, especially if this is part of some configuration-time setup and not a code-time thing.
It's also 1000x easier to discuss the implications of a particular SQL query with other non-wizards. Emailing my project managers C# method snippets is just going to return more questions to my inbox.
Its really worth taking the time to learn well. Any library that abstracts that for you, is using some rules to create sql underneath, so knowing what its (probably) doing can give you a lot of insight when tracking down performance problems or bugs. And, suggest changes you can make to the schema, or stored procedures if necessary to improve performance as well.
I bounced when I saw the Class extends syntax.
Elaborate
Enforced class hierarchies are not very adaptable. A simple approach would expect records, a mapping function and the findOne, findList, findSet plumbing. There should not be a need to enforce a class hierarchy framework upon the integrating code.
Somewhat counter intuitively, type hierarchies cause leaky abstraction layers.
Somewhat counter intuitively, type hierarchies cause leaky abstraction layers.
In this space but a little lighter:
https://github.com/gajus/slonik
https://github.com/gajus/slonik
FYI, it's only for PostgreSQL.
I mean, good luck when you are later porting to another DB system.
How often does one even migrate DB’s? I’ve always seen the advice, but other than an explicit goal of supporting SQLite + Postgres simultaneously, I’ve never actually ending up needing to move.
And given that RDBMS’s tend to do have roughly similar performance profiles, except when they have radically different profiles (e.g. switching from row to columnar, or unique optimizations eg GIS in postgres), there’s not much incentive to be moving around — unless you hit the end of the envelope, at which point you’re probably looking at a re-design anyways if you’re already looking at re-architecting it.
And if you’re switching to something with a different performance profile, you’re going to be rewriting those queries anyways.
Personally I stopped considering databases as “standardized” or swappable — and using an ORM for hot swapping purposes I think might be as ridiculous as using a library for unifying web server frameworks (you can definitely do it… but your common denominator is fairly pathetic).
The only scenario I’ve seen where one changes DB with an explicit goal of minor code changes is migrating from some legacy DB on some ancient version to something more modern (especially today, targeting a DB the cloud can auto-manage), where your ORM DB flexibility won’t help you whatsoever
And given that RDBMS’s tend to do have roughly similar performance profiles, except when they have radically different profiles (e.g. switching from row to columnar, or unique optimizations eg GIS in postgres), there’s not much incentive to be moving around — unless you hit the end of the envelope, at which point you’re probably looking at a re-design anyways if you’re already looking at re-architecting it.
And if you’re switching to something with a different performance profile, you’re going to be rewriting those queries anyways.
Personally I stopped considering databases as “standardized” or swappable — and using an ORM for hot swapping purposes I think might be as ridiculous as using a library for unifying web server frameworks (you can definitely do it… but your common denominator is fairly pathetic).
The only scenario I’ve seen where one changes DB with an explicit goal of minor code changes is migrating from some legacy DB on some ancient version to something more modern (especially today, targeting a DB the cloud can auto-manage), where your ORM DB flexibility won’t help you whatsoever
> How often does one even migrate DB’s?
I've never seen it happen on a production system in ~20 years.
Might happen at the current gig but that's more down to "Firebase is junk for this job and we're rewriting everything anyway, might as well consider switching DB too".
I've never seen it happen on a production system in ~20 years.
Might happen at the current gig but that's more down to "Firebase is junk for this job and we're rewriting everything anyway, might as well consider switching DB too".
Not the point. The point is that people can then build db agnostic libs on top, so that several projects from different teams can use it despite using a different db.
E.G: you can use django, no matter if you are a mysql or a posgreq shop and still use the entire ecosystem of apps.
E.G: you can use django, no matter if you are a mysql or a posgreq shop and still use the entire ecosystem of apps.
I've seen it happen once. Back in 2011, the company I was working for decided to switch all of their apps from MySQL to postgres. We had built everything in Django, so the transition was a lot less painful than it could have been, but it definitely wasn't free.
Lucky! I've done a few.
> How often does one even migrate DB’s?
We (an insurance company in Europe) have around 200 DBs and half of them have been running for over 15 years. We need to migrate at least these to modern systems in the next 5 years. These new systems all come with their own ORM and RDBMS.
Most of the longer existing institutions have the same challenges. But even new companies are in a similar situation. The main problem with ORMs always arises when the business itself changes and/or new requirements (e.g. GDPR) come in.
We (an insurance company in Europe) have around 200 DBs and half of them have been running for over 15 years. We need to migrate at least these to modern systems in the next 5 years. These new systems all come with their own ORM and RDBMS.
Most of the longer existing institutions have the same challenges. But even new companies are in a similar situation. The main problem with ORMs always arises when the business itself changes and/or new requirements (e.g. GDPR) come in.
> How often does one even migrate DB’s?
Never happened with me. Only used something different (or even a newer major version) when writing a new tool or doing a whole rewrite of a current tool. Which kind of counts as a new one, right?
Never happened with me. Only used something different (or even a newer major version) when writing a new tool or doing a whole rewrite of a current tool. Which kind of counts as a new one, right?
In my experience you seldom switch databases. Sometimes you need (to start) supporting multiple.
I’m really in favour of having tighter and clearer/cleaner integrations using technology specific features (eg PostgreSQL specific or gRPC specific) getting the most out of the tool than wasting potential for the eventuality that you might need to change it someday. As long as you stay open to the idea of having to change it all someday.
I’m really in favour of having tighter and clearer/cleaner integrations using technology specific features (eg PostgreSQL specific or gRPC specific) getting the most out of the tool than wasting potential for the eventuality that you might need to change it someday. As long as you stay open to the idea of having to change it all someday.
More often than not, you aren't porting. You are making the system work with more than one kind of database. When my first company was acquired and was built on PostgreSQL (which was designed to be hosted) the acquirer wanted an on-premise version that also supported Oracle. Using an ORM made that a relatively simple process with only a few annoying differences between the two databases. Specifically, treatment of strings (oracle empty string is a null string), booleans (no boolean type in oracle) and DDL (oracle doesn't have transactional DDL).
That makes me think, is there a tool or document about the intersections of features provided by DBs? For example, if you're building for Postgres and MySQL, is there a way to only use features supported by the two? Same for MySQL and SQLite, or Postgres and SQL Server?
My anecdata says once in two decades. I migrated a few simple Perl webapps some time ago, the oldest being 20+ years old or something? Not completely sure when it was created.
I moved them from Oracle to PostgreSQL because we would have had to start paying for Oracle licenses ourselves. It's a very good reason to change databases.
There was no ORM, so I just rewrote the queries to be portable and added some runtime branching in cases where that wasn't possible. PostgreSQL has an extension that adds some Oracle compatibility functions and views; that helped. The initial data migration went pretty nicely with ora2pg.
For the largest (and most important) application I had the writing side duplicate writes to both databases so I could run two instances in "production" and compare their behaviour.
It took a couple weeks of work and testing and then a couple months of observing that the new system works fine; the biggest pain was Oracle's '' = NULL thing and the lack of common SQL syntax for sequences; and the complete lack of tests, of course...
I moved them from Oracle to PostgreSQL because we would have had to start paying for Oracle licenses ourselves. It's a very good reason to change databases.
There was no ORM, so I just rewrote the queries to be portable and added some runtime branching in cases where that wasn't possible. PostgreSQL has an extension that adds some Oracle compatibility functions and views; that helped. The initial data migration went pretty nicely with ora2pg.
For the largest (and most important) application I had the writing side duplicate writes to both databases so I could run two instances in "production" and compare their behaviour.
It took a couple weeks of work and testing and then a couple months of observing that the new system works fine; the biggest pain was Oracle's '' = NULL thing and the lack of common SQL syntax for sequences; and the complete lack of tests, of course...
I mean hell, I've seen so many libraries need upgrading every few years. Two decades is a fucking steal in terms of man hours.
> How often does one even migrate DB’s?
I have had multiple clients who have wanted to migrate from MySQL/MariaDB to Postgres, but afaik none have ever undertaken the Herculean effort to do so with the multiple years of slightly incorrect cruft that has accumulated in their old databases that have evolved over time.
I have had multiple clients who have wanted to migrate from MySQL/MariaDB to Postgres, but afaik none have ever undertaken the Herculean effort to do so with the multiple years of slightly incorrect cruft that has accumulated in their old databases that have evolved over time.
If anything, you're more likely to keep the database and migrate whatever application is using it.
Over time I've come to see DB-independence as a benefit that is seldom realized and therefore not a strong point in favor of a given system.
Data is generally far more valuable than application code and often times your database will far outlive your application code (and sometimes even your choice of programming language).
Moreover if you're using DB-specific features rather than generic SQL, there's usually a strong business need driving it that would cause DB-specific coupling even in a supposedly DB-independent ORM.
Data is generally far more valuable than application code and often times your database will far outlive your application code (and sometimes even your choice of programming language).
Moreover if you're using DB-specific features rather than generic SQL, there's usually a strong business need driving it that would cause DB-specific coupling even in a supposedly DB-independent ORM.
> Data is generally far more valuable than application code and often times your database will far outlive your application code (and sometimes even your choice of programming language).
I agree with this. At my current company, we have stored procedures that are over 30 years old still running. The original application code was VB6, then VB.NET, and now there are C# NET5 services. There’s also all kinds of other document stores, but that old SQL DB isn’t going away any time soon.
I agree with this. At my current company, we have stored procedures that are over 30 years old still running. The original application code was VB6, then VB.NET, and now there are C# NET5 services. There’s also all kinds of other document stores, but that old SQL DB isn’t going away any time soon.
Done it once..in 20+ years accompanied by an entire re-write, so not often enough to care about this at all.
In my experience this is an overrated concern. And when it does happen, it's usually accompanied by a rewrite anyway.
Mid and low tail risk applications, certainly.
Some applications have regulatory requirements associated with them, especially retention, or otherwise serve critical needs areas.
Some applications have regulatory requirements associated with them, especially retention, or otherwise serve critical needs areas.
> Some applications have regulatory requirements associated with them, especially retention, or otherwise serve critical needs areas.
Which is why the choice ends up between “keep a legacy system older than the average age in the IT industry” and “take it apart and start over”.
Which is why the choice ends up between “keep a legacy system older than the average age in the IT industry” and “take it apart and start over”.
How big of a concern is this? Nowhere I’ve ever worked has been concerned with the ability to swap the database for a completely different one, in the same way they are not concerned with the ability to swap whatever programming language is being used for a different one.
I worked for an organization that used Oracle for relatively trivial applications. The difficulty of switching to MySQL and then later to Postgres convinced them to use an ORM going forward.
Basically if your app might last 20 years (this one went online when I was 4) it might be a consideration.
Basically if your app might last 20 years (this one went online when I was 4) it might be a consideration.
IME if you didn’t use the DB
-specific features, migration is painful, but not that much so.
If you did use the DB specific features, you’re screwed regardless of an ORM.
NB: I work for a DB migration firm, specializing in legacy->modern — where our usual customer has thousands of SP’s that have to be migrated and an ORM would have saved you maybe 5% of the effort; so my experience is biased towards that level of difficulty.
If you did use the DB specific features, you’re screwed regardless of an ORM.
NB: I work for a DB migration firm, specializing in legacy->modern — where our usual customer has thousands of SP’s that have to be migrated and an ORM would have saved you maybe 5% of the effort; so my experience is biased towards that level of difficulty.
TSQL and PSQL ideas of a stored procedure are not really the same.
If your SQL is mostly bound columns or some sort of ORM only then you can get lucky and just play the data type matching game. But most of the ones I have ever had to port was playing the 'rewrite these 300 stored procs again' game. They usually ended up in some sort of stored procedure for one of a few reasons 'they only knew how to do that', performance, that is what the senior guy wants.
If your SQL is mostly bound columns or some sort of ORM only then you can get lucky and just play the data type matching game. But most of the ones I have ever had to port was playing the 'rewrite these 300 stored procs again' game. They usually ended up in some sort of stored procedure for one of a few reasons 'they only knew how to do that', performance, that is what the senior guy wants.
In a type-safe environment I think you should just be able to switch your pur sang SQL builder to another dialect. Because of the type-safety you’ll be able to find incompatibilities at compile time which makes the migration easy enough (ignoring data migration). This avoids creating a weird ad hoc SQL dialect trying to fit all the others in a single API.
I work on Mammoth which is a pur sang Postgres query builder, see https://github.com/Ff00ff/mammoth.
I work on Mammoth which is a pur sang Postgres query builder, see https://github.com/Ff00ff/mammoth.
RDBMs have proved to have far more staying power than random ORMs for various fashion languages.