{"id":1256,"date":"2017-06-26T11:39:25","date_gmt":"2017-06-26T04:39:25","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1256"},"modified":"2024-10-27T21:24:53","modified_gmt":"2024-10-27T14:24:53","slug":"inserting-data-sqlite-table-node-js-application","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-nodejs\/insert\/","title":{"rendered":"SQLite Node.js: Inserting Data Into a Table"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to insert a row into a table in SQLite databases in Node.js applications.<\/p>\n\n\n\n

Inserting a row into a table from Node.js<\/h2>\n\n\n\n

To insert data into an SQLite table from a Node.js app, you can follow these steps:<\/p>\n\n\n\n

First, import sqlite3<\/code> from the sqlite3<\/code> module:<\/p>\n\n\n

import<\/span> sqlite3 from<\/span> \"sqlite3\"<\/span>;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Second, open a database connection to an SQLite database<\/a>:<\/p>\n\n\n
const<\/span> db = new<\/span> sqlite3.Database(filename);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
db<\/span>.run<\/span>(sql<\/span>, params<\/span>, callback<\/span>);<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, close the database connection:<\/p>\n\n\n
db<\/span>.close<\/span>()<\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Defining a wrapper function<\/h2>\n\n\n\n
Since the run()<\/code> method uses the traditional callback style, it’ll be challenging to work with. <\/p>\n\n\n\n
To make it more convenient, we can define a wrapper function execute<\/code> that calls the run()<\/code> method  and returns a Promise<\/a>:<\/p>\n\n\n
const<\/span> execute = async<\/span> (db, sql, params = []) => {\n  if<\/span> (params && params.length > 0<\/span>) {\n    return<\/span> new<\/span> Promise<\/span>((resolve, reject<\/span>) =><\/span> {\n      db.run(sql, params, (err) => {\n        if<\/span> (err) reject(err);\n        resolve();\n      });\n    });\n  }\n  return<\/span> new<\/span> Promise<\/span>((resolve, reject<\/span>) =><\/span> {\n    db.exec(sql, (err) => {\n      if<\/span> (err) reject(err);\n      resolve();\n    });\n  });\n};<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
How it works.<\/p>\n\n\n\n
The execute()<\/code> is an async<\/code> function that has three parameters:<\/p>\n\n\n\n
    \n
  • db<\/code> is an instance of the Database<\/code> class.<\/li>\n\n\n\n
  • sql<\/code> is an SQL statement to execute.<\/li>\n\n\n\n
  • params<\/code> is the argument that will substitute the placeholders (?<\/code>) in the sql<\/code> statement.<\/li>\n<\/ul>\n\n\n\n
    The execute()<\/code> function calls the run()<\/code> method if the params<\/code> parameter has items or the exec()<\/code> method if it does not.<\/p>\n\n\n\n
    Insert one row into a table<\/h2>\n\n\n\n
    Step 1. Modify the execute()<\/code> function from sql.js<\/code> module:<\/p>\n\n\n
    export<\/span> const<\/span> execute = async<\/span> (db, sql, params = []) => {\n  if<\/span> (params && params.length > 0<\/span>) {\n    return<\/span> new<\/span> Promise<\/span>((resolve, reject<\/span>) =><\/span> {\n      db.run(sql, params, (err) => {\n        if<\/span> (err) reject(err);\n        resolve();\n      });\n    });\n  }\n  return<\/span> new<\/span> Promise<\/span>((resolve, reject<\/span>) =><\/span> {\n    db.exec(sql, (err) => {\n      if<\/span> (err) reject(err);\n      resolve();\n    });\n  });\n};<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 2. Add the insert.js<\/code> file to insert one row into the products<\/code> table:<\/p>\n\n\n
    import<\/span> sqlite3 from<\/span> \"sqlite3\"<\/span>;\nimport<\/span> { execute } from<\/span> \".\/sql.js\"<\/span>;\n\nconst<\/span> main = async<\/span> () => {\n  const<\/span> db = new<\/span> sqlite3.Database(\"my.db\"<\/span>);\n  const<\/span> sql = `INSERT INTO products(name, price) VALUES(?, ?)`<\/span>;\n  try<\/span> {\n    await<\/span> execute(db, sql, [\"iPhone\"<\/span>, 899.99<\/span>]);\n  } catch<\/span> (err) {\n    console<\/span>.log(err);\n  } finally<\/span> {\n    db.close();\n  }\n};\n\nmain();<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 3. Open the terminal and execute the following command to run the Node.js app:<\/p>\n\n\n
    node<\/span> insert<\/span>.js<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
    Step 4. Verify the insertion:<\/p>\n\n\n\n
    First, open a new terminal and use the sqlite3<\/code> tool to connect to the my.db<\/code> database:<\/p>\n\n\n
    sqlite3 my.db<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, format the output:<\/p>\n\n\n
    .header<\/span> on<\/span>\n.mode<\/span> column<\/span>\n.nullvalue<\/span> null<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, query data from the products<\/code> table:<\/p>\n\n\n
    SELECT * FROM products;<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    id  name    price\n--  ------  ------\n1<\/span>   iPhone  899.99<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the run()<\/code> method of the Database object to execute an INSERT<\/code> statement to insert a new row into a table.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful ?<\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t
Third, execute an INSERT <\/a>statement statement using the run()<\/code> method of the Database<\/code> object:<\/p>\n\n\n