Which is the Best ORM?

[zinc willow]

We have been using TypeORM for a while now and multiple developers have suggested switching to Prisma.

I notice now NestJS supports 4 different solutions, TypeORM, MikroORM, Sequelize & Prisma

• So which one are you using and why?
• Have you noticed a movement towards one?
• Which do you think will best future-proof our development?
• Which one does NestJs prefer and why?

Also, is there any value in us "abstracting" this away from our client code, so in the future we can switch ORM more easily?

Would love your thoughts on this.

[shell mica]

How are you defining "best"?

Are you only looking at ORMs, or are query builders also a viable option for you?

Are you looking only for "prebuilt" packages, or are you okay with custom approaches?

[zinc willow]

@shell mica open to all at the moment, as it’s a bit of a fact finding mission. “Best” is up for debate but my gut is telling me that is should be well supported and maintained, well documented and be something that developers can pickup quickly and easily.

[shell mica]

Personally, I've really been liking kysely

Type safe query builder. I've set up migrations in an nx monorepo using nest-commander very successfully, and have practically no complaints. Occasionally, complex queries take a bit of tinkering due to some necessary documentation, but usually I can find what I need

I wanted a query to be able to update a row's position (its index) and shift all the other rows with it, so if I have indeces 5-> 10 and I want to move index 9 to index 6, then 6, 7, 8 all need to increment by 1 and 9 needs to become 6.

I came up with the query

 WITH imagesToUpdateMinusOriginal AS (
   SELECT id, (index :operand 1) as index FROM image
   WHERE index >= :lowerIndex
   AND index <= :upperIindex
   AND id != :imageId
 ),
 newImage AS (
   SELECT :newIndex as index, :imageId::ulid as id
 ),
 imagesToUpdate AS (
   SELECT index, id FROM imagesToUpdateMinusOriginal
   UNION
   SELECT index, id FROM newImage
 )
 UPDATE image as i SET 
   index = itu.index
 FROM imagesToUpdate as itu
 WHERE i.id = itu.id;

Which is kinda hairy, but honestly, not the worst thing in the world.

This is the same query in Kysely

await this.db
   .with('imagesToUpdateMinusOriginal', (db) =>
     db
       .selectFrom('image')
       .select(() => [
         sql<string>`id`.as('id'),
         sql<number>`index ${sql.raw(operand)} 1`.as('index'),
       ])
       .where('index', '>=', lowerIndex)
       .where('index', '<=', upperIndex)
       .where('id', '!=', imageId)
   )
   .with(
     'newImage',
     () =>
       sql`(SELECT ${updatedImage.index}::int as index, ${imageId}::ulid as id)`
   )
   .with('imagesToUpdate', (db) =>
     db
       .selectFrom('imagesToUpdateMinusOriginal')
       .select(['index', 'id'])
       .union(db.selectFrom('newImage').select(['index', 'id']))
   )
   .updateTable('image')
   .from('imagesToUpdate')
   .set((ub) => ({
     index: ub.ref('imagesToUpdate.index'),
   }))
   .whereRef('image.id', '=', 'imagesToUpdate.id')
   .execute();

[crimson fossil]

I like the idea of the sql verbs in the api. I have used sequelize successfully in AWS lambda and with Nest that it’s hard for me to justify a switch and be productive quick. I guess I need to learn something different in a non production environment

[lime gorge]

I use prisma for my commands and kysely for my queries. Though I might like mikroORM better instead of prisma

[silver cobalt]

I use prisma + kysely.
prisma for schema because of it's great DX and kysely for queries
https://www.npmjs.com/package/prisma-kysely

[lime gorge]

interesting. I have been toying around with generic solutions like https://atlasgo.io/ for schema generation and migrations instead of relying on some obsecure version of an orm

[latent siren]

I would not recommend prisma for larger projects. There are 2 main issues with it. First, you cant use transactions across multiple services. this would be important if you want something like transactional requests. more in this GH Issue and the referenced once in there-> https://github.com/prisma/prisma/issues/12458#issuecomment-1250973929

Second Issue is a Memoryleak problem with nestjs and prisma -> https://github.com/prisma/prisma/issues/17925 Open since February and no one has a clue why this happens. So larger applications might suffer from this and crash the server as reported by user users.

We now use TypeOrm and that works pretty well for us.

[lime gorge]

Utilize unit of work and you can have a transaction across multiple services 🤷‍♂️

[latent siren]

wdym with unit of work?

[lime gorge]

A unit of work is a behavioral pattern in software development. Martin Fowler has defined it as everything one does during a business transaction which can affect the database. When the unit of work is finished it will provide everything that needs to be done to change the database as a result of the work.

Now obviously prisma doesnt support it out of the box and its a bit tricky to implement but it definitely is possible

[latent siren]

ah, well yes, that could work in some cases. but If you concider a Post request as one unit of work, but still have mutliple services involved, ist hard. The Example I Posted is pretty simple and I know it could be solved differntly. In the project I worked back then, we had decided to have Accessor Services for every entity because some of them had multiple custom raw queries with dynamic query building... not going more into detail, but some of these services had 2k+ lines. Passing round the transaction would be one solution. but then you would have to move database logic out of the Database Layer/module. and that would be against a clean architecture style. and this callback style of transaction did not allow nested transactions, a potential transaction annotation could do that.

Not saying prisma did not work. But I would not recommend it for larger enterprise applications with the current state of transactions. even if you could hack it, It would then be a mess for every new developer that just reads the prisma docs

[lime gorge]

What do you specifically mean by multiple services here? are they all deployed on their own?

If something requires so many services to be involved, then I doubt they all need to be in the same unit of work. It smells like a design mistake where you failed to define your consistency boundary to me

but that's another discussion hehe. Though relying on an ORM for tracking entity changes is kind of a controversial topic. Because in one way or the other you will have to leak some implementation details of your database into your core. Where with a custom unit of work you can just keep it out of there, but then again it really depends on the app and how much you care about those leaks

[latent siren]

Service classes.

@Injectable()
export class UserAccessor {
  constructor(private prismaClient: PrismaClient) {
  }

  async create(...): Promise<User> {
    return this.prismaClient.user.create(...)
  }
}

@Injectable()
export class SettingsAccessor {
  constructor(private prismaClient: PrismaClient) {
  }

  async create(user: User): Promise<Settings> {
    return this.prismaClient.userSettings.create(...)
  }
}


@Injectable()
export class UserDomain {
  constructor(private readonly userAccessor: UserAccessor,
              private readonly settingsAccessor: SettingsAccessor,
  ) {
  }

  async createUser(...) {
    // start Transaction
    const user = await this.userAccessor.create(...)
    // maybe more stuff done between this accessor calls
    const settings = await this.settingsAccessor.create(user)
    // end Transaction
  }
}

edit, the transactional request is something hibernate offers with Spring. So a transaction is opend for each incomming request and if it fails, the changes are rolled back. for sure, thats not good for every usecase.

[lime gorge]

You can do the same thing with ALS(async local storage) just pass the transaction / uow and use it where you want.

[latent siren]

ha, didn't know about that, thanks. But issue with nested transactions still remains. Ether way, i'm not working in this project anymore 😄 and like I said, we decided to use typeorm and are way more satisfied with it 😄

[ashen nest]

Prisma works like a charm with less boilerplate

[celest kraken]

Huge fan of TypeORM. Also, for context, it's the only one that I've ever used in prod. I did some Nest.js course with Prisma, but didn't really like it. Before choosing TypeORM, I also checked out Sequelize and Knex.

[scenic gorge]

Don't use Prisma! It's slow and I once had a segmentation fault or malloc(): unaligned tcache chunk detected.

See: https://github.com/prisma/prisma/issues/18510

It's not memory safe. I dont like that. It has its own middle layer written in rust or some language that isn't memory safe.

TypeOrm is written in javascript I think. It directly queries the db. It's much better. I hate Prisma.

[celest kraken]

TypeORM is written in TypeScript.
https://github.com/typeorm/typeorm

[shell mica]

It's not memory safe. I dont like that. It has its own middle layer written in rust or some language that isn't memory safe.
I wanna break this apart for a moment. Now, let me preface this by saying: I don't like prisma, or generally any ORMs. I prefer to use a query builder.

It's not memory safe.
Can you explain more what you mean by this?

It has its own middle layer written in rust
Correct.

that isn't memory safe.
Incorrect. Rust's homepage even boasts about its memory safety

[unborn bobcat]

Drizzle ORM provides the best performance and type safety for me, you can also use Kyely along with drizzle since drizzle officially provides ways to use drizzle schema with kysely

[unborn bobcat]

Typeorm is a nightmare for performance.

[celest kraken]

Why do you say that? We haven't seen anything like that so far.

[unborn bobcat]

https://github.com/typeorm/typeorm/issues/3507

[unborn bobcat]

https://github.com/drizzle-team/drizzle-northwind-benchmarks

See this test. Typeorm is always champion from the last. 😅

[scenic gorge]

bro look here it's not memory safe:

https://github.com/prisma/prisma/issues/18510

[weak helm]

I wanna know about ORM.
Now I am using Prisma as ORM.
But I am not familiar with ORM.
Help me!
What is ORM, How to use ORM, What's tool for ORM, And The best ORM?

[shell mica]

And if you look at the root cause it came from a mismatch/mislinked OpenSSL binary between OpenSSL 1.1 and the newer OpenSSL 3. More than likely, this attempt at reading and using the 1.1 binary when v3 was installed was indeed that memory invalidation, which could happen in just about any language when you call off to other binaries of the machine. OpenSSL itself is written in C, Perl, and Assembly.

Now, again, do I suggest the usage of ORMs? No, I like query builders and keeping things closer to SQL, but I do dislike what looks to be blatantly incorrect information and blame being passed around

[shell mica]

Learn SQL first. At least enough to know your selects, inserts, updates, and deletes. Know howto join and what's going on when you do. I promise it'll make most ORMs easier to learn in the long run, and most likely not even necessary as you can use a query-builder to get the same functionality instead

[scenic gorge]

I don't care what the cause is. Prisma = Segmentation Fault.

Never got Segmentation Fault with TypeOrm

[covert lichen]

How persistent

[soft lichen]

I've recently been looking into this topic and did a tiny benchmarking experiment for read-heavy usecase of the project. Code available here - https://github.com/naz/orm-benchmark

TLDR: will stick to old and proven knex for the time being 😛

[scenic gorge]

Sorry I think I was a bit ignorant. Does does TypeOrm also buil and link OpenSSL in cross-compilation images?

So can this error also happen in TypeOrm?

[scenic gorge]

https://www.youtube.com/watch?v=Xk43fwSQ8No

[shell mica]

TypeORM doesn't link to an OpenSSL binary like prisma does, but it does make use of the OpenSSL implementation that's connected with NodeJS. Back at Node v12, there was an issue with OpenSSL that caused problems with some of the underlying query engines. This isn't really unique to Node or ORMS, there's plenty of things in the tech world that start having issues across OpenSSL versions, I rememebr I've fought with it several times. But, if you're looking at it in terms of "will this package have a middleman enginge that has an issue with OpenSSL" then anything directly written in Node (TypeORM, MikroORM, Sequelize, Drizzle, Kysely, Knex, Bookshelf, Objection, sloink, massive, etc) will not, unless Node itself has an issue with OpenSSL. Because Prisma's query engine is a separate Rust binary, it might due to reasons in that first issue I linked.

[scenic gorge]

