Insert row to table in SQLite and Node.js

Question

I am studying SQLite for Node.js. Currently I am at the very beginning. I tried to insert row to a table but got error. The code I am using comes from this tutorial.

This is the code:

const sqlite3 = require('sqlite3').verbose();
let db = new sqlite3.Database('./database/sq-lite-data-base.db', (err) => {
	if(err) {
		return console.log(err.message);
	}
	console.log("Connected to database!")
});

// Create table named 'users' with two columns: 1 - 'name' which values to be of type "text",
// and "age" of type "integer"
// db.run('CREATE TABLE users(name text, age integer)'); //When this line is not connected I get different error: "The table users already exists"

// Insert two rows to table "users"
db.run('INSERT INTO users(name, age) VALUES("Riko", 29)', ['C'], (err) => {
	if(err) {
		return console.log(err.message); 
	}
	console.log('Row was added to the table: ${this.lastID}');
})

The error I get is:

SQLITE_ERROR: no such table: users

Another thing that puzzles me is the second parameter of the function db.run. In my case I have passed the argument ['C']. But what does this argument do, or what is it designated for?

Answer 1

First you have to check the user table in your db '/database/sq-lite-data-base.db'. If not:

db.run('CREATE TABLE users(name TEXT,age INT)');

And you have to change the code in above snippet like this

db.run('INSERT INTO users(name, age) VALUES(?, ?)', ['Raiko',29], (err) => {
	if(err) {
		return console.log(err.message); 
	}
	console.log('Row was added to the table: ${this.lastID}');
})

You can also refer about sqlite tutorial.http://www.sqlitetutorial.net/sqlite-nodejs/insert/

Answer 2

Try creating the table users first, before inserting any data in it (this probably caused the error):

db.run('CREATE TABLE users(name)');

Then insert the data into the table by putting a questionmark as placeholder in VALUES. The second argument of db.run is then used to pass the actual name value to sqllite.

    db.run('INSERT INTO users(name) VALUES(?)', ['Riko'])

You can also use a database browser to gain more insight as to why your having problems: http://sqlitebrowser.org/.

Answer 3

If you have to insert data for multiple columns with large data. see the query if you want good Ui for handling db.

use sqlitestudio for userface based Sqlite db view: SqliteStudio

firstly check db and table is exist or not.then insert multiple values :

var data=[
['Mungade','26'],
['Nagnath','27']
]

for (var i=0;i<data.length; i++){
     db.run("INSERT INTO user(name,age) values(?,?)",data[i][0],data[i][1],(err,rows)=>{
     if(err){
        throw err;
     }
      console.log('Insert Success');
 })
}

its working for me.

Answer 4

A possible answer might be that your DB is referenced incorrectly. If this is the case it will create an empty DB named 'databasesq-lite-data-base.db' and since this DB is empty it will not find the table.

So a connection will work but it will not be the DB you wanted it to be.

In your windows explorer look whether and empty DB with the above name was created somewhere.

Answer 5

you need to serialize it all.

It is not possible to do 2 things at the same time except if you serialize

db.serialize(() => {
    db.run('CREATE TABLE users(name text, age integer)'); 

    // Insert two rows to table "users"
    db.run('INSERT INTO users(name, age) VALUES("Riko", 29)', ['C'], (err) => {
        if (err) {
            return console.log(err.message); 
        }
        console.log('Row was added to the table: ${this.lastID}');
    })
}