I have a Nodejs project that involves a sqlite database (better-sqlite3) that stores various entities and their relationships between them in several tables. On a set schedule (am using node-schedule) a job in the code will be kicked off that uses an external HTTP API and makes any updates as needed which can take a decent amount of time. Additionally some more frequent but smaller update jobs will occur on a schedule when we detect through the API that some changes occurred affecting specific entities.

I want to avoid having these query functions running when the updates are in progress as it could be working with partial data.

I'm looking for the best way to be able to indicate to the query functions that an update is in progress and that they should either wait for that to finish or at least skip that run. It is easy enough to use 'promise chains' and have the queries occur after the database updates but I do not want to do a database update every time we need to query it.

Time or speed isn't really an issue. Below is the steps that will build it initially, there will be a similar one for doing the occasional big updates.

I'm not sure if using some 'flag' and having a way to wait for it or rather something to pause all other activity until the update is finished makes the most sense. This is the most JavaScript I've worked on yet so not overly familiar with the terminology and best practices.

async function buildDatabase() {
            db.exec('CREATE TABLE IF NOT EXISTS VMS (name TEXT, id TEXT, environment TEXT, ait TEXT)');
            db.exec('CREATE TABLE IF NOT EXISTS HYPERVISORS (name TEXT, id TEXT, environment TEXT)');
            db.exec('CREATE TABLE IF NOT EXISTS HOSTING (vm TEXT, hypervisor TEXT)');

            let hypervisors = await api.getEntities(test_config, 'type("HYPERVISOR")')
            hypervisors.forEach(function(hypervisor) {
                const insertStmt = db.prepare("INSERT INTO HYPERVISORS VALUES (?, ?, ?)")
                insertStmt.run(hypervisor.displayName, hypervisor.entityId, test_config.host)
            })

            hypervisors.forEach(async function(hypervisor) {
                let running_vms = await api.getCurrentVMs(test_config, hypervisor.entityId)
                running_vms.forEach((vm) => {
                    const insertStmt = db.prepare("INSERT INTO HOSTING VALUES (?, ?)");
                    insertStmt.run(vm, hypervisor.entityId);
                })
            })

            let vms = await api.getEntities(test_config, 'type("HOST"),hypervisorType("VMWARE")');
            vms.forEach((vm) => {

                let ait_tag_value = "missing";

                for (const tag of vm.tags) {
                    if (tag.key === AIT_TAG_KEY) {
                        ait_tag_value = tag.value;
                        break;
                    }
                }

                const insertStmt = db.prepare("INSERT INTO VMS VALUES (?, ?, ?, ?)");
                insertStmt.run(vm.displayName, vm.entityId, test_config.host, ait_tag_value);
            })

    }

buildDatabase().then(() => {
            let stmt = db.prepare('SELECT * FROM HYPERVISORS');
            let hvs = stmt.all();
            // console.log(hvs)
        }).then(() => {
            let stmt = db.prepare('SELECT * FROM VMS');
            let vms = stmt.all();
            console.log(vms)
        }).then(() => {
            let stmt = db.prepare('SELECT * FROM HOSTING');
            let mappings = stmt.all();
            // console.log(mappings)
        })

Thank you!

CodePudding user response：

The simplest thing is that you can just maintain a flag (e.g. boolean variable) whether an update is in progress or not and other code can just check that flag if they want. You just have to make sure your update code is bulletproof and that it will always clear that flag, even in all possible error conditions when its done.

The flag can be used either directly within the same module or via an exported function isUpdateInProgress() that simply returns a boolean.

In a little bit more elegant version, you can export a function such as getUpdate() that will return a promise that will be pending when an update is in progress and will be resolved when the update is done. That way, some other task can actually use that promise to wait until the update is done to then do something.

If you're trying to protect from the update code starting while other code is using the database, then that's a bit more complicated.