We need to create an API that connects Express.js with a SQLite database. This combination allows us to serve dynamic data through web endpoints. Think of it like building a bridge between a web server (Express) and a data storage system (SQLite).
Our database has a simple structure defined in seed-data.sql:
CREATE TABLE colors (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(32) UNIQUE
);
We need to implement three main features:
1. Connect Express to our SQLite database
2. Implement an endpoint to fetch a single color by ID
3. Create an endpoint to add new colors and return the newly created record
Let's break this down into manageable steps:
We'll establish a connection to our SQLite database by:
1. Importing the sqlite3 package
2. Creating a database connection with proper permissions
3. Verifying the connection works through the /colors endpoint
We'll create an endpoint to fetch one color by:
1. Writing an SQL query with a parameter placeholder
2. Extracting the ID from the request parameters
3. Using the database's get method for single-row queries
We'll implement new color creation by:
1. Using an INSERT statement with the provided name
2. Querying for the newly inserted record
3. Returning the complete new record in the response
// Import sqlite3 with verbose mode for better debugging
const sqlite3 = require('sqlite3').verbose();
// Create database connection
const db = new sqlite3.Database(
DB_FILE,
sqlite3.OPEN_READWRITE,
(err) => {
if (err) console.error('Could not connect to database:', err.message);
else console.log('Connected to SQLite database');
}
);
The verbose() mode is like turning on detailed logging - it helps us understand what's happening inside our database operations. The connection setup is similar to opening a secure channel to our data storage.
app.get('/colors/:id', (req, res, next) => {
// SQL query with placeholder for safe parameter insertion
const sql = 'SELECT * FROM colors WHERE id = ?';
// Parameters array ensures safe query execution
const params = [req.params.id];
// Use db.get() for expecting a single row result
db.get(sql, params, (err, row) => {
if (err) {
next(err);
return;
}
if (!row) {
res.status(404).json({ message: 'Color not found' });
return;
}
res.json(row);
});
});
Think of this endpoint like a librarian who can find a specific book when you provide its ID. The SQL query is like the search criteria, and the parameters array is like a safe way to handle the user's request without risking database security.
app.get('/colors/add/:name', (req, res, next) => {
// Insert new color
const sql = "INSERT INTO colors (name) VALUES (?)";
const params = [req.params.name];
// Query to get the newly inserted color
const sqlLast = 'SELECT * FROM colors ORDER BY id DESC LIMIT 1';
// First run the insert operation
db.run(sql, params, function(err) {
if (err) {
next(err);
return;
}
// Then fetch the newly inserted color
db.get(sqlLast, [], (err, row) => {
if (err) {
next(err);
return;
}
res.json(row);
});
});
});
This process is like a two-step transaction: first, we add a new entry to our collection (INSERT), then we look up what we just added to confirm and return the complete information. It's similar to how a library would process a new book - first adding it to the system, then retrieving its full catalog entry.
SQLite offers different modes for opening a database:
OPEN_READONLY: Like having a library card that only lets you read books
OPEN_READWRITE: Like having full library privileges to both read and modify
OPEN_CREATE: Like having permission to establish a new library branch
We use parameterized queries (the ? placeholders) instead of string concatenation. This is like having a secure form that validates inputs before processing them, rather than trusting raw user input. The database handles these parameters safely, preventing malicious code injection.
SQLite3 provides different methods for different query types:
db.all(): For queries that might return multiple rows
db.get(): For queries expecting a single row
db.run(): For queries that modify data but don't return rows
1. Always use parameterized queries for security
2. Handle database errors appropriately and pass them to error handling middleware
3. Close database connections when they're no longer needed
4. Use appropriate HTTP status codes in responses
5. Validate input data before running database operations
Consider these common scenarios:
1. Database connection failures
2. Duplicate entries for UNIQUE constraints
3. Invalid input data types
4. Missing required fields
Try extending the application with these features:
1. Add an endpoint to update existing colors
2. Implement soft deletion for colors
3. Add pagination to the colors list endpoint
4. Implement color search functionality
5. Add input validation middleware