Building a Node.js API with Knex and MySQL: A Step-by-Step Guide

logo cloudi africa, knex and mMysql
2 mn read

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:

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:

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

Reading is essential for those who seek to rise above the ordinary.

ABOUT US

The internet as we know is powerful. Its underlying technologies are transformative, but also, there’s a plethora of haphazard information out there.We are here to serve you as a reliable and credible source to gain consistent information

© 2024, cloudiafrica
Cloudi Africa