In this tutorial, we’ll walk through the process of creating a Node.js API using Knex.js as our query builder and MySQL as our database. We’ll build a simple Bookstore API to demonstrate how to combine these powerful tools for efficient database management and data retrieval.
Prerequisites:
- Basic knowledge of Node.js and Express
- MySQL installed on your system or access to an online MySQL database
- Node.js and npm installed on your machine
Step 1: Setting Up the Project
First, let’s create a new project and install the necessary dependencies:
mkdir bookstore-api && cd bookstore-api
npm init -y
npm install express knex mysql dotenv
Step 2: Configuring the Database
Create a .env
file in your project root and add your database credentials:
DB_HOST=your_host
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database_name
Step 3: Setting Up Express Server
Create an index.js
file in your project root:
const express = require("express");
const app = express();
const port = 3000;
app.use(express.json());
app.listen(port, () => {
console.log(`Bookstore API listening on port ${port}`);
});
Step 4: Configuring Knex
Run npx knex init
to generate a knexfile.js
, then modify it:
require("dotenv").config();
module.exports = {
development: {
client: "mysql",
connection: {
host: process.env.DB_HOST,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
database: process.env.DB_NAME,
},
migrations: {
directory: "./db/migrations",
},
seeds: {
directory: "./db/seeds",
},
},
};
Create a db/db.js
file to export the Knex instance:
const knex = require("knex");
const knexFile = require("../knexfile.js");
const environment = process.env.NODE_ENV || "development";
module.exports = knex(knexFile[environment]);
Step 5: Creating Migrations
Generate migration files for our tables:
npx knex migrate:make create_authors_table
npx knex migrate:make create_genres_table
npx knex migrate:make create_books_table
Fill in the migration files (in the db/migrations
directory) with the table schemas.
Step 6: Running Migrations
Apply the migrations to create the database tables:
npx knex migrate:latest
Step 7: Seeding the Database
Create seed files for initial data:
npx knex seed:make 01_authors
npx knex seed:make 02_genres
npx knex seed:make 03_books
Fill in the seed files (in the db/seeds
directory) with initial data.
Run the seeds:
npx knex seed:run
Step 8: Implementing API Routes
Create a routes/bookstore.js
file:
const express = require("express");
const router = express.Router();
const db = require("../db/db.js");
// Get all books
router.get("/books", async (req, res) => {
try {
const books = await db("books");
res.json(books);
} catch (err) {
console.error(err);
res.status(500).send("Internal Server Error");
}
});
// Get books by genre
router.get("/genres/:id/books", async (req, res) => {
try {
const genreId = req.params.id;
const books = await db("books").where("genre_id", genreId);
const genre = await db("genres").where("id", genreId).first();
res.json({ genre: genre.name, books });
} catch (err) {
console.error(err);
res.status(500).send("Internal Server Error");
}
});
// Get books by author
router.get("/authors/:id/books", async (req, res) => {
try {
const authorId = req.params.id;
const books = await db("books").where("author_id", authorId);
const author = await db("authors").where("id", authorId).first();
res.json({ author: author.name, books });
} catch (err) {
console.error(err);
res.status(500).send("Internal Server Error");
}
});
module.exports = router;
Update index.js
to use the routes:
const bookstoreRoutes = require("./routes/bookstore");
app.use("/api", bookstoreRoutes);
Conclusion
In this tutorial, we’ve built a simple Bookstore API using Node.js, Express, Knex, and MySQL. We’ve covered setting up the project, configuring the database, creating and running migrations, seeding data, and implementing API routes.
This foundation can be expanded to include more complex queries, additional CRUD operations, and authentication for a full-fledged API. Happy coding!