{"id":3872,"date":"2024-10-27T15:05:13","date_gmt":"2024-10-27T08:05:13","guid":{"rendered":"https:\/\/www.sqlitetutorial.net\/?page_id=3872"},"modified":"2024-10-27T21:17:50","modified_gmt":"2024-10-27T14:17:50","slug":"create-tables","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-nodejs\/create-tables\/","title":{"rendered":"SQLite Node.js: Creating Tables"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to create new tables in SQLite from Node.js apps.<\/p>\n\n\n\n

This tutorial begins where the “Connecting to SQLite Databases from Node.js<\/a>” tutorial left off.<\/p>\n\n\n\n

Creating a table from the Node.js app<\/h2>\n\n\n\n

To create a new table in an SQLite database from a Node.js app, you follow these steps:<\/p>\n\n\n\n

First, import sqlite3<\/code> from 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 new database connection to the SQLite database:<\/p>\n\n\n
const<\/span> db = new<\/span> sqlite3.Database(\"my.db\"<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
db.exec(sql, (err) => {\n    \/\/ handle error here<\/span>\n});<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, close the database connection:<\/p>\n\n\n
db.close()<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
const<\/span> execute = async<\/span> (db, sql) => {\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
And we can use the execute()<\/code> function using async\/await<\/code> syntax:<\/p>\n\n\n
try<\/span> {\n  await<\/span> execute(sql);\n} catch<\/span>(error) {\n  console<\/span>.log(error);\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Modifying the Node.js app<\/h2>\n\n\n\n
Step 1. Create sql.js<\/code> and export the execute<\/code> function:<\/p>\n\n\n
export<\/span> const<\/span> execute = async<\/span> (db, sql) => {\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};\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 2. Add the createTable.js<\/code> to the project:<\/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  try<\/span> {\n    await<\/span> execute(\n      db,\n      `CREATE TABLE IF NOT EXISTS products (\n        id INTEGER PRIMARY KEY,\n        name TEXT NOT NULL,\n        price DECIMAL(10, 2) NOT NULL)`<\/span>\n    );\n  } catch<\/span> (error) {\n    console<\/span>.log(error);\n  } finally<\/span> {\n    db.close();\n  }\n};\n\nmain();\n<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 3. Open the terminal and run the app:<\/p>\n\n\n
npm start<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 4. Verify the table creation.<\/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, list all the tables in the my.db<\/code> database:<\/p>\n\n\n
.tables<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
products<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
The output indicates that the products<\/code> table was created.<\/p>\n\n\n\n
Third, examine the structure of the products<\/code> table:<\/p>\n\n\n
.schema products<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
CREATE TABLE products (\n    id INTEGER PRIMARY KEY,\n    name TEXT NOT NULL,\n    price DECIMAL(10<\/span>, 2<\/span>) NOT NULL\n);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, quit the sqlite3<\/code> tool:<\/p>\n\n\n
.quit<\/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 exec()<\/code> method of the Database<\/code> object to execute a CREATE TABLE<\/code> statement and create a new table in an SQLite database from Node.js.<\/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
Since the exec()<\/code> method uses the traditional callback style which is quite challenging to work with. To make it easier, we can define a function execute()<\/code> that wraps the exec() method and returns a Promise<\/a>:<\/p>\n\n\n
Third, call the exec()<\/code> method of the Database<\/code> object to execute a CREATE TABLE<\/a> statement to create a table:<\/p>\n\n\n