provide better comparison with other options

[alexanderniebuhr]

Firstly I want to say that I really like Orchid ORM on the initial view.
However I'm missing some better comparison with other options, maybe a comparison table would be cool.
I do have some questions, and hope the community can answer them:

• Why is the plain object approach better then the approach following the Active Record Pattern with classes?
• I've seen there are some community projects and tutorials on how to add relations to Kysely? What is the difference of Orchid?
• Orchid only does support Postgres, while others support more, is there any features Orchid can support, because it focuses on Postgres? e.g. Triggers, Stored Procedures, RLS
• How does it compare to the current version of Drizzle? If I understood correctly Drizzle also only maps to plain objects and doesn't follow the Active Record Pattern?
• What is Orchids happy path for migrations with a bigger team, we face a lot off issues with conflicts as well as having schema drift whenever the database get's changed?

[romeerez]

Thank you for checking out, glad you're liking it!

Why is the plain object approach better then the approach following the Active Record Pattern with classes?

Two topics here:

• ActiveRecord
• Plain objects

ActiveRecord is considered to be an anti-pattern. MikroORM is OOP-oriented, does instantiate classes, but does not officially support ActiveRecord, because it favors the DataMapper pattern. (source)
I've worked with the ActiveRecord ORM in Ruby and enjoyed it a lot, so it definitely works and can be great in practice.
But in general, it's better to decouple logic from persistence. In ActiveRecord the same objects that are responsible for business logic are also responsible for persistence, so it mixes these two concerns. With DataMapper the objects aren't responsible for persistence, therefore it's considered to be a better way.

Plain objects:

• All TS ORMs that do it with classes are sacrificing type safety. They allow selecting just a few fields of a table, but the class has more fields, TS type says the object has a lot of fields, but in runtime it's not, so it's not type safe. Similar problem with relations: you may load objects with their related objects, or without their related objects, and it's not reflected in the type system, it's not type safe. I've seen that MikroORM had a progress in that direction and handles this better than TypeORM, but, I believe it's practically impossible to follow OOP approach for loading objects and not sacrifice on type-safety.
• Plain objects are easier to deal with (subjectively).
• No mixing of business logic with persistence. It's just a plain object, it has no behavior, no methods. It is up to you if you prefer to pass this object to the "service" layer to perform logic and then pass it to "repository" layer for persisting, or if you want to mix them, or if you want to apply any kind of architecture. A plain object gives a full freedom of how you're going to use it.

I've seen there are some community projects and tutorials on how to add relations to Kysely? What is the difference of Orchid?

Kysely is a query builder that conceptually doesn't deal with relations.

https://kysely.dev/docs/examples/select/nested-object:

While kysely is not an ORM and it doesn't have the concept of relations, we do provide helpers for fetching nested objects

As you can see, the nested objects are loaded via a sub-select in SELECT. You can't join in Kysely, but you can join them in Orchid ORM. I mean, imagine loading posts with their comments, you can do that in Kysely, but you can't straightforwardly load only those posts that have at least 1 comment - what "INNER JOIN" does.

Orchid ORM has lots of relational queries that are aiming to cover what Prisma can do. Kysely does not have any of those.

But Kysely does have a quite large community, active discord, and maybe there are additional extensions and plugins to enlarge functionality.

Orchid only does support Postgres, while others support more, is there any features Orchid can support, because it focuses on Postgres? e.g. Triggers, Stored Procedures, RLS

Well, not really, not much. One of such features is full text search. Better support of views is in plans for near future. There are recurrent migrations to keep your custom triggers and stored procedures, but triggers and stored procedures aren't integrated to the ORM.

How does it compare to the current version of Drizzle? If I understood correctly Drizzle also only maps to plain objects and doesn't follow the Active Record Pattern?

Yes, Drizzle is similar to some degree.

• the most standing out difference is that in Orchid ORM it's a single querying interface for all use cases, while Drizzle offers to do single-table queries in one way, and relation queries in a different way.
• other difference is how tables are defined, but I like how Drizzle does it better and there are plans to change it in OrchidORM to be more similar to Drizzle (not exact, but similar).
• type safety: I've opened one issue in Kysely for incorrect selectAll, Kysely's type-safety is awesome, but due to this issue I can state that OrchidORM's type safety is a little bit better. As for Drizzle, I don't know, maybe it also has an awesome type safety.
• and the querying interface is different. In Orchid ORM it's partly inspired by Knex, and partly by RoR's ActiveRecord.
  You can see in compare with Kysely how OrchidORM's queries are designed to be more compact, more laconic.

For example:

db.person.select('id').where({ firstName: 'Arnold' })

In Drizzle:

db.select({ id: person.id }).from(person).where(eq(person.firstName, "Arnold"));

What is Orchids happy path for migrations with a bigger team, we face a lot off issues with conflicts as well as having schema drift whenever the database get's changed?

Db migrations are just like everywhere else - either generate migrations (similar to how Prisma and Drizzle does it), or you can write them yourself (similar to Kysely and Knex, but here it's nicer).

There is one difference that's worth noticing: thanks to this issue, in Orchid ORM migrations are versioned with a sequential number (by default, can be changed to timestamps).
After considerations that you can read in that issue, sequential number is more reliable than timestamps.
So you will have file conflicts in a larger team, and that's good, it is better than having less conflicts but more inconsistencies.

[alexanderniebuhr]

Thanks for the detailed answer. That helps a lot :)

[alexanderniebuhr]

@romeerez follow up question would you be able to provide your query builder standalone, so I can use it with any driver and schema definition?

[romeerez]

The query builder is standalone: https://www.npmjs.com/package/pqb.
The ORM is built upon the query builder, while the query builder can be used without the ORM. Initially I was documenting usage of the query builder as a standalone lib, but I think it doesn't give much value on its own and without additional docs it is less docs to write for me, and less docs to read for others.

any driver

It only supports pg, more drivers (postgres) will be supported eventually.

and schema definition

The schema definition is a significant part of the query builder, which relies heavy on it both at runtime and at the type level. Though the query builder can be used without schema, then it allows any columns and doesn't provide strict types for the returned data.

[alexanderniebuhr]

I still think it gives a lot of value, e.g. it API looks better than Kysely, however probably don't want to switch to Orchid. So similar how you can use Kysely on top of Drizzle Schema (e.g. some type conversions), I think it would be great if pqb could also be used on top of it, if that makes sense, or some kind of introspection, so we don't have to manually define schema on orchids syntax if ad already have it defined somehow else, but still be able to use pqb 🤔