top | item 24363493

Show HN: ORM for TypeScript with no query-builder, supporting full SQL queries

220 points| Seb-C | 5 years ago |github.com

119 comments

order
[+] Seb-C|5 years ago|reply
For a long time, I have been frustrated with the state-of-the-art about the existing ORMs. I like things to be simple, but somehow all ORMs seems to be bloated and overcomplicate a lot of things.

When designing a new project, I have been trying to find a more satisfying design while avoiding the existing ORMs. I especially wanted to use proper SQL rather than reducing it's syntax to fit it in another language.

This is the result of my experiments. This is a completely new balance between productivity, simplicity and readability, while providing useful features.

I use the template-string tagging syntax to allow writing and concatenating raw SQL queries, while keeping it safe from injections, which allowed me to build kiss-orm. There is no query-building involved, you can freely use the full-power of SQL.

I would appreciate feedback on this (and contributions if you love it :D ).

[+] waheoo|5 years ago|reply
Still not very convinced your ORM is solving a problem it didn't create but I certainly like the approach more than more traditional ORMs.

I feel like Im not really the type that wants an ORM to take care of SQL or relational functionality, all I really want is an object mapper at the edges, going in, I want to pass an object and have it map into the right fields, coming out, I want it mapped to the right place.

Doing stuff like preloading or relation loading should be done through convention or by database schema querying.

Its a tricky problem to solve, while some relational mapping is super helpful to prototype it almost always results in a mess down the line where just writing SQL upfront prevents pain later.

[+] twodave|5 years ago|reply
This is great. I built something a bit lower-level than this targeting MySQL not long ago. This library has taught me a couple language features I didn't know, however. The SQL tag is pretty clever. I was pleasantly surprised to see transaction support (I feel like people who don't actually use their libraries in real products tend to leave this kind of thing out).

I noticed the support for soft-delete (which seems to be a simpler thing in PgSql than in other relational db systems), which is also nice. I think another fairly easy, generic win would be a way to specify that a model has audit tracking fields (createdAt/By, modifiedAt/By, deletedAt/By, etc.). I know some database servers also have a way to track this separately (no idea whether PgSql specifically does), but there are use cases for showing that kind of stuff at an application level as well (and also can make ETL type jobs a bit less painful).

All in all, great work. It looks very polished!

[+] thunderbong|5 years ago|reply
In Ruby, I have always been extremely fond of the Sequel ORM - http://sequel.jeremyevans.net/

The amount of expressiveness and capability it gives is simple outstanding. It lets me drop into pure SQL as well as write statements which are simpler and easire to grok when I'm using the ORM level abstractions.

Sequel for SQL users - http://sequel.jeremyevans.net/rdoc/files/doc/sql_rdoc.html

Cheat sheet - http://sequel.jeremyevans.net/rdoc/files/doc/cheat_sheet_rdo...

[+] axlee|5 years ago|reply
I'm extremely happy with Django's ORM. It never felt bloated or overcomplicated to me, especially when compared with the innate verbosity and complexity of SQL.
[+] mqus|5 years ago|reply
What do you think about ORMs like Androids Room[1], where you do specify your own queries with sql (by annotating abstract methods) and can bring your own model and the ORM creates the database schema in the database and gives you access to easy insertion methods.

I don't know how Room does it but darts floor library(which is similar)[2] then generates the code that is necessary, in addition to a very slim runtime layer for managing update events. I find that this is also an approach which in some ways only does half the work for more flexibility, while still depending on a fixed database schema.

[1] https://developer.android.com/training/data-storage/room

[2] Shameless plug: https://github.com/vitusortner/floor

[+] marius_k|5 years ago|reply
thanks. I love it! It is not clear where the `article.id` comes from in many-to-many example. Anyway I don't think I would use those relationships patterns, instead I would just load and operate in plain (shallow) model objects.
[+] exevp|5 years ago|reply
tl;dr: thank god it finally has been done.

Long version: i've been seriously frustrated with the state of ORM (in Javascript in particular) for years.

Javascript ORM are nice and handy if all you're doing is simple CRUD stuff. If you're starting with more complex relational queries (we're using an RDBMS so why wouldn't we?) you quickly reach the limits of what the ORM can map. If you start doing more complex aggregations or stuff like window functions and the likes, you most certainly have to fallback to raw queries, usually rendering the whole mapping function of the ORM completely useless.

Also projects like Knex.js (or for example HQL in the Java world) look nice at first but are mostly useless IMHO because they just replace SQL with another syntax you have to learn. Why stay with the language everybody familiar with RDBMS can speak if you can invent some useless abstraction, right? And please don't tell me you want to support multiple RDBMS in the same codebase. How often is this really an important use-case?