Thanks for the clarification. Prove me wrong but I also think that it's less likely that Node will have issues with OpenSSL then Prisma's Middleman cause it's well maintained and it's more mature.

But could be that in future Prisma becomes more stable with time.

[shell mica]

I don't disagree there. I'm not really a big fan of prisma in the first place. I only originally responded to you to correct the misinformation about why there was a segmentation fault and to point out that it wasn't a fault of the language, but a fault of an incorrectly linked binary

[scenic gorge]

Yeah thank you for correcting me. I actually wasn't sure when I spread that misinformation and was just lazy to research it before posting.

[acoustic fox]

I ran this query (adapted for mysql) with TypeSQL - https://github.com/wsporto/typesql (a library I have been implementing). With TypeSQL you write raw SQL (mysql only) and it generates the functions and types to run the queries in a type-safe way. SQL gives a lot more flexibility than using an ORM.

[vital egret]

@shell mica I am curious, would you also prefer query builders over something like https://github.com/JasonShin/sqlx-ts or https://safeql.dev/?

[shell mica]

As cool as sqlx-ts seems, I'd have to pass

The way it works is that sqlx-ts connects to your local or development database at compile-time and have database itself to verify the queries, also generate types based on information_schema

Needing a database connection at compile time seems off to me.

safeql looks like more something I would appreciate in use, but I haven't tried it before so I can't say for certain

[tardy ice]

I have been using DrizzleORM and it is pretty good. I was using Prisma earlier but it was not very performant for our use case.

[slim grove]

@shell mica I agree with you on that. I believe using query builder is a lot more professional way of interacting with databases as you have full control over what's going on. However, my only concern is when you need to do joins, it becomes a pain to process it line by line and turn it into nested objects before returning to the client. When I send a query to fetch posts and users, I'd get something like this

[
  {id: 1, postTitle: 'JS', comment: 'Comment 1'},
  {id: 1, postTitle: 'JS', comment: 'Comment 2'}
  {id: 1, postTitle: 'JS', comment: 'Comment 2'}
]

But then I'd need to turn it into something like that:

[
    {id: 1, postTitle: 'JS', comments: ['Comment 1', 'Comment 2', 'Comment 3']}
]

This is the only reason why I doubt using query builders to send raw queries. I wanted to ask how do you handle this kind of scenarios. Do you always process everything manually or is there a shortcut which would make it easier?

[slim grove]

I'm leaning towards using query builder in typeorm which handles all the processing for us and at the same time, allows for us to build the queries. I just wanted to have your opinion on this issue

[shell mica]

I use a loop or a reduce to get the data in the format I want. Just how dealing with SQL queries goes

[slim grove]

Oh okay thanks

[slim grove]

Btw I just found that lodash could also be used. Here's a small sample:

import { groupBy, map } from 'lodash';

const data = [
  { id: 1, title: 'SQL', commentId: 10, commentContent: 'SQL comment 1' },
  { id: 1, title: 'SQL', commentId: 11, commentContent: 'SQL comment 2' },
  { id: 1, title: 'SQL', commentId: 12, commentContent: 'SQL comment 3' },
  { id: 2, title: 'Java', commentId: 20, commentContent: 'Java comment 1' },
  { id: 2, title: 'Java', commentId: 21, commentContent: 'Java comment 2' },
  { id: 2, title: 'Java', commentId: 22, commentContent: 'Java comment 3' },
];

const postGroup = groupBy(data, 'id');

const result = map(postGroup, (posts) => {
  const commentGroup = groupBy(posts, 'commentId');

  const comments = map(commentGroup, (comments) => {
    return {
      commentId: comments[0].commentId,
      commentContent: comments[0].commentContent,
    };
  });

  return {
    id: posts[0].id,
    title: posts[0].title,
    comments: comments,
  };
});

console.log(JSON.stringify(result, null, 2));

[steel dagger]

knex + objection.js

[slim grove]

Do these address the issue I was talking about above?

[acoustic fox]

I use this approach on TypeSQL. You write the raw SQL (mysql) and annotate it with @nested, the result is what you described here. TypeSQL initially used the lodash.groupBy to return the nested data, but the problem is that it doesn't keep the original row order.

--- @nested
SELECT   
  p.id,
  p.title,
  c.id,
  c.content
FROM posts p 
LEFT JOIN comments c on c.postId = p.id
WHERE p.id = :postId

You can use the generated function:

const postsWithComments = await selectPostWithCommentsNested(connection, {postId: 1});
/*resultType 
{
  id: number,
  title: string,
  comments: {
    id: number,
    content: string
  }[]
}[]
*/

[slim grove]

Looks cool. I'll look into that. Thanks

[slim grove]

I never found anything related to typeSQL on the internet. Do you have any documentation for that at all?

[acoustic fox]

I am working on it's documentation, but you basically need to create a typesql.json config file on your project (can be done with npx typesql-cli init) and write the queries in the folder specified on the config file (ex. ./src/sql/mysqlery.sql). You just write plain SQL with the parameters in the form:myparam.
The documentation page is on the project description:
https://typesql.pages.dev/

I have an example project here showing how to use TypeSQL in a nestjs project:
https://github.com/wsporto/nest-typesql

[slim grove]

I see thanks

[forest pumice]

for me personally, nothing beat the generated api from prisma

[tulip moss]

Drizzle-ORM the best one.

[slate pecan]

I recommend Sequelize for you; because:

• It is used by big comapanies like PayPal.
• I use TypeORM, Prisma, Sequelize and I can say Sequelize is amazing at all 😍😎

If you want scale your application and you want stability, I recommend Sequelize for you

⚠️ I do not work with Drizzle until now.

@zinc willow

[olive oriole]

certainly, it is typeorm

[weak helm]

I personally like to use Prisma + Keysley.

Prisma for schema modelling, migrations,
and keysley as query builder with more low level control.

[tidal lark]

I’ve enjoyed Prisma a lot! I feel it’s a bit more organized to have all the models and relationships defined in a single file, plus the automatic migrations are awesome

[fluid mason]

Typeorm has gotten so bad for us the second we had to start scaling that we're at the point of ripping it out in favour of bare metal postgres.

[slim grove]

Was it because of the performance issues?

[fluid mason]

Yup, only a few thousand rows and it was topping out the .emery on our k8s pods , doing far too much reconciliation in js memory instead of clean queries

[slim grove]

Oh I see. Thanks for the insight. I was going back and forth between typeorm and kysely the past couple of weeks. Almost everyone suggests getting rid of orms wherever I read an article

[fluid mason]

Yeah Orms never end well

[halcyon kestrel]

So do you support keysley then?

[fluid mason]

Support? In what way?

[halcyon kestrel]

I mean do you think it’s a good solution

I am also trying to decide on typeorm or keysley

[fluid mason]

Can it do recursive ctes in postgres cleanly?

If trh answers no, then won't really fit my needs

But for a simple query builder it looks fine

[halcyon kestrel]

Are you just writing raw sql then? How manageable has that been in a nest project

[fluid mason]

Very

[halcyon kestrel]

Care to explain? So orm benefits all thrown out the window and that’s okay?

Sorry just asking cause I’m new and learning. Like should I just go straight sql lol

Hearing all this stuff about orm being terrible over a couple thousand rows seems really ridiculous that anyone would even consider them

[fluid mason]

We use them now purely for migrations and simplistic crud. Anything beyond that, especially with complex auditing and report building we do in postgres

[halcyon kestrel]

So you just send a raw Postgres query string right

[fluid mason]

For most of them yep

Obviously with parameterised values to avoid sql injection

[halcyon kestrel]

I was going to use cloud spanner but didn’t because no orm supported it. Now I’m thinking if I’m going to raw sql anyway might entertain that idea

They have a nodejs library I can use to send raw sql to it

[halcyon kestrel]

Im just wondering if orm have bottlenecks at a few thousand rows how in the world is anyone using or supporting them ?

[fluid mason]

They tend to scale the resources around them

[halcyon kestrel]

Aha so it will perform you just have to give it a ton more resources thus costing you more to run it. Got it

[fluid mason]

By all means you can tune your db, ind3x ev4ytbing and get it optimal , but a lot of devs don't even know how to relate the entities correctly for owning and inverse sides, and that alone adds to a large amount of complexity I simplest of queries

[halcyon kestrel]

Is it a huge deal just settling for typeorm to start with and changing later on when your saas grows?

Once the company has more funding

Or do you think every project should not use them from the start

[fluid mason]

Applications evolve. Tech debt gets introduced, usually at the points where we are now ( scale up )

Can 100% guarantee that the tech you use in your decisions on day 1 will be wrong and evolve as the needs arise

I've seen badly implemented uses of orms where a simple query to get a single user, some of their info rlfeom a related table or two, was under the hood returning a query of over 100, 000 rows and the reconciling the results down into the singe users data

[halcyon kestrel]

If I’m understanding you correctly orm make it easy to get bad performance right

[fluid mason]

Indeed

[weak helm]

yeah but it lacks query builder,

hence using it with a query builder would make it 3x better

[weak helm]

we cannot blame whole orm for it,
i just replace that specific query from kysely and done.
we get best from both worlds

[fluid mason]

I'm not blaming anything or anyone. I'm just speaking from past experience

[weak helm]

ohw ok mb

[covert lichen]

Agreed, I have mixed feeling about $queryRaw

[tidal lark]

I didn’t think of this, this is something that I’ve always needed at some point in my projects. I’ve been working on this project for the last 3 months and so far I haven’t needed to write queries manually, but for reporting I will for sure need it. Thanks for bringing that up 😄 What do you use for building queries?

[weak helm]

well if you will reach to a point where have complex relationship and with that you also want to write a method which conditionally make the query and then return back.
In that scenero it requires

also we cant use most of aggregate function with just orm, as well as sets like UNION, INTERSECT which could be used in complex situation to improve performance in some query. in these places query builder is required

[tidal lark]

I see, I didn't think of it when decided to go for Prisma. I saw in another post of yours that you like Kysely, so I'll give it a try when the time comes! Thanks a lot @weak helm 😄

[fluid mason]

Prisma is even worse. It doesn't even do low level table joins, it just dumps all the data into js memory and manipulates it there

[crisp bronze]

I wanted to use prisma on my app but since I'm using a monorepo I couldn't make it work because it uses the same folder in node_modules or something like that

[slim grove]

I got a question here for those that use kysely or raw sql for querying. It is obviously the best option to use the db to it's full power but how do you manage types? Do you still create entities, do you simply rely on types generated from kysely? Each time you need to join results of two tables, do you create a new interface reflecting the result of the join? I'm super curious about that. Thanks in advance

Especially I'd like to get your valuable opinion @fluid mason

[fluid mason]

You're talking more about typescript types I presume

[slim grove]

Yes right

[tidal lark]

really?? I thought it did a good job with relationships!! I’ll have to look into this :/

[fluid mason]

It takes all the data, unjoined and shoves it into its rust engine

[grand mica]

The longer I use ORMs the more I generally hate them.

I've used Sequelize for the 5+ years, and TypeORM ~3 years. TypeORM seemed like the best option when we started using NestJS for TypeScript support; however, the projects is basically stalled and other ORMs in the TypeScript world have surpassed it for support.

Regardless, fighting with any ORM to build the query I want ,through whatever their alien non-SQL relation-building syntax is, becomes a bigger and bigger chore. Sequelize hasn't had a query builder, so experiencing that with TypeORM was amazing at first. While it is better than using an ORMs find method it still becomes a trudge.

