Hey, dear visitors!

[romeerez]

I'm very grateful to all of you who have left inspiring comments, who have starred the project, and who are looking forward to its growth!

For me this means a lot, it is a credit of trust. And I'm absolutely set up for improving this project in the foreseeable future.

Feel free to leave any questions or suggestions, or point out unclean parts of the docs, - community feedback is in priority for me.

Thank you all for the motivation, hoping to build a small cozy community here!

[capaj]

Wow, I gotta say I am impressed by this project already. I am using Prisma in production ATM and seeing how slow it is and how bad defaults it has, I am wishing for a new ORM.
I am keeping my eye on Drizzle ORM(https://github.com/drizzle-team/drizzle-orm), but Orchid ORM API looks a bit nicer for me. Can you add drizzle orm to the benchmarks next time you work on the docs?

I used to work years and years on Objection.js and this has a very similar feel.
I build mostly graphql APIs using codefirst approach and not having the model classes with Prisma is also a small pain point.
I generate them for now, but it would be good if it was the other way around.

I think I will use orchid ORM in my next project, so I might report a few bugs/issues that I come across. Thank you for creating this project.

[romeerez]

Can you add drizzle orm to the benchmarks next time you work on the docs?

Yes, I'll do that eventually, interesting to know it myself. There are many current todos here, so I'll jump to adding Drizzle later.

I used to work years and years on Objection.js and this has a very similar feel.

True, Orchid and Objection are following a similar model: queries are written with a query builder (like Knex, Drizzle, Kysely), and relations are added on top of it.

I think I will use orchid ORM in my next project, so I might report a few bugs/issues that I come across. Thank you for creating this project.

That would be awesome and will help a lot! Not recommend using it on a serious large production, but for something smaller, I think Orchid feels stable enough and have enough features.

[akutruff]

First, thanks so much for writing this. I'm going to spending the next few days giving it a whirl.

Meanwhile, I'll be working on the support of MariaDB/MySQL (because it's essential for the ORM to support many databases), and then SQLite.

One thing I'd caution against that totally made me leave Prisma - a lot of wonderful functionality and extensions of Postgres is neglected by Prisma's multi-db support. Row level security, custom data types, decent GIS support, jsonb, subscriptions, extensibility/plugins for arbitrary Postgres extensions, and on and on. My advice is to keep building on Postgres until you've nailed it, gained a bunch of users, and then start worrying about the other DB's. Then, I'd bet adding in the other DBs should be a matter of supporting a subset of what you've implemented for PG.

You can see a lot of basic PG stuff missing by browsing Prisma's most commented issues

[romeerez]

You're right, I think so as well, removed that part from the message. Orchid ORM will be focused on Postgres only, it's such a giant amount of work to support a second db that I can't imagine where to find the time for this. But iteratively improving Postgres-oriented features is absolutely doable.

Thank you for the advice, and for the link to issues, and for trying it out!

We have a Discord (chat badge in readme) for any questions and feedbacks, github works for that well too.

[capaj]

When it comes to DB support I absolutely agree. PG is all I need in 99% of cases.

The only other DB I sometimes use is SQLite.So for me SQLite support would be nice to have too.

[romeerez]

To clarify what support takes:

• column types: reflect actual data types from db documentation to the code. For example, pg has various types for dates and time, it's reflected with methods t.timestamp() and others, but in SQLite it's not supported and needs to study what it has and how to address that, so the ORM has properly typed and complete set of types.
• query builder: reflects capabilities of the db, SQLite is minimalistic, but still needs to look at what it supports and which options it supports, reflect it in the code, and cover all with tests.
• ORM: selecting relations depends on postgres json_agg. SQLite also has some json methods, but they are different, probably it's possible to rewrite it to support SQLite in a similar manner, but need to rewrite it.
• Converting schema to validation, currently only Zod but more to come: also this should be rewritten for new db because it has a different set of columns
• db pull - this is the feature I'm proud of, you can take an existing database and generate a migration for it and the project files. It highly depends on the db and should be fully rewritten for a new one.
• migrations also must reflect the capabilities of the db
• docs: this is the biggest challenge. Should it be fully rewritten for a second db? Probably not, but then how to support it for a second db - that's a question.

By the way, I once worked with SQLite in Electron app, and well, it's not a place for a full-featured ORM. SQLite is embeddable by nature, and for embed cases, we don't want to bloat the bundle with a big heavy ORM. Instead, raw SQL worked just perfectly for that case, though I'm against it when developing backends.

