Benefits over Drizzle ORM?

[CanRau]

Why would I choose rdb over Drizzle ORM, or why are you not using/contributing to Drizzle?

A little impatient maybe but also could be beneficial to other stumbling upon this repo

[lroal]

• RDB has richer and more powerful filtering mechanisms. You don’t need to import the operators for equal, not, startsWith and so forth. In RDB it is straightforward:

  db.customer.getOne( db.customer.name.startsWith('Roal') );

• Filtering by nested relations is not possible in Drizzle #1069 . In RDB you can filter in nested relations by the all(), any() and exists() in an intuitive way:

  db.order.getOne( db.order.lines.any(x => line.product.startsWith('foo')) );

• Drizzle does not support http transport. In RDB you can use the same API from the browser as in the backend (except for raw sql and transactions which are not safe from browser).

• Drizzle is not database agnostic. It has a different syntax in the mapping for each kind of database.

• In Drizzle you have to manually write conflict resolutions on insert/upsert - which has different syntax depending on sql dialect. In RDB you use db agnostic concurrency strategy saying which columns are overwritten or skipped.

• As far as I can see, Drizzle does not have concurrency handling on updates (only on inserts).

• In RDB you can use concurrency strategy on column level to handle row being changed by other user. Strategies are: optimistic, skipOnConflict and overwrite.

• RDB has a very succint, fluent and intuitive syntax. It feels more like natural language.

• RDB supports MS SQL Server, Oracle and Sybase SAP ASE as well. And it is extremely fast for MS Sql because it utilizes the FOR JSON PATH operator so it can fetch everything in one hierarchical query.

• Downside: RDB has not migrations - but I consider adding this (but not reverse db aka pull).

But it is hard to convince someone to replace their favourite ORM. Just as test drive is essential for truly understanding a car's qualities, experiencing RDB firsthand is the only way to fully grasp its slickness.

RDB is quite old - much older than Drizzle ORM. It's first release on npm was in spring 2014. Before deciding to create our own orm, we considered Sequelize. But we were horrified by the number of bugs and the inconsistent api. I had already experience with creating an inhouse ORM in csharp - so I had a basic idea what the API and the implementation should look like.

The last two years, I have added typescript on top of it. The core is pretty much the same as in 2014. RDB is not widely adopted, but it is very mature, and is running in 100+ instances for major Logistics companies in Norway - receiving thousands of transactions every minute.

[CanRau]

Thanks a lot for the write-up and also the "downsides" 👍🏼

Had no idea it's been around for that long, curious why I never stumbled upon this in all those years 😅

Does rdb currently turn all/most queries into 1 SQL statement like Drizzle does or tries to do? Or is it more like Prisma doing like 6 queries?

Asking because currrently I've got quite a lot of sub-queris, like aggregating related rows as a json array into a field on select.

HTTP transport is interesting though I think I wouldn't currently benefit from it

The API is definitely nice and having to import separate operators is a little annoying, though can give you tree-shaking I guess, but also might be lesser of an issue since Drizzle added queries (docs) like

await db.query.posts.findMany({
  where: (posts, { eq }) => (eq(posts.id, 1)),
  with: {
    comments: {
      where: (comments, { lt }) => lt(comments.createdAt, new Date()),
    },
  },
});

in addition to their existing more SQL like syntax.

Tho definitely need to look more into concurrency (on column level sounds very helpful) & conflict resolutions

[lroal]

And thank's for your interest. It is highly appreciated. ❤️ I have a limited social media network and no "evangelists" to push the framework. Maybe that explains why RDB goes off the radar. I have been playing with the thought of making a youtube video lately. We will see.

RDB create one extra query per one-to-many relation. Except for SQL Server, which can do everything in a single query because it utilizes the FOR JSON PATH operator that can return hierarchical results with extreme performance. Previously, RDB also did sub queries with json aggregate functions for Postgres. But this turned out to be less performant, so I ditched it.