I really loved the way MyBatis did this in Java: instead of mapping tables to objects, mapping result sets to objects and leaving the full power of SQL to the developer.

Always wanted (and actually started something almost the same as you did some weeks ago) to basically do MyBatis in Javascript and never had the time to.

Thanks for getting it started.

[+] midrus|5 years ago|reply
All these things fall short the moment you need real "production" features, such as reliable migrations (writing them by hand? no thanks. Outsourcing to another library like knex? no thanks), transactions, community support, relationship/nested/join queries without a ton of boilerplate and being battle tested.

So far, the best thing I've found in the node ecosystem is Prisma [1], and it's better than the alternatives by a very long shot in my opinion.

[1] https://www.prisma.io/docs/understand-prisma/why-prisma

[+] nicoburns|5 years ago|reply
I actually much prefer writing migrations by hand. It's actually pretty simple, and it gives you complete control over the schema.
[+] nerdkid93|5 years ago|reply
We investigated using Prisma v2 as a way of auto-creating a GraphQL API that directly interfaces with a PostgreSQL database, but we immediately pivoted to other solutions as soon as we discovered that the Prisma Client is really spinning up a behind-the-scenes GraphQL rust server itself to access the db. The performance of fetching a mildly complicated query (3 joins) ended up being more than three times slower than Hasura or PostGraphile.

For the life of me, I couldn't figure out why it's desirable to have an intermediary GraphQL server issuing database requests. Is that something you have also discovered while using Prisma?

[+] Seb-C|5 years ago|reply
> reliable migrations (writing them by hand? no thanks. Outsourcing to another library like knex? no thanks)

IMHO, "real production features" means also caring about the long term maintenance, performance and reliability of the data.

My experience with ORMs (especially ActiveRecord patterns) is that it always becomes a mess once you reach a certain level of complexity. It is very fast to get started, but gets slower over time once you start having a lot of bugs and hard-to-solve behaviours.

[+] exevp|5 years ago|reply
I find that most of the production features you mentioned are actually more difficult using a fat ORM.

How many hours have i wasted figuring out how i can write and map some complex joins or aggregation query with <insert ORM name here>? Would have been a 3 minute task if all i had to write was just SQL ...

Plus i have a hard time seeing the benefit of Prisma. You are learning an entirely new DSL just to define your schema - which actually isn't that far off standard JS or TS syntax-wise so it feels like a complete waste of time to come up with the DSL in the first place. I can only imagine the hard time you have once you first have to break out of the frameworks cage because you hit a case which isn't easily solved by the framework itself ...

[+] xixixao|5 years ago|reply
Looks nice, pretty close to what we use at FB, except we use the builder pattern, so you can compose the query more easily across function calls (instead of passing in a single bag of options to findMany).
[+] bradstewart|5 years ago|reply
This. I've actually used Rails/ActiveRecord migrations in Node projects. More than once...
[+] string|5 years ago|reply
I love Prisma and am a huge fan of Nexus[1] in particular. But it's interesting that you mention transactions as Prisma does not yet support long running transactions[2]. I have a side project that I've been working on for a little while, but I haven't touched it in months as I'm waiting on LRT support. Prisma is being very actively developed though as far as I can see, so I'm confident that there will be a solution for this eventually.

[1] https://nexusjs.org/

[2] https://github.com/prisma/prisma/issues/1844

[+] dathinab|5 years ago|reply
> reliable migrations

My experience is that libraries which do handle that for you automatically do a really bad job at it.

Writing it by hand and most important thoroughly testing it and trying to avoid doing any changes needing complex migrations is in my experience more reliable on the long run.

EDIT: removed inappropriate caps usage

[+] Dragory|5 years ago|reply
It seems you cannot load relationships for a collection of entities easily without N+1 queries, unless I'm missing something. Based on the many-to-many section of the docs (https://github.com/Seb-C/kiss-orm#many-to-many), I would have to load relationships for each entity separately, and then if they have further nested relationships, run a query for each again. The subsequent section also mentions eager loading is not supported.

For me, being able to load relationships (and especially nested relationships) with little boilerplate and few queries is probably the most useful feature in an ORM (usually explicitly eager-loaded), so I'm sad to see it's not supported.

[+] Seb-C|5 years ago|reply
Implementing eager-loading with the current philosophy of kiss-orm would be tricky and difficult to use/read. It did not seem a high-priority, so I chose to not implement it for now.