As a result, I write anything larger than a simple CRUD operation on a single entity as raw parameterized SQL. It affords me complete control over the syntax, and I can build my query in a database client for rapid testing and optimization. In regards to returning nested results from raw SQL or query builders there are several options, some of those already stated above, Postgres provides some excellent JSON functions which allow me to build it directly into the query itself.

While we are still saddled with TypeORM, for now, I am squarely in @shell mica camp of "please, no ORMs for me" 😁

[slim grove]

Quick question. Would you also build a dto class for each query you are executing to achieve type safety? Or would you work on raw query result set directly?

[grand mica]

We do make use DTOs, and create response types/interfaces for recordsets

export type StudentLevelData = { studentId: number; levelId: number };
export interface LessonsAtLevelSummaryResult {
  studentId: number;
  levelId: number;
  lessonsAtLevel: number;
  attendedLessonsAtLevel: number;
}

export const lessonsAtLevelSummarySQL = `--sql
WITH "studentsLevels" AS (SELECT * FROM json_to_recordset($1::JSON) AS studentLevels("studentId" int, "levelId" int))
SELECT "sl"."studentId", "sl"."levelId",
    COUNT("a"."id") AS "lessonsAtLevel",
    COUNT("a"."id") FILTER (WHERE "wasPresent" = TRUE)::INTEGER AS "attendedLessonsAtLevel"
FROM "studentsLevels" AS "sl"
    LEFT JOIN "registry"."attendances" AS "a" ON "a"."studentId" = "sl"."studentId"
        AND "a"."levelId" = "sl"."levelId"
        AND "a"."deletedAt" IS NULL
GROUP BY "sl"."studentId", "sl"."levelId";`;

[slim grove]

Hmm I see. Thank you

[grand mica]

One of the "downsides" of not utilizing an ORM is handling "automatic" things like tracking timestamp fields.

