Node-sqlite3: SQLITE_ERROR: no such table

[devanp92]

I'm getting the following error:

in serialize
err:  { [Error: SQLITE_ERROR: no such table: temp] errno: 1, code: 'SQLITE_ERROR' }
/home/project/node_modules/sqlite3/lib/trace.js:28
                    throw err;
                          ^
Error: SQLITE_ERROR: no such table: temp
    at Error (native)
--> in Database#each('select * from temp', [Function])

temp.db is in the same folder, so there shouldn't be any pathing issue.

Setup:

• node v0.12.2
• npm v2.7.5
• node-sqlite3 v.3.0.5
• OSX Yosemite

I am writing the querying logic in my Sails controller. Just testing it out with a simple "SELECT * FROM TEMP" statement. Here is my source code:

var sqlite = require('sqlite3').verbose()
var db = new sqlite.Database('temp.db')

module.exports = {
  search: function(next) {
    db.serialize(function(){
            db.all('SELECT * FROM TEMP', function(err, rows) {
                if(err) {
                    console.log("err: ", err)
                    throw err
                }
                rows.forEach(function (row) {
                    console.log(row)
                })
                closeDb()
                next(err, rows)
            })
        })
    }
}

function closeDb(rows) {
    console.log("closeDb")
    db.close()
}

I feel like the reason is due to asynchro call to DB, however, I am using serializable to thwart that.

[talkingtab]

Opening a table in node-sqlite3 is such a fundamental thing that the
first thought would be the database is not what you expect, for example
that the database temp.db does not contain a table "temp" or a similar
error.

You can use sqlite3 from the command line to open the database and then
to check the table.

sqlite3 temp.db
.tables
.schema

If the db has the table "temp" then you would need to provide more
information (like the result of the .tables command), a full code
listing perhaps one that shows the creation of the table as well
insertion of data and then a select.

On 4/21/15 12:00 PM, Devan Patel wrote:

I'm getting the following error:

in serialize
err: { [Error: SQLITE_ERROR: no such table: temp] errno: 1, code: 'SQLITE_ERROR' }
/home/node_modules/sqlite3/lib/trace.js:28
throw err;
^
Error: SQLITE_ERROR: no such table: temp
at Error (native)
--> in Database#each('select * from temp', [Function])

Setup:

• node v0.12.2
• npm v2.7.5
• node-sqlite3 v.3.0.5
• OSX Yosemite

I am writing the querying logic in my Sails controller. Just testing it out with a simple "SELECT * FROM TEMP" statement. Here is my source code:

var sqlite = require('sqlite3').verbose()
var db = new sqlite.Database('temp.db')

module.exports = {
search: function(next) {
db.serialize(function(){
db.all('SELECT * FROM TEMP', function(err, rows) {
if(err) {
console.log("err: ", err)
throw err
}
rows.forEach(function (row) {
console.log(row)
})
closeDb()
next(err, rows)
})
})
}
}

function closeDb(rows) {
console.log("closeDb")
db.close()
}

---

Reply to this email directly or view it on GitHub:
https://github.com/mapbox/node-sqlite3/issues/441

[devanp92]

Hi, sorry for the lack of information. Here is what I have:

    $ sqlite3 temp.db

sqlite> .tables
temp

sqlite> .schema temp;
CREATE TABLE temp (a TEXT, b REAL, c TEXT, d TEXT, e REAL, PRIMARY KEY(a, b, c));

sqlite> select count(*) from temp;
563

My insert statements imitate:
insert or ignore into temp values ('string',0,'another string','another string',-1.0);

[springmeyer]

the behavior of sqlite3 core is to create a db if it does not already exist. So what is likely happening is that the path to the temp.db is wrong and a new one is being created.

[Letladi]

I was having a similar problem and ended up here, the following seems to solve the problem for me (assuming you want to create a table called 'boards');

db.get("SELECT name FROM sqlite_master WHERE type='table' AND name='boards'", (err, boardTable)=> {
  if (boardTable === undefined) {
    db.run(`CREATE TABLE boards (
      id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
      name TEXT NOT NULL,
      starred INTEGER NOT NULL DEFAULT 0
    );`);
  }
});

[christophemarois]

In my case too, I was referring to the location using a relative path ../dev.sqlite3. Changing it for path.resolve(__dirname, '../dev.sqlite3') fixed it

[janzenz]

Thanks! @christophemarois

Thanks for this library as well. However, it would have been helpful to know if the logs would have indicated that the DB file wasn't found and that a new database have been created instead would have save time debugging this error.

[stevematdavies]

Ive not managed to get sqlite working in Mac Mojave and latest Node at all, always no table found, and no solutions offered.

It seems to create the Db and the table, but any actions after that it throws a 'no such table' error

[shsma]

@springmeyer did you try either to await the sync or to trigger the function chaining once the sync promise is resolved?

await Address.sync({ force : true }); // drops addresses table and re-creates it

   Address.sync({ force : true }).then(async () => {
     // your logic here
    });

[LeonardoGentile]

Experiencing a similar problem and the above solution didn't work for me.

[tksilicon]

path.resolve(__dirname, '../dev.sqlite3'

Solves the problem

[shakhawatfci]

In my case too, I was referring to the location using a relative path ../dev.sqlite3. Changing it for path.resolve(__dirname, '../dev.sqlite3') fixed it

thanks man this save my day

[stock-dd]

the behavior of sqlite3 core is to create a db if it does not already exist. So what is likely happening is that the path to the temp.db is wrong and a new one is being created.

Strange behaviour. More logical would be to throw an error with the erroneous path.