Using SQLite in Node.js with Real-World Examples

SQLite is a popular open-source, serverless, and self-contained SQL database engine that is used in a wide range of applications, including mobile apps, desktop software, and web applications. In this blog post, we will explore how to use SQLite in Node.js and provide real-world examples of its applications.

What is SQLite?

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows direct interaction with the database files. It is often considered a zero-configuration, serverless, and self-contained database engine. Here are some key features:

• Self-contained: The entire SQLite database is a single ordinary file on disk.
• Zero-configuration: No need to set up a separate server or install complex software.
• Transactional: Supports ACID (Atomicity, Consistency, Isolation, Durability) transactions.
• Highly reliable: Widely used in production for mobile and desktop applications.
• Cross-platform: Runs on various operating systems.

Using SQLite in Node.js

To use SQLite in Node.js, you’ll need to install the sqlite3 package. You can do this by running:

npm install sqlite3

Now, let’s dive into some real-world examples of how to use SQLite in Node.js.

Example 1: Creating a SQLite Database

In this example, we will create a new SQLite database and define a table.

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('mydb.db');

db.serialize(() => {
  db.run('CREATE TABLE users (id INT, name TEXT)');
});

db.close();

Example 2: Inserting Data

Now, let’s insert data into the database:

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('mydb.db');

const statement = db.prepare('INSERT INTO users VALUES (?, ?)');

statement.run(1, 'John Doe');
statement.run(2, 'Jane Smith');

statement.finalize();

db.close();

Example 3: Querying Data

You can query data from the database using SQL statements:

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('mydb.db');

db.each('SELECT id, name FROM users', (err, row) => {
  console.log(row.id, row.name);
});

db.close();

Example 4: Updating and Deleting Data

Updating and deleting data is also straightforward:

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('mydb.db');

// Update
db.run('UPDATE users SET name = "Updated Name" WHERE id = 1');

// Delete
db.run('DELETE FROM users WHERE id = 2');

db.close();

Example 5: Error Handling

SQLite operations may encounter errors. You should include proper error handling in your code:

const sqlite3 = require('sqlite3').verbose();
const db = new sqlite3.Database('mydb.db');

db.run('sect from users', (err) => {
  if (err) {
    console.error('Error:', err.message);
  }
});

db.close();

Conclusion

SQLite is a versatile and lightweight database engine that can be a great choice for many Node.js applications. In this blog post, we’ve covered the basics of using SQLite in Node.js with real-world examples. Feel free to explore more advanced features and libraries available for SQLite to build robust and efficient applications.

Happy coding with SQLite in Node.js!