(e.g. remembering to update updated_at on any record change, and filtering deleted_at for all queries if you're using the "soft delete" pattern)

[slim grove]

Oh yes. There's that. But for me, it's also a bit of a challenge to create types for every query. Usually an orm would handle it

But there's nothing to do. I am using kysely wherever possible but still there may be times where raw query is needed

[fluid mason]

Yeah this is where we all end up I think 😆

[weak helm]

can you give me a reference?

[fluid mason]

Sure https://codedamn.com/news/product/dont-use-prisma

[weak helm]

also even if the query engine is worse,
we can use prisma to model schema and utilize its migration engine.

and use kysely for query then run it with prisma raw query fn

[fluid mason]

Yeah, perfectly valid approach. We still keep typeorm purely for migrations

[weak helm]

is this same with typeorm too?

like typeorm also dont do low level table joins while querying?

[fluid mason]

Typeorm is just plain garbage, we've had nothing performance issues / weird little bugs.

Stuff they have known about for years but never actually bothered fixing

[weak helm]

ohh got it

[fluid mason]

We're putting kysley through our dd process atm to see if we can use it

[weak helm]

thank you very much for this article.
i want to ask how can i get articles like this or these infos about the libraries?

[fluid mason]

No I just google

[weak helm]

i guess typeorm has query builder,
why not using it's query builder?

to do the joins yourself

[fluid mason]

And usually hear from peer groups in other discords

[weak helm]

like 😄... can you list some top discord serv

[fluid mason]

We found a load of bugs in it , that we couldn't resolve without forking

[fluid mason]

Reactiflux

[weak helm]

that's strange i have been using it for like 1.5years. i didnt got blocked by it yet.
i do got some bugs but found a workaround for it.
can you list any specific bug in it?

[fluid mason]

Right this second, no. Not in work and was a fair while back

[weak helm]

oh okay np.
oh yeah using kysely would give you typescript benefits too

[fluid mason]

Indeed

[pine kite]

and which orm you recommend if i already have a postgresql database with all tables set, i dont want to create manually schema for each table, any automatic way?

[covert lichen]

Prisma can generate its model from an existing database with ´db pull’ then you can use a generator for kysely so you dont have to define types

[tacit bear]

I'm not a big Prisma fan myself, but for the sake of accuracy, and given that this is one of the hottest active posts here, I'd like to point out that most of the claims on the post are not necessarily true anymore.

There is no concept of SQL-level joins in Prisma
They started to implement support in the recent release. Postgres & Cockroach for now, under a preview flag.

Every new insert via Prisma opened a database-level transaction
This also got fixed on drivers where the behaviour guaranteed by the ORM is supported directly in the database. Starting in 5.1 they began optimising simple and more complex queries.

we had to ship a 12-13MB query engine that Prisma absolutely needed
Also not needed for a few releases now.

And regarding planetscale, "No foreign key support" - they recently revealed they are adding the support.

Most of what I mention is in beta under feature flag, but that alone is a fair point to mention, at least in this discussion.

Don't leave Prisma out of your evaluation process just because of the article. Do your homework and give it a fair shot.
While not too happy with Prisma myself, I can safely say that the amount of effort they put into the platform makes me feel much more confident compared to what I (don't) see with TypeORM.

[fluid mason]

Good to see they're moving in that direction with the previews. And to help be clear orms on a whole don't fit a lot of our companies needs around reporting. And after fighting typeorm for so many years I would literally take any other orm 😆.

Maybe I should have been clearer. Orms solve a lot of problems for a lot of teams / devs. Ranging from lack of sql skill, ease of relations etc. And in no way am I saying don't use em, just be very aware of the pitfalls when adopting one .

[trail maple]

Is there a way to use the same connection pool if I want to use both prisma and kysely?

[tacit bear]

Exactly what I was wondering.
I believe it's just a matter of time until someone implements postgres.js or node-postgres driver using the new driver adapter feature.
Until then probably only an external pooler could help.

[versed geode]

out of curiosity, why would you use both - within the same node process? what's the reasoning behind it?

[trail maple]

@versed geode it'd be a hassle to have to migrate all the prisma code. Also I actually prefer prisma, it's just that it doesn't support some intricate queries

[versed geode]

what would those intricate queries be and why can't you use raw SQL to achieve it?

still, it doesn't answer why use BOTH at once 😄

[trail maple]

@versed geode raw SQL is not typesafe. An example of unsupported query is ordering by a property of json's field

It does, I don't want to migrate all the existing prisma code.

[versed geode]

how can it not be typesafe? an ORM can't do magic that you are not capable of doing yourself

it just generates sql, which you are capable of creating way better, and then represents values as objects in the language. all of which you can achieve yourself

[trail maple]

Raw queries don't go through typescript

[versed geode]

in what direction? when you send to db or when you interpret results?

[trail maple]

In both directions

[versed geode]

since DB's don't understand typescript, that direction makes no sense

but when you receive data back, what do you think ORM uses to represent it as JS/TS structures?

[trail maple]

Prisma types are generated based on schema which is initially pulled from the database itself, making it a single source of truth

I'm not sure what you're trying to say

[versed geode]

that what you said can't be true

when you have a complex query, swapping underlying ORM to achieve some slight difference seems like a huge overkill if you are capable of writing that query yourself

but, it's your system, i am not trying to persuade you or anything. have at it, best of luck 🙂

[trail maple]

Why would it be an overkill? What's the overhead? especially considering that one day they could use the same connection pool

[versed geode]

overkill is when you mix two libraries that are trying to bridge the gap between developer and underlying app language (TS in this case) because you find one to be able to produce the query the other can't

it's like swapping out foundation of a house because you want to buy new tupperware for your kitchen

connection pools are trivial and not a resource hog, you could create hundreds of them per process and share between threads, so is there any impact that you're trying to avoid in particular?

is there a problem or are you prematurely optimizing? it sounds like you could just drop in both ORM's, use their own methods of connecting to DB's and forget about it

[trail maple]

You didn't really explain why you think that's an overkill. I disagree adding a second typesafe orm for edge cases the primary one doesn't support imitates your analogy.

The impact I'm trying to avoid is open connections limit of my RDS instance

[versed geode]

RDS has 64 000 connections limit, you got that much?

i can't explain it in simpler terms rather than it's odd to use TWO libraries that do the SAME job simply because you deem one type-safe, without explaining what type-safe even means in the context of your workload

however, you managed to explain what the final boss-problem is: connection limit. are you near that limit at all?

[trail maple]

This is the connection limit formula, so it's nowhere near 64k for small instances

I actually do hit that limit when scaling out due to ddos attacks so that's an actual concern

[trail maple]

typesafe as in the queries and their results are validated against the codebase at compile time

[versed geode]

how many nodes do you run to exceed the limit? i'm asking because we went into a different territory that reveals real world problems and is tangible

[versed geode]

but you can't have validation of query at compile time, only at run time, no?

how would that even work? you'd have to start your node process with transpiled TS, then it would have to run all the queries to ensure all the data you receive is coerced to corresponding types

regardless, how many connections do you get? what does RDS say?

[trail maple]

RDS chokes around 50-60 connections already

And prismas default is 3 per process afaik

[versed geode]

you have 1 server that runs node in cluster or some sort of autoscale?

[trail maple]

Aws autoscalling group

[versed geode]

it'd be too much guessing from my side, but if you get ddoses it means you're getting traction with your app, which means you're doing something right and it's potentially making money.

it's odd that RDS chokes at 60 connections, i guess it's not connection count but what your app does to the DB which has to spit out a ton of data in return

sounds like the problem isn't in swapping ORM's or making two work together but reducing the DDOS impact alltogether

[trail maple]

I mean I didn't come here to get help with ddos, these are under control. Just to discuss potential multi orm setup without increasing open connections. And I want to add a second orm because the raw query that I wrote actually let a bug through with my recent refactoring solely because they're not typesafe and didn't detect the change of a type in the query. That's all there's to it.

[versed geode]

alright, good luck

[vale aspen]

has anyone cracked how to do lazy evaluation instead of lazy loading for relationships in typeorm ? anything aside from actually making another query

[slim grove]

What do you mean by lazy evaluation? You either fetch all data you want via joins or you do it lazily which means you make subsequent queries

There’s no other options

[empty root]

For applications containing some more complex business logic while performance is not crucial I highly reccommend MikroORM. I've been using it for 5 months now, 0 problems so far (previously I worked with prisma). The biggest downside is that it is not as popular as TypeORM or Prisma so if there is something weird going on or I simply need help usually I ask the creator himself on MikroORM Slack chanell and ofter I get a quick reply. If you want to use DDD for example Mikro is great, it also makes transactional consistency simple by utilizing the UoW pattern as in EF core for example. It allows much more domain-oriented approach than Prisma, detects changes and automatically generates needed queries. It also provides query builder
Also I have to add something from me about some of You saying that ORMs are not a good choice and they just make performance worse. I mean You are not wrong, your statements are completely true, but honestly they apply to certain specific cases.
At my work we have some 15+ projects on production, the oldest of them is 8 years old, mostly car-sharing and transport apis (some of them are integrated with sensors sending real-time data) and in every project we use ORMs, mostly EF Core and we've never had problems with performance because of ORM, but rather because of bad code quality and lack of optimizations from our side. With the help of ORMs we are able to handle complex domain logic much more explicitly, quickly and with focus on behaviours and business features, rather than infrastructural concerns like DB. Speed and developer experience is vital as well and ORM helps with that.
I just wanted to share my experience to give some insight how ORMs work in real project. Of course (I'll repeat it once more) if an app has to be blazingly fast, you have to reduce query times to minimum use lightweight drivers instead of ORMs.

[pine kite]

best orm to use raw sql queries? and pagination support limit, offset

[stray summit]

imo drizzle is straight forward for this and it's integration with alll of those are pretty seamless

[slim grove]

Having tried a couple of ORMs so far, I saw all of them have their advantages and disadvantages. I've seen issues with performance, lack of features etc. I think ORMs are really good when it comes to basic crud operations. However, I started to dislike the level of abstraction from the low level featues of the database system you are using. It makes sense to use an ORM for crud operations which might be the only thing you need most of the time but I personally decided to use kysely (typesafe query builder) for crud and more complex stuff. Any time I need to do something that kysely cannot handle, I would do raw querying (not so often tho)

The only problem with raw querying is that when joining data from multiple tables, the raw results that come from the driver will be row-by-row. No nesting at all. So I built the following library to make it easy to play around with that raw data and convert it into a nested structure:

https://medium.com/@dev.muhammet.ozen/raw-json-to-nested-schema-46a0444749be

It relies on decorators and it does the nesting in a type safe manner. I'm just sharing in case it will help someone else out here

And here's the link to npm package:
https://www.npmjs.com/package/raw-to-schema

[vale aspen]

damn pretty concise didn't expect an answer like this,thank you for all the info its pretty useful ,i would love to change into prisma or MikroORM but i'd be hardpressed to convience the lead to authorize a change that might set us off by a month (but win us time for the long run , management can't see that far ahead )

[empty root]

You can check out this thread as well https://github.com/mikro-orm/mikro-orm/issues/296
There are also some thoughts and opinions about this orm by people who use it in production

[fresh yacht]

is there any ORM for dynamodb or nah?

[fluid mason]

The clue is in the R of orm, as in relational. Dynamodb is nosql. Falls back to the usual question of.' Don't you really need a relational db'

[fresh yacht]

idk prisma somehow works in mongodb which is a NoSQL db

but ig not much support for dynamodb

[fluid mason]

Prisma pulls, currently, from both collections and pushes all the data into a rust layer to process them in memory

[weak rapids]

I don't like typeorm but our project at work depends so much on it (and worse, on an old version, if we update it we get a breaking change and it won't be worth)

[lime gorge]

How did you end up integration testing without it coming from a controller? Considering it gets real mad about global context when trying to seed and or assert against the database.

[vapid forge]

I always prefer Prisma because of its DX

[empty root]

Actually in my case most of the use cases are triggered by controllers so want to integration test a feature of "create post" for example, I assert if the result has been saved in DB through "browsePosts" or "getPostById" endpoints. For seeding I use this approach https://mikro-orm.io/docs/seeding#use-in-tests

[lime gorge]

Right. Yeah my issue is/was that I am writing a couple of tests against my command handler or from a gateway which causes it to go all bonkers on me because of global context. Also realised that unit testing is a definite pain if you decide to decorate a method with @CreateRequestContext() because it throws if the passed in orm isn't the actual instance. I'm liking it though but my testing experience has been an utter hell so far.

[proud briar]

@lime gorge we ran into that before too. For that reason, we use a separate entry point and top level module for the CLI (via nest-commander) and when we register our database module which is just a wrapper around Mikro ORM, we override the allowGlobalContext option and that works pretty well. One word of caution for anyone who uses Mikro ORM in wrappers and needs a lot of batch inserts or queries, please keep in mind that Mikro ORM keeps managed entities in an Identity Map right after you query them and until you detach them or clear the entity manager using em.clear(). What this means is that you have to "release objects" to prevent memory usage from growing infinitely. People who aren't used to working with ORMs that use the Unit of Work pattern are especially prone to running into this.

Otherwise, hands down, I do believe that Mikro ORM is the most well-rounded ORM in the JS/TS world right now. I have tried many of them (Prisma, Sequelize, Mikro ORM, TypeORM among others) and none of them was as universal as Mikro ORM is. It'll have your back when you're just starting out on a small hobby project as well as when you get a lot of users all of a sudden and your project grows significantly. It comes with a lot of batteries included like DB migrations, solid batching, multi-tenancy, replica support, typed query builder (via knex) - this is an often overlooked piece, trust me, you'll get to a point where you need to write a complex query that would take a lot of time to write in other ORMs unless you drop to a plain string query.

[austere galleon]

https://orm.drizzle.team/

[mild wigeon]

Has there been a comparison between TypeORM with PostgreSQL caching disabled and raw query mode, versus SQL queries built and executed using Kysely in terms of performance and efficiency?

[limber patio]

IMO Sequelize is overrated and I'm honestly surprised how it hasn't been deprecated yet. For reference, take a look at the fuck-ton-amount of issues (marked as bugs) it has. I would be pleasantly surprised if you manage to run anything large with it and not get an internal orm error or a bug.

[slim grove]

To be honest, I think all javascript orms that offer a decent amount of features have a ton of issues. Typeorm and Prisma both have over 2k issues in their github repository at the time of this message. I've never found anything that's as mature in javascript world as spring data JPA & hibernate I would use with my java/spring projects. That's probably because almost all these JS projects are open source and they do not have much funding

[pallid monolith]

I'm not getting on with TypeORM at all:
No changes in database schema were found - cannot generate a migration. To create a new empty migration use "typeorm migration:create" command

How many javascript ORMs are there? I have a python background, so we only really have 2 and it's pretty great.

[tiny palm]

help

[minor epoch]

This looks like you're trying to run migrations but the tool doesn't see any migration files.

None of the ORMs are great. They definitely don't feel like Doctrine or Eloquent felt in PHP where things largely just worked. But TypeORM works fine once you learn its quirks if you're not needing to do very complex database work. I tend to lean more towards tools that let me do close to raw SQL if use cases get more complex.

[pallid monolith]

Thanks, yeah. I found out: #1208115845444931644 message

Don't have two very confusing classes with the same name in a project.

I'm main Django ORM and with that I really never had anything to complain about.

[weak helm]

I just had the pleasure of learning nestjs over the past 2 weeks and building the same project with 2 different orms; TypeORM and Drizzle. I very, very much loved my experience with Drizzle. It is 100% what I'll be using going forward.

I also hear that Prisma has sql joins now and performance issues have been relatively fixed for those who prefer to use an more abstracted orm over sql

[covert lichen]

Yeah prisma is getting faster over time

[kind fable]

can't tell if you're joking, or the project is a joke xD

[weak helm]

Ha. I think that’s a joke since they took a bit of inspiration from prisma.io on some things. If you go to prismas website they have a similar wall

[austere galleon]

The gh issue on that project where they show someone migrating from Prisma to Drizzle will put you off using it for the time being

Though it has some great features that Prisma lacks! Looking forward to see this space expand

[sullen lark]

Mongoose I preferred

[soft arrow]

I had some issues with TypeORM and switched over to MikroOrm and so it's solid. I see a lot of folks talk about Drizzle but it seems like query builder, not an ORM in the traditional sense. I also used ObjectionJS in a prior project and it was fine, but it also was not full featured.

[shadow thicket]

I used Prisma in one project and had to change to TypeORM in the very next one for the reasons below:

• Prisma does not automatically run migrations on application startup
• Unsolved memory leak issue: I have a memory leak in the project, I know it's from prisma, but I don't know how to solve it
• Transactions: I need to run transactional requests, and doing that with prisma sucks
• Postgres features: I need to use things like expression indices and other stuff, and prisma.schema does not support that yet.

[fresh yacht]

drizzle

[feral axle]

What about performance though? Im using typeorm and we're getting cpu overloads

[frozen trench]

To be honnest, I'm using Typeorm on my new projects, but I don't understand how it can be so used. Too many contraints, I really like the simplicity of Sequelize regarding queries, even if Typeorm is very great regarding entities generation.

[topaz geode]

I've been using Objection.js and love it

https://vincit.github.io/objection.js/

The queries are very human readable.

[covert lichen]

Nah

[brazen citrus]

IMO
Drizzle for performance
Prisma for DX

[slim grove]

I think it's the other way around 😄

[agile yew]

🤣

[reef axle]

MikroORM

But it's not as popular and funded as the others

[plush heart]

i can def recommend prisma or drizzle

i love prisma, but it definitely got some downsides:

• All prisma configuration (schema) must be stored in on single file (schema.prisma) [lmk if i am wrong]

drizzle is very nice because it gives u the option to write queries that feel like sql, which can aswell be seen as an downside bc u need more sql knowledge than in prisma

for your usecase i would use drizzle because i think this can and will be better for larger teams bc ur able to abstract the schemas more

[boreal cloak]

Check out the A/B Test between Drizzle and Prisma😁
https://zenstack.dev/blog/drizzle-prisma

[regal karma]

nah

how are you even grouping prisma, typeorm and mikro with mysql and postgres

[sick stream]

Hey there!
We've been using MikroORM for our projects sharing the same DB.
It's really great that you can ask the creator and he answers really promptly.
For me, as a JS developer ordinaire, MikroORM's docs are truly complex, all this ORM DB stuff.
There's a drawback, of course. Since they have only one (?) developer working on it, if he, say, decides to stop working on it, what should we do? Rewrite everything on some other technology?
I've skimmed through all the answers in this chat and see these main competitors:

• TypeORM - used throughout the industry, but many people hate it for bugs and performance issues
• Prisma - but it must be used alongside with some query builder for complex DB queries, some people find it not suitable for some specific tasks, advantage is less boilerplate and that it is being continuously developed
• MikroORM - the most well-rounded ORM in the JS/TS world right now, but not as popular and funded as others
• Kysely - many people mention it, often used in conjunction with Prisma (but as far as I understand, it can be a separate solution)
  Some people also mentioned Objection.js
  And lots of replies from you, senior developers, that there's no need in these fancy relieving technology, as you can speak to your database directly and without intermediaries. But, for example, official Nest.js docs use TypeORM everywhere, are they missing something?
  I almost forgot to mention that the DB that we use is MongoDB.
  And we utilize ORM only to be able to change it (if need be) in future.
  One person mention Mongoose, but is it any good compared to the above mentioned ORMs?
  Another worth thing to mention and a question to MikroORM users.
  When first starting using this ORM we were absolutely sure that it only supports 1 DB.
  And nowadays docs say that you can connect to multiple databases with ease. Does someone know for sure, can we use MikroORM with Nest and, for example, 1 MongoDB and 1 PostgreSQL DBs at the same time?

[digital light]

One person mention Mongoose, but is it any good compared to the above mentioned ORMs?

Well it's the first thing that comes up for mongodb orm, and nest even has @nestjs/mongoose package, so it's supported almost out of the box.

I had positive experience with mikroorm when working with pg, and recently I needed to create a project with mongodb (first time I interacted with that engine). I first tried to do it with mikroorm, and honestly couldn't figure out how to make the schema I needed. Tried nest/mongoose, and it was really easy

Though probably if you've figured out how to make mikroorm work for you, there's no need to switch

[sick stream]

The things that bothers our tech lead with MikroORM:

1. It's easier to hire developers who know popular Prisma and it seems easier to learn for those who start on the project
2. MikroORM is supported by the only developer and its creator
3. It seemed that MikroORM with Nest.js didn't support or had some issues with multiple databases

[sick stream]

I also looked at this benchmark test for SQLite and different ORMs: https://github.com/drizzle-team/drizzle-northwind-benchmarks
And the results are a little overwhelming.
Out favourite and easy-to-use full fledged ORMs like Prisma, TypeORM and MikroORM perform real slow compared to drizzle and kysely.
May be I'm missing something or is it because these fast libraries are so fast because they are just query builders or something like that?

[digital light]

yeah perhaps

I wanted to try drizzle in a nestjs project, but at the time the thing that stopped me was lack of official documentation about how to do it

[restive hatch]

https://orm.drizzle.team/ - this one

[grand mica]

My largest complaint with Drizzle when I tested it was the unusable migration experience.

It only goes "up"

[lime gorge]

how many times do you use down? Modern ORMS nowadays only use up / move to branching

you can always use a custom solution for migrations, I tend to use dbmate or atlasgo

[restive hatch]

Yeah, I use liquibase. But just fix forward - I’ve not known a time where ‘down’ has actually been useful beyond a false sense of security. If additive schema changes accrue user data then a down is destructive and will cause data loss.

[normal seal]

You would need to migrate down in case of rolling back a release, e.g. when you notice a security issue or similar. It’s also useful for local development when you applied a migration but need to adjust it afterwards and don’t want to throw away your DB.

[pure zenith]

We have a nest app that using using mikro-orm with mongodb and postgresql

[raw copper]

Our team use TypeORM. We don't need to looking for other options.

[wet coyote]

mongoDB

9/10 times you dont need this, a regular relational database is good enough
where mongo really shines is that 1/10 for example with online games like a MMORPG
i would argue a platform like discord here would also be good, where users have different profiles (nitro) roles, permissions per server
in moments like this you throw the data in without a worry, a regular relational database wouldn't work so great here

prisma

great for proof of concepts demos and small scale projects it's simple quick and easy to setup without much trouble
it will become a problem when the application grows over time, this even gets worse when things like many to many relations needs to be brought in
this will get messy real fast, the way prisma handles nested data is a mess

typeOrm

complex, requires code to get things right, and would't work out of the box, you are expected to define things your self using entity schemas
before typeorm will understand, but YOU are in control and once it is working, it is truely a powerhouse
in short this is more for power users

microOrm

simular like typeOrm, i think this is just a preference of syntax, i dont have any experience working with this one but felt to at least mention it

SQL (no ORM)

good old queries, old and fashioned, nothing wrong in particular, but these days people rather avoid it
you have to write queries, a mistake with user input and you have a SQL injection and by by database, but if you know your way and know your security it is a great tool none the less
still think people should start here before learning a ORM

conclusion

there isn't a best ORM they all got their ups and downs, stick with what works best, depending on the requirements of the project
ask your self these questions

• is the project big or small
• is there going to be a lot of data stored
• is this data consistent

then decide either alone or together what to use

[boreal ferry]

Prisma

[blissful gust]

With TypeORM, never needed to see anything else, works pretty well with multiple SQL DBs (i used it with PostgreSQL, Oracle and MySQL) and it is also good for migrations.
For MongoDB i never used TypeORM, just Mongoose, which does what is supposed to do but never shines (and sometimes it is really frustrating to use it with Typescript).

Also, based on TypeORM, you have nestjsx/crud library, which is amazing for standard RESTs, especially filtered/paginated GET.

later forked into rewiko/crud
later forked into https://github.com/gid-oss/dataui-nestjs-crud

[abstract cliff]

It appears that TypeORM became just another abandoned project. There hasn't been any active development since January

[lyric jay]

i heard that there's a new trendy orm name drizzle is nice. Kinda try it in my next project 🥸

[lusty idol]

Since I am working with oracledb options are super limited in regard to ORMs or query builders.
So I am a bit stuck at typeorm

[teal grove]

I'm late but from my very short exposure to different ORMs. Node.js has no ORM that's "finished".

[sharp musk]

Would you guys share a repo where is shown how to correctly implement DrizzleORM with NestJS?

[lucid glen]

My team/project is targeted to exactly one database: postgres. We've opted to just use the raw postgres node library. We did some spikes using typeorm and sequelize and we found the raw pg library to be simpler to use and more easily support our use cases.

We rolled some boiler plate functions to make life a bit easier but all of our queires are 3+ table joins, all of our inserts are really upserts, and all of our inserts need to be in a transaction, and it was easier and more performant to just use the raw pg library. I find it way easier to just write the damn sql than it is to figure out how to get the ORM to generate the sql I want.

[teal grove]

I want to be that comfortable with SQL 😬

btw, try Kysely.

I think you'll really like it (or really hate it).

[lucid glen]

@teal grove sql is pretty simple once you get the basics and the basics can get you pretty far..plus a lot of companies still employ DBAs that will help with the really complicated sql. Some orgs won't allow "sql generators" (what they call ORMs) for certain types of databases, usually mainframe db2, and will provide you with the sql and you just run it. A worthwhile skill and once you get the bascis you'll find it's a lot easier than using an ORM once you're past the "select by primary key" statements 🙂

[weak helm]

i agree

[brazen canopy]

Typeorm is powerful but its docs is a bit messy. I love its migration feature. Repository pattern is nice. Working with most of db systems. It needs a bit of typesafety like drizzle and it is perfect for most of use case.

[teal grove]

TypeORM is too buggy.

It has many bugs that remain unresolved for... years.

[brazen canopy]

I'm not sure about how you use TypeORM, but i treat it as dump ORM, without complicated business logic there.

[slim grove]

I agree. You should use typeorm for basic crud operations. Anything that's beyond that should be solved with custom raw queries. It has a lot of bug reports but I never ran into any of these bugs myself. People try to use orms for everything even for complex queries but that's a horrible idea

[digital light]

typeorm is one of the worst orms so that's expected

[brazen canopy]

Our production code is based on DDD, that's why ORM choice is flexible, because we don't rely much on "smart ORM features".
Migration and basic CRUD (with nested relations support) is enough for us.

It has tradeoff (performance is not the best), but for an enterprise app, correctness is top priority.

Also, MSSQL support is a must for us. We don't have any issues so far with TypeORM.

We don't use any of SelectQueryBuilder, it's ugly and maybe buggy as many of you pointed out.

[lusty idol]

I would be interested to see that exquisite DDD orm approach 😄

[brazen canopy]

i'm also curious to know what're you using if not DDD 😄

[short flower]

I was watching this thread for the longest time just for my own person amusement. I also gave my 2-cents way back when. It kinda shows the predicament TypeORM is (or maybe was) in. Needless to say, the package gets very little TLC, which is a red flag to me for any serious application usage.

There is a fairly recent comment where an Italian company announced is taking over TypeORM's maintenance and possibly rewrite (which it needs desperately, see the comment above the one I linked to). However, they also called for support from the community for devs to become maintainers with them. That to me sounds like a fail waiting to happen. But, I'll try to be positive and wish the the best.

So, my personal view on TypeORM is, avoid it for now. If that "new" team can get it on its feet, then it might be worth looking at again. I don't use ORMs much or rather relational databases. So, it is even easier for me to say that. 🙂

[lusty idol]

sweat and tears mainly

[lusty idol]

I also tried to help by reviewing PRs and by creating a discord channel for more immediate support

[brazen canopy]

Could you clarify more on this please ?

[lusty idol]

Wrestling with all involved parties what boundries could be and rudimentary implement that

but no defined bounded context, definition of aggregates and so on

cardhouse DDD 😄

[brazen canopy]

lol, you're doing it wrong ?

[lusty idol]

that would be one way to put it

[brazen canopy]

I think DDD has only guidelines with concepts, so it's fine to be wrong.

The important part for me, is we keep single source of truth for correct behaviour.

[lusty idol]

there was no precept to DDD but I am trying to sneak it in

but I lack the experience and knowledge to push it

[brazen canopy]

I see, it requires a bit of motivation.

[lusty idol]

hence the interest in examples

[brazen canopy]

My experience without DDD is one word: PiTA

Because you have no single place to verify your system.

[lusty idol]

@brazen canopy if you were not aware yet https://discord.gg/9kstXXux

[brazen canopy]

The fun and biggest win is we moved all E2E testing logic into the unit test of the core 😄

[lusty idol]

our business logic resides in the DB

[digital light]

Nest still endorses TypeORM in its official documentation though

[short flower]

Yeah. What I said is my own opinion.

[digital light]

[brazen canopy]

Lol, DDD with JS/TS is a real challenge, because runtime error is everywhere .

[lucid glen]

I agree that ORMs are only useful for really basic crud operations, usually just about limited to "find by primary key" type of statements. But what about the case where basic crud operations are the exception and the rule is custom "seven table joins" ? Does it even make sense to bring in an ORM? Especially if you're using something else (flyway, liquibase, etc) for migrations.

I'm asking because frankly I've yet to find a viable use case in 20+ years of programming for an ORM. I've always had something else (usually forced) for migrations and have never had an application made up of simple queries.

[slim grove]

Typeorm has an issue with joins involving multiple tables. In that case I think performance has to be evaluated carefully and if raw querying makes more sense I'd go with that. Even sometimes it could give better performance to execute queries separately instead of joins when you involve a lot of tables. To be honest with you, my favourite orm is hibernate that's available for java & spring projects. It supports all of these options to optimize performance. You can decide whether to join in a single query or send queries separately and specifying that is very easy via a language called JPQL (Java persistance query language). Also it has the concept of persistence context and batching where queries are sent in batches to optimize performance

In javascript / typescript world, I've never seen an ORM that's this mature to be honest. So the only ORM that I can recommend without hesitation is hibernate

[lucid glen]

Yeah, Hibernate wasn't half bad. I preferred something called "MyBatis" in the Java world though.

Though, I don't think MyBatis is technically an ORM.

Or what most people consider one to be.

[slim grove]

Yet, I think still an ORM can never be enough for all use cases. It just saves time for development

[lucid glen]

Until you have to rip it out 🙂

[slim grove]

Oh yes. So languages like Java have really mature libraries. But for nest, the one I like the most is typeorm because of utilities it provides

[lucid glen]

Really? nothing quite as mature as hibernate? Ugh, that does explain why I've not found one that I liked in the js/ts world. But I'm used to hibernate, entityframework (dotnet), activerecord (ruby) which were very mature and good

[slim grove]

Yes they are really good in my opinion

Maybe this is not a channel for praising java tho 🙂 But I just wanted to share I like it the most

Even entity schema validation against the actual database is really cool

[lucid glen]

Java as a language isn't that great but it's not THAT bad. It's got some great software built around it though.

But I hear ya. I'd take js/ts over java anyday though

[slim grove]

Yes language is kinda old. It's the frameworks and utilities around it that make it a good choice

[brazen canopy]

MikroORM fits my production-grade ORM standard.

But hey, the cost of Java is well-known 😄

[slim grove]

I think it's a tradeoff. If you want advanced features like AOP, strong typing, all the testing utilities etc, you can compormise a bit on resource usage and development time. Still a lot of enterprise scale apps use java for a reason

[minor epoch]

I think that reason is probably more that enterprise scale companies are slow to change and java is "good enough". Not that it has anything special.

I love the concept of Java for what it did with the "run in any machine" concept with the JRE and stuff and for that I get why it was well used. And I think it's still useful on integrated hardware. But I don't think it has much of a place in modern web dev. I also detest java. Partially because I find the community support and documentation lacking and just in general because the language is way too verbose and ugly.

But in any case I digress given this is a thread about ORMs. 😅

[crimson fossil]

what about drizzell?

[slim grove]

I wouldnt say it does not offer anything special. Spring AOP is a game changer for me. Just today I spent hours trying to find weird tricks to add AOP to our nestjs project at my job. But this is a 5 min job in spring. I'm not even talking about the pain of making sure swagger doc is correct in ts. People tend to think in terms of language only. But it's the framework that makes it powerful. Also Java seems to be used quite a lot for the reasons I'm talking about if you see the image. But NestJS for sure is the best framework in TS world. I'm not trying to praise another framework over nest. I just shared my opinion about Java's ORM which I think is the most mature one. I'm still a NestJS developer myself

[nova wyvern]

I am using TypeORM, and for my team, it's the best solution due to its great migrations and type safety out of the box. In my side projects, I use Prisma ORM, but migrations are challenging; I often encounter issues with them.

It often depends on your expectations.

On the other hand, there's Drizzle ORM, which I believe is great for low-level SQL work.

[shy bloom]

When you say help with migrations. You have manually type them right in production environment?

[nova wyvern]

When I mention issues with migrations in Prisma, I’m referring to challenges that sometimes arise during the migration process itself, not that we manually type them out in production. Prisma’s migration system is generally reliable, but there can be complexities or edge cases that might require manual intervention or additional steps. For instance, there might be situations where you need to adjust or handle specific scenarios that Prisma's automatic migrations don’t fully address.

In production environments, we use the migration tools provided by Prisma to apply changes, but we always ensure to thoroughly test and review migrations in a staging environment before applying them to production. This helps in minimizing any potential issues that could arise from automated migrations.

[steel hull]

I found here interesting discussion and article about choosing db
https://substack.com/@refactoring/note/c-65851391?utm_source=feed-email-digest

[faint sand]

how about MicroORM its seems like to be nextgen of TypeORM, also looks active repository

[lusty idol]

no oracledb support

I think none of those have seen the light of day yet

oh well mssql did

[still tulip]

Do you have any tips on testing migrations in a staging environment? How are you 100% sure that it won’t put prod in a weird state?

I think something my team struggles with the most is migrating columns with JSON columns. The SQL to manipulate JSON columns can be really difficult to write and understand.

[covert lichen]

Take your prod data regularly, anonymize it, load it into staging, then run your migrations in staging as usual

[lusty idol]

can you elaborate that more ? how do you anonymize and get empheral staging and so on 😄

[tacit bear]

I'd appreciate if you shared more insights on your setup here: #1290666645949251727 message 🙏

[minor meadow]

I use mikro orm in production application. And i find this a good orm and also a good alternative to typeorm (similar configuration, annotation , .. ) but with fewer problems in case of issues on github , .. And the main thin that mikro is active also

[little chasm]

With how established, well-supported and battle-proven sequelize is, I'm surprised it doesn't get mentioned more. Any specific reason or just not "hot stuff" enough? With the future of typeorm being very uncertain, I'm considering to switch to another ORM, but considering how baked-in an ORM can be in an application I have serious hesitation towards other pre-1.0 packages like drizzle.

[digital light]

seems like sequelize has poor typescript support

Drizzle indeed feels underbaked, but there are better alternatives. Mikroorm for example. Or just go querybuilder route and use kysely

[little chasm]

Most things can get done with sequelize-typescript. It feels pretty typeorm-ey when used with nestjs

[digital light]

I feel like this 3rd party package defeats the argument of "sequelize is battle tested" because it abstracts some of the actual sequelize

also it hasn't been updated in a while

[storm thorn]

All these tools do be getting us stressed out, damn. As of the time of this comment, unfortunately Typeorm kinda dying since there haven't been any commits for past 9-10 months. As for Prisma and Drizzle, Prisma is pretty active, they have +3k issues open, but I like the fact that they are constantly working on the project and this is really promising, I dont like the fact that relationJoins is a preview feature while it should have been there at first by default INSTEAD of doing separate queries and join objects lol. Drizzle is also active, what I dont like in terms of maintenance is that they have left so many open issues on read, like at least add some labels to it, or if it inst an issue then just close it. It makes me feel like they not even looking at the issue, on the other hand Prsima label their issues quickly which makes me feel safer.

as myself being stuck in what to choose, to have good DX, type safety and fast queries, I think so far Prisma+Kysely is the best option to get advantage from since Kysely is available as a plugin to Prisma. I guess this stress never finishes ...

And I dont get why people are very against Nosql? I have used Mongodb in production, thousands of big companies do, if it was trash, they are smart enough to not use it ..

[little chasm]

Fair. But I believe sequelize is effectively working on making it obsolete. They recommend using it at this time in their own docs.

Point is: sequelize has been around forever. They are on their 6th major. They have 2 million downloads per week. They have some big backers. And most importantly, their github org has some 10÷ people, not just two.

Just seems the safest bet in terms of "will this still be maintained in 2 years".

Imo, at this point, nestjs might be best advised two make it their default recommendation. It's kinda their second choice already and with typeorm being in such a bad place it only seems natural to take action 🤔

[digital light]

Yeah

[lusty idol]

maybe ppl prefer the decorators from typeorm or the helper functions. Selling point for me was at that time oracle db support. That's where all newer ORMs fail. Prisma promised custom connectors in 2019, but I guess it came in the way of monetization so it never came, and since for example Oracle is not financially attractive enough it was never done as a prisma connector also, nobody else was made able to provide it.
Maybe it is just the DX that feels nicer with typeorm. You know, having annotated classes instead of sequelize.define and so on

btw. that may change in sequelize v7

https://sequelize.org/docs/v7/models/model-typing/

[crisp ginkgo]

I have used sequelize, TypeORM, knex, prisma . 😄
in my experience, prisma is the best to use. simple and stable, type-based ORM.

[lusty idol]

no support for oracle 😄 otherwise I am pretty sure I would have tried it

https://github.com/prisma/prisma/issues/2853 maybe someone wants to chime in 😄

[crisp ginkgo]

@lusty idol probably you and me will do ? 😄

[trail plover]

query builders > ORMs

[plush zodiac]

hardcoded queries > query builders

[covert lichen]

Coding your own rdbms > hardcoded queries

[trail plover]

coding your own relational database > coding your own rdbms

[covert lichen]

Who needs a rdbms when you can have a rdb

[lyric jay]

I'm currently using Mikro for company project, will deploy production next week

[nova wyvern]

Great question! Testing migrations thoroughly in a staging environment is key to avoiding issues in production. Here are some tips that have worked for me:

Mirror Production: Make sure your staging environment closely resembles production in terms of schema and data. The more similar it is, the better you'll catch potential issues.

Run Migrations Multiple Times: Apply the migrations in staging a few times, especially if you're tweaking them. This helps uncover any hidden dependencies or order issues.

Backup: Always back up your staging database before running migrations. If something goes wrong, you can easily restore it.

Use Realistic Test Data: Populate your staging with data that mimics what you have in production. This way, you can see how the migrations will behave in real scenarios.

Manual Testing: After applying migrations, go through the application and test the critical areas to ensure everything is working as expected.

Monitor Logs: Keep an eye on logs during and after migrations. Look for any errors or warnings that might pop up.

Version Control: Keep your migration files in version control. This helps track changes and makes rolling back easier if needed.

Collaborate: Share your findings with the team, especially when it comes to tricky migrations like those involving JSON columns. Two heads (or more) are better than one!

For JSON columns specifically, I recommend using helper functions or libraries to simplify the SQL manipulation. It can get tricky, so documenting any complex queries can help the team understand what's going on.

By following these steps, you can feel much more confident that your migrations will go smoothly when it’s time to deploy to production!

[tulip onyx]

In my company after lots of disapointments using of typeorm we've migrated to mikro-orm. Long story short, mikro-orm has been an excellent choice.

[nova wyvern]

Hi there! You mentioned experiencing a lot of disappointments with TypeORM before migrating to MikroORM. Could you share a bit more about what issues or challenges you faced with TypeORM? I'm curious to hear more about your experience! In my company, we are using TypeORM and it's working perfectly fine for us.

[lusty idol]

https://github.com/typeorm/typeorm/blob/master/docs/future-of-typeorm.md just to give an update

[blissful kindle]

prisma all day long my guy

[blissful kindle]

Once you go Prisma you never go back

[lusty idol]

famous last words for vendor lock in 😄

[blissful kindle]

https://tenor.com/view/chill-gif-22122998

[undone citrus]

Glad to see TypeORM is being revived 😄
MikroORM is IMO the closest JS has to the full-fledged ORM as seen in other languages (hibernate, entity framework, etc.). I am surprised it's not used more.

[lusty idol]

and no oracle support

[lusty idol]

just a basic comparison
they look pretty similar

[undone citrus]

@lusty idol Syntax, and some concepts are similar, but main difference is in the architecture - specifically how MikroORM utilizes identity map / unit of work patterns which are very powerful.

[lusty idol]

I don't know much about the inner workings of both

[versed wadi]

Best orm = raw sql query 😄

[faint sand]

this about community bro, no community, no support system, Mikro is not bad only no supported by many community

[sharp otter]

I think this isn't about what ORM you used but about how you can use a database so well and you can optimize SQL even by just using raw database query

[brazen canopy]

Sadly, it's not the point of ORM. ORM most important goal is to abstract the sql layer so that you don't depend on any specific driver.

[frank geyser]

Sequelize.

[regal karma]

literally but in a bad way

[inland hedge]

Best orm? Don’t use any

[frank geyser]

You create your applications using pure HTTP too, right? Without Express, without Nest, you connect directly via socket and everything is fine.

[inland hedge]

With over 20y as engineer, I can assure you that you don’t need ORM, but abstraction with query builder for crud, maybe some primitive AR for operations like findOne or by id. More complex queries are easier to write by hand. Which also implicit more knowledge to learn which is always a plus

[frank geyser]

I agree, many-to-many relationships or more advanced queries work better in hand than ORM, which does not invalidate the use of ORM, or even its combined use.

Sequelize for example allows you to create custom queries, so it is very useful to use this ORM.

[mild wigeon]

I even think query builder for crud not always necessary if it's a smaller b2b application.

[inland hedge]

When I wrote query builder I was thinking about libs like knex or so, to provide types with useful interfaces and prevent sql injection

[lusty idol]

https://kysely.dev/ is there also

but as always, no oracle support 😄

[frank geyser]

Sequelize has Oracle support

[tawny tinsel]

im the best orm

try me

[frank geyser]

https://tenor.com/view/gay-phrog-lgbt-phrog-gay-pride-phrog-pride-phrog-tootymcnooty-gif-26620662

[short flower]

Please, let's keep useless posts off the thread. Thank you!

[covert prairie]

https://mikro-orm.io/ I like that I can create/ update entity and relations in that way

// Create

entity = this.entityRepository.create({ id: '1', name: 'admin', roles: [{ id: 1, name: 'admin' }, { id: 2, name: 'user, '}]}, profiles: [1,2,3])

// Update

this.entityRepository.assign(entity, { id: '1', name: 'admin', roles: [{ id: 1, name: 'admin' }, { id: 2, name: 'user, '}]}, profiles: [1,2,3])

[idle falcon]

Prisma is a cool and easy to use but its a love-hate relationship

[tacit frigate]

MikrORM !!!!!! 👌😎

[idle falcon]

MikroORM looks nice, I like that it because it uses classes and decorators and it reminds me C# and Java but it's not nearly as popular as other ORMs

[orchid plinth]

https://orm.drizzle.team/benchmarks 👀

[cunning grove]

I'm in the process of converting a large app to MikroORM. It's still pretty early, but I like it so far

[round meadow]

https://mikro-orm.io/docs/transactions

[last socket]

yeah definetely i choose this orm because so easy to learn and so powerful to use in any cases

[celest kraken]

Definitely. The repository model is super easy to use for 90%+ usage.

[regal karma]

typeorm is the sheit

(in a bad way)

[regal karma]

you should update this after playing a bit with drizzle

[wet coyote]

heard it but never looked into it but will later on
i'm dealing with personal things in my life that eats up any energy and motivation to even write code most of the time so that will be a while tough

[regal karma]

goodluck dude

hope u make it

[wet coyote]

nothing threatening tough so it's not like i wont get trough it

[brave stream]

im using drizzle for now, nothing to complain about

[dim loom]

Sequelize is the best among all

[inland hedge]

XD

[shy imp]

If you have complex typing and want to become depressed I suggest using Prisma. In all fairness though, we have been using it in a fairly large project and it has been great, there are some quirks (which every orm in ts has I believe) around return types but all-in all great

[viral turret]

Using drizzle, kinda new to nestjs, looking for the best way to modularize it (you know, injecting the schema…) any help here?

[regal karma]

https://www.youtube.com/watch?v=4xMDAqcwzp8

[versed wadi]

is Typeorm obsolete by now... im trying to find out wich orm or querybuilder to use.. constnatly bump into posts how typeorm is dead.

[tacit bear]

Typeorm was recently transferred to new maintainers, see https://typeorm.io/future-of-typeorm. It is now lead by team that also builds vendure - an ecommerce platform built on top of NestJS (that uses typeorm).

[torn thicket]

Hi, i am looking for a way to access DB using stored procedures. Could anyone suggest what would be the most suitable ORM for this? Also any resources, links or documentation on this would be highly appreciated 🙏

[lusty idol]

I guess, raw sql via the driver

[versed wadi]

const result = await myDataSource.query("CALL my_stored_procedure(?, ?)", [param1, param2]);
console.log(result);

in postgres, use SELECT my_stored_procedure($1, $2)

call it in uir backend... make an endpoint.. and call the endpoint in your frontend instead

ex:

fetch("/api/call-stored-procedure", { method: "POST", body: JSON.stringify({ param1: "value1", param2: "value2" }), headers: { "Content-Type": "application/json" } }) .then(res => res.json()) .then(data => console.log(data));

[grand mica]

Has anyone seen/built a NestJS combined with sqlc yet?

[lusty idol]

What is SQLC?

[edgy nexus]

https://sqlc.dev/
https://www.youtube.com/watch?v=VX6KzpjaPp8

[inland hedge]

Most relevant https://www.npmjs.com/package/@pgtyped/runtime

[leaden citrus]

Which ORM has good vector support. like drizzle

[cold sand]

sqlc supports javascript now?

[grand mica]

Have you ever heard of an ORM that can be provided a user by context, and it will automatically scope what things can be returned from the request?

[spring crescent]

For sure, Supabase is suitable I think, let me know if you want to discuss in more detail in a private chat

[soft arrow]

The problem with Mikro ORM is that it's one essentially one developer. I prefer using open source projects with broader support and like a more lightweight ORM. I have moved on to drizzle

[rose scarab]

SQLC sounds beautiful on paper, but sadly the typescript version is still marked as a beta experiment and there doesn´t seem to be much activity happening around the project 😦

[edgy nexus]

You're right.
I've been using the Go driver for a while now, sometimes it messes up the psql column types in the generated code, and I have to manually overwrite (map) them in its yml cfg file, but other than that I'd say was a smooth experience. Hope they release an stable TS driver

[rose scarab]

As someone who really doesn´t like using ORMs, I really wish the project moves forward. I don´t have the bandwidth to participate but would love to have such a great tool in our node land.

[acoustic fox]

I've been developing TypeSQL - https://github.com/wsporto/typesql (similar to sqlc, but for TypeScript only). It includes features like nested query results and dynamic queries. I also started developing a VSCode extension (TypeSQL Language Server), which provides autocomplete for SQL (SQLite and Postgres).

[rose scarab]

Looking good!
What difficulties are you facing? Are you alone on it?

[acoustic fox]

The main difficulty is covering all the SQL grammar—it’s quite vast. The good news is that with TypeSQL I won’t get stuck even if it doesn’t work perfectly for a specific case. I can still use it to generate the boilerplate, then either delete the .sql file or rename it (like to .sql_) and tweak the generated code. After that, TypeSQL won’t overwrite my changes.

[rose scarab]

Makes sense. When I gave it a go and started thinking about it I gave up at the idea of supporting the whole SQL grammar. It's quite a daunting task, congrats on having what it takes to climb that mountain.

[wet coyote]

why not may i ask?
it saves you a bunch of hassle dealing with SQL queries saves you the risk of SQL injections and offers features you extra features

suggest looking [here](#1156124199874732033 message) where i name the ups and down of the most common ones

[rose scarab]

I am very sorry because I'm a bit short on time (currently on holidays and not super available) to provide a proper answer but I'll give a few quick points and develop some time later if I find the time to.

Firstly, I would say most ORMs provide an unnecessary abstraction. They exist because people wanted to map data from a database into POO compliant data structures.

This is an issue because the way we interact with a database doesn't really play well when approached as if we were simply managing objects. That would be a good recipe for performances issues, at the very least.

Secondly, there's no standard when it comes to the API so every ORM has its way of doing things and every time you switch project/company you possibly have to relearn how to use one of your main tools (if you do backend). That sucks. It's a useless time sink because your knowledge doesn't transfer to new places/experiences/codebases.

That is valid for both developping and debugging.

On the topic of debugging, and as a third point, there's no way one will write effective and performant queries with an ORM unless they know how to write SQL. The same goes for debugging as you must understand how a database works and how queries are written/run in order to properly debug them. So everybody that does serious backend work and interacts with databases will have to learn SQL ultimately, if they want to be relevant.

These points make learning SQL much more interesting/relevant, in my opinion.

So what's left for the ORM is dealing with connections (pooling, and possibly other details) and security.

Other than that, I just don't see the value in using an ORM at all. That is especially true if you use a pattern like repository that would encapsulate all SQL queries in a same location per domain.

[wet coyote]

enjoy your days first of all because of this i'm leaving this as a post rather then a reply

agree on the point people should learn SQL i mentioned that in my post meant to help people pick a ORM
as somebody who experimented with several ones i know what i'm dealing with
you see it as time sink i rather see it as a learning experience and who knows maybe you need that knowledge one day

having the experience of both SQL and NO SQL already gets you a decent understanding of most ORMS
and regarding performance i think this is hardly noticeable and i have over the years used several
a ORM isn't just a translation layer it offers more then that

[shell mica]

I've absolutely used ORMs that have made some horrendously underoptimized joins that I never would have written by hand, and when I did write out the SQL it performed significantly better (I think something along the lines of two orders of magnitude better). Simple reads of a single object, an ORM won't do anything crazy. Joining across multiple tables and needing window functions or CTEs? You'll probably want to drop to the underlying query builder (your ORM does allow for that, rigth) or write raw SQL for it. I just cut the middleman out and use kysely directly

[wet coyote]

what kind of ORM was making that mess may i ask? the ones i used so far was not that bad imo

[shell mica]

Sequelize was making some cartesean joins instead of left or inner joins

[wet coyote]

that explains why i never ran into issues
TypeOrm is my preferred choice

[minor epoch]

Have you found a good way to do e2e tests with TypeOrm setting up databases? I like to have a handful of e2e tests that do real database interactions, but the way that TypeOrm does transactions and its connection pooling have made it difficult.

[wet coyote]

Have not touched unit tests or learned it so far already deep in my project so prob going to learn it after it is done

[slim grove]

Another optimization issue with ORMs in my opinion is to select all columns from all tables even when most of them are not even needed. Sometimes creating your own read models and only fetching relevant columns could affect the size of the result set

[slim grove]

But I do think ORMs can be used to avoid writing basic crud queries to speed up development. My favorite is MikroORM as it also comes with a very powerful query builder (knex) which has a very similar syntax to SQL. Only downside of knex is lack of type safety. You will need to test them carefully

[shell mica]

Isn't Mikro now built on top of Kysley instead? I remember them trying to head in that direction

[slim grove]

Oh I didnt know that. After a quick reserach, I saw they are planning to integrate kysely indeed. But their api still has getKnex method for now. There's also arguments on keeping both knex and kysely side by side which is interesting @shell mica

Wow they already implemented kysely but probably not yet in final release:
https://github.com/mikro-orm/mikro-orm/discussions/5646

[worn stratus]

I’m looking forward to the release of MikroORM v7
https://github.com/mikro-orm/mikro-orm/discussions/6116

[ocean cipher]

https://benchmarks.prisma.io/

[worn stratus]

It looks like Drizzle ORM is also preparing for a major update.
https://github.com/drizzle-team/drizzle-orm/issues/3663#issuecomment-3239498914

• https://x.com/DrizzleORM/status/1961524446638084533
• https://github.com/drizzle-team/drizzle-orm/pull/4439

[boreal cloak]

ZenStack V3 beta has just been released. It combines the benefits of both Prisma and Drizzle:

• Compatible with Prisma's schema and API, along with several enhancements.
• Drizzle-level flexibility, performance, and better extensibility based on Kysely

https://zenstack.dev/v3
https://github.com/zenstackhq/zenstack-v3

Disclaimer: I'm the creator of ZenStack

[torn laurel]

We started our large project with Prisma and we had to jettison it. Nx monorepo with ~40 NestJS services with a majority of them requiring database connectivity. With Prisma, each dist had to include its own binary which heavilty bloated our deployments by 50+MB for each service. At the time (do not know if this has changed) the Prisma config was locked to a single schema (Postgres) which didn't work for us for some cross-schema services.

The project we started was API-first (GraphQL) so Prisma seemed like a logical choice, but ultimately Hasura was doing all that work for us.

We switch to TypeORM as it supported the repository pattern and is just generally flexible and just works (tm).

RE: abstracting

I am fond of the ports+adapters pattern for abstraction. YMMV depending on the size of the project, how much unit testing you intend on implementing etc. This pattern allows you to switch out your data layer cleanly. We need to heavily rely on unit tests due to our team make-up (lacking formal QA) so we need to use development patterns that support that.

[short flower]

@torn laurel

each dist

What is a dist?

[torn laurel]

Each application that is built is a dist (distro, distribution) .

dist/app1
dist/app2

With Prisma, for each app (dist) we had to haul around a built binary for each database schema the app had access to.

[silver basin]

Wait did you use typeorm postgres ? or mysql ?

[torn laurel]

Postgres

[silver basin]

Didn’t you get any wired sql when creating migration from typeorm??

[torn laurel]

I do not use typeorm migrations. I use hasura to generate and apply them

[empty epoch]

tbh i'm wondering if I should wait for drizzle 1.0 or switch to mikrorm. i like some parts of drizzle but it's also quite buggy in other ways and the bugs have been open for years sometimes while they do the rewrite

Like I get wanting to do a refresh but it's a bit frustrating to see big bugs just sit there for so long

[crude summit]

Drizzle guys are more active on twitter than on github i think heh

That being said, I've had pretty good luck working with drizzle using the repository pattern in nest. It hits a good level of abstraction for me and I enjoy the nativeness of typescript and keeping sql closer to heart

[warm loom]

Did you guys having issue with new prisma update?

[lusty idol]

since I use oracle db I don't have an issue with prisma update 😄

and since oracle or custom connectors are still not on the roadmap I guess I won't in the future

[final grove]

Hey everyone, I've been struggling to configure Prisma 7 with Nest, Next, and Docker, so I put together this template that has everything ready (including authentication with cookies, HttpOnly, and server actions). Here it is if you want to save yourself the setup :v
If you find it useful or want to save it, I'd appreciate a star ✌️
https://github.com/RodrigoAlexander7/login-template

[rough harness]

So recently while developing an data api for my app, after like 2-3 months using Prisma i stumbled into an error, more like a lack of support of Prisma, for MongoDB, where it doesn't supports unique optional fields.

So lets say we have a user:

const type User = {
    name: String;
    email?: String;
};

you see that the email field is optional, so the user can specify it later.
When adding this data to MongoDB the empty fields become null as a default behavior of MongoDB, so if more than one user omit their email, Prisma will throw a constraint error due the the database having a column with value equals to the one were trying to add.
Despite you can clearly see that a field is optional and unique in the .prisma file, Prisma doesn't support this for MongoDB, and to fix this you have to setup the field as a sparse index, which makes so when validating the constraints if the value is null its ignored.

To continue using Prisma I would need to manually set the index as sparse directly connecting with MongoDB after Prisma sets the schema to the db, which to be fair doesn't sounds good at all.
So ive spent some time, asking the ai where had occurrencies of prisma being used on the codebase, dropped it and replaced with Mongoose wich has full support for MongoDB.
It was quite easy to migrate to Mongoose actually, but yet i need to run some tests to ensure it works as intended.

[short flower]

I use Typegoose for MongoDB. 🙂 I think it is the best solution for MongoDB usage in Nest. I'm biased though, because I am the maintainer of the Nest Module for Typegoose. 😉

[rough harness]

lol, thats cool, i'm currently using mongoose with @nestjs/mongoose
may take a look at typemongoose later

[rough canopy]

Best one so far is Drizzle ORM, moved away from TypeORM after 4 years of use. Now all my codebase is using Drizzle.

BTW, i used primsa for couple of projects. but wouldnt really recommend it anymore. It's just too much boilerplate code. Typescript is enough.

[rough canopy]

Big time TypeORM fan though. I'd suggest this for MongoDB

[short flower]

TypeORM doesn't have the biggest feature of Mongoose called discriminators. It's quite powerful, because it allows for data polymorphism. It's why I also love MongoDB. Sure, you could do discrimination in SQL, but it is much, much harder.

[rough canopy]

Pretty cool. I think the usecase is diffrent here. TypeORM want you to make the DB predictable, as such if you read the entity file you know what's happening.

[short flower]

you to make the DB predictable

With Mongoose discrimination, the DB is predictable, it's just you can change the object's form at will, depending on the type. It's similar to a union in TypeScript, sort of. The idea being, the schema is shared across any number of objects. The Typegoose docs have a good example of what I mean.

Oh, and you can have nested/ sub-document discrimination too. This is practically impossible to do with SQL.

[rough canopy]

Just read the typgoose docs, this is pretty neat.

I think usecases like capturing unstructured data, other than relying on JSON in SQL. This would do a lot of help here. Very intersting.

[short flower]

I think usecases like capturing unstructured data

What do you mean by unstructured data?

[rough canopy]

somehting that isnt predefined

[short flower]

To be clear, the data you store in MongoDB via Mongoose/ Typegoose should always be structured. The huge paradigm shift between MongoDB (and most NoSQL dbs) is the structure is purely in the code, not in the database. This is what makes NoSQL liberating compared to SQL and once you get the paradigm shift, you'll never want to go back.

In Mongoose (via Nest's module) or Typegoose, your TypeScript entity classes are schema definitions for your database. You don't need extra SQL migration commands to get your application running.

No migration versioning. No SQL friction at all. Liberating.

[timid gate]

The main problem of Typegoose is validation via blazingly slow class-validator/class-transformer.

[short flower]

Typegoose doesn't use class-validator at all, for anything. You can use class-transformer, but that is your choice to make. In fact, I personally created an example of using a much better transformer in the Typegoose docs.

[ionic token]

But how is then migration handled. E.g. you have a prop that is called “isDisabled” with boolean value and want to migrate to “status” enum prop (e.g. “inactive”, “active”, “pending”)?

[short flower]

Good question. You'd usually create a new property, make business logic code to handle both properties, update the new property in the database for whatever status should match the old true or false value, then deprecate the old field. Once you are certain it is safe to do so, you can refactor out the old property. This process has no down time.

In other words, the only migration work you do in MongoDB is data mutating. 🙂

[proper cargo]

momngose

[dusty depot]

Is TypeORM still the recommended/best way to start learning NestJS?

[shell mica]

I'm of the personal preference that you shouldn't rely too heavily on an ORM to write your SQL and you should know what queries are being written, so I really like query builders (kysely in particular). But you can use whatever query builder/SQL tool/ORM you want with Nest and it shouldn't change too much of the experience

[dusty depot]

I saw your comment from a few years as well about the kysely. I guess it's still the best one for you throughout these few years?
I do like to write SQL, and I heard drizzle is suitable for those who know SQL as well. I wonder what's your view about drizzle vs kysely

[shell mica]

I've not used drizzle much myself. It looks like it does a fantastic job at being an ORM, but an ORM is more than what I want. Kysely seems to hit that balance between being a type safe query builder, and staying out of the way. There's some times where it's a little annoying to figure out what the type error is, especially when you for an .as() clause for a jsonArrayFrom, but overall I find it to be straightforward and easy to use.

If an ORM is specifically what you're looking for, then drizzle seems like a good choice, but for me it's still too much of an ORM

[dim loom]

I have experience with Prisma, Sequelize, TypeORM, and Mongoose, with a preference for Sequelize

[dim loom]

yeah you can start but i would prefer sequelize

[worn stratus]

https://github.com/mikro-orm/mikro-orm/discussions/6116#discussioncomment-15740509

MikroORM 7.0.0-rc.0 is out!
Help us test it before the stable release!

[winter heart]

Mikro orm , simple as that !

[dry swallow]

In mikro-orm:

rep.find({ name: 'xxx', address: { street: 'yyy' } })

In drizzle:

import { eq, and, ilike, gte, lte } from 'drizzle-orm';
import { users } from './schema';

type UserFilter = {
  name?: string;
  age?: number;
  email?: string;
  address?: {
    street?: string;
    city?: string;
  };
  ageMin?: number;
  ageMax?: number;
};

async function findUsers(filter: UserFilter) {
  const conditions = [];
  
  // Handle direct fields
  if (filter.name) {
    conditions.push(eq(users.name, filter.name));
  }
  
  if (filter.age) {
    conditions.push(eq(users.age, filter.age));
  }
  
  if (filter.email) {
    conditions.push(ilike(users.email, `%${filter.email}%`));
  }
  
  // Handle nested address (if you have a relation)
  if (filter.address?.street) {
    // Assuming you have a relation to an address table
    conditions.push(eq(addresses.street, filter.address.street));
  }
  
  // Handle range queries
  if (filter.ageMin) {
    conditions.push(gte(users.age, filter.ageMin));
  }
  
  if (filter.ageMax) {
    conditions.push(lte(users.age, filter.ageMax));
  }
  
  // Apply all conditions
  if (conditions.length > 0) {
    return await db.select().from(users)
      .where(and(...conditions));
  }
  
  return await db.select().from(users);
}

Very crazy

Mikro-orm

rep.update(user, { id: 1, name: 'xxx', address: { street: 'yyy' } })

Drizzle

import { eq } from 'drizzle-orm';
import { users, addresses } from './schema';

async function updateUserWithAddress(data: { 
  id: number; 
  name?: string; 
  address?: { street?: string; city?: string } 
}) {
  const { id, name, address } = data;
  
  // Update user table
  if (name) {
    await db.update(users)
      .set({ name })
      .where(eq(users.id, id));
  }
  
  // Update address table (assuming one-to-one relation)
  if (address) {
    // Check if address exists
    const existingAddress = await db.select()
      .from(addresses)
      .where(eq(addresses.userId, id))
      .limit(1);
    
    if (existingAddress.length > 0) {
      // Update existing address
      await db.update(addresses)
        .set(address)
        .where(eq(addresses.userId, id));
    } else {
      // Insert new address
      await db.insert(addresses)
        .values({ ...address, userId: id });
    }
  }
}

But if you are paid for each line of code, or want to nobody know what you do and be difficult for boss to replace you, choose something else not mikro-orm :).

[dry swallow]

How to update with relationships in sequelize

// Update user and manage project associations
const user = await User.findByPk(userId, {
  include: [{ model: Project }]
});

await user.update({ name: 'New Name' });

// Add new project association
await user.addProject(projectId);

// Remove project association
await user.removeProject(oldProjectId);

// Set specific projects (replaces all existing)
await user.setProjects([projectId1, projectId2]);

[worn stratus]

https://mikro-orm.io/blog/mikro-orm-7-released

[sweet venture]

Exactly opposite with drizzle there is no magic, unless you use their relations of course, everything is clear. Everyone who has seen sql can understand. With micro - it’s all magic, implicit joins, eager/lazy loading, entity conversion. They are built with different mindset. It’s comparing apples to oranges, because they are fruits.

[dry swallow]

When don't use orm at all, because in drizzle you write magic manually (exact the same things what mikro-orm do, reinvent the wheel) and that i see make you happy (you are mazohist?)

[sweet venture]

I am not saying ORMs are useless, they have their purpose. They are great for CRUD style business apps majority of us are writing. And you have query builders - diferrent mindset. Personally I prefer opening one function and understanding what is going on, instead of navigating many entities, repositories, decorators to understand what is going to be executed in the end. I’d like to see if it is inner join or outer join, or multiple queries.
We are solving business problems not writing database access layers, once your have written you repo functions, how often do you touch them really?
As for your mazo comment, everyone on this forum is already deep into architectural mazo comparing to plain express/koa/fastify. If you just comapre number of lines to achieve the goal - surely you wouldn't use Nest if you are not getting paid by lines of codes.

[empty epoch]

I like when ORMs sorta do both? Like there's a handful that seem to handle query building and the crud stuff well

Drizzle gets close to that

But it sounds like the new version of mikroorm does too?

[obsidian echo]

Honestly, both sides make sense depending on what you’re aiming for. If you care about data modeling details or need tight performance control, something explicit like Drizzle feels solid. But when you’re just pushing out business features, it’s nice having an ORM handle the boring stuff for you.
The best tools, in my view, strike a balance, they stay explicit when you need it but don’t make you fight the framework. Drizzle and Mikro are both heading that way, which is good to see. It stopped feeling like “ORM vs query builder” a while ago; it’s really about how much control you want to keep.

[regal karma]

this thread is 2 years and a half old

and still we haven't figured out the best orm

gotta give it to drizzle

anything else and you're basically trolling but that's just my opinion

[safe sapphire]

Using Prisma for 6 months. Can't say it's bad.

[wintry sluice]

Hey ! I'm looking for a NestJS 11 project with MikroOrm v7, do you know where I can find one on GitHub?

[worn stratus]

https://github.com/mikro-orm/nestjs-example-app

[gloomy wraith]

Prisma is Good choice, you should consider tying it out.

[dry swallow]

Definition of entities is a s**t, I like decorators. I someone will write for it some decorators i will retry it.

[cerulean parcel]

Coming from .NET and EF everything feels kinda meh. Tried out mikroorm a bit, seems a lot better than typeorm atleast. But nothing feels quite right when you want to persist a rich domain model.

Anyone using something that works well for this?