Node and SQLite Problem

woravitk · March 12, 2020, 5:22am

I have this little code, that create a table and run the query immediately.

var sqlite3 = require('sqlite3').verbose();
db = new sqlite3.Database(':memory:');
db.run("CREATE TABLE IF NOT EXISTS tbl_contact(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone TEXT, photo TEXT)")
    .each("select count(*) as exp from tbl_contact", (err, row) => {
        if(err) throw err;
            console.log(`No.: ${row.exp}`);
        });

When execute, it has the following error message: Error: SQLITE_ERROR: no such table: tbl_contact → in Database#all(‘select count(*) as exp from tbl_contact’

If I change this line “db = new sqlite3.Database(‘:memory:’);” to something liked “db = new sqlite3.Database(‘./test.db’);”, it will has this error only on the first time I run.

It seems like when the table doesn’t create immediately after I run the create command. Why and how can I fix this problem.

My machine is :
Mac OS: 10.15.3
Node: v12.14.1
SQLite version 3.28.0

woravitk · March 12, 2020, 5:31am

What I try to achieve is to create a module/class that is an interface between the user code and the database. When the user create this object, it will check if table exist and create if one doesn’t exist. After that the user can do something liked “db.addRow(…)” and the object will do that.

m3g4p0p · March 12, 2020, 6:59am

Hi @woravitk, by default SQL queries will run in parallel; in order to access the newly created table when it’s ready, you’ll either have to put the SELECT query inside the optional run() callback…

const sqlite3 = require('sqlite3').verbose()
const db = new sqlite3.Database(':memory:')

db.run(`CREATE TABLE IF NOT EXISTS tbl_contact(
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT,
  phone TEXT,
  photo TEXT
)`, () => {
  db.each('SELECT count(*) AS exp FROM tbl_contact', (err, row) => {
    if (err) throw err
    console.log(`No.: ${row.exp}`)
  })
})

… or serialize() the control flow like so:

const sqlite3 = require('sqlite3').verbose()
const db = new sqlite3.Database(':memory:')

db.serialize(() => {
  db.run(`CREATE TABLE IF NOT EXISTS tbl_contact(
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT,
    phone TEXT,
    photo TEXT
  )`).each('SELECT count(*) AS exp FROM tbl_contact', (err, row) => {
    if (err) throw err
    console.log(`No.: ${row.exp}`)
  })
})

This way each statement inside the serialized block will wait for the previous one before it is getting executed.

woravitk · March 12, 2020, 7:50am

Thank you very much for your answer.

Could you please help me out with the following scenario:
suppose I create a class call ContactDB which handle everything related to table tbl_contact.

The user of this class can call something like ContactDB.add(“id”, “name”, “email”) to add, ContactDB.delete(“id”) to remove, or ContactDB.get(“id”) to get specific row.

Suppose user do the following:
ContactDB.add(“1”, “Mr. A”, “aaa@bbb.com”
ContactDB.remove(“1”);
ContactDB.get(“1”);

It’s possible that when the user call “ContactDB.get(“1”)”, he/she will still able to get the data even when he already issue command to remove it. Is it possible to prevent this?

By the way, currently I switch to use “better-sqlite3” which I understand that it run synchronously.

m3g4p0p · March 12, 2020, 8:12am

Well if you run the queries while the DB is in serialized mode this shouldn’t be possible at all… so you’d just wrap every query in a serialize() callback, or maybe implement your own execution queue. Could you provide some sample code that demonstrates the problem though? Ideally one using the actual library you’re using. ;-)

woravitk · March 12, 2020, 8:31am

Thank you again for your help. I just begin using Node and SQLite (before this I do most of my work in PHP, MySQL).

Currently, I am just try to figure it out how to create a simple CRUD web. When I test SQLite, I just pack every query in the serialize as you said. Then, I try to make a module as follow:

var sqlite3 = require('sqlite3').verbose();

class dbContact {
    constructor() {
        this.db = new sqlite3.Database(':memory:');
    }

    async createContactTable() {
        const {db} = this;
        
        try {
            db.serialize(() => {
                db.run("CREATE TABLE IF NOT EXISTS tbl_contact(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone TEXT, photo TEXT)", (err)=>{console.log(`Hah? ${err}`)})
                    .each("select count(*) as exp from tbl_contact", (err, row) => {
                        if(err) throw err;
                        console.log(`No.: ${row.exp}`);
                    });
            });
                
            
        } catch(err) {
            console.log(err);
        }
    }

    async addContact(aContact) {
        const {db} = this;
        
        const sql = `INSERT INTO tbl_contact (name, phone, photo) VALUES ('${aContact[0]}', '${aContact[1]}', '${aContact[2]}')`;
        db.run(sql, (err)=>{console.log(`Huh? SQL: ${sql} ${err}`)});
        console.log(`add data`);
    }
}
const dbc = new dbContact();
module.exports = dbc;