Depending on the ORMs, the definition of eager-loading also varies.

I have seem ORMs doing everything in a single query, returning everything in a single result-set and then de-duplicating everything client-side. This is very messy (and impossible to hack/fix most of the time).

Currently, the way to go would be something like this:

    const articles = await articlesRepository.search(sql`
        "authorId" IN (${sqlJoin(
             users.map(user => user.id),
             sql`, `,
        )})
    `);
    // Dispatch and assign the articles in the collection of users
I could consider having a helper method to make this easier, but I am afraid this would be quite difficult to use.
[+] holgerw|5 years ago|reply
Thank you for creating Kiss ORM. I have even created a HackerNews account to be able to comment on it.

I have been searching for this type of ORM in Typescript for a while. I agree to write raw SQL for queries. So easy and expressive and one less layer of abstraction. I also agree on the value of the respository pattern and methods for CRUD operations to not write this SQL by hand. Making the loading of associations an explicit decision is also the right way to go. The Rails community has good experience with performance surprises of automatic loading of relationships.

Personally I found the most useful ORM in Ecto for the Elixir (Erlang) language: https://hexdocs.pm/ecto/Ecto.html (ignoring the query capability). It follows very much the repository pattern like Kiss ORM. The API is a bit more succinct (you only define a schema for each table and use a generic repository instead of subclassing for each table) but that might be possible only due to Elixir's language capabilities like meta-programming.

