Epic: ORM for database modeling and migrations

[JamesMGreene]

My experiences with Sequelize have all become cautionary tales, so I guided us away from using it at this point and instead favored just using Knex, which is a more basic "SQL query builder".

However, for maintainability's sake, it is probably worthwhile to consider an ORM for the long-term, especially with some contributors not being too familiar with databases.

I would suggest we investigate Prisma as a potential ORM in particular. 🔺

I think it has a lot of nice features that make it a more solid choice than Sequelize. It should also be able to completely replace our Knex usage, as far as I can tell at this point.

Tasks

• Install Objection in the codebase: npm install --save objection
• Configure Objection in the codebase
  • This requires a one-time configuration something like require('objection').Model.knex(knexClient)
• Create a Model for the campaigns table #141
• Create a Model for the letter_versions table #142
• BONUS: Create a Model for the transactions table #143 (:warning: currently unused)
• BONUS: Create a Model for the constituents table (:warning: currently unused)
• BONUS: Create a Model for the sent_letters table (:warning: currently unused)
• BONUS: Create a Model for the variants table (:warning: currently unused)

Status

• There is a branch created called objection-orm that installs the objection module and sets up the project for creating models.
• Feel free to create branches off that branch and send PRs to it as the base branch
• Models should be created in the server/db/models/ directory (only exists in the objection-orm branch)
• Model filename should be in a singular (not plural) form in all lowercase letters with hyphens as needed, e.g. letter-version.js
• Model class name should in a singular (not plural) form in PascalCase letters, e.g. LetterVersion

Example, at filename server/db/models/letter-version.js:

const Model = require('./_base')

class LetterVersion extends Model {
  static get tableName() {
    return 'letter_versions'
  }

  // ...additional methods, relationships, etc.
}

module.exports = LetterVersion

[manishapriya94]

Hey all as we were exploring the seeding issue that was coming up in the contributors guideline, some questions came up:

• @BeeXiong which is the source of truth between local and database production?
• @teakopp @JamesMGreene @paultela If we're using knex right now would require more SQL knowledge, is it worth it to move to Prisma or Sequelize to speed up this process?

[BeeXiong]

• @BeeXiong which is the source of truth between local and database production?

I don't know which one is the source of truth. Don't think either are actually. Production would be closest I think. I don't think it's wise to switch yet.

I think we need to build out the POST functions to programmatically update the database so that they don't get out of sync.

[JamesMGreene]

Forgot to post my updates here.

I looked into Prisma a bit last week, though ended up moving on to work on OpenSourceFellows/amplify-back-end#36 instead for most of the time.

I was super impressed with npx prisma introspect (generates schema and models based on the existing database) and was able to create the schemas for each database (production and local). As we know, they are out-of-sync. I believe we should also be able to generate 2 separate database migrations using Prisma to get each database into our desired state but that's where I need to spend more time investigating what that looks like.

Once I get a little farther and get a better feel for how day-to-day operations would work in a post-Prisma world, I would also love to get some feedback on how folks feel about using the Prisma ORM approach vs. our current Knex query builder approach.

[manishapriya94]

Hey @JamesMGreene was the following idea tied to this?
"Since we are now storing physical/mailing addresses in multiple database tables, we should probably just make a new database table to hold addresses and reference them from the relevant tables (and load them with records via JOIN operations or an ORM).

This would also allow us to validate the addresses more directly for greater data integrity, e.g. require values for at least one line, a city, a state, and a zip code at the database level.

Not an urgent requirement but something that would probably be wise in the long term."

[manishapriya94]

@alwell-kevin for reference

[JamesMGreene]

was the following idea tied to this?

Not a hard tie-in but would work well together. 🤷🏻‍♂️

[andyfeller]

@JamesMGreene : I was curious what you've found regarding attaching logic to the models generated around prisma.

looking at https://www.prisma.io/docs/getting-started/setup-prisma/add-to-existing-project/relational-databases/install-prisma-client-typescript-postgres, it seems like the models generated by prisma are generated and updated as prisma commands are called, however I don't get a sense that methods can be attached to these models for business logic that'd you want to use all over the place.

I think https://www.prisma.io/docs/concepts/components/prisma-client/custom-models talks about this but not 💯 sure

[JamesMGreene]

@andyfeller Ah, that's a very interesting observation! 😮

Yes, you appear to correct! It looks like Prisma is only providing static methods for working with the tables/collections, and the objects they return, while capable of including resolving relationships, are just plain objects rather than class instances (a.k.a. models) that could be extended with custom behaviors. 😳

Open feature request: prisma/prisma#5998

Not as ideal as I was imagining, though I'm also not sure that is a deal breaker, per se, given the potential benefits. 🤔

P.S. I see some npm packages for creating Sequelize models from Prisma schemas, which definitely makes me more anxious. 😅

[JamesMGreene]

Discussed using Objection ORM today in our backend pairing session, which is an ORM based on Knex (which we're currently using), so it might be a nice fit.

cc @waldnzwrld

[waldnzwrld]

I've been using https://bookshelfjs.org/ in another personal project and I like it.

It's maintained and widely used. Can I suggest it?

[manishapriya94]

Thanks @waldnzwrld! @JamesMGreene, the documentation looks great - do we want to focus on this for wednesday?

[JamesMGreene]

I've been using https://bookshelfjs.org/ in another personal project and I like it.

I've used Bookshelf in the past and been fine with it. 👌🏻

It's maintained and widely used.

I would agree that Bookshelf is relatively widely used, though not as widely used as Objection. 👌🏻

However, on the point of Bookshelf being maintained, I definite disagree. 😕

Its latest published version on npm is almost 2 years old and its number of open issues are fairly high:

	Bookshelf	Objection
Last published	2020-06-07 (~21 months ago)	2021-12-30 (~3 months ago)
Open issues	222	63 (~72% fewer)
Weekly downloads	73,505	114,214 (~55% more)

As far as usage ergonomics go, I found Bookshelf's APIs (e.g. fetching records) to feel a bit unnatural. Using the bookshelf-modelbase plugin helps with that, IMHO.

Objection's APIs feel a bit better than Bookshelf's out-of-the-box, IMHO, and look like they are probably more capable of advanced usage in the long haul (through relationships, various JOIN types, etc.).

Given our project's current needs, I think either one could work fine. 👍🏻

If I had to pick one, I'd probably start with Objection. 🤷🏻‍♂️