Integrating SQLite with a Web Application (Part 1)

Connecting SQLite to a Web Application Backend

When building a web application, having a database to store and manage your data is a must. SQLite is a great choice for smaller applications or when you want a lightweight, simple-to-set-up database solution. In this blog, we'll walk you through how to integrate SQLite into the backend of your web application, connecting it with the server-side logic.

If you’re new to SQLite, make sure to check out our previous blogs to get a solid understanding of its basics.

In this part of the blog, we’ll focus on setting up SQLite and connecting it to your web application. Whether you're building an online store, a blog, or any other app, this setup will form the foundation of your app's data handling capabilities.

Step 1: Setting Up Your Project

Before we dive into the code, let’s make sure your development environment is ready.

Install SQLite

SQLite comes built-in with many web development environments, but you might need to install it manually in some cases.

Setting Up the Environment

1. Node.js (If using JavaScript backend):
   Make sure you have Node.js installed to run the server-side code.

node -v

2. Install SQLite3 Package: For a Node.js project, you can use the sqlite3 package to connect SQLite with your web application.

Step 2: Creating the SQLite Database

Now that your environment is ready, let’s create an SQLite database for your web application.

Here’s an example of how to create a database file and set up a table for storing user information in a simple web app.

const sqlite3 = require('sqlite3').verbose();

// Create or open the database
const db = new sqlite3.Database('./app_database.db');

// Create a table (if it doesn't exist)
db.serialize(() => {
  db.run("CREATE TABLE IF NOT EXISTS users (id INT, name TEXT, email TEXT)");

  // Add some data
  const stmt = db.prepare("INSERT INTO users VALUES (?, ?, ?)");
  stmt.run(1, 'John Doe', '[email protected]');
  stmt.finalize();
});

// Close the database
db.close();

Explanation:

• sqlite3.Database: This opens the database file. If it doesn’t exist, SQLite creates it.

• CREATE TABLE: This command creates a table in the database for storing user details.

• INSERT INTO: This command adds data to the table.

Now, you have an SQLite database with a users table!

Step 3: Connecting the Database to Your Web Application

To integrate SQLite into your web application, you need to use the database connection to query and manipulate the data. Let’s look at how to query data from the SQLite database and send it to the client (frontend) through an API endpoint.

Here’s how you can create a basic Express server to retrieve data from the SQLite database:

1. Install Express:

npm install express

2. Create the Server:

const express = require('express');
const sqlite3 = require('sqlite3').verbose();
const app = express();
const port = 3000;

app.get('/users', (req, res) => {
  const db = new sqlite3.Database('./app_database.db');
  
  db.all("SELECT * FROM users", (err, rows) => {
    if (err) {
      res.status(500).json({ error: err.message });
    } else {
      res.json({
        users: rows
      });
    }
  });

  db.close();
});

app.listen(port, () => {
  console.log(`Server is running on http://localhost:${port}`);
});

Explanation:

• We create a simple API route /users to fetch data from the SQLite database.

• db.all: Fetches all users from the users table.

• The data is returned as JSON, which can be consumed by the frontend.

Step 4: Testing the API

After running your server, you can test the /users endpoint to make sure everything is working.

1. Run the Server:

node app.js

2. Access the Endpoint: Open your browser and go to http://localhost:3000/users. You should see the list of users you added to the SQLite database in JSON format.

Conclusion

In this first part of integrating SQLite with a web application, we covered setting up your environment, creating a database, and connecting it to your web app using Node.js and SQLite. You now have a working backend that retrieves data from an SQLite database.

In Part 2, we’ll dive deeper into handling POST requests to add new data to the database, implementing error handling, and building more advanced features like search and pagination.

Subscribe Now

Stay up-to-date with our latest tutorials and tips for building powerful web applications with SQLite and other technologies! Subscribe now to receive expert advice and step-by-step guides directly in your inbox.