DenoDB(github.com)
github.com
DenoDB
https://github.com/eveningkid/denodb
219 comments
It's because synchronous libraries that block don't play well with async event loop code like node or deno. You'd have to run them on a thread pool and pass data back and forth to avoid blocking.
This phenomenon also happens in go and rust for similar reasons.
This phenomenon also happens in go and rust for similar reasons.
It was already common with Java and .NET, hence why the vocabulary types define multiple levels for the database drivers.
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.
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.
Most of Java drivers are different since there is official implementation. That's possible mainly because Java is very popular language. Deno is more community driven. Maybe Node is closer to Java these days.
Writing extensions in rust is also an option, not done it myself but it seems to use the systems shared library capabilities: https://blog.logrocket.com/how-to-create-a-deno-plugin-in-ru...
[4b] https://github.com/sidorares/node-mysql2
[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.
[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 think one reason is using native C/C++ SDK makes it harder to install (compared to pure JavaScript/TypeScript), because you can't assume that compiler is always available. For example, you need to install extra tools to use install node modules with native dependencies on Windows.
Every time we discuss an ORM project, someone is bound to complain about ORMs in general.
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 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.
Used ORMs in many different languages, from ActiveRecord, Hibernate, EntityFramework, SQLAlchemy, Bookshelf.js, GORM, and Ecto (Ecto was the best, ActiveRecord was the most "feature rich", Hibernate makes me cry, and EntityFramework is OK so long as you give Microsoft a lot of money).
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.
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.
Totally agree!
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 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 don't think EntityFramework Core (on NET Core) costs all that much?
To get the full power of EntityFramework, with all the cool code and graph generation, I think you need a paid version of Visual Studio and SSMS, although I could be wrong. Either way costing anything at all already puts it at a disadvantage compared to the others I mentioned.
I worked on two projects that used ORMs; Java and Node and it was a total shitshow. The reasons are well known and too many so I won't get into them.
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".
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".
I've used ORMs for 20 years in Java, PHP, C#, JavaScript, TypeScript, Python, and Kotlin. I have pushed code to production with at least 10 ORMs, possibly more.
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).
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).
Wow.
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.
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.
> A huge benefit of TypeORM is that you get full static type support in all of your queries, including in the returned results.
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.
1. https://github.com/adelsz/pgtyped
2. https://jawj.github.io/zapatos/
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.
1. https://github.com/adelsz/pgtyped
2. https://jawj.github.io/zapatos/
Pgtyped looks really cool. So does Zapatos.
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.
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.
I think you're highlighting features that are not inherent to ORMs but seem to be packaged with the ORM you use.
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.
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.
I'm actually using Prisma.io right now.
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.
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.
Please note you can add custom handlers in PostGraphile using JS as well as SQL; we have an extensive plugin API, but if you just want to use SDL and resolvers we’ve a plugin generator for that: https://www.graphile.org/postgraphile/make-extend-schema-plu...
Awesome...maybe?
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.
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.
Zapatos ftw
For me, ORMs are a sign one isn’t comfortable reading and writing SQL.
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.
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.
I am super happy and comfortable reading and writing SQL. Give me the slightest excuse and I'll write up a wicked complex query that does exactly what we need to solve a particular problem.
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 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.
> 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.
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.
I've been doing this as my $DAYJOB for the last five years.
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.
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.
We contributed some type definitions a couple years back to node-sql, which is a very lightweight query builder: https://github.com/brianc/node-sql
> 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.
It is possible to get type safety with SQL queries. Eg doobie[0], a purely functional JDBC layer for Scala.
[0]: https://tpolecat.github.io/doobie/
It is possible to get type safety with SQL queries. Eg doobie[0], a purely functional JDBC layer for Scala.
[0]: https://tpolecat.github.io/doobie/
OK, that's awesome.
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.
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.
I have read through a bunch of your comments and it frankly doesn't even sound like you are doing database queries for an application: it sounds like you were tasked with creating a very general purpose API endpoint for application developers. You thereby to me don't even seem to be having a use case for an "ORM", so I am extremely confused? It seems like you wanted a tool that let you describe an intended API surface over some schema and have it automatically construct your backend for you... that sounds useful, and if that's some side effect of working with an ORM that is really cool I guess, but it isn't what an ORM is about. I would imaging then that the client is then using a GraphQL adapter to access your data.
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?
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?
Good question! To which I respond with another question: Why not both? ;)
Actual example code from my current project (with the actual object type anonymized by renaming it to "thing"):
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...
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 am also very happy reading, writing and understanding SQL. My own side-product depends on that ability: https://dwata.com. It uses reflection of the underlying SQL, using an ORM based reflect library, generates a vector of the relations and state of schema and creates SQL on the fly.
But, would I recommend a team to start writing SQL by hand: No, absolutely not.
But, would I recommend a team to start writing SQL by hand: No, absolutely not.
Why 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?
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?
Because "getting to know your data storage technology better" isn't the business goal. On the surface, that's a great goal to have, but taking an approach that requires you to do 100x as much work so you can learn SQL better isn't software engineering. It's over-billing.
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.
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.
I develop a backup program, HashBackup. It uses SQLite. I wrote all the SQL. Most of the SQL stmts are a few lines long.
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.
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, what, a couple dozen (at most) SQL statements that are custom designed for a single application? Not really a big deal. Sure, write your SQLite queries by hand. Fine. Whatever; I wouldn't even say it's inefficient if it's that few.
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.
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.
I don’t necessarily agree with the approach, but I completely agree with the thinking. Thank you for sharing.
By that logic, I should also know compilers, lexers, kernel, and the whole pile of protocols that run the Internet in a great amount of details. Where do you draw the line?
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.
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.
SQL is a high level language, more declarative and better at manipulating data than your project's programming language.
Comparing a compiler implementation would be in this case comparing to a database implementation.
Comparing a compiler implementation would be in this case comparing to a database implementation.
Damn right! I'm not comfortable with writing nor reading SQL, and I avoid it usually when doing my projects.
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.
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.
Yep. It’s good that these options exist.
I have tried ORMs in only 3 languages, they all sucked. It is not all fault of the language though - writing SQL in SQL feels natural, but writing SQL in PHP/Go etc feels just plain weird, no matter how well the ORM libraries are defined.
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 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 have a small suggestion: please try ORMs in different languages.
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.
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.
FWIW, some people rave about SQLAlchemy and for the life in me I can't understand why. It does too much behind the scenes, the high level interface overloads a lot of operators (IIRC `==` and/or `and`/`or`) are overloaded and return objects rather than booleans, and by default all objects are truthy--this has lead to bugs at least a few times. There is the "core" which is basically just a query builder and the only part worth using as far as I can tell, but searching for things in the core always turns up results from the ORM layer. The documentation is hard to read because it's all rendered on a single page and many method names and class names are overloaded, so if you ctrl+f for a method, you have to scroll up several pages to find the class heading. Further, because it's all dynamically typed, it's very difficult to tell precisely what types a method accepts or returns.
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.
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.
My biggest issue with _many_ ORMs don’t deal with production deployment as a thing but as an afterthought (I’m looking at _most_ of the JS ORMs for this nonsense; `.sync()` should _NOT_ be how you introduce database changes).
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.
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.
You can't yet have multiple where conditions seperated by an OR operator. I've been using this library for a side project and unfortunately I just don't think it's mature for production use cases yet.
Hopefully it keeps maturing as it otherwise shows quite a bit of promise.
Hopefully it keeps maturing as it otherwise shows quite a bit of promise.
I hate ORMs with the fury of a thousand suns.
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).
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).
You're being downvoted, but I completely agree. If you are on Node and Postgres I highly recommend using slonik - it makes it easy to just write SQL but at the same time makes it almost impossible to have mistakes like SQL injections:
https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
https://github.com/gajus/slonik
https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...
https://github.com/gajus/slonik
Along similar lines but more simple/agnostic, check out node-sql-template-strings[0] or sqlate[1].
[0]: https://github.com/felixfbecker/node-sql-template-strings
[1]: https://github.com/moll/js-sqlate
[0]: https://github.com/felixfbecker/node-sql-template-strings
[1]: https://github.com/moll/js-sqlate
> it makes it easy to just write SQL but at the same time makes it almost impossible to have mistakes like SQL injections
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
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
Meh, not really. Some of the biggest benefits from slonik are only possible because of Javascript's support for tagged template literals [1], e.g. you write a statement like
1. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...
sql`SELECT id FROM foo WHERE bar = ${barValue}`
1. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...
That is exactly what PDO’s prepared statements do. It’s not string concatenation, and it’s explicitly to prevent SQL injection?
I must be misunderstanding I guess.
I must be misunderstanding I guess.
The "Stop using knex.js" blog post misses a very important point in favor of using knex.js: database migrations.
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.
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.
I don't really think that blog post misses that, I just think that DB migrations functionality is very different from SQL builder functionality, and that blog post is referring to the SQL builder part of this.
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.
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.
Idempotent SQL updates are my preference.
``` 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.)
``` 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.)
I prefer the postgres module. 0 dependencies. Easy to use API. Wicked fast. Also really hard to fall victim to SQL injection attacks unless you actually put some extra effort into doing something foolish.
https://github.com/porsager/postgres#readme
https://github.com/porsager/postgres#readme
Thanks for the link to the slonik, never heard about it before. But if I'm not mistaken, if doesn't generate types for result days from the queries, unlike pgtyped?
> But if I'm not mistaken, if doesn't generate types for result days from the queries, unlike pgtyped?
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...
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...
The premise of an ORM is that you will be saving time using simple abstractions.
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.
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.
> the fury of a thousand suns
off topic, but I recently learn that this phrase dates back to the Bhagavad Gita!
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 OneHow literally was that translated though? Ngram viewer shows [0] plenty of usage of 'of a thousand suns' well before that 1944 translation [1], though it certainly picked up after that, no doubt helped by Oppenheimer quoting it (that translation specifically, recently published at the time) w.r.t. the Manhattan project.
Confusingly [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+...
[1] - https://www.bartleby.com/73/123.html
[2] - https://www.bartleby.com/45/4/11.html#44
Confusingly [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+...
[1] - https://www.bartleby.com/73/123.html
[2] - https://www.bartleby.com/45/4/11.html#44
If I remember well was a phrase used by Oppenheimer about the atomic bomb, a sentence that he read from Bhagavad Gita.
Where I'm at now, we have a big huge Java app that uses MyBatis (https://mybatis.org/mybatis-3/)
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.
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.
I love SQL and being able to squeeze performance from these magical blackboxes called database servers.
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.
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.
> 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
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!
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!
> Where I'm at right now, we have multiple microservices reading from the same database
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?
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?
From the same base tables. And yes, it is very incompatible with the definition of a microservice and causes plenty of horrible issues.
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...)
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...)
> But how do you deal with refactoring?
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
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
Tests are nice. But I see disadvantages with relying on them:
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.
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.
Agreed. My reasoning:
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.
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.
"Database-agnostic" is a pervasive and pernicious lie. It almost always means "lowest common denominator amongst all supported databases".
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.
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.
What's the alternative? Are you saying it's better to use raw SQL or to use your own home grown convenience functions for creating tables, selecting rows, etc.?
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.
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.
> What's the alternative?
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.
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.
For TypeScript, there’s a very helpful list here: https://phiresky.github.io/blog/2020/sql-libs-for-typescript...
Is there something like this for MongoDB with Typescript?
The language appropriate tool for writing SQL is SQL. Anything else is a mess; even the ORM best case (generating a simple query from suitable metadata, without redundant source code) is both very complicated compared to just having the text of the SQL statement and very constraining for future evolution (e.g. when the query involves a new table, doing multiple queries and filtering data in the applications instead of using joins because it's the path of least resistance).
> The language appropriate tool for writing SQL is SQL. Anything else is a mess...
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.
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.
There was a PHP library called NotORM which was great.
ezSQL is a similar project, also good.
For Java, JDBI is also great choice.
I came to like the approach, but I got the impression that the dev team has never seen a breaking change they didn't like, so upkeep was painful. It's been a few years since I've used it, so maybe they've chilled out a bit.
I’ve been using https://prisma.io lately and I love it. It builds a Db client right from your db schema including Typescript typings.
I don’t consider it an ORM in the classical sense. I see it more as a query builder.
I don’t consider it an ORM in the classical sense. I see it more as a query builder.
Prisma looks great as someone who lives in Typescript land. Have you been using it "in anger", are there any limitations you've run into that its worth keeping in mind? I'm going to give it a shot on my personal project.
Hi! Sorry for the late response. I've been using it "in a lot of pleasure".
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!
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!
>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?
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.
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.
As I've already mentioned twice in this comment section, pgtyped and similar libraries. You wrote raw SQL, then they check it against a database at compile time and give you static types for your inputs and outputs.
Example of alternative https://observablehq.com/@mirek/tsql-examples
I like ORMs for simple things like basic joins and wheres but in many cases more complicated SQL feels actually easier and more predictable than trying to convert them into ORM syntax. There's a limit to the usefulness of the ORM syntaxes.
I couldn't agree more. ORMs might be an ok choice to get started quickly and to maintain a consistent way of working across teams, but over time they very frequently become a bottleneck. Having a handwritten, optimized SQL with specific joins and subqueries, tailored to the exact problem - or even better yet, a pre-planned query or postgres function is often orders of magnitudes more efficient and can make a substantial difference in terms of response times and running costs for your db servers.
Why not both?
Approach where you use pure SQL for reads and ORM for saves is not that uncommon
Approach where you use pure SQL for reads and ORM for saves is not that uncommon
That's fair - though I feel that ORM enthusiasts would advocate for the consistency that comes with the ORM on read, the automated resolution of relationships and the mapping to an object in the native language.
I don't agree
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.
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.
Don't most ORMs have ways to do raw queries when needed?
Yes. It's often a false dichotomy. You don't have to make a decision 100% either way. Just write SQL when it's complicated enough for a query to be useful. And you can still use ORM to write that `foo = Foos.recent.for_user(id)` which happens in small variations many times in the app, where you gain nothing but typing practice by writing pure SQL.
With JDBI you can just write
@SqlQuery("SELECT * FROM users WHERE id = :id")
User find(String id);
I posted an example which is not a simple lookup by id. It was specifically "Foos.recent.for_user(id)" because you can have multiple Foos, with a (reusable) scope `.recent` that you can use in other queries and a lookup for a given user which likely includes a join.
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.
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.
I find people hate/are ok with ORMs based on how they're used.
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.
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.
> 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’.
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’.
That's why I prefer idempotent SQL DDL updates. A disk full of up/down scripts makes me nervous.
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.
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.
I'm with you. ORMs feel like _exactly_ the wrong place to abstract. In my experience, database calls always end up being the thing you want the most control over.
I've been using pgTyped in production for months and it has been phenomenal.
There's another one called (I think) Zapatos that has some similar qualities.
There's another one called (I think) Zapatos that has some similar qualities.
Have you tried Ruby on Rails' ActiveRecord? I like it! It's easy to use and easy to integrate with "raw sql" if needed.
No, ORMs do much more. They turn slices of resultsets into objects. They lazy-load related objects instead of using one efficient join and doing one query; they in general have trouble representing results of projections and joins. They fetch all "attributes of the object" when you need to select a couple of columns from two dozen. They make the objects mutable, and introduce dirty state without transactional control. They allow running other code, including other DML, in hooks before or after loading or saving an object, leading to very strange errors sometimes, where a database trigger would fail to run and indicate an error. They make DML over a group of records, trivial and efficient in SQL, a litany of one-record updates, each requiring a round-trip.
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.
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.
Sounds like all your gripes are from a naive usage of ORMs.
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...
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...
Not GP, and I've never used Rails so I'm not fanboying over it, but I've long had the impression that ActiveRecord is at least one of the most sophisticated ORMs; it wouldn't surprise me if it handles more complex cases better than others.
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.
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.
Indeed, I have seen a lot of semi-naive usage of ORMs, and enough expert usage that sidesteps the leaking abstractions and drops into non-ORM access.
Table models are hugely useful. "Business object" models, not as much.
Table models are hugely useful. "Business object" models, not as much.
All uses of any technology are naive given enough members on a team.
I use TypeORM quite often, especially in projects with fairly simple queries. But I always make it a point to:
* 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
* 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
I mostly agree with you, but to be fair TypeORM is a particularly bad ORM.
Check out slonik if you want something that does not go full pg-types. I combine it with io-ts for runtime validation.
Why are you being forced to use ORMs if you don't want to and they're not the correct tool for the job?
There are a lot of insufferable tech leads that make the decision. I know how to spot them because I’m insufferable too. My only saving grace is that I make a deliberate attempt to always say to myself ‘would this feel like cognitive overhead to others?’, and walk away from picking that fight.
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.
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.
When one is yet another cog on the machine, without any saying on the project technical directions.
...or when one is an empowered individual contributor yet doesn't have full decision power in the project technical direction. Other people also have opinions
I’m usually not a fan of dissing technologies that one does not use/like but others do. I don’t use ORMs but I’m sure the reason they are so popular is that they provide something of value to the people that do use them.
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.
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.
I've been using [1] which is SQLite compiled to WASM (what a weird world we're in now). It's got some limitations and caveats, but it's working well for my current modest needs. It's a pretty straightforward wrap of the SQLite API - no ORM. The advantage is that it doesn't depend on having anything installed. Just import and you're good to go.
[1] https://deno.land/x/[email protected]
[1] https://deno.land/x/[email protected]
I'd love to see a benchmark between using this and a natively compiled sqlite. Just to see how much performance is lost to this convenience, so I can judge if it would be worth it in a concrete project for me.
Is it just me or is it weird to see an ORM that wraps over 4 relational databases and… Mongo…
It is weird. TypeORM does this too, and based on its documentation, there's a lot of caveats. Judging just on that, I imagine there's a lot of compromises that have to be made.
Defining the schema in application ORM with Mongo seems helpful. While the DB doesn’t enforce a schema, I always want to have one.
At first glance -- async/await mitigates a lot of my least favorite things about orm dsl's -- namely that it can be difficult to tell when the orm framework is actually going to generate a db round trip without being familiar with the implementation ... thinking back to the most recent orm i had to learn (rails) and how it was quite annoying to get started with while interacting with an existing codebase. clear suspension marking would have made the code vastly easier to follow and allow making inferences about where things were happening even as a unfamiliar with the dsl ...
This doesn’t always work with Node APIs, one example that comes to mind is fetch (for making URL requests). When you await a fetch(url) you get a response object, where a bunch of functions on it (like json() IIRC) are themselves promises - but not making another API call. I’m not actually sure why it’s structured like that, I guess to give unified error handling/callback syntax between the call failed and the unexpected response branches maybe? But it means that you can’t rely on await to delineate where round trips are happening at least in that case.
I think it’s because of the different actions you might want to take after the fetch.
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. 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().
I agree that it might never be parsed/you might not want to, and that it shouldn’t be done implicitly. But I’m not sure why that requires await instead of just a plain function call to parse it explicitly.
Parsing JSON is synchronous, but fetch itself doesn’t read the body data.
> [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.
[1] https://stackoverflow.com/a/59555579
> [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.
[1] https://stackoverflow.com/a/59555579
Is that basically the same point at a lower level though? I don't think reading the stream involves a round trip?
Personally I prefer functional combinators like interfaces [0]. Js/ts have tagged templates which enhances those type of interfaces a lot. It gives access to full set of functionality of underlying database, not just common denominator of all used. It allows arbitrary compositions etc.
[0] https://github.com/appliedblockchain/tsql/
[0] https://github.com/appliedblockchain/tsql/
Really dislike active record style ORMs. Even if the entities are proxies w/ some magic (e.g. lazy loading), they should still read like plain records in the code, and be programmed with a data-first style. Because methods accumulate on these active record entities, I’ve found devs tend to treat them more “thingly” than just data projections. The user doesn’t save itself. It’s just a row in a database.
Yeah, and we already have TypeORM in Node, which is an excellent ORM (despite some clear haters here in the comments).
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.
[1] https://github.com/denolib/typeorm
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.
[1] https://github.com/denolib/typeorm
I agree.
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.
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.
If you want an ORM, use GraphQL. It has the one thing missing in all other ORMs: it's a spec, not an implementation. Don't like one vendor, swap in another without breaking upstream services. Seriously, folks really underestimate the benefits of a published spec over a sea of inconsistent implementations.
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.
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've played with SQLAlchemy and Django's ORM in the past with Python but as of late I have been taking a liken to Eloquent while working with PHP's Laravel. It's always weird trying an ORM when coming from native SQL but I think eloquent might of found a happy medium as it seems flexible to me.
https://laravel.com/docs/8.x/eloquent
https://laravel.com/docs/8.x/eloquent
I am just discovering Laravel and years of Rails, React, etc and it's been a lot of fun. At first glance, DenoDB also seems to have a good level of abstraction.
During my years as a dev i have really started to dislike ORMs. They always fail in the end. SQL is universal, and transfers between languages and tech fields. This is why im pro-sql, and always try to avoid unnecessary abstractions.
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).
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).
I always feel weird about comments like this. Instead of discussing the merits of the actual project shared, we're going on a rant about whether or not ORMs are a good idea?
It's like whenever someone shares something they made with Electron, and the comments are just Electron hate.
It's like whenever someone shares something they made with Electron, and the comments are just Electron hate.
Not really, Electron would be the tool but not the end goal. Here an ORM is the end goal and that's what the parent complains about.
I think it’s useful to debate both the general concepts and specific implementations
[deleted]
I am with you. And it's always the top comment.
If the programming language can offer type safety for queries, it's a big win. For instance in the .Net framework, Entity Framework continues to be the default choice after a decade because it is well integrated with the language and also allows you to use SQL if needed. Meanwhile in say JS, an ORM has less to offer.
So really, just like most other programming decisions, the answer is "it depends".
So really, just like most other programming decisions, the answer is "it depends".
I generate Typescript types directly from a Postgres database. That works great with Knex and a bit of custom code I've written on top.
How do you know you're saving and retrieving data that correctly matches your typescript definition? Or are you just saying "trust me"?
This has been an issue for me as well. I created https://github.com/wwwouter/typed-knex to mitigate this. At first I had to do a lot of weird things, but with the evolution of TypeScript, it is getting more and more like 'normal' Knex.js. The end goal is to be nothing more than a very small wrapper, if any.
Knex has some built-in support for it, but I've made a wrapper that does it quite well. So I can write db.select('member', { displayName: 'John' }) -- and because I specify the table name in the first parameter, I get type checking on the second.
> SQL is universal
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.
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.
> 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.
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.
SQL dialects vastly differ especially past SQL-92. And those differences tend to be very annoying (e.g. RETURNING). There is a reason that people do not generally work across different RDBMSes and instead pick and stick to a single RDBMS because it is really a PITA; I do dislike ORMs for usual reasons, but a bare SQL is not the alternative either.
And yet the strategy most ORMs take is the common denominator where you have no idea if it'll actually use RETURNING or fire off a completely separate query with a potential race condition because SELECTs go to the read-only replica.
(Ask me how I know about this kind of problem with ORMs.)
(Ask me how I know about this kind of problem with ORMs.)
I'd say for most standard use cases, the same SQL can be utilised across different DBs.
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.
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.
Meaning it’s vastly more universal than your ORM du jour.
> And ORMs have the advantage of abstracting this SQL away for you allowing that you to work across databases if you need to.
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".
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".
True, but the only case where yoy really need to work across dbs is if you are creating a product that allows your customers to bring their own db. Otherwise you are much better off picking a db and sticking with it.
Regardless of whether ORMs or SQL are more effectively database-agnostic, how often are you changing databases that this is even a concern?
It's always been the opposite for me. Whenever I had to use something PG-specific in Rails I had to resort to SQL.
I mean universal in the sense that going from mysql to postgres is really not a big shift.
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.
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 event triggers in MySQL?
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.
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.
Well if you are comparing non-standard database features you are out of luck. Im talking about the 95% CRUD stuff that you do. Basic selects/joins/aggregations etc.
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.
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 been keeping one eye on EdgeDB [0], which I believe could remove a lot of the desire for ORMs. In my experience a lot of the drive behind ORM use is that SQL kinda sucks at returning anything that looks vaguely like a tree.
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.
[0] https://www.edgedb.com/docs/tutorial/queries
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.
[0] https://www.edgedb.com/docs/tutorial/queries
Never seen edgedb before. Ill have a look, thanks for the tip.
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?
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?
> Edgedb seems to be build on postgres, so does it come with the same power
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.
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.
Wow that looks great, thanks!
I'd tend to agree with you in javascript-land, but the Django ORM is black magic...the (mostly) performant and complex queries you can build with it are simply amazing. Add in the migration system, and it's really tough to complain, especially since you can always drop into raw sql as needed.
Every single time I've seen somebody not using ORM on something more complicated than a todo list side project, the alternative becomes an imposible to understand and maintain mess of joins, custom made query builders, manual computation of computed fields, custom calls to fetch related data and hand made crappy shell scripts to do schema migrations.
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.
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.
At least in node, there are migration tools that let you write vanilla SQL. My current project is ORMless, and I think it’s turning out just fine. Time will tell.
How many people are working on it and how big is your project?
I agree that SQL is generally superior to using an ORM. However I'd argue that SQL is a lot harder to learn and significantly harder to master than any given ORM for most developers. It's very easy to write code for basic queries that performs terribly when writing SQL by hand, whereas ORMs will frequently produce code that is reasonably optimized.
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).
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).
I wonder if you've ever worked on a Hibernate project that's more than a couple years old.
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.
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.
In my experience dealing with code written by people who only know ORM:s is about as fun as dealing with code written by people who refuse to use ORM:s when they really should have. The latter like to prove they don't need an ORM by creating their own "simple" libraries full of half broken functionality that pretty soon turns into to the ORM:s from hell.
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.
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.
Give slonik a try. It's a very nice step up from raw drivers https://github.com/gajus/slonik
I have reached the same conclusion regarding ORMs after strugling with Hibernate/JPA for some time.
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.
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.
JDBI is amazing.
I never shy away from complex SQL when it's warranted. But frameworks can make reading simple queries much easier, e.g. when using ActiveRecord in Rails. Readability is a big advantage in my book.
I agree that ORMs have a very narrow area of applicability, at best.
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.
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.
Have you tried pgtyped? I'm in love with it: it allows us check SQL queries at compile times and statically type them.
[deleted]
Agreed on the appeal of sticking to SQL. I’ve written an alternative non-ORM for Node and TypeScript (which doesn’t separate out your queries): https://jawj.github.io/zapatos/
ActiveRecord is the only ORM that I feel comfortable with. It's great.
It suffers from the same issues all others orms do.
I really recommend to check out his channel: https://www.youtube.com/c/eveningkid
One of the lower subscribed well produced tech channels I've seen in a while.
One of the lower subscribed well produced tech channels I've seen in a while.
Is this referencing some unexplained relation to the post or just vlog spam?
The channel name is the same as the github username of the deno db author, so I think it is actually related despite the content not seeming to be
Edit: And the authors website links to this channel
Edit: And the authors website links to this channel
Same person. Their GitHub profile is linked from the above Deno repo, and their website is linked from their GitHub page, and that in turn links the Youtube channel, in addition to the obvious common user name.
So, pick a database, use DenoDB and then use the lowest-common-denominator feature set of all the databases it supports.
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.
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.
Of course. Developers experienced enough to see the benefits of and understand an ORM will likely not be working on your average Wordpress site.
Initial disclosure: I worked for Six Apart (makers of Movable Type) a dozen years ago, so maybe I'm still inadvertently biased, but I certainly try not to be.
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.
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.
ORMs are unnecessary abstraction.
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.
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.
Query composing is probably 5% of the ORM framework. This is a valid thing to use. It's not an ORM replacement in any way.
I disagree. Query composing is probably 70% of an ORM framework. Rest are schema generation, serialization and validation. All of which can live outside the ORM.
Mapping to actual objects. Type mapping. Merging requests over relationships. Transaction integration. Dealing with dirty markers and writebacks. All of those along with validation depend on the database and object formats. Those are why ORMs exist in the first place.
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".
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".
Is the general consensus that we are just happy slapping an 'await' in front of instructions we want to execute sincronously, or I am missing some fundamental idea here?
Pretty much, yeah. It's yet another sign that JS/Node should have had a keyword to let async functions appear, to the calling code to act async, rather than requiring a keyword to stop that. IOW its idiom is backwards from what would be best for the programmer in the typical project. The other signs have been... every live codebase using every previous method of dealing with JS & node's async patterns. "Callback hell", the very common pattern of using promises to wrangle async code into acting synchronous, and now "await", comically, prefacing more async calls than not.
Not to be too negative but I really appreciate Java's DataSources and other abstractions every time I see some python/node "connectors".
Too bad that MS SQL is not included.
Agree, wish MS ecosystem had more support. Looks like it was given some thought though:
https://github.com/eveningkid/denodb/blob/bb319c03085612c108...
https://github.com/eveningkid/denodb/blob/bb319c03085612c108...
As a JS and async/await outsider, just looking at the README example, can anybody please explain what's the reason to have the await prefix syntax at all? At this rate it's going to be all over the place. If the function is asynchronous, what's the difference for the caller? Can't it just be omitted?
If `await` could be omitted, every async call would be blocking. Sometimes you don't want that. E.g. you can store a `Promise` in a variable and then `await` it later (or even just never `await` it and let it resolve/reject and ignore the result).
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`.
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`.
If `await` could be omitted, every async call would be blocking. Sometimes you don't want that
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:
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.
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.
If you omit await, the returned calue is a promise which you can keep for later, instead of waiting right now.
Say you want to kick off something you know will take a while, do some ofher things (async or not), THEN wait for the original async operation. (Or even say, wait for multiple promises to finish)
Thanks, I understand it, but my issue is that more and more libraries and functions are now asynchronous, so the await prefix is going to be everywhere. Maybe it should've been the other way around - when calling an asynchronous function you get the result by default, and can get the promise if you want (as it happens much less frequently):
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 promise> Maybe it should've been the other way around - when calling an asynchronous function you get the result by default, and can get the promise if you want
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.
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.
If a function is async it's because it is potentially long-running or expensive. I don't want that fact hidden.
I 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.
I 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.
Thanks, I guess that's a good reason.
Another reason: when you `await` a promise your function pauses until promise resolution and other code will run meanwhile.
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.
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.
It is okayish in TypeScript, but in js one subtle missing await also leads to unexpected everything. Mass-await in a language without types is a bad idea generally, but we have no alternatives for “the web”.
It's not a problem with types. It's a problem with call semantics.
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.
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.
You may still have race conditions with await. It doesn’t prevent anything, though I see your point if we are talking about flow control (not just net/io) via coroutines. That is race-prone in general, but most of the code is a non-leaking ‘let; await fetch(); return’ in one way or another. If someone is writing code like:
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'.
The link and title made it seem like something official from the Deno developers but it's just a mirror for https://github.com/eveningkid/denodb
OK, we'll change to that from https://deno.land/x/[email protected]. Thanks.
https://deno.land/x/ is a service from Deno, but it's a registry for 3rd party modules. There's no need to change the URL
The HN guidelines ask "Please submit the original source. If a post reports on something found on another site, submit the latter," and I assume the github link is closer to being the original source.
https://news.ycombinator.com/newsguidelines.html
https://news.ycombinator.com/newsguidelines.html
https://deno.land/x/ is explicitly for third party modules.
Holy shit, this is actually a killer app in Deno.
Great work developers!
Great work developers!
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/[email protected]
- [2] https://deno.land/x/[email protected]
- [3] https://github.com/brianc/node-postgres/tree/master/packages...
- [4] https://github.com/mysqljs/mysql