It may seem that supporting a second db is easier than writing code from scratch for Postgres, but: doing the same work a second time is no fun, and it's an important factor. I'm not very familiar with other dbs. Postgres docs are far the best, and I expect that learning the specifics of other dbs would be more problematic. The philosophy of Orchid ORM is to support every feature that may seem useful, but SQLite is lightweight for limited use cases, so existing libraries for it should be just fine.

Instead, I have ideas to try out in near future:

• join lateral - hard to explain, in theory, it can make relation queries even more powerful and still more efficient, because currently there are some limitations that aren't possible without this join lateral
• there is room for improving the query interface for relations
• jsonb first-class support. The is no ORM or query builder that fully utilizes postgres capabilities for it, let's fix this.
• I have a few ideas to research and write articles, for instance, how operations with jsonb compares to Mongo in terms of performance and flexibility. I suspect that Postgres will destroy Mongo in its own field, but that's up to research.
• RoR has a cool feature counter_cache: when one table relates to the other, simply define a column other_count and ORM will take care of updating the count cache. AFAIK, no node.js ORMs has this, but it's a nice feature, we usually want to get relation counts, but doing it by querying may be very inefficient in some cases.
• I'd like to take step further from counter_cache and add JSON cache, to cache results of relation queries in a JSON column. For instance, post has many tags, we could cache the tags into JSON array in a post column. And in general it could be a powerful optimization: instead of performing complex queries on a db, we could cache parts of it in JSON columns, and the cache will be updated automatically when creating/updating/deleting relation records.
• People love how Prisma and Drizzle are generating migrations from the models, and I'd like to have it as well.
• Code generation for different frameworks
  ...and so on!

I really wanted to add support for MySQL and SQLite, so then more people could enjoy it, but it's too much work, and it's just better for the ORM to focus on a single db, so I can add more useful features for Postgres that alternatives don't have, rather than adding generic features for other dbs that alternatives already have.

[capaj]

counter_cache

this feature would be amazing. In our current production API this would save us a tons of custom code. Ideally it can be connected through redis or some other way to work across many instances of the same API.

[romeerez]

Well, I didn't mean redis or anything external, if it must be done with redis then it still will be a custom code. OrchidORM has callbacks on a query builder level, they yet to be added to the ORM level, to define afterCreate, afterUpdate right in the model file. And similar callbacks yet to be supported on relations, so this.hasMany(() => Other, { ...config }) also in future could accept afterCreate, afterUpdate.

Counter cache that I mean is a simply integer column in postgres database. For example we have commentsCount integer column, and it is updated automatically when creating, updating, deleting comments that are related to some entity, and the entity record has that column comments_count. It's better than redis because fewer things to maintain, it's efficient, records can be filtered and ordered by that column. Updates will happen in transactions, so no chances for the cache to run out of sync as with redis, no need to invalidate it and take care of it manually.

And the idea with JSON cache is to have a JSON column, it also could be used to filter, order, etc, and it's faster to read from a db than running a subquery.

So a lot of improvements, ideas, and experiments ahead!

[IlyaSemenov]

I am so glad I somehow noticed a humble mention about orchid-orm in the infamous The future of Objection.js thread. It seems to hit the exact sweet spot between primitive SQL builders and overly constrictive ActiveRecord-style ORMs (even better than Objection.js itself). A truly brilliant piece of work.

I ported the objection.js automated GraphQL resolver I've been recently building to orchid: orchid-graphql. As a direct naive port for the time being, it's not as coherently typed as Orchid actually allows, but the actual underlying machinery works already.

[minkoonim]

Roses Are Red, Violets Are Blue
You are god, orchid is GOAT

[mordechaim]

Just came here to say that OrchidORM is fantastic. I didn't even start using it, I just read through the docs like a book!!

I really want to use it in production, is it production ready?

[romeerez]

@mordechaim thank you, I'm very glad you liked it!

That's a tough question about production readiness, I'd say it's not ready, and it's safer to pick other time-proven tool for a production project. As the old wisdom says, "nobody gets fired for buying IBM" :)

I'm planning to support all Kysely's features in the future, rewrite all example queries from Kysely's docs to OrchidORM and make sure everything works nicely (no clues regarding timing). After that, it will be safe to claim production-readyness, as I believe Kysely is more production ready than any popular ORM.