We're building an API that will serve as a digital catalog of America's largest trees. Think of this like creating a virtual ranger station where people can look up information about remarkable trees, add new discoveries, update measurements, and maintain records. Just as a park ranger needs to keep track of important trees in their forest, our API will help manage a database of significant trees.
Our project has several key components that work together:
The database (like a filing cabinet) stores all our tree information
Express routes (like different service counters) handle different types of requests
SQL queries (like detailed search instructions) help us find and modify the exact information we need
server/
├── app.js # Main application file (like the ranger station's foundation)
├── routes/
│ └── trees.js # Route handlers (like different service desks)
├── seed-data.sql # Initial database setup (like stocking the filing cabinet)
└── .env # Environment configuration (like the station's operating manual)
Let's start by creating our database schema in seed-data.sql:
-- First, ensure we're starting fresh
DROP TABLE IF EXISTS trees;
-- Create our trees table with all necessary columns
CREATE TABLE trees (
id INTEGER PRIMARY KEY AUTOINCREMENT,
tree VARCHAR(32) NOT NULL,
location VARCHAR(64) NOT NULL,
height_ft DECIMAL(5,1) NOT NULL,
ground_circumference_ft DECIMAL(4,1) NOT NULL
);
-- Add our initial collection of remarkable trees
INSERT INTO trees (tree, location, height_ft, ground_circumference_ft) VALUES
('General Sherman', 'Sequoia National Park', 274.9, 102.6),
('General Grant', 'Kings Canyon National Park', 268.1, 107.5),
('President', 'Sequoia National Park', 240.9, 93.0),
('Lincoln', 'Sequoia National Park', 255.8, 98.3),
('Stagg', 'Private Land', 243.0, 109.0);
Think of this schema like designing a standardized form for recording tree information. Each column represents a specific piece of information we want to track about each tree:
id: A unique identifier (like a tree's serial number)
tree: The tree's name (like its given name)
location: Where to find it (like its address)
height_ft: How tall it stands (measured in feet)
ground_circumference_ft: How thick it is at the base (measured in feet)
In trees.js, we'll first set up our database connection:
// First, we need our tools
const sqlite3 = require('sqlite3').verbose();
// Create our connection to the database
const db = new sqlite3.Database(
process.env.DB_FILE,
sqlite3.OPEN_READWRITE,
(err) => {
if (err) console.error('Could not connect to database:', err.message);
else console.log('Connected to SQLite database');
}
);
// Route to get all trees
router.get('/', (req, res, next) => {
// Our SQL query to get tree information, ordered by height
const sql = `
SELECT id, tree, height_ft
FROM trees
ORDER BY height_ft DESC
`;
// Execute the query
db.all(sql, [], (err, rows) => {
if (err) return next(err);
res.json(rows);
});
});
This is like setting up the main information desk at our ranger station. When visitors ask for a list of all trees, we check our records and return them in order from tallest to shortest.
Now let's add the ability to look up specific trees:
router.get('/:id', (req, res, next) => {
const sql = `
SELECT *
FROM trees
WHERE id = ?
`;
const params = [req.params.id];
db.get(sql, params, (err, row) => {
if (err) return next(err);
if (!row) {
return res.status(404).json({ error: 'Tree not found' });
}
res.json(row);
});
});
This is like having a specialized service where visitors can ask about a specific tree by its ID number and get all the details we have about it. The question mark in our SQL query is like a placeholder that we'll fill in with the actual ID when someone makes a request.
Let's implement the ability to add new trees to our database:
router.post('/', (req, res, next) => {
const { name, location, height, size } = req.body;
const sql = `
INSERT INTO trees (tree, location, height_ft, ground_circumference_ft)
VALUES (?, ?, ?, ?)
`;
const params = [name, location, height, size];
db.run(sql, params, (err) => {
if (err) return next(err);
res.json({ message: 'success' });
});
});
This is similar to having a form where forest rangers can register newly discovered remarkable trees. We carefully record all the important details about the tree in our database.
Sometimes we need to remove records:
router.delete('/:id', (req, res, next) => {
const sql = 'DELETE FROM trees WHERE id = ?';
const params = [req.params.id];
db.run(sql, params, (err) => {
if (err) return next(err);
res.json({ message: 'success' });
});
});
Think of this like having a process for removing outdated or incorrect records from our files. We want to be very careful with this operation, which is why we double-check the ID before removing anything.
Finally, let's add the ability to update existing records:
router.put('/:id', (req, res, next) => {
const { id, name, location, height, size } = req.body;
// First verify the IDs match
if (parseInt(req.params.id) !== id) {
return res.status(400).json({ error: 'ids do not match' });
}
const sql = `
UPDATE trees
SET tree = ?, location = ?, height_ft = ?, ground_circumference_ft = ?
WHERE id = ?
`;
const params = [name, location, height, size, id];
db.run(sql, params, (err) => {
if (err) return next(err);
res.json({ message: 'success' });
});
});
This is like having a process for updating our records when new measurements or information become available. We're extra careful here, making sure the ID in the request matches the record we're trying to update.
Throughout our code, we've implemented error handling using next(err). This is like having a protocol for when things go wrong - instead of leaving visitors confused, we pass the problem to our error handling system which can explain what went wrong.
We use parameterized queries (the question marks in our SQL) to prevent SQL injection. This is like having a secure form that sanitizes all input before processing it, protecting our database from malicious input.
We use different SQLite methods for different operations:
db.all(): When we need multiple rows (like listing all trees)
db.get(): When we need one specific row (like finding one tree)
db.run(): When we're changing data (like adding or updating trees)
You can test each endpoint using Postman:
GET /trees: Lists all trees
GET /trees/1: Gets details for one tree
POST /trees: Adds a new tree
PUT /trees/1: Updates an existing tree
DELETE /trees/1: Removes a tree record
Consider adding these enhancements to your API:
Validation to ensure measurements are positive numbers
Sorting options for different tree attributes
Search functionality by tree name or location
Pagination for large datasets