Investigate TypeORM Replacement

[seanmalbert]

[pbn4]

Very interesting comparison of ORM technologies available for Typescript can be found here.

I believe most important things for us at the moment are:

• ease of migration
• performance
• type safety

From this list it seems we should evaluate MikroORM and Prisma as both have strong type safety.

Ease of migration

For both ORMs we need to consider seeds we have defined at SQL migrations level. Prisma generates an .sql file so that means we must move typescript logic into SQL language, which might be time consuming. MikroORM has an almost identical approach to migration files as TypeORM so we could just copy paste the content of our existing migrations into newly generated initial migration.

Prisma

Prisma describes migrating incrementally from TypeORM here, in short there is a tool for introspecting the existing DB schema and generating a language agnostic model file from it. It also updates a metadata table in the DB so that prisma schema considers this DB introspection as an already applied migration.

Prisma will autogenerate all types based on the language agnostic model file which means that our code is now only responsible for defining the DTOs, TypeORM model files should be removed.

MikroORM

To define entities MikroORM uses decorators the same way as TypeORM, which means that to fully switch we need to rewite all those decorators. on existing models. Initial migration can then be generated from those newly decorated models. There are no utils to not apply initial migration we must manually tweak migrations metadata table as if it was already applied.

To switch incrementally we probably need to maintain two ORMs at the same time.

Performance

[TODO] It's hard to find any comparison between those two and TypeORM

Type safety

Both ORMs support a very interesting feature: if a nested relation has not been explicitly joined during the query a compiler will throw an error if someone tries to access it later, in other words typing is relation loading aware, which might save us some debugging time.

A nice thing about Prisma client is that it seems there is only one complete interface for querying data (TypeORM repository like). No need to user query builder when repository does not support something. Raw querying is also supported.

MikroORM exposes both a repository and a query builder.

Prisma

Quote from the linked comparison:

Prisma's unique design of generating a local CRUD client that encodes your data model allows it to achieve an unparalleled level of type safety among TypeScript ORMs. When using Prisma to manipulate and query data from your database, you'll have accurate typings for nested relation queries and also partial queries that modify the shape of returned models.

MikroORM

Both inserting and fetching guarantees strong type safety for base model and nested relations, details here.

Issues

Prisma

• No union type support
• json columns are not typed
• Allow sorting by NULLS FIRST / NULLS LAST

MikroORM

• it seems that it's mostly developed by a single person

[mmahalwy]

Following this.

[YazeedLoonat]

in addition to what @pbn4 said:
https://formidable.com/blog/2021/prisma-orm/ is a pretty solid walk through of prisma working well

nest does support both of them:
https://docs.nestjs.com/recipes/prisma
https://docs.nestjs.com/recipes/mikroorm

for prisma it seems like to do pagination & count we would do something similar to what was suggested: prisma/prisma#3087

with https://www.prisma.io/docs/concepts/components/prisma-client/pagination explaining pagination in prisma

for mikroorm
https://mikro-orm.io/docs/entity-manager/#fetching-paginated-results covers how pagination and count would function

It seems like either one of these could cover our use case, I do have a bit of a bias towards Prisma, as it seems to have more usage

[jaredcwhite]

I don't particularly have skin in this game, but I've noticed a lot of JS/TS frameworks in general seem to be rallying around Prisma, so I sort of expect that even if it's missing certain features here or there, those gaps will be paved over in time as users of those frameworks will likely be bumping up against similar issues.

[pbn4]

From my initial benchmarks it seems that a fully joined listings query takes 1/6th of the time it takes with TypeORM, which is a significant improvement but it still is an average of 300ms per request over 100 requests, which means that we simply have a lot of joins and paginating listings endpoint was a very good idea. :)

[pbn4]

Some notes on the migrations from TypeORM to prisma:

• Prisma requires Typescript 4.1.x to work, this migration worked smoothly for backend, but 4.5.3 does not work out of the box,
• autogenerated model file requries a lot of manual tweaking because we have snake_case naming convention everywhere in the DB so every column has to be renamed to camelCase and an additional @map("snake_case_column") is required for it to point to an appropriate source column
• some model field names require fixing because they are simply wrong e.g. paperApplications have a relation to assets table under file property name, but prisma autogenerated assets property
• columns of type numeric are exported as db.Decimal(x, x) and this fails during class-transformer deserialization. I changed them to @db.DoublePrecision and added @Transform decorator with a toString transformation for it to keep the API interface unchanged
• basically entire autogenerated model requires a rewrite to keep some naming conventions and fix some errors, at least this generated model is 1:1 with what's in the database, so that's something compared to manually writing it. :)

[mmahalwy]

Heads up - validations with Prisma are a do-it-yourself concern. Wherever you are creating or updating models, you have to run validations yourself, outside of the DB ones. For example, these validations:

• https://github.com/bloom-housing/bloom/blob/dev/backend/core/src/listings/entities/listing.entity.ts#L448
• https://github.com/bloom-housing/bloom/blob/dev/backend/core/src/listings/entities/listing.entity.ts#L308
• https://github.com/bloom-housing/bloom/blob/dev/backend/core/src/listings/entities/listing.entity.ts#L309

This has been my biggest gripe with Prisma atm. Someone will forget and not add validation (in our case, we didnt lowercase emails). Ideally, you create wrapper functions around the Prisma operations.

[pbn4]

@seanmalbert Right now after running sole typeorm migration:run we have some rows in the DB that we probably should move to seeding logic if we want to get rid of typeorm migration:run completely. These are:

• unit accessibility priotity types (7 rows)

 Mobility
 Hearing
 Visual
 Hearing and Visual
 Mobility and Hearing
 Mobility and Visual
 Mobility, Hearing and Visual

• unit types

 studio
 oneBdrm
 twoBdrm
 threeBdrm
 fourBdrm
 SRO

• reserved community types:

 specialNeeds
 senior55
 senior62
 developmentalDisability
 developmentalDisability
 developmentalDisability

• translations (entire jsons)
• jurisdictions (3)

 San Jose
 San Mateo
 Alameda

• unit rent types

 fixed
 percentageOfIncome

If those were moved to seeding completely we could rely only on the migration generated by prisma and prisma.schema file.

[pbn4]

@seanmalbert Since Listing model is touching almost every other model directly or indirectly I think it will be very hard to iteratively move to Prisma for existing models because:

• naming of every model must be refactored in generated prisma schema file e.g.:

model ami_chart_item {
  id             String     @id(map: "PK_50c1f3d69f4675d775e08d7465e") @default(dbgenerated("uuid_generate_v4()")) @db.Uuid
  created_at     DateTime   @default(now()) @db.Timestamp(6)
  updated_at     DateTime   @default(now()) @db.Timestamp(6)
  percent_of_ami Int
  household_size Int
  income         Int
  ami_chart_id   String?    @db.Uuid
  ami_chart      ami_chart? @relation(fields: [ami_chart_id], references: [id], onDelete: NoAction, onUpdate: NoAction, map: "FK_98d10c0d335d9e4aca6fb5335b3")
}

Now out of this definition Prisma will generate it's own Typescript *type* definitions which will be different from all *.entity.ts we have (they are generated types not classes). It means that every DTO which is using mapped types from nestjs has to also be modified. Since our main problem is with Listing it means that moving it to Prisma would yield the most profit for us, other models are not that problematic, but listing touches directly or indirectly almost every model in the system. This seems like a huge effort with many unknowns. 

I think to move in Prisma direction we should think about making Listings more loosely coupled to other models and then proceed with migrating.