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

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

#111

Post by twodave » Fri, Sep 04, 2020, 12:32 AM UTC

Seb-C wrote: post #25

twodave wrote: post #6

Earlier quoted context omitted.

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 t…

Thanks! It was a bit tricky to solve the transactions problem, because I did not want to abstract the BEGIN / COMMIT / ROLLBACK instructions, but at the same time I needed to provide something to ensure the integrity of a transaction made of multiple commands. As for the audit fields, I decided not to include it because it very simple to implement, and would be clearer to do it specifically. Most of those fields coul…

Another interesting way to solve the transaction problem is following more of a Unit of Work pattern. Then the transaction is more or less held until the unit is committed. Or rolled back. This is also wonderful for writing integration tests because your repositories all contribute to the same UoW and your test can just roll back when it’s finished to return to a clean state.

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

#112

Post by ben509 » Fri, Sep 04, 2020, 12:36 AM UTC

cryptica wrote: post #87

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…

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.

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

#113

Post by christophilus » Fri, Sep 04, 2020, 2:04 AM UTC

exevp wrote: post #26

midrus wrote: post #11

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…

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 ? 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 actuall…

> How many hours...?

Plenty. Just this week, I spent the better part of a day trying to figure out why customers were losing data. Turns out it was a bad ActiveRecord polymorphic definition that was written over 4 years ago, but which only started surfacing recently due to overlapping ids in the relevant tables. When I looked at the generated SQL, the bug was obvious. But looking only at the ActiveRecord definitions, the bug was nonobvious. Give me plain SQL all day everyday. I’m tired of ORMs.

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

#114

Post by Seb-C » Fri, Sep 04, 2020, 2:26 AM UTC

Dragory wrote: post #62

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…

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.

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

#115

Post by ummonk » Fri, Sep 04, 2020, 2:38 AM UTC

gmac wrote: post #75

Seb-C wrote: post #2

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 i…

My own TypeScript non-ORM, Zapatos, shares much of the same design philosophy (and indeed a sql`...` tagged template function): https://jawj.github.io/zapatos/ Previous discussion: https://news.ycombinator.com/item?id=23273543

Wow this is amazing. Really bridges the last remaining gap to close the type-safety gap from SQL -> TS/Node.JS -> GraphQL -> TS / client code.

Exactly the kind of "use SQL in typescript code with type-safety" non-ORM that I've always wanted.

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

#116

Post by Seb-C » Fri, Sep 04, 2020, 2:56 AM UTC

holgerw wrote: post #58

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…

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 :) .

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

#117

Post by Seb-C » Fri, Sep 04, 2020, 2:56 AM UTC

holgerw wrote: post #65

holgerw wrote: post #58

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…

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 e…

This multi-api indeed seems similar to the sequence function.

If you can try-catch the failure in the external service, you can rollback the transaction with kiss-orm. Actually kiss-orm does not abstract the transaction itself, so you can do whatever you want.

I just realized a flaw in my current implementation, which is that directly using the repository CRUD methods from inside the sequence (rather than a query) function would execute those operations outside the scope of the sequence.

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

#118

Post by exevp » Fri, Sep 04, 2020, 8:17 AM UTC

Seb-C wrote: post #109

exevp wrote: post #55

Earlier quoted context omitted.

> In my experience this is just a very, very small percent of the cases. Most of the time I find myself doing pretty simple CRUD operations, and the boilerplate for the simple cases goes out of hand quickly, specially when running joins. That's true, although the most frustrating percent of the cases :) I think what KISS will eventually need is some simple toolbelt for the basic CRUD queries, e.g. expanding lists of…

> expanding lists of column names I am not sure about what do you mean here? > dealing with casing (snake_case to camelCase) This is one of the opinionated parts of kiss-orm I guess, because I specifically do not want to implement this. I think having consistency in the naming of the properties/columns is more important. I would rather break the naming convention by having snake_case properties than automagically ren…

> I am not sure about what do you mean here?

Given i have defined the columns of interest in my data objects anyways...

  class User { name = null; email = null; }

... i wouldn't necessarily have to repeat them when writing my SQL:

   sql`SELECT ${Object.getOwnPropertyNames(new User).map(camelCase).join(', ')} FROM users`

Of course using a better helper function.

There's a few repetitive tasks when writing SQL where don't necessarily need a powerful query builder but still end up writing a few handler functions. Shipping some common helpers with the framework might be handy.

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

#119

Post by 2color » Fri, Sep 04, 2020, 8:30 AM UTC

exevp wrote: post #88

2color wrote: post #60

Earlier quoted context omitted.

Thanks for sharing your thoughts about Prisma. > Plus i have a hard time seeing the benefit of Prisma Prisma is supposed to improve your productivity and confidence when working with a database. It does so with a strong focus on type safety. Most ORMs and query builders in the Node.js/TypeScript ecosystem do not provide the level of type safety that Prisma does. For example in a blog with users and posts (1:n) queryi…

I understand where Prisma is coming from with the custom DSL: they want to guarantee type safety and therefore need to know exactly the structure of the types the result set is supposed to be mapped to. In most other languages you'd shout "reflection" but unfortunately, there is no such thing in TS. Hence the custom DSL so you know, while parsing, what the structure of the type is. I'm just asking myself: why invent…

> I'm just asking myself: why invent the custom DSL for that?

Fair question. Besides all the type safety features I mentioned above, having the DSL (Prisma schema) allows generating database clients in more than one language, e.g. Go without the database declaration being tied to a specific programming language.

It's also the reason it's declarative in contrast with most ORMs that rely on an imperative language to define these mappings.

The second reason is that the Prisma schema is used for declarative database migrations with Prisma Migrate.

The third reason is that Prisma supports introspection of an existing database. So if you were to introduce Prisma to an existing project, you'd introspect the database which would populate the Prisma schema based on the database schema. This would then allow you to use Prisma for migrations.

Could all that be achieved without a custom DSL? Perhaps. But it'd probably tie Prisma to a specific language ecosystem and would diminish the developer experience of the features it offers.

I can understand the reluctance around a new DSL, but in reality, I haven't seen many complaints about the need to use it.

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

#120

Post by 2color » Fri, Sep 04, 2020, 8:41 AM UTC

vosper wrote: post #98

nerdkid93 wrote: post #63

Earlier quoted context omitted.

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…

> 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 tha…

Hasura is a GraphQL layer for the database. It introspects a PostgreSQL database and creates a GraphQL API which exposes access/operations to the database.

Prisma is a toolkit that consists of a database client (Prisma Client), a migrations tool (Prisma Migrate), and a database IDE/UI (Prisma Studio). The generated Prisma Client is in TypeScript and can be imported into a Node.js application.

While Prisma can be used to build a GraphQL API that connects to a database, Prisma is completely agnostic to the GraphQL tools you use. (https://www.prisma.io/docs/understand-prisma/prisma-in-your-...)