Show HN: Node.js ORM to query SQL database through an array-like API | Hacker News Reader

Show HN: Node.js ORM to query SQL database through an array-like API

github.com

123 pointstilyupo87 comments

Hello everyone! I'm exited to share a NodeJS package I was working on for the past two months.

The package is designed to simplify querying SQL databases through an array-like API. Qustar supports PostgreSQL, SQLite, MySQL, and MariaDB, and offers TypeScript support for a robust development experience.

It's in early stage of development. I would like to hear your thoughts about it.

Open articleView on HN

My take on this is that it's not always the best idea to abstract-out SQL. You see, the SQL itself is too valuable abstraction, and also a very "wide" one. Any attempt to hide it behind another abstraction layer will face these problems:

- need to learn secondary API which still doesn't cover the whole scope of SQL

- abstraction which is guaranteed to leak, because any time you'll need to optimize - you'll need to start reason in terms of SQL and try to force the ORM produce SQL you need.

- performance

- deceptive simplicity, when it's super-easy to start on simple examples, but it's getting increasingly hard as you go. But at the point you realize it doesn't work (well) - you already produced tons of code which business will disallow you to simply rewrite

(knowledge based on my own hard experiences)

Have you use BI tools, such as Looker, Tableau, and the like?

LookerML is their abstracted version - but they always have an expander panel for seeing the sql.

---

What I would like is to use this in reverse - such that I can feed it a JSON output from my GPT bots Tribute - and use this to craft a sql schema dynamically into a more structured way where my table might be a mark-down version of the {Q} query - and it does SQL to create table if not exist, insert [these objects from this json for these things into this DB, now these json objects from this output into this other DB. Now I am pulling data into the DB that I can then RAG off as I fill it with Cauldrons of Knowledge I am scvraping for my rabbit-hole project thingamijiggers.

I’ve taken more and more to thinking of them as a zero sum tool.

Super fast and easier to use force multiplier in the beginning, but eventually you break free of the siren song and run into some negative that eats away at your time until you reach that “if you had just sucked it up and written the damn sql you’d be done yesterday” stage.

This just seems like a normal part of the growth curve. You cannot simultaneously build an infinitely scalable solution and complete something in a reasonable timeframe with the features that users will pay for. If you get to the point where you have enough users to justify working on efficiency or scaling out your infrastructure that’s a sign that you are winning. Unsuccessful companies never have to clean up their tech debt. For successful companies, it is a constant balance. You’re lucky to ever be in a position to have to clean up your short sightedness from previous work. By the time Facebook needed to mature beyond their PHP codebase, they were already wildly successful by every metric and had the resources to tackle such a problem. Early stage CRUD APIs should absolutely be generated and use the shitty ORM generated queries. By the time you run into serious performance issues with the ORM generated queries, you should be successful enough and have enough runway to plan a better future.

The vast majority of companies like this don’t fail because their UI is too slow. It’s because they don’t have “essential” features that other platforms do. If you have good monitoring and metrics, you should be able to find the bottleneck in your ORM and resolve it before any users even notice. And that means you’re hand rolling a few queries instead of the entire data storage layer.

"...premature optimization is the root of all evil."

Sometimes you just wanna get stuff out there, other times you're winning and you wanna give users the best experience. Many people have had to do both. You start with an ORM, eventually your queries are slow and all, you gradually reap them out. Almost every Engineer I know has had to do that at some POINT. Nonetheless, I am not about writing SQL for a simple barbing saloon booking app that I am not sure anybody will eventually use.

Yes, yes and yes. ORM are marvelous when you do not know well SQL. With experience, you always end up needing to learn more about SQL. In the end, ORM is as much a hindrance as a help. So instead of spending energy learning the ORM of the day, it's better to invest in longer lasting technologies like SQL.

I know SQL and I like ORMs. For most simple CRUD, an ORM is fine. I don’t understand how they are “as much a hindrance as a help”; using an ORM only adds functionality, it cannot prevent you from using SQL against the data source in the same manner you would if you weren’t using an ORM.

It’s really just syntactic sugar for the subset of very basic queries that are easily expressed in the ORM. If other parts of your codebase are expecting ORM objects, it’s maybe two lines of code to re-wrap your SQL-fetched PK values back into ORM ducks.

I love ORMs for setting up entities and relationships, but I mostly use sql/query builder for all queries that are not trivial.

This is one of 4 reasons why I'm building pg-nano [1] and honestly the main catalyst. The other 3 reasons are: I still want to call my Postgres functions from TypeScript in a safe manner; I want declarative schemas with generated migrations; and I want the ability to write compile-time plugins that can generate SQL or TypeScript through introspection.

It's not released yet, but give it a look :) (v0.1 is almost done)

[1]: https://github.com/pg-nano/pg-nano

ORMs are usually used for speed until it's time to optimize with writing the SQL.

Some ORMs have def have some more experience getting optimized in delaying the need to optimize the query, indirectly, or directly by rewriting it.

ORM with a bit of SQL might still be less work than using a nosql db and trying to make it relational, but not.

The author of slonik, a great (IMO) tool for composing queries in raw SQL in Node for Postgres, has a good blog post explaining this same general idea: https://gajus.medium.com/stop-using-knex-js-and-earn-30-bf41...

The way I've always put it is "ORMs make the easy stuff a bit easier, and the harder stuff way harder." Just learn SQL, it's not that hard and it's a much better, transferable skill.

Yes this applies to a lot of abstractions of SQL, this one (inspired by Entity Framework/Linq) however works _with_ the grain by more or less by finding a sweet-spot between the SQL and the source language and most importantly doesn't try to hide the SQL.

My experience with Linq over the years has been great, only time I've needed to go raw SQL was to supply index hints (you can add that to Linq but we opted not to) and doing special things with merge statements. But EF allows you do submit raw SQL queries where needed.

The important part is, when you have a good system that actually provides benefits(Linq is properly typed) and doesn't get in the way or produce weird SQL then it'll work out.

I've only needed to use around 10 raw SQL queries where Linq failed to hundreds or maybe thousands of Linq queries where it worked perfectly well and this includes some fairly heavy queries.

Nice, looks promising. How does this compare to drizzle?

Context:

We've had a lot of ORM frameworks come and go in node.js - sequelize, typeorm etc, but none of them have really caught on.

Things have been changing a lot lately after typescript took over, so we've seen a bunch of ORMs take off that give you a really good typescript experience.

So, the juggernaut in this space is of course prisma, which is super expressive and over all pretty decent - it comes with its own way to define schemas, migrations etc .. so that might not be everybody's cup of tea. (and then there's the larger runtime, that have lambda-users complaining - though that has mostly been addressed now where the binary is much smaller)

