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

[tilyupo]

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.

[xonix]

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)

[DimmieMan]

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.

[whizzter]

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.

[reacweb]

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.

[hn_throwaway_99]

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.

[aleclarsoniv]

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

[j45]

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.

[cambaceres]

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

[samstave]

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.

[arnorhs]

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

[anonzzzies]

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?

[efitz]

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.

[l5870uoo9y]

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.

[theonething]

> 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.

[ARandomerDude]

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.

[j45]

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.

[sigseg1v]

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.

[tilyupo]

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!

[anonzzzies]

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!

[tilyupo]

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

[Grazester]

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

[EarthLaunch]

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?

[pjerem]

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.

[Eric_WVGG]

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)‘

[tilyupo]

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.

[v_b]

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!

[tilyupo]

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

[wruza]

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

[hu3]

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

[richwater]

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!

[pjerem]

Oh, that’s Entity Framework but in typescript ?

[tilyupo]

Exactly! Qustar was heavily inspired by EF.

[codr7]

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

[tehlike]

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.

[gedy]

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!

[tilyupo]

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

[mannyv]

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!

[gsck]

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!

[ericyd]

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.

[brap]

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?

[tilyupo]

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

[bearjaws]

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

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

[jitl]

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

[spankalee]

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.

[atishay811]

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

[todotask]

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

[jdthedisciple]

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

but for nodejs

[arrty88]

Very cool. Reminds me of linq to sql

[shortrounddev2]

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

[EGreg]

"Codegen free"

why is codegen bad?

[joseferben]

it adds complexity to your build process

[marcelr]

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

[nsonha]

> array-like API

why is this arbitrary property desirable?

[shortrounddev2]

I think they mean functional

[layer8]

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

[v_b]

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.

[fourseventy]

I've come to the conclusion that ORMs are good for simple queries like User.find_by(email: "[e-mail]"), but once you get beyond that you are better off just writing sql.

[notsylver]

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?

[tilyupo]

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.

[hk__2]

> I've come to the conclusion that ORMs are good for simple queries like User.find_by(email: "[e-mail]"), 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.

[khy]

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.

[hahn-kev]

Yes, in my opinion the biggest problem with straight SQL is dynamic filters. It easily becomes a huge mess and that filter is only good for that one query, sure you can layer on stuff to make it better, but then you might as well use a library.

[enobrev]

In general, I tend to prefer straight SQL, but for a project I expect to maintain over time, I like having an orm. To go further, I like to write and maintain my own orm(s), that auto-generate the classes and functions that I'll be using.

This is mostly for making sure my code is up to date with the database. A migration _requires_ a code-change due to the orm code-gen and thus i can't deploy the migration until I ensure my codebase is ready for it

Overall, I would much prefer native SQL support in whatever language I'm working in. But a light ORM tends not to be a terrible trade-off.

Also I like this style of orm because sometimes the order of definining SQL is annoying to me. I prefer to start with the "from" and the joins, then add the conditions, and finally, the columns, which likely reference the other parts and thus make more sense at the end.

[gmac]

I didn't like any of the existing JS/TS ones, so I wrote my own! https://jawj.github.io/zapatos/

But this one looks nice too.

[randomdata]

No, not really, but they are composable, which in a practical setting is way nicer than having to write a thousand different SQL queries that are almost the same.

Why SQL itself isn't designed to composable, and why we are happy with that remaining the status quo, will remain one of life's mysteries.

[szundi]

If you are tossing around documents or objects with metadata etc, want to remain open for future features and schema modifications, having just 100k users not 100m - so so so comfortable.

AFTER you learned to use it hahahhahahhhhahha - evil laughter.

Ps I love hibernate.