SQLite in Web Development Practical Example

Introduction

SQLite is a lightweight, serverless database engine that integrates seamlessly with web development frameworks. Its simplicity, portability, and compatibility with multiple programming languages make it an ideal choice for building web applications. In this practical example, we’ll demonstrate how to create a simple web application using SQLite to manage a to-do list, showcasing key concepts such as database creation, data manipulation, and user interaction.

Key Concepts

Before diving into the example, understanding the following is essential:

• SQLite Basics: SQLite is a relational database management system (RDBMS) that stores data in a single file. It supports SQL queries and is natively supported in many programming languages.
• Web Development Integration: Web applications often use client-server architectures, where the backend (e.g., a server with a database) handles data storage and retrieval, and the frontend (e.g., HTML, CSS, JavaScript) presents the data to users.
• Data Modeling: In a to-do list application, a tasks table would store id, text, and completed fields.

Practical Example: To-Do List with SQLite

Step 1: Set Up the SQLite Database

Create a SQLite database file (e.g., todo.db) and define the tasks table:

CREATE TABLE tasks (  
    id INTEGER PRIMARY KEY AUTOINCREMENT,  
    text TEXT NOT NULL,  
    completed BOOLEAN DEFAULT FALSE  
);  

This table stores task IDs, text, and a flag to indicate completion.

Step 2: Connect to the Database in a Web Framework

Use a web framework like Python Flask or Node.js to connect to the database. For example, in Python:

import sqlite3  
app = Flask()  

@app.route('/add')  
def add_task():  
    conn = sqlite3.connect('todo.db')  
    c = conn.cursor()  
    c.execute("INSERT INTO tasks (text) VALUES (?)", (request.args.get('text'),))  
    conn.commit()  
    conn.close()  

Step 3: Create the Web Interface

Develop a simple HTML frontend to interact with the database:

<!DOCTYPE html>  
<html>  
<head>  
    <title>To-Do List</title>  
</head>  
<body>  
    <h1>To-Do List</h1>  
    <form action="/add" method="GET">  
        <input type="text" name="text" placeholder="Enter a task..." required>  
        <button type="submit">Add</button>  
    </form>  
    <ul id="tasks">  
        <!-- Tasks will be dynamically inserted here -->  
    </ul>  
</body>  
</html>  

Step 4: Fetch and Display Tasks

Use JavaScript to fetch and display tasks from the database:

fetch('/tasks')  
    .then(response => response.json())  
    .then(data => {  
        const tasksList = document.getElementById('tasks');  
        data.forEach(task => {  
            const li = document.createElement('li');  
            li.textContent = task.text;  
            li.innerHTML += `<button onclick="markAsCompleted(${task.id})">✓</button>`;  
            tasksList.appendChild(li);  
        });  
    });  

Step 5: Mark Tasks as Completed

Add a JavaScript function to mark tasks as completed:

function markAsCompleted(id) {  
    fetch(`/update?id=${id}`)  
        .then(response => response.json())  
        .then(data => {  
            // Update the UI or database  
        });  
}  

Step 6: Deploy the Application

Run the web server and test the application:

• For Python: python app.py (using Flask).
• For Node.js: npm init and npm install express, then node app.js.

Key Features Demonstrated

• Database Integration: SQLite is used to store and manage task data.
• CRUD Operations: Add, retrieve, and update tasks.
• Frontend Interaction: HTML, CSS, and JavaScript handle user input and display data.
• Real-Time Updates: Tasks are dynamically reflected in the browser.

Conclusion

This practical example illustrates how SQLite can be effectively integrated into web development to build interactive applications. By leveraging SQLite’s simplicity and compatibility, developers can create robust backend systems that handle data storage and retrieval efficiently. The to-do list example demonstrates the full workflow from database creation to user interaction, highlighting the importance of structured data modeling and seamless integration between frontend and backend technologies.