End to End test for migrations?

[BorntraegerMarc]

I'm submitting a...

[ ] Bug report  
[ ] Feature request
[X] Question

I was wondering if you @wzrdtales have an idea or a concept on how to make a e2e test for node-db-migrate migrations?

The problem
Multiple times when we wrote DB migrations for our production database we noticed that although the migrations were actually executed perfectly, we made logic mistakes in our migrations; So ether we missed an edge case or an unpredictable DB value appeared and our app crashed afterwards.

Of course we have a database with test data for our development environment. But (as it's usual in development) not 100% of the edge cases are covered in that database.

These cases would have been easily avoidable by having some kind of test framework that developers could adopt to actually test their database migrations.

Possible Solution
A possible solution would be a database e2e testing framework where developers could could configure:

1. An in-memory database and how the model looks like before the migration is executed
2. The node-db-migrate migration that needs to be executed
3. A mocha.js (https://mochajs.org/) file which will execute the JS tests

So the testing framework would check multiple scenarios (with the defined in-memory database) and then check the results with the mocha.js tests.

This issue is more an open discussion than really a feature request. I'm interest in opinions if node-db-migrate should cover such a testing framework.

[wzrdtales]

Simple question first:

When do you write your migrations?

• While you develop
• After you've developed

From my experience migrations work best if you utilize them to actually design your databases in the first place. And that they hardly work right when you use migrations "just" for having database versioning later on. I've done many developments with several teams were we worked like this and we never experienced crashing apps due to missing fields or who knows what else could be missing, b/c if it would be missing it would have never been developed in the first place.

To your points:
There is an in memory database: SQlite with filename ':memory:'

Actually what you describe here sounds like what I've experienced with some other teams some years ago. This team was especially unexperienced in writing tests and the only thing they ever produced have been e2e tests instead of developing unit tests and the applications they developed hardly ever went beyond a CRUD application where I can always suggest if you really have basically no useful logic at all and your application is just a dump my data, give me data application, you're probably better off doing bdd tests. So in that case e2e is not that wrong, at least not completely. In most other cases, you can live completely without them unless of course the translation layer to the database itself is in a way complex that they themself fail.
And that is actually a question of: Do my queries I'm gonna execute here actually work? Is this overcomplicated query generator I wrote really worth it and does not generate complete rubbish?

So you see there is a lot to think about here, when we come to finally making a statement, whether it makes sense to test a database being migrated in the stance you want it to be, I would rather state no it does not. And this is b/c you're using your tools wrong. Your database will only be incomplete if you've developed features on that database before the database exists. As this is impossible to do, as you can't develop anything this way, the database was created before and now needs to be translated again. In the latter case you could build a tool that diffs your migration generated tables against the database you're aiming to reproduce. A long time ago I have written this tool which depends on db-migrate:

https://github.com/wzrdtales/node-ultimate-migrate

This actually generates migrations from an exisiting database and was created out of the frustration I had with a team that did not used migrations before and translated a big ass database into migrations, which was a nightmare, so I ended up writing a tool to do this instead.

[BorntraegerMarc]

We are writing database migrations before we develop code (I guess that goes into the "While you develop" category). Our process is quite straightforward:

• Product Owner specifies a new feature
• Software team realizes that a DB migration needs to be run
• Software team writes the DB migration
• On first start of the new version of the app the migration gets executed

I'm not sure what you mean with

they hardly work right when you use migrations "just" for having database versioning later on.

Could you elaborate? I was talking about having a migration to add alter the DB schema. Not about versioning.

I think we had a bit of a misunderstanding. In my scenario we write a very complex application where customer requirements change rapidly. So we had to write a very big DB migration with node-db-migrate. My only concern was that we did not test 100% of the cases and that I would have liked to write some kind of test for the migration.

Not sure how a DB Migration helper should solve this issue?

[wzrdtales]

So you mean, you want to test for regressions? In that case you're probably best suited doing BDD tests to cover the actual business value and check for their functionality. My comments heavily originated from your statement

So ether we missed an edge case or an unpredictable DB value appeared and our app crashed afterwards.

Given what you've written now this pretty much sounds like regression testing which BDD should be perfect for, although they cost a lot of time to develop. But actually you do not have that many choices left, when it comes to side effects due to changes on third party systems, be it a database or just everything else, which tests are ungrateful, it is always somewhat of a struggle to get this right.

Do you employ a thorough (BDD) Test stage in your CI already?

[BorntraegerMarc]

I think we had a misunderstanding again. No, I didn't mean regression tests. Yes we do have approximately 150 integration tests for our BE API some unit tests for our BE code and 100 E2E tests for our UI which are run on a CI system.

What I meant was simply a testing framework for our database migrations.

When I write the following DB migration:

/**
 * Because the user has some nested "roomId" property inside an array we need to iterate manually over all users to update it.
 *
 * @param {*} dbDriver
 */
function updateUsers(dbDriver) {
    return new Promise((resolve, reject) => {
        dbDriver
            .collection(userCollection)
            .find({})
            .toArray((err, users) => {
                // After getting all users we set the properties from "roomId" to "channelId" manually.
                for (let i = 0; i < users.length; i++) {
                    let user = users[i];
                    for (let k = 0; k < user.channels.length; k++) {
                        let channel = user.channels[k];
                        channel.channelId = channel.roomId;
                        delete channel.roomId;
                    }
                }
                for (let i = 0; i < users.length; i++) {
                    let user = users[i];
                    dbDriver.collection(userCollection).save(user, err => {
                        if (i + 1 === users.length) {
                            // We only want to resolve the promise if we have updated all users
                            dbDriver.close();
                            if (err) {
                                reject(err);
                            }
                            resolve();
                        }
                    });
                }
            });
    });
}

We have quite a lot of tests that check whether our API can handle the property name change (from roomId to channelId) correctly.

However what we can not test at the moment if we did not cover an edge case with the actual DB values.

To give you some background: the field roomId is not set in our mongoose schema. Because it was of type Mixed

So naturally we forgot that we had to migrate something inside the Mixed object.

And then after running the DB migration in production our production app crashed.

This is a specific example for the use case I see for DB migration tests. If we would have the possibility to write a test we certainly would have noticed that we need to check various scenarios inside the Mixed type.

[wzrdtales]

No in that case I did not understand you wrong and testing your business value actually is more beneficial to you than testing if a migration works as intended. Why?

Simply b/c if you're testing your business value correctly, you will notice when something went wrong on that level. And if you try to write a test just for database migration stuff, how do you come to the conclusion that you would not forget to test what missed in your migrations? How do you want to produce a correct schema for your tests, to test against? I really don't think that you're able to solve your problem by testing the outcome of a migration though. If you've missed your column/field while writing the migration, there is no reason why you should not forget it when writing a test to test it.
Whenever you have got anything that consumes, then it is best to test the functionality range of the consumer, since they have to fail when stuff wents south.

We have quite a lot of tests that check whether our API can handle the property name change (from roomId to channelId) correctly.

I do not mean to test explicitly for that name change, but to have tests in place that do cover your actual business value.

For example:

You have got a shopping cart and the business value is that you have a 1click-buy option.
You're not testing the 6 API methods that are executing this business value directly, but you test the business value as a whole and the expectation is the outcome, that you successfully, or non successfully passed the 1-click-buy.

The next problem you mentioned:

And then after running the DB migration in production our production app crashed.

I wonder how this would only happen as soon as you deployed to production. This should have happened while development already. Do you employ a Continuous Delivery Pipeline already (not just CI) and a staging environment? Having the production side crash is really a first class emergency evolving there and I can understand that you do want to avoid that.

However:

From my experience, it does not make any sense at all, to test whether a schema went into a certain state. To give another example why:

Given you want to transform

{
  x: 1
  y: 2
}

Into

{
  z: 1
  y: 2
}

If you test for this, you might succcessfully prove that you can transform your data, from state a to state b. But you did not at all prove that any of the consumers could handle it. Additionally, you could still miss all sorts of things, especially when you do have different states in different environment and created through that some nondeterministic testing environments.

Testing against actual functionality of your application and thus your business value has many attempts. One is doing BDD, another is combining BDD with automated testing tools like twitters diffy https://github.com/twitter/diffy . By the way I would never do BDD tests on the UI, you should want to completely separate those two, the frontend as the consumer of the backend and the backend as the lone provider of actual functionality.

[BorntraegerMarc]

You have got a shopping cart and the business value is that you have a 1click-buy option.
You're not testing the 6 API methods that are executing this business value directly, but you test the business value as a whole and the expectation is the outcome, that you successfully, or non successfully passed the 1-click-buy.

This case we already cover with our integration tests. As mentioned above

Do you employ a Continuous Delivery Pipeline already (not just CI) and a staging environment?

We have both. With production I actually meant our staging environment. I left out the specifics for easier understanding. But the fact that it crashed in staging is already a warning sign for me that db migrations should be tested.

If you test for this, you might succcessfully prove that you can transform your data, from state a to state b. But you did not at all prove that any of the consumers could handle it.

Exactly my point. With my proposal I wanted to tell you that we probably need a testing framework to prove that data can be transformed from state a to state b.

The tests if consumers could handle it is definitely not part of this framework. We have other tests to ensure that.

If you still don't see the value of such a testing framework you may close this issue. I guess in that case we just disagree on the topic and we will come up with some framework ourselves.

[wzrdtales]

@BorntraegerMarc I'm fine if you want to work on such a thing, but I don't see how you want to test something that is non deterministic. My point is:

Given your updateUsers function, you can write a test for it, to test that it does some things with the data with some input that you give to it. But actually this does not help you when the input data itself is the source of the failure afterwards and your transformation given your test data just worked fine. So the benefits deminish here. Concerning this, you can do that already, given your usual testing tools, you just need to export the functions themself. But when the transformation was written and tested with data that works with it in the first place and the real world data is something different, the outcome is equal to zero.

[wzrdtales]

Let me know should you come up with something useful though :)

[BorntraegerMarc]

sure :) thanks for your input!