So despite it being a pretty opinionated framework really, what it gives you are really rich typescript integrated queries. And all in all it works pretty well - i've been using it at work for about 3 years and I'm just really pleased with it for the most part.

The newcomer in the space that's gaining a lot of traction is Drizzle - where it's mostly a way to define tables and queries - it also gives you really rich typed queries - and it happens all in TS/JS land.

this project of yours reminds of drizzle - kind of similar in a lot of ways.

I'm super interested to understand how this compares to drizzle and which problems with drizzle this attempts to solve

Hmm. I might be wrong as I haven't used Drizzle, just read the docs, but isn't Drizzle just like Prisma? That's really not the same as this. I find Prisma at least one of the most terrible things I ever worked with in my life; the rigidity (which I guess is the arrogance of the devs which they call opinionated; their right but he), the weird querying dsl, the terrible tooling. Just checked 'Drizzle queries' again and see it looks exactly like Prisma is it not? That's really not anything like this imho?

The "Drizzle Queries" section of the docs describes additional APIs for relations (referred to in the docs as db.query). There is also an API that looks/works much more like SQL (see db.select(), db.insert(), db.update()) with good types.

Now that we have about 15 years of ORMs, do they really make things easier?

SQL is not a difficult language to learn, and views and stored procedures provide a stable interface that decouples the underlying table schema, allowing for migrations and refactoring of the database structure without having to rewrite a lot of code.

ORMs seem to me to be mostly about syntactic sugar nowadays; I’m worried that the abstractions that they set up insulate the developer from the reality of the system they’re depending on - like any abstraction, they probably work fine right to the very point they don’t work at all.

I’m not complaining about this project; it looks cool and I can see the attraction of staying in a single language paradigm, but I am very wary of abstractions, especially those that hide complex systems behind them.

You're being downvoted, but you're not wrong. Here's another benefit to just using SQL: it's cross-language, cross-framework, cross-decade. So "select firstName from users where id=?;" works in 2024 with Go, JavaScript, etc., but it also worked in 2010 with Ruby and 1999 with PHP.

Every time you switch languages, or stay in the same language for 2 years, you have to learn another ORM. SQL is about as close as timeless gets in this business.

What I find valuable is that many ORMs provide type support and manage migrations, not so much the day-to-day interaction with the database.

> manage migrations

I feel like it is one of their major drawbacks. But I'm mostly working maintenance so what I usually see are databases outliving many applications and my view will differ from greenfield project people.

