Database Migration Management Framework

[kjdelisle]

From @kjdelisle on July 28, 2017 18:37

Scenario

FooCorp wants to deploy version 2.0 of their LoopBack application on their production server, but doing so will require some alteration of the schema in their database. Some of these changes require a more delicate touch that cannot necessarily be performed by the autoupdate function.

Currently, FooCorp would have to create their own scripts and execute them to modify their database schema to match their expectations, which may require them to more directly interface with drivers that are used by their chosen LoopBack connector, or using another implementation entirely!

Proposal

Create a component for use in loopback-next that handles the idea of production-system migration within a LoopBack application as it is upgraded from one release to another.

Originally, I had a design in my mind for how this would work, but I noticed that there are some generic migration tools on npm (https://www.npmjs.com/package/migration), and I was wondering if there was anything additional that we'd want to add to make it easier to do this sort of thing.

Martin Fowler's Evolutionary Database Design provides a comprehensive information on this topic, including implementation examples.

Other ORM frameworks have been supporting migrations for quite some time, we can inspire from them.

• Migrations in Ruby on Rails
• .NET EntityFramework's Code First Migrations

[kjdelisle]

From @bajtos on July 31, 2017 14:38

I quite like the approach of Knex - see http://perkframework.com/v1/guides/database-migrations-knex.html. It's similar e.g. to EntityFramework. I have personally used this approach at my previous job and our experience was great.

• Migrations are granular and version-controlled together with application code.
• Each code change in the app is accompanied by a small migration script.
• Each migration script has a unique id.
• The database contains a special table containing list of migration script ids that have been applied.
• There is tooling to automatically run missing migration scripts to update the database from the current schema to the latest (or any other) version.

Since we already have autoupdate functionality, our tooling should be able to generate the initial version of a migration script (create newly added columns, remove deleted columns, etc.)

[kjdelisle]

From @bajtos on July 31, 2017 14:39

Can we move this issue to loopback-next repository please, so that we can discuss this feature in public and get early feedback?

[kjdelisle]

From @bajtos on July 31, 2017 14:39
Can we move this issue to loopback-next repository please, so that we can discuss this feature in public and get early feedback?

Done. :)

[ritch]

In the spirit of LoopBack.next, we should build the foundational features for a framework like this, document those really well, and support 3rd party implementations. If one of those implementations gains a lot of adoption, we can pull it into the modules in this repo.

Our typical approach to this is to build a reference implementation, but not necessarily release it on npm (or if released, marked as "lab" or "example only" somehow). That way someone can take our approach and use it one off in their app, or build a re-usable solution.

We should focus our energy on what the best practices for developing LB apps, and making sure they are feasible. Migration is a good example of this. You should be able to write migration scripts in the same DSL / framework as you are implementing your app.

[bajtos]

Cross-posting from #53 for posterity:

strongloop/loopback#591 (comment)

