Learn Basic Operations with SQLite and Nodejs
In this lesson, we are going to learn about how to interact with our nodejs app to an SQLite database.
SQLite is a powerful database that lives in a single file or memory and gives us all the basic operations(queries, views, triggers, indexes … ) that have big enterprise databases such as MySQL, Postgress, etc…
In nodejs with have sqlite3
a module that gives us all the features we need to interact with an SQLite database.
1. Install sqlite3
and connect with database
In this section, we will create users.db database, establish and close a connection with users.db.
Create a new nodejs project
cd sqliteBasics
npm init -y
npm i sqlite3</span>
Now create an ./index.js file
terminal output
2. Create table, insert and retrieve data
The task is to create a users table with 2 columns (username, password) then insert and retrieve data. But in nodejs framework serial operations are tricky. Don’t freak out sqlite3
has db.serialize() function that gives us the power to serialize our query statements.
We are going to use 3 main functions :
db.serialize([callback]) Inside the callback method we can run operations in serial order.
db.run(SQL,[param, …], [callback]) Run SQL statement but not retrieve any data.
db.each(SQL, [param, …], [callback], [complete]) Run SQL statement retrieve the data from query and loop each row separately.
Update ./index.js file
terminal output
3. Update and Delete data
Now that we have the users.db database with data, we can run an update and delete queries.
Create a new file ./updateDelete.js
Terminal output
Conclusion
This was a quick lesson with sqlite3
module on how to:
- Open-close connection with the database
- Run CRUD (Create, Update, Delete) queries
- Set the flow in serial mode with db.serialize([callback]) method
References
- https://www.sqlite.org/index.html
- https://www.sqlitetutorial.net
- https://www.npmjs.com/package/sqlite3
- https://github.com/mapbox/node-sqlite3/wiki/API