One piece of Ecto that might be a win to implement in Kiss ORM is the "Changeset" pattern to give a canonical, succinct and productive solution to validate data (https://hexdocs.pm/ecto/Ecto.Changeset.html#content). For example have a look at how Ecto unifies validation (checked without hitting the DB) and constraints (checked by hitting the DB) in a single API. This type of functionality increases the usefulness of the repository's CRUD operations.

Thank's for your initiative to create KISS ORM. I will sure try it out and follow along it's evolution.

[+] holgerw|5 years ago|reply
Just forgot to mention Ecto's "Multi API", that is worth knowing. Allows to construct a chain of operations as a data structure and to execute it later transactionally. You may even include operations that are part of transactional business logic but that do not hit the DB (like sending an email). (https://hexdocs.pm/ecto/Ecto.Multi.html#module-run)

As I understand KISS ORM's sequence function would also allow to express business logic transactionally and operations beyond the DB. Obviously the rollback would only effect the DB, but other failing operations can at least trigger the rollback, right? I think this is usefull as integration with external services (like email providers, payment APIs..) are really the source of runtime surprise that might fail a business operation and demand a DB rollback.

[+] Seb-C|5 years ago|reply
Wow, thank you for the kind message!

I did not know about Ecto. It is interesting, but I think more abstract than what I would like kiss-orm to become.

About the ChangeSet stuff, from what I understood it is actually already possible in kiss-orm. The main difference being that I decided to not do the validation at runtime, but rely on typescript.

At worst you could have a runtime SQL error (inserting the wrong type of data in the wrong column for example), but the queries would be safe from injection.

You can definitely keep using kiss-orm with the default `any` type for the insert and update operation, but you can also specify it: https://github.com/Seb-C/kiss-orm#advanced-typings This way, your typings have to be right to use those methods. Runtime validation of inputs should not be done in the repository/database/orm layer anyway :) .

[+] golergka|5 years ago|reply
Love to see more options in this space! I've made a switch from traditional ORM (TypeORM and Sequelize) to a similar "light ORM", Pgtyped, and never looked back since.

Like in Kiss, you write queries in SQL. But unlike other "light ORMs", it also provides type safety by generating type declarations by directly connecting to your database instance and type-checking your query templates.

Honestly, I think it's the best of both worlds, and would love to see more developers finally learning SQL and ditching "fat ORMs" that try to hide it under abstraction layers that always end up leaking.

[+] wayneftw|5 years ago|reply
I started using Objection.js and Knex last year and I think it might be the best ORM I've used on any platform.

There's no way I'm going back to writing raw queries. If I did that, eventually I'd rewrite Knex.

If and when I need a raw query, I can already do that with Knex.

[+] timmy-turner|5 years ago|reply
When using raw SQL in strings, I really miss the automatic formatting that is provided for HTML, TSX and TS with prettier.

Raw SQL query strings also do not compose well and I miss auto completion when writing them (yes, I'm a spoiled kid after so much Typescript usage).

As with everybody else, I didn't like existing ORM/builder approaches, so I built and use my own with type-inference: https://github.com/hoeck/typesafe-query-builder. Any feedback would be great because I have the gut feeling that this one has gone way too far on the type astronaut side of things.

[+] hv42|5 years ago|reply
You should be able to do this with IntelliJ e.g. where you can inject a language into a string. This is quite handy as you can reformat and open the string in a separate editor if needed.

The caveat is that it does not work well if you are composing the SQL queries from multiple small parts.

see https://www.jetbrains.com/help/idea/running-injected-sql-sta...

I suppose that other editors or IDE can do similar things.

[+] CoffeeDregs|5 years ago|reply

   sql"SOME SQL";
This looks to be a "tagged template": https://basarat.gitbook.io/typescript/future-javascript/temp.... I'm a TS user but hadn't seen that feature before. AFAICT, the library is using a side-side-side-feature (tagged templates) of TS as the core abstraction [1]. Impressive. Might be a little brittle in the face of significant SQL or query composition?

Anyhow, I'm a heavier-ORM user but that's impressive.

--- [1] https://github.com/Seb-C/kiss-orm/blob/c4b6c9ad2f81337938a9c...

[+] marton78|5 years ago|reply
This is nothing special, it's commonly used e.g. with GraphQL (the `gql` tag).
[+] blaufast|5 years ago|reply
I think its funny that 'opinionated' could be considered a virtue. I'm imagining a charity that uses 'opinionated' to describe itself, or even worse, a person. Even Apple, a brand famous for its strong design stances, does not use that word as a description of its values.
[+] ryanmarsh|5 years ago|reply
This is very nicely done, kudos to the author. I'm sure many will find this useful.

Personally I've gotten away from using SQL RDBMSs. Since I primarily build on AWS I use DynamoDB but the same principle would apply elsewhere. I like to store data in the format that best supports the read model. Event sourcing allows me to change the structure of the read model, or add new read models, or vary strategies depending on data, as needed. I like that I no longer have the impedance mis-match of the normalized relational model.

It was a big jump to make, and I had to unlearn a lot. I'm not dismissing the value of RDBMSs at all. I love them, especially star schemas for analysis. I just want to share that it's ok to not use an relational model for your transaction store.

[+] capnorange|5 years ago|reply
still yet to find an ActiveRecord equivalent for javascript.
[+] haakts|5 years ago|reply
Seems similar to PureORM[1]. It allows you to write regular native SQL and receive back properly structured (nested) pure business objects.

The name pureORM reflects both that it is pure ORM (there is no query builder dimension) as well as the purity of the mapped Objects.

[1] https://github.com/craigmichaelmartin/pure-orm

[+] gigatexal|5 years ago|reply
As a never-ORM guy I really like what this is trying to do. It seems like the best of both worlds. Kudos!
[+] Seb-C|5 years ago|reply
I am glad that it is appreciated! Thank you.
[+] nhumrich|5 years ago|reply
I actually built something almost exactly like this internally. This is awesome. But, its a far cry to call this an ORM. Its just a safe query serializer. ORM takes an object and writes the query for you.
[+] nesarkvechnep|5 years ago|reply
There's still nothing like Elixir's Ecto in JS land...
[+] cryptica|5 years ago|reply
History keeps repeating itself. People never seem to learn anything. At first there were no ORMs, then ORMs became extremely popular and everyone was using them, then everyone learned that ORMs were a very bad idea and we stopped using them, vowing never to make the mistake again... And here we are again in 2020, ORMs are back. They will be in for a while, then out again, then in again....

Same with statically typed vs dynamically typed. First everything was statically typed, then dynamically typed (interpreted) languages gained popularity, developers LOVED not having to wait for the compiler to finish to test their changes; this was a revolution... Now again, we're going back to statically typed languages, everyone uses clunky bundling and transpilation tools which add bloat and everyone is happy to wait 20 seconds to a minute for their code to compile.

Every few years, developers believe the opposite of what they believed before and the consensus seems to be close to 100% every time. It's ridiculous.

[+] lemontruth|5 years ago|reply
That is because many people believe in extremes. After a while people should learn that there is no absolute truth, everything has it's place.

ORM is good sometimes, pure queries are better on others. So what is needed is more like relaxed ORMS.

[+] ben509|5 years ago|reply
That's overly broad. A lot of people jump on the latest trends, especially people who are simply new to development, because there's no way you can know stuff you haven't had time to learn.
[+] nesarkvechnep|5 years ago|reply
What I've learned from my career so far is that developers hate history. They just don't want to learn from experience. They see the new shiny thing and jump on the hype train. Then they come to conclusions like "mongo was not the right choice since we never needed to scale and our data is relational".