10 ms·
Show HN: Node.js ORM to query SQL database through an array-like API
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.
- nsonha 2y ago> array-like API why is this arbitrary property desirable?
- shortrounddev2 2y agoI think they mean functional
- wredcoll 2y ago[flagged]
- deleted 2y ago[deleted]
- EarthLaunch 2y agoAn 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 2y agoIt’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.
- recursive 2y agoPerforming 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.
- pjerem 2y agoYes 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.
- recursive 2y agoThis 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.
- dangsux 2y ago[dead]
- pjerem 2y agoOh, that’s Entity Framework but in typescript ?
- tilyupo 2y agoExactly! Qustar was heavily inspired by EF.
- thr0w 2y ago[flagged]
- deleted 2y ago[deleted]
- fourseventy 2y agoI'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.
- tilyupo 2y agoI 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.
- mst 2y agoPeople have often said of https://p3rl.org/DBIx::Class 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.
- deleted 2y ago[deleted]
- hk__2 2y ago> 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.
- notsylver 2y agoIt 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?
- matt-p 2y ago[flagged]
- deleted 2y ago[deleted]
- khy 2y agoScala has a library called Slick which takes a similar approach: https://scala-slick.org 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.
- deleted 2y ago[deleted]
- layer8 2y agoThe API doesn’t really look “array-like”.
- deleted 2y ago[deleted]
- v_b 2y agoWhen 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.
- deleted 2y ago[deleted]
- bearjaws 2y agoI am not sure I am understanding array-like in this context? It seems to be more like knex or https://kysely.dev/ https://kysely.dev/
- deleted 2y ago[deleted]
- jitl 2y agoThe "array-like" refers to the similar interface of the ".map" and ".filter" methods between Array and Q.table
- Eric_WVGG 2y agoI 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 2y agoIt 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.
- deleted 2y ago[deleted]
- jgoyvaerts 2y agoWhat 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()
- williamdclt 2y agoI’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
- todotask 2y agoQustar sounds nice, I would think "Exact" is what it is.
- arrty88 2y agoVery cool. Reminds me of linq to sql
- shortrounddev2 2y agoYes, as an efcore fan, I often wish that we had better ORM in my company's node projects. Sequelize seriously drives me insane
- cies 2y agoA 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)
- _1 2y ago[flagged]
- randomdata 2y agoNo, 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.
- enobrev 2y agoIn 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.
- hahn-kev 2y agoYes, 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.
- suchar 2y agoSometimes you can avoid writing multiple queries with different filters by creating single parameterized query with conditions like: WHERE (name LIKE :name OR :name IS NULL) AND (city = :city OR :city IS NULL) AND ... By no means it is perfect, but can save you from writing many different queries for different filters while being easy to optimize by db (:name and :city are known before query execution). Still, I prefer explicit SQL in webservices/microservices/etc. the code and its logic is "irrelevant" - we care only about external effects: database content, result of a db query, calls to external services (db can be considered to be nothing more than an external service). And it's easier to understand what's going on when there is one less layer of abstraction (orm)
- v_b 2y agoIt 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 2y agoI've big plans for Qustar, thanks for kind words!
- sigseg1v 2y agoCool 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 2y agoQustar 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!
- gedy 2y agoThanks 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 2y agoSame, I hope Qustar will provide better developer experience than raw SQL without sacrificing flexibility.
- richwater 2y agoThis 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!
- marcelr 2y agocan i suggest saying “iterator api” instead of array-like?
- anonzzzies 2y agoVery 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!
- brap 2y agoPretty 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 2y agoYep, I would love to use plain "==" and "+", but JS doesn't support it.
- mst 2y agoI'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 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.
- chatmasta 2y agoYou 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/Reference/Global_Objects/Symbol/toPrimitive#modifying_primitive_values_converted_from_an_object https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe... And this 2015 blog: https://www.keithcirkel.co.uk/metaprogramming-in-es6-symbols/ https://www.keithcirkel.co.uk/metaprogramming-in-es6-symbols...
- efitz 2y agoNow 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.
- ARandomerDude 2y agoYou'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.
- dangsux 2y ago[dead]
- l5870uoo9y 2y agoWhat I find valuable is that many ORMs provide type support and manage migrations, not so much the day-to-day interaction with the database.
- arkh 2y ago> 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.
- arnorhs 2y agoNice, 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 2y agoHmm. 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?
- deleted 2y ago[deleted]
- onion90 2y agoThe "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.
- EGreg 2y ago"Codegen free" why is codegen bad?
- joseferben 2y agoit adds complexity to your build process
- xonix 2y agoMy 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)
- samstave 2y agoHave 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.
- DimmieMan 2y agoI’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.
- tstrimple 2y agoThis 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.
- ericyd 2y agoI 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.
- jdthedisciple 2y agoSo basically like entity framework & LINQ in the C# world but for nodejs
- wruza 2y agoI 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 2y agoInteresting, it throws an error if result rows don't match expected quantity?
- wruza 2y agoYes, 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.
- gsck 2y agoA 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!
- tehlike 2y agoThis 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.
- spankalee 2y agoThis 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 2y agoJust like we did HTML in JS via JSX or lit html, I wonder if we should have better SQL in JS that way.
- mannyv 2y agoIt 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!
- codr7 2y agoWhy 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 https://github.com/codr7/hostr/tree/main/src/Hostr/DB
- FutureCrafter 2y ago[dead]