Using MySQL db functions (?) with SQLite (Node.js)

Question

I'm using a tutorial to do JWT/bcryptjs auth and then INSERT into a SQlite table. Thing is the tutorial is for MySQL and I get errors like db.query is not a function and db.escape is not a function

The db :

const sqlite3 = require('sqlite3').verbose()

const DBSOURCE = "./src/db/db.sqlite"

let db = new sqlite3.Database(DBSOURCE, (err) => {
if (err) {
  // Cannot open database
  console.error(err.message)
  throw err
}else{
    console.log('Connected to the SQLite database.')
}
});

module.exports = db

Example query :

db.query(
`SELECT * FROM users WHERE LOWER(username) = LOWER(${db.escape(
  req.body.username
)});`,
(err, result) => {
  if (result.length) {
    return res.status(409).send({
      msg: 'This username is already in use!'
    });
  } else { .........

My best guess is that the functions are different?

How do I get this right?

Artistan · Accepted Answer · 2020-07-11 17:21:49Z

1

There are a lot of proprietary functions in MySQL that will not work with standard SQL in other database systems.

That is just the beginning of the differences between Mysql and SQLite

Provide some query examples and we may be able to assist you with each one.

-- update after your addition of query code...

Here is an example of sqlite-nodejs

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

// open the database
let db = new sqlite3.Database('./db/chinook.db');

let sql = `SELECT * FROM users WHERE LOWER(username) = LOWER(?)`;

db.all(sql, [req.body.username], (err, rows) => {
    if (err) {
        throw err;
    }
    rows.forEach((row) => {
        console.log(row.name);
    });
});

// close the database connection
db.close();

edited Jul 11, 2020 at 17:21

answered Jul 11, 2020 at 17:08

Artistan

2,0571 gold badge23 silver badges34 bronze badges

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

4 Comments

apollo24 Over a year ago

Thank u. I edited the post with an example query. How would u do it with SQLite?

Artistan Over a year ago

sqlite-nodejs sqlitetutorial.net/sqlite-nodejs

Artistan Over a year ago

did not run code with updated prepared statement, but it should be close to right. :)

Artistan Over a year ago

db.run example with unescaped string ... stackoverflow.com/a/30080602/372215

Collectives™ on Stack Overflow

Using MySQL db functions (?) with SQLite (Node.js)

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related