Epic: ORM for database modeling and migrations about amplify HOT 12 CLOSED

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?

from amplify.

• @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.

from amplify.

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.

from amplify.

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."

from amplify.

@alwell-kevin for reference

from amplify.

was the following idea tied to this?

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

from amplify.

@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

from amplify.

@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. 😅

from amplify.

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

from amplify.

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?

from amplify.

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

from amplify.

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:

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. 🤷🏻‍♂️

from amplify.