I see this come up a lot, like how you have to "extend built in user model" + "automigrate the ACL, Role, etc" tables. We need a better experience than this (not to mention good docs up front if we're going to leave a bad experience like this for people just getting started).

[stale]

This issue has been marked stale because it has not seen activity within six months. If you believe this to be in error, please contact one of the code owners, listed in the CODEOWNERS file at the top-level of this repository. This issue will be closed within 30 days of being stale.

[bajtos]

Ruby on Rails is providing an interesting high-level API for defining migration steps in such way that a single sequence can be used both to upgrade the db schema and downgrade it (rollback changes).

https://guides.rubyonrails.org/v3.2/migrations.html

[BertholetDamien]

Hi @bajtos ,
Do you plan to introduced this kind of migrations features in the futur ?

We can already (did not do it yet) used automigration and npm migration package to accomplish this purpose. But, I think implementing this solution will benefit Loopback.

Thank for your feedback !

[bajtos]

Hi @BertholetDamien, we definitely want to see this feature implemented, but unfortunately our bandwidth is limited and database migration framework did not make it onto our short-term back-log (see #1839).

Would you like to work together with us and contribute the implementation yourself?

[bajtos]

Ping back from https://stackoverflow.com/q/53054245/69868.

An edge case to consider: when generating migration scripts, it would be great to detect when a model was removed from the application and generate DROP TABLE script.

[BertholetDamien]

I haven't take time to answer. No, unfortunately, I haven't time theses days to contribute into the project. Maybe in the futur, I will keep that in my TODO list if I'm available.

[hajonsoft]

I am new to loopback and would like to know if I can discover an existing table in my database and build the model using cli. It is tedious and defeats the purpose to run lb4 model and start typing my column name, datatype and whether it is null or not null. lb2 and lb3 provided a way to create a script in the boot folder to discover a specific table.

Is there is a way to have a cli command to generate the model based on a table without typing column by column?

[mamiller93]

Any recommendations on how to handle database migrations in Loopback4? I read your (@bajtos) message on StackOverflow saying Loopback doesn't support migration scripts yet but it seems as though this thread has gone a bit stale.

[romansavrulin]

I'm interested about how to handle migrations in loopback 4 too. Can you provide any tutorials on that?

[mamiller93]

@romansavrulin we ended up implementing a knex migration/seed framework as the loopback framework in its current state just wasn’t enough for production. On seed startup, we also started up an instance of the application to get context and ultimately access to the repositories so that we don’t necessarily have to muck with the database directly but let our API handle the seeding! Hope that helps a little bit.

[romansavrulin]

@mamiller93 Thanks for your explanation! I'm quite new to loopback, so any information is appreciated! do you have any notes about how to make it work?

Also, have you considered using sequelize to handle migrations ?

[mamiller93]

@romansavrulin We thought about it but decided with knex because we had previously implemented knex in a prior iteration of our application. We don't have notes on anything we've done - just the code itself, unfortunately, so below is my best notes!

The gist of seeds is as follows:

• Create a helper file that initializes your application ./application => new RestApplication(); await app.boot() and await app.start()
• In the first seed, await the initialization from your helper.
• Run seeds like you would any knex implementation (according to their docs)

I think setting up the migrations is fairly straightforward following their docs. You need to create the migration tables in your database and give access to your user. Add a configuration file (knexfile.ts) with your database configuration like the docs say and add a script in your package.json similar to: "knex:config": "knex --cwd knex/config"
I got this ^ line from a google search of using knex with typescript. Led me to a GitHub thread of trial and error. Good luck!

[romansavrulin]

@mamiller93 Thanks a lot! I'll dive into that!

[bajtos]

Ping-back from https://stackoverflow.com/q/57996164/69868

Suppose I have a project which is in production. In database I have table users with fields:

first_name: string
last_name: string

Then I want to change my schema to

full_name: string

[bajtos]

Prior art: loopback-component-migrate - a community-maintained migration framework for LB3.

[lswith]

I'd like to add to the scope of this. In Martin Fowler's Evolutionary Database Design he mentions at the bottom an extremely common occurence:

In many enterprises, many applications end up using the same database - the Shared Database integration pattern. In these situations, when one application makes a change to the database, it’s quite likely that the change will break other applications. To deal with this it’s better to extract the database as a separate code repository which is used by all the dependent applications. This common database repository should have automated behavior tests which ensure that cross application dependencies are tested, failing the build if dependent applications are affected. This is not different than having a shared software component with its own code repository.

I think loopback lends itself well here as the data access layer (models, repositories, connectors, DataSources) are all dependency injected and could potentially be separated into an external library. I'd like to see this alongside the more controlled migration codepath, as I would expect the migrations to be kept with the data acess layer.

[zzhenryquezz]

I use knex to handle the migrations of some small projects and is very cool and easy to use, In my opinion for the SQL databases is a good option to make a package that uses him in the background.

[nflaig]

I created a module to handle database migrations in lb4, see loopback4-migration. It provides a common interface to implement custom migration scripts and automatically handles the versioning of the database and the execution of the scripts based on the application version compared to the database version.