Race condition in SQLite in NodeJS

1501 views node.js
-4

I'm newcomer to NodeJS and databases. And I'm confused with my program's behaviour. I'm just trying to create table and then delete it. But something is wrong in my code. Results are really strange. I ran my code several times and the results are different. Example results:

First execution
[ { name: 'first' } ]
createTable error SQLITE_ERROR: table first already exists
[ { name: 'first' } ]
table first dropped

Second execution
[]
[]
table first(name text) created succesfully

Third execution
createTable error SQLITE_ERROR: table first already exists
[ { name: 'first' } ]
[ { name: 'first' } ]
table first dropped

Code listed below. If someone could point to my mistake I would be grateful.

import { Database } from 'sqlite3';

class DBWrapper {
    private db: Database;
    constructor(dbName: string) {
        this.db = new Database(dbName, (error) => {
            if (error) {
                console.log('Database construction failed');
            } else {
                console.log('Database created successfully');
            }
        });
    }

    public createTable(tableName: string): void {
        this.db.serialize(() => {
            this.db.run('CREATE TABLE ' + tableName, (error) => {
                if (error) {
                    console.log('createTable error ' + error.message);
                } else {
                    console.log('table ' + tableName + ' created succesfully');
                }
            });
        });
        this.db.close();
        this.db = new Database('sqlitest');
    }

    public printTables(): void {
        this.db.serialize(() => {
            this.db.all('select name from sqlite_master where type=\'table\'', (error, tables) => {
                console.log(tables);
            });
        });
        this.db.close();
        this.db = new Database('sqlitest');
    }

    public clear(): void {
        this.db.serialize(() => {
            this.db.all('select name from sqlite_master where type=\'table\'', (error, tables) => {
                if (error) {
                    console.log('error in select all tables');
                } else {
                    tables.forEach((table) => {
                        this.db.run('DROP TABLE ' + table.name, (innerError) => {
                            if (innerError) {
                                console.log('drop table ' + table.name + ' error ' + innerError.message);
                            } else {
                                console.log('table ' + table.name + ' dropped');
                            }
                        });
                    });
                }
            });
        });
        this.db.close();
        this.db = new Database('sqlitest');
    }
}

const testDB = new DBWrapper('sqlitest');
testDB.createTable('first(name text)');
testDB.printTables();
testDB.clear();
testDB.printTables();

answered question

1 Answer

6

You need to wait for each command to finish before starting the next command.

Normally, db.serialize() will do that for you, but you're creating a new db instance after each command, which is not aware of the pending operation from the previous instance.

You should reused the db instance.

posted this

Have an answer?

JD

Please login first before posting an answer.