[HN Gopher] Show HN: Node.js ORM to query SQL database through a...
___________________________________________________________________
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.
Author : tilyupo
Score : 70 points
Date : 2024-09-05 11:55 UTC (11 hours ago)
(HTM) web link (github.com)
(TXT) w3m dump (github.com)
| EarthLaunch wrote:
| 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 wrote:
| 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.
| pjerem wrote:
| Oh, that's Entity Framework but in typescript ?
| tilyupo wrote:
| Exactly! Qustar was heavily inspired by EF.
| fourseventy wrote:
| 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.
| tilyupo wrote:
| 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.
| mst wrote:
| 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.
| hk__2 wrote:
| > 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 wrote:
| 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?
| eglintondust wrote:
| 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.
| eglintondust wrote:
| 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.
| evilduck wrote:
| 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.
| layer8 wrote:
| The API doesn't really look "array-like".
| v_b wrote:
| 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.
| khy wrote:
| 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.
| bearjaws wrote:
| I am not sure I am understanding array-like in this context?
|
| It seems to be more like knex or https://kysely.dev/
| jitl wrote:
| The "array-like" refers to the similar interface of the ".map"
| and ".filter" methods between Array and Q.table
| Eric_WVGG wrote:
| 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 wrote:
| 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.
| jgoyvaerts wrote:
| 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()
| nsonha wrote:
| > array-like API
|
| why is this arbitrary property desirable?
| shortrounddev2 wrote:
| I think they mean functional
| todotask wrote:
| Qustar sounds nice, I would think "Exact" is what it is.
| arrty88 wrote:
| Very cool. Reminds me of linq to sql
| shortrounddev2 wrote:
| Yes, as an efcore fan, I often wish that we had better ORM in
| my company's node projects. Sequelize seriously drives me
| insane
| cies wrote:
| 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)
| v_b wrote:
| 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 wrote:
| I've big plans for Qustar, thanks for kind words!
| sigseg1v wrote:
| 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 wrote:
| 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!
| gedy wrote:
| 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 wrote:
| Same, I hope Qustar will provide better developer experience
| than raw SQL without sacrificing flexibility.
| richwater wrote:
| 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!
| marcelr wrote:
| can i suggest saying "iterator api" instead of array-like?
| anonzzzies wrote:
| 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 wrote:
| Thanks! I miss Entity Framework too, one of the best ORMs out
| there.
| brap wrote:
| 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 wrote:
| Yep, I would love to use plain "==" and "+", but JS doesn't
| support it.
| mst wrote:
| 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.
| chatmasta wrote:
| 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...
| efitz wrote:
| 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.
| arnorhs wrote:
| 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 wrote:
| 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?
| onion90 wrote:
| 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.
| EGreg wrote:
| "Codegen free"
|
| why is codegen bad?
___________________________________________________________________
(page generated 2024-09-05 23:00 UTC)