SQL and Express

Imagine you're a chef managing a restaurant. You have a notebook to track all your recipes, but now, customers want to place orders online. To handle this efficiently, you need to connect your notebook (the database) with an ordering system (the Express API). This tutorial shows you how to combine SQL with Express to build a dynamic and functional application.

Combining Express and SQL

By integrating SQL statements with an Express application, you can create powerful APIs that interact with a SQLite3 database to perform CRUD operations (Create, Read, Update, Delete).

Starting with Express Boilerplate

Every Express application for an API follows a similar structure:

// Import Express and set up the application
const express = require('express');
const app = express();

// Load environment variables
require('dotenv').config();

// Initialize database (details below)
// ...

// Enable JSON parsing in Express
app.use(express.json());

// Define routes here
// ...

// Set up the server to listen on a port
const port = 5000;
app.listen(port, () => console.log('Server is listening on port', port));
    

Connecting SQLite3 to Your Express App

To connect your app to a SQLite3 database, install the sqlite3 package:

npm install sqlite3

Then, initialize the database at the beginning of your application file:

// Import and initialize SQLite3
const sqlite3 = require('sqlite3');
const db = new sqlite3.Database(
    process.env.data_source, 
    sqlite3.OPEN_READWRITE
);
    

Using sqlite3.OPEN_READWRITE ensures that your server can read and write data but not create or drop tables—this is a security best practice.

Querying All Records

Let's start with the simplest SQL query: SELECT. This ensures your database connection works properly.

Define a route to retrieve all rows:

// Define a route to get all records
app.get('/trees', (req, res, next) => {
    const sql = 'SELECT * FROM trees';
    const params = [];

    db.all(sql, params, (err, rows) => {
        if (err) {
            next(err); // Pass errors to Express error handler
        } else {
            res.json(rows); // Respond with the data
        }
    });
});
    

Best Practices

• Query only what you need: Be specific about columns to improve performance (e.g., SELECT id, name FROM trees).
• Catch errors: Always handle errors to ensure your app doesn't crash.

Querying a Single Row

To retrieve a specific row, use db.get(). Dynamic values in the SQL query are replaced with placeholders ?, which prevent SQL injection.

// Define a route to get a single record by ID
app.get('/trees/:id', (req, res, next) => {
    const sql = 'SELECT * FROM trees WHERE id = ?';
    const params = [req.params.id];

    db.get(sql, params, (err, row) => {
        if (err) {
            next(err);
        } else {
            res.json(row);
        }
    });
});
    

Inserting Data

To insert new data, use db.run(). For example, you can create a new tree record:

// Define a route to insert a new record
app.post('/trees', (req, res, next) => {
    const sql = `
        INSERT INTO trees (tree, location, height_ft, ground_circumference_ft)
        VALUES (?, ?, ?, ?);
    `;
    const params = [
        req.body.name,
        req.body.location,
        req.body.height,
        req.body.size
    ];

    db.run(sql, params, (err) => {
        if (err) {
            next(err);
        } else {
            const sqlLast = 'SELECT * FROM trees ORDER BY id DESC LIMIT 1';
            db.get(sqlLast, [], (err, row) => {
                res.json(row); // Return the newly added row
            });
        }
    });
});
    

Best Practices

• Return meaningful responses: Instead of a generic success message, return the inserted record.
• Use transactions: For complex operations, ensure all queries succeed or none do.

Summary of SQL Functions in SQLite3

• db.all(sql, params, callback): Retrieves multiple rows.
• db.get(sql, params, callback): Retrieves a single row.
• db.run(sql, params, callback): Executes non-SELECT statements (e.g., INSERT, DELETE, UPDATE).

What You've Learned

Combining SQL and Express enables you to build APIs that interact dynamically with a database. You learned:

• How to connect SQLite3 to an Express application.
• How to define routes for SELECT, INSERT, and other SQL operations.
• Best practices for error handling and securing your database interactions.

Real-World Applications

• E-commerce platforms: Manage product catalogs and customer orders.
• Content management systems: Store and retrieve articles or user-generated content.
• Reporting tools: Fetch and display aggregated data.

Now, try expanding this app! Add more routes for updating and deleting records, or connect this API to a frontend application for a complete project.