Show HN: Write universally accessible SQL, not library-specific ORM wrapper APIs
github.com
github.com
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.
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].
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')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.
https://blog.logrocket.com/why-you-should-avoid-orms-with-ex...
Personally, I love solving fancy puzzles with SQL but honestly prefer Rails' ActiveRecord ORM because it drastically cuts down on typing.
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.
>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)
Aside, Kotlin collection extensions might be my favorite thing I learned in the last two years.
>>> 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 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().
However if you'd like to view your relational data gasp relationally, then the ORM is a giant anchor around your neck.
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.
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.
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.
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.
How so? I've never found this to be the case.
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.
The useful bit is de/serialisation from 'model' <-> query/record, IMO. Everything else is just limitation.
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.
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.
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 just find it pointlessly different to SQL, not really saying it's worse.
The SQL would be `update table set thing = ? where id = ?`.
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.
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?
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.
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.
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 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.
Or, if for some reason views couldn't be added (lack of access) a set of generic CTE includes?
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.
I feel so dirty after just reading this, that I have to take a shower! ;)
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.
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.
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.
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.
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.
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...
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.
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.
One major advantage of vi is that it's always there (except on Windows :().
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.
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.
Somewhat counter intuitively, type hierarchies cause leaky abstraction layers.
Basically if your app might last 20 years (this one went online when I was 4) it might be a consideration.
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 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.
I work on Mammoth which is a pur sang Postgres query builder, see https://github.com/Ff00ff/mammoth.
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
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".
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.
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.
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?
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 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 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.
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.
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.
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”.