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?

If you use parametrized query, then ['C'] makes sense. If your query is INSERT INTO users(name, age) VALUES(?, ?), then you give value array as ['Riko', '29'] which is simply parametrized insert query. In your case, you append values in the query, so no need to use parameter array. — ycagri
– ycagri, Commented Oct 9, 2018 at 13:53

spacjalex · Accepted Answer · 2025-02-18 19:59:17Z

11

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 (note that it's important to use an old-school function () { ... } style callback rather than an arrow function, otherwise this.lastID will be undefined, source: official docs):

db.run('INSERT INTO users(name, age) VALUES(?, ?)', ['Raiko',29], function (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/

edited Feb 18 at 19:59

spacjalex

54 bronze badges

answered Mar 3, 2019 at 9:21

Loghi Perinpanayagam

3402 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TAS · Accepted Answer · 2018-03-30 14:10:10Z

1

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/.

answered Mar 30, 2018 at 14:10

TAS

15612 bronze badges

Comments

Nagnath Mungade · Accepted Answer · 2019-08-19 14:06:35Z

0

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.

answered Aug 19, 2019 at 14:06

Nagnath Mungade

1,00911 silver badges12 bronze badges

Comments

Gerardsson · Accepted Answer · 2020-03-27 14:59:31Z

0

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.

answered Mar 27, 2020 at 14:59

Gerardsson

3824 silver badges11 bronze badges

Comments

Tyler2P · Accepted Answer · 2022-04-29 14:48:58Z

0

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}');
    })
}

edited Apr 29, 2022 at 14:48

Tyler2P

2,37030 gold badges26 silver badges34 bronze badges

answered Apr 29, 2022 at 10:13

Hannes De Kesel

1

Collectives™ on Stack Overflow

Insert row to table in SQLite and Node.js

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related