Learn Basic Operations with SQLite and Nodejs

Learn Basic Operations with SQLite and Nodejs

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.

Untitled Diagram.png

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 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

4.png

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

5.png

Terminal output

Conclusion

This was a quick lesson with sqlite3 module on how to:

  1. Open-close connection with the database
  2. Run CRUD (Create, Update, Delete) queries
  3. Set the flow in serial mode with db.serialize([callback]) method

References

Thanks🙏🙏 for reading my story stay tuned for more nodejs and SQLite blogs