Your ORM is tied to your app. Tying your database to your app through your ORM is IMO an error. Managing schema change in your application is even worse.

Database and their schema should be independent from your app. So you can release new versions of your database without depending on app releases. As mentioned by other people the best would be to have views per app for reading and procedure for writing so you can totally decouple your app access from your data schema.

Databases are not dumb key value stores. Stop using them like they are and start enjoying the functionalities they offer.

How do you manage database migrations then? Are they in Git repository? Genuinely curious.

Yes, like any other piece of your system.

It can feel overkill when you have one app with its code repository, infra repository and now schema repository. But most people are not doing microservices so the database is central and used by multiple applications. Then one more repository, which you'd want DBAs to handle, is nothing.

Also, migrations should only go up and be non destructive.

The main problem and I think it is one of the current open ones for the gitops / CD ecosystem is managing which versions of your software are compatible so you know what can be running together or not. Package management but for your whole software architecture.

All this are personal opinions and I'd be happy to have to change it if presented with good arguments against it.

It might depend on the ORM and the time to lightbulb on the differences between them and the relevance if any to the project at hand.

> I’m worried that the abstractions that they set up insulate the developer from the reality of the system they’re depending on

except for the the simplest of queries, I always check my ORM based queries by looking at the translated SQL. This seems like common sense to me, but maybe not.

Cool project!

Looking at the docs, for example the pg connector, I couldn't easily find information about how it parameterizes the queries built through method chaining.

For example, if I run

   .filter(user => user.name.eq(unsanitizedInput))

I am presuming that the unsanitizedInput will be put into a parameter? For me, using ORMs on a team that may include juniors, that is one of the key things an ORM provides: the ability to know for sure that a query is immune to SQL injection.

If you had more examples on the connectors of queries like this, and also maybe some larger ones, with the resulting SQL output, I think that might increase adoption.

Qustar parametrizes all queries by default, so it's immune to SQL injections. I'll add info about that with examples to the docs, thank for the feedback!

Very nice! Almost everyone I know misses Entityframework if they ever worked with it and similar ergonomic ways in other languages (clojure/cl). Entityframework has it's downsides, but it's so nice to develop with. I don't mind (and often use SQL), in fact, since no longer using C#, I find myself using SQL more often than ORMs as everything is so ... clumsy... compared to entityframework.

Continue doing the excellent work please!

Thanks! I miss Entity Framework too, one of the best ORMs out there.

One of my main reasons for using C# is entity framework and Visual Studio itself.

An intriguing idea! I like this approach for being an innovative interface to SQL. I wonder if it would reduce cognitive load when interfacing with the DB.

I'm a game dev and often need to avoid situations where I'm using '.map' to iterate an entire array, for performance reasons. It would feel odd to use the concept, knowing it wasn't really iterating and/or was using an index. Is that how it works?

It’s exactly what Entity Framework does in dotnet. It allows you to query the database like it’s an enumerable.

In fact, in EF, an IQueryable (which is the interface you use to query a SQL dataset) implements IEnumerable. So you can 100% manipulate your dataset like a normal array/list.

Sure it comes with its own shenanigans but 90% of the time it’s easy to read and to manipulate.

Performing a query with EF is able to do stuff that can't be done with `IEnumerable`. So that a filter()/.Where() can actually generate a WHERE clause instead of looping over every record.

Yes of course it generates the corresponding SQL and don’t iterate over the table.

But in the framework’s code, IQueryable implements IEnumerable, it’s just a totally different implementation but for the developer it’s 100% the same API and so any IQueryable can be used where a IEnumerable is expected.

This is a hazard that trips people up commonly. If you use an IQueryable where an IEnumerable is expected, it will use brute-force iteration semantics, and not do things like generating a WHERE clause. Linq provides similar extension methods for both interfaces, but you need to be sure your call resolves to the right interface, otherwise you'll end up doing things like pulling the whole table into memory.

I love your syntax for joins and unions!

A bit puzzled by why the connector slots into the query, instead of the query slotting into the connector, given that it’s the connector that’s actually doing the work. I.e. ‘connector.fetch(query)‘ … rather than… ‘query.fetch(connector)‘

It was more of an ergonomics choice. To me it seems like it's more readable to write `await users.filter(user => user.id.eq(42).fetch(connector)` instead of `await connector.fetch(users.filter(user => user.id.eq(42))`.

But I might be wrong, your idea makes more sense from logical perspective.

What about moving the connector to the table declaration, similar to dbcontext in .net?

