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.
- 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)
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.
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.
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.
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.
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.
It's not released yet, but give it a look :) (v0.1 is almost done)
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.
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.
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
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.
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.
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.
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.
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.
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))
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.
Continue doing the excellent work please!
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?
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.
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.
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)‘
But I might be wrong, your idea makes more sense from logical perspective.
Something like Q.table(definition, connector), which would then allow you to just write users.filter(user => user.id.eq(42).fetch()
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 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
`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!
I've had more success modelling database concepts directly in the language; tables, columns, keys, indexes, queries, records etc.
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!
Ended up using drizzle and just hated every moment of it. This is definitely going in the "Use this eventually" folder!
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...
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.
It seems to be more like knex or https://kysely.dev/
but for nodejs
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)
why is codegen bad?
why is this arbitrary property desirable?
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?
I think Qustar is closer to a query builder than ORM tbh. You can compose arbitrary queries using it.
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.
When your queries become very complex having a good ORM like SQLAlchemy in Python is a life-saver.
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.
WHERE (name LIKE :name OR :name IS NULL)
AND (city = :city OR :city IS NULL)
AND ...
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)
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.
But this one looks nice too.
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.
AFTER you learned to use it hahahhahahhhhahha - evil laughter.
Ps I love hibernate.