{"id":1246,"date":"2017-06-26T11:30:05","date_gmt":"2017-06-26T04:30:05","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=1246"},"modified":"2024-10-27T21:13:44","modified_gmt":"2024-10-27T14:13:44","slug":"connecting-sqlite-database-using-node-js","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-nodejs\/connect\/","title":{"rendered":"SQLite Node.js: Connecting to a SQLite Database"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to connect to an SQLite database from Node.js applications.<\/p>\n\n\n\n

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

Step 1. Open your terminal and create a new project directory:<\/p>\n\n\n

mkdir nodejs-sqlite\ncd nodejs-sqlite<\/code><\/span><\/pre>\n\n\n
Step 2. Create a package.json<\/code> file:<\/p>\n\n\n
npm init -y<\/code><\/span><\/pre>\n\n\n
Step 3. Install the sqlite3<\/code> package to interact with an SQLite database from the Node.js app:<\/p>\n\n\n
npm install sqlite3<\/code><\/span><\/pre>\n\n\n
Step 4. Create a new index.js<\/code> in the project directory with the following source code:<\/p>\n\n\n
console<\/span>.log(\"Node.js + SQLite\"<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 5. Add the type module to the ackage.json<\/code> file to use the ES module:<\/p>\n\n\n
\"type\"<\/span>: \"module\"<\/span>,<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Change the scripts<\/code> section to the following to run the npm start<\/code> command to start a Node.js app:<\/p>\n\n\n
\"start\"<\/span>: \"node index.js\"<\/span><\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Step 6. Open the terminal and run the following command:<\/p>\n\n\n
npm start<\/code><\/span><\/pre>\n\n\n
You should see the following message:<\/p>\n\n\n
Node<\/span>.js<\/span> SQLite<\/span><\/code><\/span>Code language:<\/span> CSS<\/span> (<\/span>css<\/span>)<\/span><\/small><\/pre>\n\n\n
Creating a new SQLite database file<\/h2>\n\n\n\n
To create a new SQLite database file, you follow these steps:<\/p>\n\n\n\n
First, import 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, create a new instance of the Database<\/code> class of the sqlite3<\/code> module:<\/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
When creating a Database<\/code> object, you specify a SQLite database file specified by the filename<\/code> argument.<\/p>\n\n\n\n
If the filename<\/code> already exists, the app opens a new connection to the SQLite database file. Otherwise, it creates a new SQLite database file and opens a connection.<\/p>\n\n\n\n
The Database()<\/code> constructor returns an object (db<\/code>) representing the database connection to the SQLite database.<\/p>\n\n\n\n
Third, change the index.js<\/code> to the following and save it:<\/p>\n\n\n
import<\/span> sqlite3 from<\/span> \"sqlite3\"<\/span>;\n\nconst<\/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
Finally, run the following command in the terminal:<\/p>\n\n\n
npm start<\/code><\/span><\/pre>\n\n\n
You’ll see that the program creates a new SQLite database file my.db<\/code> within the project directory.<\/p>\n\n\n\n
Connecting to an existing SQLite database file<\/h2>\n\n\n\n
First, download the chinook.db<\/code> file from the sample database<\/a> page and copy it to your project directory.<\/p>\n\n\n\n
Second, modify the index.js<\/code> to change the filename tochinook.db<\/code> to connect to an existing database:<\/p>\n\n\n
import<\/span> sqlite3 from<\/span> \"sqlite3\"<\/span>;\n\nconst<\/span> db = new<\/span> sqlite3.Database(\"chinook.db\"<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
When you open a connection to an SQLite database file, you can use one or a combination of the following modes:<\/p>\n\n\n\n
Mode<\/th>Description<\/th><\/tr><\/thead>
sqlite3.OPEN_READONLY<\/code><\/td>Open the database for read-only.<\/td><\/tr>
sqlite3.OPEN_READWRITE<\/code><\/td>Open the database for reading and writing.<\/td><\/tr>
sqlite3.OPEN_CREATE<\/code><\/td>Open the database and create a new one if the database does not exist.<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
The Database()<\/code> constructor accepts one or more modes as the second argument. <\/p>\n\n\n\n
The mode defaults to the OPEN_READWRITE | OPEN_CREATE<\/code>, meaning that if the database does not exist, it’ll create a new database for reading and writing.<\/p>\n\n\n\n
For example, if you want to open the chinook.db<\/code> database for reading and writing and don’t want to create a new one if it does not exist, you can use the OPEN_READWRITE<\/code> mode as follows:<\/p>\n\n\n
import<\/span> sqlite3 from<\/span> \"sqlite3\"<\/span>;\n\nconst<\/span> db = new<\/span> sqlite3.Database(\"chinook.db\"<\/span>, sqlite3.OPEN_READWRITE);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
If the chinook.db<\/code> does not exist in the project directory, the app will issue the following error in the console instead of creating a new one:<\/p>\n\n\n
[Error<\/span>: SQLITE_CANTOPEN: unable to open database file\nEmitted 'error'<\/span> event on Database instance at:\n] {\n  errno<\/span>: 14<\/span>,\n  code<\/span>: 'SQLITE_CANTOPEN'<\/span>\n}<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Connecting to an in-memory SQLite database<\/h2>\n\n\n\n
To open a database connection to an in-memory SQLite database, you pass the literal string ‘:memory:'<\/code> to the Database<\/code> constructor:<\/p>\n\n\n
const<\/span> db = new<\/span> sqlite3.Database(':memory:'<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Here’s the complete program:<\/p>\n\n\n
import<\/span> sqlite3 from<\/span> \"sqlite3\"<\/span>;\n\nconst<\/span> db = new<\/span> sqlite3.Database(\":memory:\"<\/span>);<\/code><\/span>Code language:<\/span> JavaScript<\/span> (<\/span>javascript<\/span>)<\/span><\/small><\/pre>\n\n\n
Note that the in-memory database can be useful in some cases like speeding up tests significantly.<\/p>\n\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Use the sqlite3<\/code> module to interact with SQLite from Node.js apps.<\/li>\n\n\n\n
  • Create a new instance of the Database<\/code> class to create a new SQLite database file if it does not exist or open a connection to an existing one.<\/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