Show HN: Node.js ORM to query SQL database through an array-like API
Qustar is a TypeScript library for querying SQL databases, supporting PostgreSQL, SQLite, MySQL, and MariaDB, with a high-level query builder and immutable queries for safe reuse.
Read original article
Qustar is a TypeScript library that facilitates querying SQL databases through an array-like API. It features a high-level query builder that allows users to construct SQL queries in an expressive manner. The library is fully compatible with TypeScript and supports multiple databases, including PostgreSQL, SQLite, MySQL, and MariaDB, with ongoing support for SQL Server and Oracle. Qustar includes navigation properties for easy data traversal, the ability to execute raw SQL queries, and ensures query immutability for safe reuse. To get started with Qustar using PostgreSQL, users can install it via npm and follow a simple example that demonstrates how to define a schema, compose a query, connect to the database, and execute the query. The library offers specific drivers for each supported database, such as `qustar-pg` for PostgreSQL and `qustar-better-sqlite3` for SQLite. Qustar is licensed under the MIT License, and further details can be found on its GitHub repository.
- Qustar is a TypeScript library for querying SQL databases.
- It supports PostgreSQL, SQLite, MySQL, and MariaDB, with SQL Server and Oracle in development.
- The library features a high-level query builder and raw SQL execution.
- Queries are immutable, promoting safe reuse.
- Qustar is licensed under the MIT License.
Related
First Contact with SQLite
The article explores surprising aspects of SQLite, like limitations in altering columns and data types. It notes the recent jsonb support and handling date/time values, praising SQLite's streaming features but favoring PostgreSQL.
PostgREST – Serve a RESTful API from Any Postgres Database
PostgREST creates RESTful APIs from PostgreSQL databases, offering high performance, security via JWT, multiple API versions, self-documentation with OpenAPI, and community support for contributions and sponsorships.
SQLite vs. PostgreSQL
SQLite is ideal for simple, self-hosted projects with low latency, while PostgreSQL is better for applications needing advanced features and geographical distribution. The choice depends on project requirements.
Postgres.new: In-browser Postgres with an AI interface
postgres.new is an in-browser Postgres sandbox that integrates AI assistance for managing databases, supporting features like CSV imports, report generation, and semantic search, with future cost-effective deployments planned.
Show HN: PGlite – in-browser WASM Postgres with pgvector and live sync
PGlite is a lightweight, embeddable Postgres database under 3MB, operating in a WebAssembly environment, supporting dynamic extensions, reactive programming, and AI-driven database creation via Supabase.
- Many users appreciate the TypeScript integration and the potential for type safety and autocomplete features.
- There are concerns about the effectiveness of abstractions, with some commenters arguing that SQL is valuable and should not be overly abstracted.
- Comparisons are made to existing ORMs and libraries, such as Drizzle and Entity Framework, with users expressing interest in how Qustar differentiates itself.
- Some commenters highlight the importance of preventing SQL injection and request more documentation on query parameterization.
- Overall, there is a desire for a balance between ease of use and the power of raw SQL, with some advocating for a more straightforward approach.
35 comments- 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)
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.
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?
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)‘
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 seems to be more like knex or https://kysely.dev/
but for nodejs
why is codegen bad?
why is this arbitrary property desirable?
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.
Related
First Contact with SQLite
The article explores surprising aspects of SQLite, like limitations in altering columns and data types. It notes the recent jsonb support and handling date/time values, praising SQLite's streaming features but favoring PostgreSQL.
PostgREST – Serve a RESTful API from Any Postgres Database
PostgREST creates RESTful APIs from PostgreSQL databases, offering high performance, security via JWT, multiple API versions, self-documentation with OpenAPI, and community support for contributions and sponsorships.
SQLite vs. PostgreSQL
SQLite is ideal for simple, self-hosted projects with low latency, while PostgreSQL is better for applications needing advanced features and geographical distribution. The choice depends on project requirements.
Postgres.new: In-browser Postgres with an AI interface
postgres.new is an in-browser Postgres sandbox that integrates AI assistance for managing databases, supporting features like CSV imports, report generation, and semantic search, with future cost-effective deployments planned.
Show HN: PGlite – in-browser WASM Postgres with pgvector and live sync
PGlite is a lightweight, embeddable Postgres database under 3MB, operating in a WebAssembly environment, supporting dynamic extensions, reactive programming, and AI-driven database creation via Supabase.