then I expected to use it some what like this:

const dbc = require('./db_engine_3');

dbc.createContactTable();
dbc.addContact(["Woravit", "xx-4149-xxxx","./images/a.png"]);
dbc.addContact(["Tazz", "xx-4694-xxxx","./images/b.png"]);
}

At first, when it doesn’t work, I think that it is about the asynchronize nature of Node, so it try

const dbc = require('./db_engine_3');

async function prepareDatabase() {
    const a = await dbc.createContactTable();
    console.log(a);
    await dbc.addContact(["Woravit", "08-4149-7009","./images/a.png"]);
    await dbc.addContact(["Tassanee", "08-4694-4171","./images/b.png"]);
}


prepareDatabase();

which also doesn’t work as you have explain above.

I am not sure but I think maybe when I really do it web based (e.g. request update, delete, etc. via browser instead of writing a whole test code in single js file) it will not has this problem.

m3g4p0p · March 12, 2020, 8:41am

No, the async in your code is not necessary here – as it seems sqlite3 doesn’t even use promises itself but a pure callback style. You didn’t serialize() the addContact() method body though, which is why you’re still running that code asynchronously.

PS: Thinking about it, if you’re doing a lot of such operations an execution queue would indeed more appropriate here, so that you can still keep the code asynchronous w/o blocking the main thread. I’m off to work now but I can put together an example later…

woravitk · March 12, 2020, 8:53am

Thank you very much for your help. Have a nice day.

After reading your comment and follow it, I can finally solve the problem.

Here is the code for db_engine_3

var sqlite3 = require('sqlite3').verbose();

class dbContact {
    constructor() {
        this.db = new sqlite3.Database(':memory:');
    }

    createContactTable() {
        const {db} = this;
        
        try {
            db.serialize(() => {
                db.run("CREATE TABLE IF NOT EXISTS tbl_contact(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, phone TEXT, photo TEXT)", (err)=>{if(err) { console.log(`Problem with Table? ${err}`)}})
            });
                
            
        } catch(err) {
            console.log(err);
        }
    }

    addContact(aContact) {
        const {db} = this;
        
        db.serialize(() => {
            const sql = `INSERT INTO tbl_contact (name, phone, photo) VALUES ('${aContact[0]}', '${aContact[1]}', '${aContact[2]}')`;
            db.run(sql, (err)=>{if(err) {console.log(`Problem with Insert? SQL: ${sql} ${err}`)}});
        });

        
    }

    showAllContact() {
        const {db} = this;
        db.serialize(() => {
            db.all("select * from tbl_contact",[], (err, rows) => {
                if(err) {console.log(err);}
                rows.forEach((row) => {
                    console.log(`${row.name} ${row.phone}`);
                });
            });
        });
    }
}
const dbc = new dbContact();
module.exports = dbc;

This is the code of the test file

const dbc = require('./db_engine_3');


dbc.createContactTable();
dbc.addContact(["Woravit", "xx-4149-xxxx","./images/a.png"]);
dbc.addContact(["Tazz", "xx-4694-xxxx","./images/b.png"]);
dbc.showAllContact();

And the result is as expected!!!

Woravit xx-4149-xxxx
Tazz xx-4694-xxxx

Thank you very much!

m3g4p0p · March 12, 2020, 10:06pm

Ah nice, yes that was what I had in mind as well. :-) Glad you got it solved!

woravitk · March 13, 2020, 10:21am

Hello m3g4p0p,

I can achieve what I want. I just wonder if this is the correct way of doing it with Node, Express, SQLite.

In the db_engine I have the following method:

...

getAllContact(callback) {
        const {db} = this;
        db.all("select * from tbl_contact",[], (err, rows) => {
                callback(rows);
        });
}

...

and on my route.js I have this:

const express = require('express');
const router = express.Router();
const sqlite3 = require('sqlite3').verbose();
const dbc = require('../db_engine');

router.get('/test', function (req, res) {
    dbc.getAllContact((rows) => {
        res.render('index', { title: 'Simple Contact', app_name: 'Simple Contact App',aContact: rows });
    });
})

m3g4p0p · March 14, 2020, 12:43pm

Yes, apart from the now missing error handling that seems right to me.

woravitk · March 14, 2020, 1:36pm

Thank you very much for your advice.

system · June 13, 2020, 8:36pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
Nodejs and SQLite how create multiply tables? JavaScript scripts , sqlite , nodejs	1	1269	April 24, 2021
Light SQLite trouble.. ("IF NOT EXISTS") PHP	14	7004	October 8, 2014
Javascript+SQLite JavaScript	1	754	October 8, 2014
Web sql - only database i can see? not see tables not see entries JavaScript	2	744	October 8, 2014
Simply rails create a table Ruby	2	990	May 3, 2010

Node and SQLite Problem

Related topics