Something like Q.table(definition, connector), which would then allow you to just write users.filter(user => user.id.eq(42).fetch()

I’m not sure I can say which is _objectively_ better, but I was also surprised, connector.fetch would be more consistent with common JS practices

It is dope, please continue on this.

I used to work with TypeORM and really missed using EntityFramework. That actually led me to switch to Mongo (Mongoose).

I'm really looking forward to this project!

I've big plans for Qustar, thanks for kind words!

I never use orms and don’t find them appealing, but one thing I do with my sqls may interest you.

I always wrap .query(…) or simply pass its result to a set of quantifiers: .all(), .one(), .opt(), run(), count(). These assert there’s 0+, 1, 0-1, 0, 0 rows.

This is useful to control singletons (and nonetons), otherwise you end up check-throwing every other sql line. One/opt de-array results automatically from T[] to T and T | undefined. Count returns a number.

Sometimes I add many() which means 1+, but that’s rare in practice, cause sqls that return 1+ are semantically non-singleton related but business logic related, so explicit check is better.

I also thought about .{run,count}([max,[min]]) sometimes to limit destructiveness of unbounded updates and deletes, but never implemented that in real projects.

Maybe there’s a better way but I’m fine with this one.

Edit: messed up paragraphs on my phone, now it’s ok

Interesting, it throws an error if result rows don't match expected quantity?

Yes, and together with .in_transaction(cb) wrapper it also rolls everything back. Sadly SQL itself doesn't have something like ASSERT ROWCOUNT <expr>, cause it's such an obvious check, especially in destructive ops. LIMIT exists, but it is silent and quirky with non-SELECTs.

This is a really cool project, but I'm not sure I like some of the APIs.

`orderByDesc` seems like it could be better suited for an object constant indicating the sort direction.

``` orderBy(OrderBy.Desc, user => user.age) ```

Overall still very nice and looking forward to seeing more development!

Oh, that’s Entity Framework but in typescript ?

Exactly! Qustar was heavily inspired by EF.

Why are we trying so hard to pretend the database is something else?

I've had more success modelling database concepts directly in the language; tables, columns, keys, indexes, queries, records etc.

https://github.com/codr7/hostr/tree/main/src/Hostr/DB

This is like the lambda / Linq on .NET. Well done. Take a look at PRQL too. You may enjoy it, it may even help you simplify query transformations to sql.

Thanks for this. While I have no problem with SQL, I enjoy the type checking, autocomplete, and 'compilation' this TS syntax gives you. Please continue!

Same, I hope Qustar will provide better developer experience than raw SQL without sacrificing flexibility.

It looks like this isn't really an ORM, it's more like a node-based layer to simplify DB access.

Which I actually like more, because I want to understand the database, not abstract it away. But dealing with SQL is/can be awkward. This library means I don't have to dynamically build sql queries in code.

Handy!

A while back I wanted to do a project in NodeJS to refresh my JS skills a bit, wanted to find a nice ORM similar to EF because I use it so frequently but unfortunately didn't come across anything.

Ended up using drizzle and just hated every moment of it. This is definitely going in the "Use this eventually" folder!

I might have missed it but I would like to see what the return types look like, and how type safe they are. The query interface is interesting, I'm not sure I'm sold but if I don't know how to use the result then I'm not going to adopt it.

Pretty cool! The only thing I didn't like in the examples were things like .eq and .add, which are kind of a DSL, so it takes away from the "just plain JS" approach. But I assume it's because JS doesn't allow operator overloading?

Yep, I would love to use plain "==" and "+", but JS doesn't support it.

I've seen (and implemented myself) operator overloading based systems for query builder type things ... and the way such facilities work in every language I've seen/tried them in has had enough limitations that it wasn't really a great idea in the end anyway.

https://p3rl.org/DBIx::Perlish does it pretty nicely, but only because instead of using operator overloading the author lets the query code compile as a lambda and then pulls apart the perl5 VM opcodes and translates -those- into a query, which is ... awesome in its own way but not something you'd want to try and reproduce.

Interestingly, Scala actually turns 'x + y' into 'x.+(y)' and you could maybe get somewhere with that style.

For javascript, you'd probably need instead to provide a Babel transform and rely on the fact that like 90%+ of javascript projects are already 'compile to javascript' code except that the source is also sort of javascript.

My plan instead is to have an API much like yours (... or possibly just (ab)use yours, see my other comment ...) and then a format string based DSL for nicer querying.

... now that I think about it, making the DSL I have in mind work with qustar might be a good "dual implementations keep you honest" thing, but I have a lot of yaks to shave before that becomes relevant, so please nobody hold your breath.

You can achieve some hacky form of operator overloading by implementing the “well-known” Symbol.toPrimitive, and exploiting the fact that the addition operator coerces its operands to either a Number or String.

It won’t be perfect but maybe you can do something useful with it. Symbols in general are a really powerful tool that almost enable meta-programming in JS. I searched “Symbol” in your repository and didn’t see any results, so if you aren’t familiar with them, I recommend taking the time to read up on how you can use them.

See: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe...

And this 2015 blog: https://www.keithcirkel.co.uk/metaprogramming-in-es6-symbols...

I am not sure I am understanding array-like in this context?

It seems to be more like knex or https://kysely.dev/

The "array-like" refers to the similar interface of the ".map" and ".filter" methods between Array and Q.table

This looks really nice. It's not so much an ORM as a embedded DSL for SQL. The raw SQL with the tagged template literal is quite nice too.

Just like we did HTML in JS via JSX or lit html, I wonder if we should have better SQL in JS that way.

Qustar sounds nice, I would think "Exact" is what it is.

So basically like entity framework & LINQ in the C# world

but for nodejs

Very cool. Reminds me of linq to sql

Yes, as an efcore fan, I often wish that we had better ORM in my company's node projects. Sequelize seriously drives me insane

A jooq-like for TypeScript (as vanilla JS would kind of defy jooq's purpose) would be really nice.

I'm not sold on ORMs. They make the easy queries slightly easier, and have no solution more complex queries. Not worth the learning-curve (life times, caching, dirty state, associations, cascading, mapping, etc)

"Codegen free"

why is codegen bad?

it adds complexity to your build process

can i suggest saying “iterator api” instead of array-like?

> array-like API

why is this arbitrary property desirable?

I think they mean functional

The API doesn’t really look “array-like”.

When I think of "array-like," I envision using brackets [i].

But the OP isn't wrong; all the methods used to construct the query also function as instance methods of arrays in both JavaScript and TypeScript.

I've come to the conclusion that ORMs are good for simple queries like User.find_by(email: "john@snow.com"), but once you get beyond that you are better off just writing sql.

I agree, classic ORMs usually don't play well with complex queries.

I think Qustar is closer to a query builder than ORM tbh. You can compose arbitrary queries using it.

People have often said of https://p3rl.org/DBIx::Class that's it's more a Relational to Object Mapper than an Object to Relational Mapper.

We've (I was the original author, bias alert) always had a policy of "if you can't convince it to produce the exact same query that you'd've written by hand, that's either a bug or a missing feature."

Some of said features do still remain missing, because of course they do, but the attitude is hugely important nevertheless.

You're doing an awesome thing here, and ... I've been considering trying to write a better ROM for JS on and off for a while, and though I may still do so anyway, assuming my sieve-like brain doesn't forget about qustar first I think I should really talk to you about whether we can work together instead before I strike out on my own.

> I've come to the conclusion that ORMs are good for simple queries like User.find_by(email: "john@snow.com"), but once you get beyond that you are better off just writing sql.

When your queries become very complex having a good ORM like SQLAlchemy in Python is a life-saver.

It might be because I'm not used to SQL, but I've found the opposite. Writing a large query with lots of conditions (eg, if the user is signed in, hiding content they've blocked) is miserable without an ORM that can build the query and map the results.

I don't like ORMs for lots of reasons but I find them a necessary evil. How do you deal with that in plain SQL, when a query can look completely different depending on the variables?

A good query builder is more important than ORM imo. That's what I like about sqlalchemy. The query builder pretty much maps 1:1 to SQL and you can use it with or without the ORM mapping. Most of my projects have a mix of both along with plain SQL for some of the meatier queries.

It's like a bell curve for me personally. The things I do with databases between the 25th and 75th percentiles of query complexity fit into an ORM or query builder tool nicely. These also tend to be the bulk of what I need. But those tools add more overhead to the effort of writing simple <25th percentile SQL (like small single-table queries), and then end up being wholly inadequate for the complexity above the 75th percentile, where the library often doesn't have good representation or documentation for the complex things a database can actually do for you in advanced use cases (leading you to do a less efficient similar operation in your programming language), or they try to be too generic to many databases and then aren't able to provide anything that might be specific to Postgres which leads you back to writing raw SQL again.

Scala has a library called Slick which takes a similar approach: https://scala-slick.org

The DSL is nice for simple querying and for composing queries based upon user input. But, for anything slightly complex, I found it's better to just use regular SQL.

Reply on news.ycombinator.com