DenoDB
github.com
github.com
Deno PG [1] and MySQL [2] does it. This makes sense considering Deno's security model. But Node libs do the same [3][4]! This also kinda make sense, as node-based JS has to be async most of the time. Still it's such a hassle.
Anyways, kudos to both communities. You've done a lot!
- [1] https://deno.land/x/postgres@v0.11.2
- [2] https://deno.land/x/mysql@v2.9.0
- [3] https://github.com/brianc/node-postgres/tree/master/packages...
This phenomenon also happens in go and rust for similar reasons.
For example with JDBC:
Type 1 driver - JDBC-ODBC bridge
Type 2 driver - Native-API driver
Type 3 driver - Network-Protocol driver (Middleware driver)
Type 4 driver - Database-Protocol driver (Pure Java driver) or thin driver.
Most modern drivers are all type 4.
[4] doesn't use parameter binding; rather, it inserts escaped values into the SQL statement client-side in place of any "?" placeholders. [4] also doesn't support prepared statements. [4b] uses parameter binding, sending the SQL statement as is separate from the values, as well as supports prepared statements.
I have a small suggestion: please try ORMs in different languages. A lot of the power of an ORM depends on the language. Your ORM may not be the same as my ORMs - Django ORM, SQLAlchemy, Pony ORM. Try them and you will know why. I will wait.
From my limited understanding, the language needs to allow reflecting and manipulating your model definitions (like a model Class) which define the data model as a set of tuples (field name and field type, which is a representation of SQL type) into instances that hold data in the native types of the programming language and "magically" convert them to the SQL types. It usually falls under the realm of meta programming and is not exactly a first-class feature in many languages.
I am totally not a language expert and perhaps did not articulate this well enough, but if you are curious just search a bit and try. Just my 2 Rupees.
I mean, I've done it and had entirely different experience. Starting from the entire premise of learning N different abstraction layers over one abstraction layer.
ORMs are a bad idea. They are a productivity killer. Do not use them.
I also used to think I'd find a "good" ORM because the early magic is so exciting and efficient. But for anything beyond a toy or proof-of-concept, they are more cost than benefit.
Some of them (e.g. TypeORM, Entity Framework) are literally not debuggable because they use some much configuration magic.
Just avoid them. Write SQL in SQL (or a query builder that compiles to SQL).
See this other comment I wrote:
https://news.ycombinator.com/item?id=27551288
TypeORM in particular is well designed and quite awesome. A huge benefit of TypeORM is that you get full static type support in all of your queries, including in the returned results.
SQL results are effectively one-off dynamic type result piles that you need to validate by hand, and are insanely inefficient to code.
When did I say I wanted to give up static typing? You can have both[1][2]. I would never give up static typing and would not have used Node if TypeScript/Flow were unavailable.
> TypeORM in particular is well designed and quite awesome.
I've used TypeORM extensively in production on multiple projects for years, including with a fairly large team.
It has silent failures, and its API was unstable/unclear for a long time. It's been a very messy project for most of its lifetime. They couldn't even decide on an API for lazy-loading of relations, and there was some hidden API that was semi-supported and not type-checked.
Partly because it's built on Node, it's very difficult to use a debugger to see the relevant frames when you hit a breakpoint or an exception -- if you can even set a breakpoint where you want it to be before just running into an error at runtime.
There's also the same core issue that all ORMs suffer from, which is that the abstraction becomes leaky as soon as you need to do anything complicated with lots of joins or advanced SQL features.
But my current gig has an absolute non-negotiable business requirement to support multiple database backends. Specifically SQL Server and MySQL in addition to PostgreSQL.
Additionally: Not seeing any way to take the schema info from either of the above and have it crank out a full GraphQL server code base. Check out my linked comment if you want more details, but I'm seriously talking 20-40k lines of code that I didn't need to write because the resolvers were auto-generated by the stack I'm using.
At least a first pass of Google searching isn't coming up with anything similar with pgtyped or Zapatos. And no, you're not going to convince me that 40k lines of code that shouldn't be necessary to write by hand are somehow superior to 1/100 the code that just includes the queries that don't map as well onto the ORM or the GraphQL generated code.
There are libraries that will transform a Postgres or MySQL schema into a GraphQL API. I don't keep up with them, but the names Postgraphile and Hasura come to mind.
I also think that you're saying that (Type)ORM is a good solution because it saves you a lot of time, but are you a typical user? Your case sounds very niche to me. And do you need an ORM to solve it without writing code by hand? No, you don't.
TypeORM has another similar solution.
Postgraphile looks cool, but ... you're writing your custom handler in PostgreSQL script. This strikes me as suboptimal for debugging and general developer productivity. Otherwise, you're right, it's a similar solution. Which actually contradicts your assertion that I'm highlighting features "packaged with the ORM I use."
Hasura...looks like a black box that you'd have very little direct control over, and if they went out of business, you'd be totally screwed. I'm very firmly against lock-in with no fallback. Been there. Don't want to do it again.
Regardless, I'm only six-months-new on the GraphQL version of this stack. Before that I was using FeathersJS with Sequelize: Similar to GraphQL only with more traditional REST endpoints and less built-in JOINing between tables. FeathersJS actually has more flexibility in querying a single table, and adding indirect JOINs was possible/generalizable across your whole API with a few lines of code, but what wasn't as clean was the security model for who could see what.
Another similar stack is LoopbackJS. I'm sure there are more.
So it's really, really not that of a niche case. I've used a similar approach again and again for pretty much every backend project that needed a CRUD API. Every one of those projects I touched probably had 1/100th the amount of raw code required compared to the traditional wasteful approach.
Does it support TypeScript? Because I'm not going back to dynamic types for nontrivial code. Ever.
Does it support debugging? Because having a real interactive debugger with source mapping, ideally integrated into VS Code, is another minimum bar for me to consider it a viable platform.
If yes to both, then: Awesome! I'll keep it in mind for future projects where it's appropriate.
I think this happens because in so many projects, commercial and hobbyist, you write queries a few times and then forget about it. So, it’s totally understandable. I was there once.
I’m very comfortable reading and writing basic SQL now, and so I’d prefer to think in SQL when working with a database, and think about objects when I’m in a programming language.
I think you do yourself a disservice when you hide from it that you eventually have to face.
But, would I recommend a team to start writing SQL by hand: No, absolutely not.
If you're going to use a tool (be it SQL, NoSQL, docker, kubernetes), your team needs to have/build some expertise in it.
IMHO, using something like an ORM abstracts that layer. It also introduces a "black box" into your ecosystem.
Going by my opinion in the first paragraph above, you'd have to build some expertise in this ORM. Why spend that time when you can just spend time getting to know your data storage technology better?
I throw together 200 lines of schema in Prisma.io and it creates 20,000 lines of GraphQL handling code plus more ORM-specific code that allows me to create type-safe access to the database.
And "the database" right now is PostgreSQL, but one of the business requirements is that "the database" could also be SQL Server or MySQL.
Having learned the salient details of the ORM in ... hmm ... about three days? ... I feel that the advantages of getting a full GraphQL API that can be wired up to an arbitrary backend database for 99% of the queries vastly outweigh the fact that most of my database queries are entirely inside a "black box."
In six months I've encountered exactly one problem where the black box made my life more difficult. I think I wasted about an hour, maybe 90 minutes, on tracking down why it didn't work as expected. Given that "writing custom SQL" for all of the queries that I've created would have required 10s of thousands of lines of additional code plus system tests to verify that the queries were all working as expected? That's a profoundly huge win.
I'd still be writing SQL queries months from now if I had taken the approach you're suggesting. Maybe it's advice that's good for consulting firms that charge by the hour and love to find ways to make their developers work more hours, but in my book it's not even ethical to recommend.
Another backup program, Duplicati, uses SQLite. Sometimes they post SQL that is having problems, and one query will go on forever, like 50-100 lines of SQL. Duplicati is written in C# so I'm guessing (but don't know) that it uses some kind of ORM. Figuring out why a 100-line SQL statement was slow would be very unfun.
Not intending to dis Duplicati; just saying that black boxes are only good if they work 100% of the time, and my experience is, they don't. And any black box you can reverse engineer in an hour is not giving much productivity gain IMO.
Writing ~20 SQL queries for each of 20 different data structures (including various JOINs and search options) to create a CRUD API? And maintaining them each individually? And needing to make changes to the query every time the clients need to make a slightly different search request?
When I can literally write the 20 different schemas in one file, type a build command, and get a fully featured GraphQL API with full JOIN and custom boolean logic search capability in a few seconds?
I'm saving myself probably 40,000 lines of code, counting all of the data wrangling and test cases that I'd need to create to provide the minimal functionality that I would need--and the GraphQL code wouldn't need to be modified to change the search or order-by options of a particular query. And lines of code you don't write are lines of code you don't need to maintain. It's a huge win. Not even close.
And that said, the above is based on a real project I'm currently involved with, and that project does have six custom GraphQL queries that I wrote by hand--most of which also leveraging the ORM to some degree.
In exactly three cases, I wrote raw SQL queries that the ORM didn't support directly.
So I'm going to say no, the black boxes don't need to work 100% of the time. For me they're working 99% of the time, and I write the last 1% by hand, which is just fine. If I had run across a terrible query in that one corner case, I'd probably not try to dig inside the black box; instead I'd create a custom query that does exactly what I want and expose it for that bit of functionality. Done. [1]
As to the ORM used by Duplicati? Crappy ORMs exist. That doesn't mean all ORMs are bad. I can see the exact queries (minus parameters) scroll by in my log that the ORM I'm using is writing out. My own custom SQL queries tend to be more lines of code in practice, and none of the queries that it has generated so far have been slow.
[1] Figuring out why a 100 line SQL statement is slow is generally not that hard: You put "EXPLAIN ANALYZE" in front of it, and it will point out where the time is going. That's a PostgreSQL feature, so you'd need to have a Postgres backend in order to use it. Good thing we're talking about using an ORM that could easily switch between SQLite and PostgreSQL! Optimize the schema and indices on PostgreSQL and then switch to SQLite for embedded work.
We need abstractions. Sure some abstractions are leaky, others are simply not good. But that is exactly why people should try to make better ones.
Comparing a compiler implementation would be in this case comparing to a database implementation.
I simply also feel that having 100% type safety in your results (which you really can't do with SQL queries) is a huge bonus. Add in database portability for most of your operations (including between, say, PostgreSQL and MongoDB), and you end up with a huge software engineering win over writing custom SQL for every query.
And frankly? That's only the tip of the iceberg in the advantages of relying on an ORM.
The next step is to use something like FeathersJS or GraphQL that will prevent you from ever needing to create a basic CRUD API by hand.
Once your ORM knows the shape of the data, you can simply tell FeathersJS to serve that API, complete with POST/GET (one)/GET (find/search)/PATCH/UPDATE/DELETE, and poof, you're done except for any custom behavior or security that you want to add using hooks. Or similarly, you hook your ORM into your GraphQL adapter, and you get a great graph querying API with very little effort.
And in both cases it can be very type safe. The best query to maintain is the one that you never need to write by hand. The GraphQL code generator I'm currently using took 200 lines of clean schema definition and turned that into 20,000 lines of code that I never need to maintain or even think about.
ORMs are tools. If you ignore the power tools you have available, you're going to end up as obsolete as someone who insists on still building houses or cabinets using only hand tools. Yes, of course it can be done. But it takes 100-1000x longer, and the results are often not as good.
I'm very skeptical that there are any productivity benefits to ORMs. Note you can forego the ORM and still use a query builder to offload details of SQL syntax and even abstract over multiple SQL syntaxes for different databases. Type safety is also orthogonal to ORMs--you can have type safety without an ORM or you can have ORMs that don't provide type safety (these are very common).
In my experience, any productivity that you might gain from an ORM is immediately swallowed by the time it takes to debug problems in the magical ORM layers (and then some). I could make an equally silly analogy that using an ORM is like using a bulldozer to build a house rather than carpentry power tools, but I won't because these analogies don't add any substance to the conversation.
Your examples/predicted catastrophes are straw men that I've never seen in reality myself--though I've heard reports of Rails/ActiveRecord having issues similar to what you describe. Maybe that's the real problem you're worried about? RoR sucks. We can agree on that.
In the code I've been working with, though? An ORM can have annoying limitations, but doesn't suffer from weird magic. So in that case, write custom SQL to solve the exact issue the ORM doesn't support. Problem solved!
I'm confused by "use a query builder instead of an ORM," though. The ORMs I'm using are effectively query builders with knowledge of your object relationships. See for example Sequelize, TypeORM, or Prisma.io. Lighter query builders like knex.js don't seem to have enough information on the schema to give you type safety or auto-generated code (like FeathersJS or GraphQL resolver builders).
TypeORM and Prisma.io even allow you to use the Data Mapper pattern instead of the annoying ActiveRecord pattern. I suspect you're confusing the ActiveRecord pattern with "ORM". No "magical ORM layers" required.
Don't confuse Rails/ActiveRecord garbage for a modern ORM.
It is possible to get type safety with SQL queries. Eg doobie[0], a purely functional JDBC layer for Scala.
But type safety is only part of the equation.
Automatic CRUD or GraphQL code generation and/or other abstraction such that you never need to write basic CRUD code ever again? That should be a minimal software engineering best practice at this point, and yet we have tons of people insisting on writing every single query, either as a SQL query directly or using a query builder.
It's on the order of professional negligence to ever write complex code to handle the same situation over and over. CRUD is exactly that. Huge swaths of CRUD code are blatant DRY violations, and using an ORM or another kind of query builder that can interface with CRUD/GraphQL automation should be the minimal best practice we're all using.
In contrast, when all of us who despite ORMs are using that term, we are talking about a library or--worse--a framework designed to have the developer directly make queries using object-oriented data modeling. These layers then generate SQL that is pretty much guaranteed to be not just inefficient but ridiculously unscalable in ways that you run into even in simple software.
It sounds like in your architecture you would have no use for what I would call an ORM: at best, maybe the application developer that is consuming your API would in turn (I would argue, incorrectly) choose to use an ORM to access your GraphQL... but they are not generating SQL and you are not making queries. So I guess I just feel like you and we are talking about entirely unrelated use cases?
Actual example code from my current project (with the actual object type anonymized by renaming it to "thing"):
const result = await prisma.thingInstance.findMany({
where: {
thingTypeId: {
in: thingTypes.map((id) => id.childThingTypeId),
},
thingInstanceChildren: {
none: {
parentThingId: {
equals: args.parentThingId,
},
},
},
},
orderBy: {
thingTypeId: "desc",
},
take: 120,
});
It's because Prisma (in this case) is an ORM that fully understands the data architecture that you can layer a full automation/code generation/GraphQL server on top of it. Not all ORMs have this feature, but you mostly need to start with an ORM in order to bootstrap this functionality.
I guess you could do what you're describing by creating tons of highly specialized code given a schema without adding the ORM/query building features--but adding the layer that gives you a Data Mapper pattern ORM is a tiny amount of additional effort at that point.
I did mention elsewhere: Some people seem to think that the ActiveRecord pattern is the only "ORM" approach, but Data Mapper is another approach--and one that is usually referred to as another view strategy of an ORM. [1]
[1] https://culttt.com/2014/06/18/whats-difference-active-record...
I know SQL. I just don't care enough or want to write it. For 99% of the stuff I do, it's just easier to grab an ORM.
My sense is ORMs are terrific to get a quickly launch a product/project i.e., 0 -> 1 when worked upon by a small (3-5 member) team. But as a project succeeds and more people begin contributing it derails very fast and very badly. By the time an experienced data person comes in and sees the mess it'll be too late. Foreign keys everywhere, incomprahensible auto-generated queries, all sorts of joins etc., So they won't touch a data/model layer with a 100ft pole and continue to get entangled. When a data layer becomes unsalvagable the project is doomed. No amount of re-design/refactor/re-architecture can revive a project if the data layer is messed up.
Data model is the heart and soul of a software please don't outsource its design to an ORM. There is a good reason Linus said something to the effect of "Bad programmers worry about the code. Good programmers worry about data structures and their relationships".
For dynamic languages the downsides are increased runtime cost over simply writing and executing sql and doing the mapping yourself, and tons of magic. Magic everywhere. Magic makes things harder to debug.
For static languagges the downsides are heavy impedence mismatch, obtuse to use APIs at times (to overcome type systems), and again, a performance hit.
What I have found is for the majority of the time, people who say they want an ORM, what they really want is a type-safe (or type-hinted) way to write queries and operate on returned data. That's probably 90% of the value add. That can be done without a heavy, complex ORM framework.
I come from the world of Perl, at the start of my career. Perl has DBIx::Class, still one of the best ORMs IMO.
I’ve transitioned via PHP and Laravel, which also has a decent ORM.
Nowadays I’m heavy into TypeScript and have tried every ORM there is in the JS/TypeScript land.
And I’ve settled on Zapatos, which is not an ORM, but exactly what you describe. It’s a utility that helps writing type safe and hinted queries.
This approach contrasted with, let’s say, TypeORM which is full of broken magic under the hood, is a breath of fresh air and is a delight to use.
Together with that I write simple repository wrappers manually and surprisingly there aren’t that many of them.
I also don't enjoy writing HTML in non-HTML languages - it just doesn't feel nice or natural.
This is before getting into the other problems (having to learn the libraries, the libraries generating inefficient SQL etc).
Maybe I am old and not hip, I dunno. Using ORMs is just annoying and unpleasant
I'm not going to use that alone to write off all ORMs--I've heard the Ruby folks like their Active Record--but it seems like if such a thing as a helpful ORM exists, they are few and far between.
Early Rails got many things wrong from a DB perspective (what do you expect when MySQL is the baseline for capabilities?), but its insistence on using migrations from the first word was one of the best decisions ever made.
Hopefully it keeps maturing as it otherwise shows quite a bit of promise.
The problem is that I know SQL but now I have to spend a bunch of time trying to figure out how to convert SQL into ORM X just so it can convert it back to inefficient SQL. SQL mostly translates between various databases but ORMs are unique and you have to learn a new API for each one.
I'm on a project using TypeORM and it has been fantastic at helping developers on my team make really bad schemas due to not understanding how to use TypeORM to make the right relationships.
Currently I'm looking at pg-types because you just write SQL and it just helps by making some TypeScript types for you.
(I have used ORMs in C#, PHP, and JavaScript and I hate all of them).
https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
[0]: https://github.com/felixfbecker/node-sql-template-strings
While I think this is totally the way to go, it is kind of amusing that PHP's PDO system was the "right" way to go all along in some ways
sql`SELECT id FROM foo WHERE bar = ${barValue}`
1. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...
I must be misunderstanding I guess.
You are correct. But note that pgtyped is really only able to generate types because it actually needs to run your queries against a running instance of your DB. Some contributors have commented they are working on something similar for Slonik, https://github.com/gajus/slonik/pull/267#issuecomment-840559...
Migrations allow a development team to synchronize database schema changes across multiple branches of development.
It is the main reason I end up using tools like knex.js (node) and sqlalchemy (python).
As far as knex is concerned, you can use it to manage migrations without using it in your application code.
FWIW, I do use knex for migrations, but I hate the SQL builder part of it, so most of my migrations are littered with knex.raw statements.
This was discussed at length in this slonik issue, https://github.com/gajus/slonik/issues/42 , with some recommendations for migrations functionality.
``` ALTER TABLE IF EXISTS ADD COLUMN IF NOT EXISTS foo varchar; CREATE TABLE IF NOT EXISTS sample ( -- all the fields including foo ); ```
Run it on a clean DB, you get the table. Run it on an existing DB, it adds the column. Run it on a current DB, it does nothing. Run it once or a hundred times, the DB ends up in the same target state.
Can only be used on some databases though. The DBs without transactional DDL are a risk. Then again, they were a risk no matter what method you use. (I'm looking at you, MySQL.)
off topic, but I recently learn that this phrase dates back to the Bhagavad Gita!
If the radiance of a thousand suns
Were to burst at once into the sky
That would be like the splendour of the Mighty OneConfusingly [1] links to the passage in a different (1909-14) translation [2] which features the 'of a thousand suns' part but is otherwise different.
I just wonder, for example if the original was something like लक्षाणाम् सूर्याणाम् 'lakshāñām sūryāñām' (I think that's what I mean, I'm learning Hindi not Sanskrit! Just looking up लाख & सूरज roots.) but translated to 'thousand' on the basis that it just meant 'a lot', and that sounded better in English and was perhaps even already a phrase.
I don't mean to doubt you, just thought it was interesting, and translated texts are a bit of a can of worms.
[0] - https://books.google.com/ngrams/graph?content=of+a+thousand+...
However, your reasons for disliking ORMs seems a little like hearsay. I am interested in seeing how the responses to this post will be, although I’m afraid that this might turn into a flame-war. I wonder how often the pro-ORM flame wars the anti-ORM camp here on HN.
Many people don’t do that. I’ve been on projects where every other week the tech lead shows up and adds more and more layers to the stack. Unless you want to constantly fight, you just let it go and deal with it like a professional. Otherwise, I would have reached across my screen by now and slapped someone.
I can tell you this, there’s someone out there that’s going to build something (at an actual paying job with other humans) with bleeding edge Deno, DenoDB, Typescript, AWS infra sometime very soon for no good reason. There’s not enough slaps in the world to stop them. The fear of god does not exist in these people because no one ever says shit.
But in reality, those abstractions will be leaky, and you'll be spending significant time trying to understand what the ORM is doing, and finding workarounds for ORM problems.
Also, each time something fails, there will be one more moving part to troubleshoot.
Once you troubleshoot your problems and analyze what the ORM is doing, you'll realize that the price you paid for convenience is very high: you have sold your future in exchange for some "convenience" that actually gives you more work to do, makes everything slower and you didn't even need.
Why? Because most ORMs try to target every database, so they offer a feature set that is the lowest common denominator for all the databases they support. Does that sound like a good idea? no.
It involves writing SQL inside of XML files and doing a lot of manual mapping from columns back to the objects.
At first I REALLY disliked it but over time I have grown to absolutely love being able to just write SQL queries that can return whatever. Writing raw SQL really lets you optimize and create complex queries when you need to in a way that you can't with an ORM.
But how do you deal with refactoring? If I want to change a column on a db table or a property in a Java/C# class can the compiler tell me where I forgot to update without much ceremony?
This is a problem I had in the past on a large project. The result is that we devs started to get afraid of changing schema or code that touched database because that could create runtime errors as opposed to compilation errors. Being afraid to refactor is really bad and piles up quickly as technical debt.
Some ORMs solve this by scanning the database schema and creating a class per table and a field/property per column. This way we get compilation errors if database doesn't match code and have the opportunity to investigate it before it blows on the customer screen as a runtime error.
We solved this problem by having thorough automated test coverage for all SQL queries.
Here's basic lifecycle of a test: 1) Create the test database 2) Migrate the database to the latest schema version 3) Insert test data 4) Execute the SQL query 5) Validate the results of the query
1) We now need 100% test coverage for queries. Forget one of them and the castle might fall on the client side. This can be mitigated with gatekeepers on CI/CD pipeline to ensure 100% coverage. But it's still quite the effort.
2) Our feedback loop for developing refactoring is now slower now since we need to run all tests to see what broke after a schema change. In contrast to an IDE giving compilation errors.
3) If we build SQL queries by composing them, which is common pattern for business rule validations, there will be many branches to be tested.
4) We won't have auto-completion of table and column names in the IDE. And if you mistype them your feedback is slower.
I say that as someone who went all in SQL query builders and composability in a project but when it grew large we were still afraid of changing schema despite having a very high test coverage.
Yes, this definitely happens! Like somebody else said, tests can cover a lot of this. But of course they aren't perfect.
Where I'm at right now, we have multiple microservices reading from the same database so schema changes are a terrifying nightmare ("which service will break when we remove this column?!"). Unused columns have piled up over the years; in our `users` table we have almost a dozen unused columns. Onboarding new folks is always fun; "No, you don't want to look at the `role` column, you want to look at `role_id`!" Note that this issue would still be a problem if we were using an ORM (unless we had a shared library between all microservices that are talking to the database )
I've used ORMs extensively before and they do make refactoring a little bit easier, but I still would rather write raw SQL. Basically personal preference at this point; if I were to join somewhere that was using an ORM I would learn it and probably be happy with it!
Like, all from the same base tables or each with their own schema with service-specific views? Also, isn't shared-DB integration by-definition incompatible with “microservices”, which are isolated and sovereign over their own data?
I didn't say it was good architecture. It's a remnant of when we were a startup in "move fast break things" mode (we have since been acquired) and as an org paying down tech debt has never been a priority so we're left with a lot of bad decisions that we have to live with (to the point where it's a miracle when we actually ship any meaningful features...)
And once you have the data from the SQL database are you keeping it just in arrays/dictionaries? Or should it at least be mapped to a class structure?
As someone dealing with a bespoke SQL schema and mix of in-house SQL translation layers for different DBs, wrapper functions... I would think any ORM would be better designed since it has a singular purpose and database experts work on their development.
Not sure the parent comment would like it, but there's a middle ground between ORM and raw SQL that I consider a sweet spot. It's more of a "query builder" library that gives you language-appropriate constructs for building any SQL you like, but also provides more correctness guarantees than just writing raw SQL strings.
SQLAlchemy's "expression" layer, for example, does this really nicely. It existed long before the higher "ORM" layer came along, and can be used without having to touch the ORM.
In Go, I like the Goqu library for the same purpose.
In Rails land, my understanding is that the underlying Arel layer is more like this pattern, as opposed to the higher level Active Record ORM.
In Java, it's jOOQ instead of Hibernate.
It sounds like you're pretty set on that opinion, and that's fine, but I suspect you just haven't run into the case where the raw SQL is far messier than using a query builder.
SQL strings are pretty inflexible. The big advantage of a query builder (note that I'm not saying "ORM") is that you can start with a simple base case and dynamically mutate the query to add clauses specific to the request you're handling. Maybe one user wants 10 results per page and another user wants 25, for example.
I have an example of how far you can take this at http://btubbs.com/postgres-search-with-facets-and-location-a.... I could not endorse trying to build the query interface in that post with raw SQL.
Modern SQL has functions that can be used to map rows into json objects and arrays. That is what I use in nodejs/postgres. Everything is returned in the structure I want it in. The node driver turns the json into javascript arrays and objects (which then get turned back into JSON to send to the client, hah!). I added some code to the driver so that snake case field names are converted to camel case.
I don’t consider it an ORM in the classical sense. I see it more as a query builder.
Bear in mind I've just used it for personal side projects, nothing too critical.
I recommend you give it a try and form your own opinion.
Feel free to get in touch!
* Write migrations first, so that you define your own schemas
* Disable TypeORM synchronize, make your migrations the source of truth instead of the entities
* Model your entities after your schema
There's another one called (I think) Zapatos that has some similar qualities.
If you're using it to ad-hoc query your db then it's understandable you'll hate it - a leaky and poor abstraction over sql. Probably a bad fit.
Projects where I've seen it work well is when most of the logic is in the app with per row/per aggregate changes. In these apps it's only used for the "object relational mapping" side of things - ie to marshal types to/from db rows.
I've never found auto-migrations in ORMs good for anything less than a 1 day project - it's a world of hurt.
Just to offer a counterpoint.
Every project I worked on that did not have automatic migrations was extremely flawed in other ways as well.
Manually keeping track of your DB schema, and indeed, seeing it as something separate from the code that needs to interact with it is a bad idea in my opinion.
It’s same as ‘infrastructure as code’, database also needs to be ‘database as code’.
Most folks test their up scripts. They almost NEVER adequately test their down scripts, so you're left with this false sense of security moving forward even though revision 63 of 64 has a bug in the down portion…which you only find when you're trying to revert to the state of rev 60.
As for ORM migrations, of course they work. They've dumbed down your use of the database to the lowest common denominator (looking at you, MySQL).
IF EXISTS and IF NOT EXISTS are your good friend. Run the script, the database will be at the target state. When all databases are past a certain point, remove the appropriate ALTER TABLE IF EXISTS ADD COLUMN statements.
Makes source diffing much easier, is a consistent single source of truth, and allows for pruning old parts as needed.
Approach where you use pure SQL for reads and ORM for saves is not that uncommon
I used EF Core and Dapper for like 3 years and ORMs boost productivity significantly.
I tend to check what SQL is generated and if something is complicated and generated SQL sucks, then I use micro ORM like Dapper and use raw sql.
I agree that ORMs aren't easy because you have to learn them, but I think it's worth, unless you have to learn many different ORMs.
@SqlQuery("SELECT * FROM users WHERE id = :id")
User find(String id);
So sure, you can write this out as multiple simple queries, but do you really want to code the repeated query for every case of: Foos.recent.for_user, Foos.active.for_user, Foos.recent.by_something, Foos.recent.active.by_something? And what if you don't want a whole object in that case, but only one column? ActiveRecord for example has you covered with `.pluck(:single_column)` that you can append at the end without writing yet another full query.
Writing the simple stuff every time, you get footguns simply by repeating trivial code - that leads to copy-paste and forgot-to-change-one-of-them mistakes.
They sort of allow a developer ignore the fact that the data are stored in an RDBMS. It looks cool in toy examples, and becomes progressively worse as your code starts doing serious things on serious amounts of data.
Rails ActiveRecord, for example, handles ALL your mentioned scenarios (includes, joins, select, Transaction.do, update_all - and you can still run SQL queries or fragments thereof).
Theres a lot more in the docs than you will see in "toy examples".
Of course, not all ORMs are created equally...
I think my preferred style (vs. full ORM or raw SQL) is that of Diesel (which is incidentally from a (former?) maintainer of ActiveRecord, Sean Griffin, though I think he may have since stepped back from it) for Rust - it's more like 'SQL bindings' than 'ORM's typically are or grow to be; so you pretty much write SQL, just in Rust syntax with type checking etc. instead of actual SQL in one big Rust string.
Table models are hugely useful. "Business object" models, not as much.
1) Experience. I've seen so many terrible performance issues because of ORM-centric programming. Talking pages that take tens of seconds or a couple minutes(!) to load when they should take under 5s.
2) Ditto on the "ORMs apparently encourage people to write terrible schemas" observation.
3) "Database-agnostic" (not strictly an ORM thing and not required for an ORM, but strongly associated with ORMs) is a bad idea at least 90% of the time (I suspect more like 99%) it's applied. I've seen codebases replaced atop databases several times. I've yet to see a database replaced on an actual, live product. Moreover, your "database-agnostic" code means it's absolute hell to write any code that touches the DB that doesn't use your main codebase as an intermediary, especially if it's written in another language. That's crippling for your liberty to Move Fast and leverage the data you have, if you're thinking in terms of the business and a product suite rather than a single software product. Use your database. Let it do the work. Your customers will thank you for faster feature delivery ("Oh no, we can't use that feature in this database that immediately and perfectly solves this problem, because that would lock us in to it"), better response times, and lower chances of data loss or corruption.
4) Object-per-table isn't technically the only way to operate in ORMs, but boy is it sure treated that way in-the-wild, more often than not. You want your DB schema and your object hierarchy to be nonsense? Have I got the technology for you!
5) For the tedious cases where you do actually just want to map a select to an object and then write "row.save()" or whatever instead of a SQL statement... that's so easy to write. For the single-table cases it's trivial to write something highly re-usable, even. There, now you have much of the day-to-day benefit of an ORM with 1% of the fat and tech debt.
And re: TypeORM, in particular—I entirely do not get the appeal. It's like some kind of obfuscation engine for both SQL and the intent of the code itself, in a way no other ORM I've seen is. What the hell.
Some databases are so far behind, they really shouldn't be abstracted by the same library. They have VERY different use cases and abilities. The access model for a SQLite-based app is quite different from a Postgres-based app.
It's like having an F-150, a Cybertruck, and a Prius while hiring a driver that won't take either off-road or for more than 100 miles at a time because he also has to be able to drive a Nissan Leaf the exact same way.
But folks still hire him because he claims to handle anything with a steering wheel and pedals. Technically true, but misses the point of the different options.
That's an ORM.
Most people are still better off with an RDBMS
1. Parse the data as json
2. Return it without parsing
3. Modify some other part of the response but never parse the body
4. Turn around and stream that body into another fetch without parsing it.
Etc. parsing it and loading a large response into memory may not always be desirable so it must be explicitly done with await res.body.json().
> [1] to parse the body as JSON, first the body data has to be read from the incoming stream. And, since reading from the TCP stream is asynchronous, the .json() operation ends up asynchronous.
The one thing that keeps bringing me back to ActiveRecord and ORMs like it is the Relation class. Being able to pass around and merge queries is great for organizing code and keeping concerns separate. For example, implementing pagination, user permissions, UI filtering, and tenant segmentation, all in the same query without these concerns depending on each other. Composable scopes on models is another joy.
One convenient trick with Postgres is to define complex calculations as SQL views. Define an ActiveRecord model for the view, point some “summary” associations at it, and read it like a table.
This is why Deno is a dead-end. We have tons of excellent solutions in the Node-verse, and they need to build everything up from scratch (or create ports that are "unstable and shouldn't be used in production" [1]).
And it's not just the ORM that's interesting. It's the tooling that will connect, say, TypeORM or Prisma.io to GraphQL, so that you can throw up an API in a few lines of code, that means you're writing something like 1/100 the number of lines of code for equivalent or even superior functionality.
Learn one ORM, you're SOL when the new team uses a different one. Time to start over again. Got a bug in your ORM? Hope they fix it, because migrating to another ORM is more painful than migrating SQL syntax. Need to work around the ORM? Why do you even have an ORM?
Can you imagine browsers if they didn't standardize on HTTP? Tied to a particular vendor's server or having ten different wire protocols competing?
That's where ORMs are now without a spec. It's insanity.
I have actually went back to writing pure SQL in files, and using those as params for whatever db engine i use, this makes it even possible to reuse the code in other projects (even its unlikely that you can use the exact same query, but just as a "it would work" in theory).
For node based projects i have used and would probably still choose pg-promise (https://github.com/vitaly-t/pg-promise).
No it's not. A small subset of it will work consistently across databases.
But if you want to get the most of your database then in almost cases you will be working with proprietary SQL. And ORMs have the advantage of abstracting this SQL away for you allowing that you to work across databases if you need to.
In theory, in practise my experience is the oppose. It's easier to understand and tweak the SQL to work across DBs. You can easily diff and compare the SQL files. The ORM is another moving part, it adds convenience for simple queries, and complexity for anything advanced or non-standard.
(Ask me how I know about this kind of problem with ORMs.)
I have worked mainly with postgres/mysql but i would imagine i would be up and running at full speed in days/a few weeks with mssql if i ever chose it for a project.
sure there are syntax differences, but the "how do i do this" translates very well across databases.
How do you make a system-versioned table/query in PostgreSQL?
How do you port regular expressions to MS SQL Server?
How do you set up an exclusion constraint on a range type (without race conditions or invalid data) in anything except Postgres?
Most relational databases fit niches that the other don't. No one RDBMS is best, but they are most certainly not interchangeable.
Even using the lowest common denominator SQL means you'll be missing out on major performance boosts because they each have their own hints/shortcuts.
Its super rare i see the need to actually change the underlying database from say postgres to mysql. And in this scenario you are still screwed if you did use a ORM, with database X only features.
My point was basic SQL knowledge transfers between databases for the bread and butter 95% of things you need to do.
I've yet to see or hear of a single project (outside libs and frameworks) that needs to "work across databases with the same ORM".
With ORMs, my big annoyance is inefficiency. When you start to scale or add complexity, ORM generated SQL queries can be rather expensive when compared to hand crafted ones.
So really, just like most other programming decisions, the answer is "it depends".
OTOH I do appreciate when a library allows to construct SQL from first-class, reusable parts, like SQLAlchemy allows in Python. Using the same where-clause object in both select and update operations, or combining descriptively named filter clauses to create or amend a where clause, or easily constructing CTEe from other queries, etc — this all is pretty empowering, and, most importantly, expresses the logic better.
For example, with SQL if you wanna load all a list of Users, and all those User's Posts, and the Thread that post was made in, you're either doing joins and some awkward transposition of the flat data into a tree, or you're doing three queries and looping through the data sets to join everything up by hand. When you have an ORM that lets you do `User::with('posts.thread')->get()`, it's easy to become reliant on it and never really dig into what's happening.
With EdgeDB, everything is a set. Retrieving a set of Users where each has a set of Posts where each has a set (of one) Threads becomes something where the database layer is pretty much a 1:1 mapping to your program's data structures, but with all the benefits of an RDBMS.
Considering insertions and updates also use sets, I could envision replacing an ORM with an ultra-thin layer that essentially just converts back and forth between trees of records and EdgeQL sets. As you might imagine this is very nice for GraphQL too.
Take this with a pinch of salt as I've only done the most basic playing around with it, but it certainly seems like an interesting idea.
I usually find the docs always giving a super trivial example. Like a join. Joins are the bread and butter of database work, and design. You model your data and write queries that operate on it.
When it comes to real-world things, i usually do complex reports that include joins from aggregated sets on some condition. Here is where the orms fail big time. Edgedb seems to be build on postgres, so does it come with the same power or is the new syntax limited in some way?
Our goal with EdgeQL (the query language of EdgeDB) is to make it more powerful than SQL. There are a few things that we're still missing, but we're getting there.
What makes EdgeQL interesting is that it's functional in its nature, making it fully composable. So both simple queries and complex queries (deeply nested subqueries, aggregates, etc) work just fine.
At least for Java and Kotlin the awesome library jdbi ( https://jdbi.org/ ) implements a very useful hybrid approach.
One creates DAOs and Repositories to abstract away the DB and map results and arguments to/from objects on the fly. All while retaining full control over the SQL and all mapping aspects.
This way SQL and mapping can be optimized to leverage the features of each database (ie. PostgreSQL's array, UUID, hash and JSON types) or be handled generically.
The SQL loading can also be customized to read SQL from pure ".sql" files in resources/files or from inline specification via annotations.
The jdbi developers have in the past reacted very fast and competent to issue reports or PRs.
If find applications built using this library more easy to understand and also better performing than ones using a full-blown ORM.
It's like whenever someone shares something they made with Electron, and the comments are just Electron hate.
In many situations the performance difference between SQL and an ORM is comparable to writing assembler code to optimize your C program: significant in theory but negligible in practice.
This gets even worse if you consider that most developers in a team will not touch the queries frequently enough to ensure their SQL skills don't get rusty eventually, even if you managed to bring them all up to speed in the first place. So in practice it'll be about deciding whether the team uses the ORM (that probably already comes with whatever framework you're using) or maintains badly written SQL with a few high performance sections nobody dares to touch because nobody remembers how it worked (e.g. pivot tables).
Once you exhaust the limits of what the ORM author deemed important (or within their ability to adequately deal with), you are left needing deep knowledge in both the ORM and SQL to debug/optimize.
Also, SQL knowledge transfer well between different engines. ORMs tend to be unique snowflakes with very few common patterns beyond the impedance mismatch that is object-relational mapping.
Also, most of the times this happens to come always from ecosystems which don't have a good ORM... such as Go, Node (up until recently... Prisma is pretty good), etc.
Oh, be aware that SQL is not universal. Especially not if you do anything remotely advanced like triggers, non-trivial indexes or select queries that returns complex types.
One of the lower subscribed well produced tech channels I've seen in a while.
Edit: And the authors website links to this channel
There's a great parable from almost two decades ago.
Movable Type was a popular open source blogging platform that used an ORM and supported multiple database backends.
Wordpress only supported MySQL.
Remember Movable Type?
I hacked on both and preferred working with Perl and PostgreSQL but the ORM layer was a pain to use. Thousands of other developers apparently agreed, as the Wordpress extension ecosystem thrived and Movable Type bombed.
Wordpress won out for a number of reasons, mostly relating to licensing (Movable Type temporarily went non-FOSS at a critical moment in time), ease of hosting (PHP vs Perl), ease of dynamic content (MT generated static pages), and better company product focus (MT's parent company tried doing too many things at once and ran out of money).
I say this as someone who doesn't love ORMs, so don't get me wrong here, but I've never seen a serious analysis that ranked MT's ORM layer anywhere as a factor in WP's dominance.
The vast majority of MT users used MySQL anyway, and MT's ORM definitely had first-class support for MySQL. In 2010, MT 5 dropped support for Postgres and SQLite entirely.
I was in Six Apart's Services org, mostly developing MT plugins for larger MT users, but also occasionally WP plugins as well. Personally I quite liked MT's ORM at least circa MT 4.x, and found that developing MT plugins was far more enjoyable than WP plugins at the time. (fwiw I was equally fluent in Perl and PHP, so that wasn't a factor.)
MT had a very powerful plugin system that, combined with Perl's ability to "monkey patch", essentially allowed you to hook into pretty much any part of the CMS or page generator that you wanted. Back when MT was popular, it had a thriving extension ecosystem that very much rivaled Wordpress's, so it is simply not accurate to say MT bombed due to a poor extension ecosystem.
Yes, eventually WP's ecosystem was massively larger, but that also simply tracks with size of PHP developer community vs size of Perl developer community over time.
Check out https://github.com/ludbek/sql-compose
Tools like this are the future. It's so simple yet flexible enough to handle any complex queries.
It scales infinitely.
If you take them out, what you have left is a repository pattern with some struct mapping helpers only. Which is fine if you want that, but you'd miss the "R" and the "O".
https://github.com/eveningkid/denodb/blob/bb319c03085612c108...
const res1 = func1(); // async or not, wait for the result
const res2 = func2(); // async or not, wait for the result
let res3_p = promise func3(); // async function, but we want the promiseI don't find `await` particularly bothersome to write, and it explicitly tells me which calls are async and can be used in promise combinators or called non-blocking.
In contrast, since JavaScript is single-threaded, calling synchronous functions means only that function will be run and will immediately return to your code, with nothing else happening in-between.
The semantics are very different. Implicit `await` will quietly introduce this concurrency point, which can lead to a variety of bugs due to the unexpected order or execution.
If `foo()` behavior depends on whether `foo` is async or not, you're implicitly introducing yield points that might lead to bugs like race conditions.
Even worse: if you're calling a sync function and you (or a library author) turns it into an async function, it will silently introduce the yield point without anybody noticing.
Both sync and async `foo()` would return a `T` so TypeScript won't help with that.
public_state = a
[await or autoawait] task()
public_state = b
Both sync and async `foo()` would return a `T` so TypeScript won't help with that.
async function foo(): Promise<string> {
return "foo"
}
async function bar() {
var s:string
s = foo()
}
Type 'Promise<string>' is not assignable to type 'string'.
I don't know why you're getting downvoted, indeed maybe it should have been the other way around. I personally would have preferred it, but there's 2 main problems:
The first is backward compatibility. If the function returns a promise in older JS implementations, it needs to continue to do so. If the promises are now magically unwrapped unless there's a `nowait`, many things would break.
The second is the single-threaded nature of JS, both Node webservers and browsers. JS execution is (usually) single-threaded, and _needs_ a lot of async calls to give the illusion that it's doing things in parallel (eg serve several HTTP requests). This illusion works well-ish because we are _forced_ to have so much asynchronicity, if it was optional it would be a huge pain for the dev to manually ensure that they are sprinking asynchronicity enough: the concurrency abstraction would leak much more than it already does.
Since `Promise` is a value itself, and it can be passed around, you can create combinators for them (e.g. wait for all promises in a list to settle, for any promise to settle, etc.) This can only be done in userspace of promises are a value themselves, and this requires a difference between `Promise<R>` and `R`, and a way to (blockingly) turn `Promise<R>` into `R`. That way is `await`.
It’s not a core detail though, you could have ‘nowait’ keyword for that, or a coroutine resume wrapper which returns a future/promise, or a coop-threading syntax like:
result = co.race [
expr1, expr2, expr3
]
Promise/generator-based only cooperative multitasking is a trade-off between low-level layer complexity and userland syntax requirements. One can have first-class stackful coroutines, see e.g. Lua. The reason it’s not implemented in js is purely historical and socially-technical. It’s hard to convince browser makers to make all of their native interfaces coroutine-aware overnight.
Great work developers!