Creating a Node.js API with Knex and MySQL
Published: 2023-05-09 23:49:27

by [David Ekete](https://blog.openreplay.com/authors/david-ekete)

<blockquote><em>
Knex.js and MySQL are essential components commonly used in modern web development for managing and interacting with databases. Knex.js, a JavaScript query builder, simplifies database management, while MySQL, a widely-used relational database management system, allows for efficient data storage and retrieval. This technical article will explore the process of creating a Node API using Knex.js and MySQL, and you will discover how to build complex SQL queries for selecting, inserting, updating, and deleting data.</em></blockquote>

## Getting started

Before you can use MySQL, you have to create a MySQL database. You can create the database locally by [installing MySQL on your system](https://dev.mysql.com/doc/refman/8.0/en/installing.html). Alternatively, you can create a MySQL database online using services like [db4free](https://db4free.net/).

After setting up your MySQL database, create a new project and `cd` into it by running the command below:

```
mkdir mysql-knex && cd mysql-knex
```

Next, initialize npm with all its defaults by running the command below:

```
npm init -y
```

Next, install Knex and the MySQL database driver by running the command below:

```
npm install knex mysql
```

To create an express server and manage your environmental variables, run the command below:

```
npm install express dotemv
```

Then, create a `.env` file in your project’s root directory and store your database credentials.

For example:

```
DB_HOST = 
DB_USER = 
DB_PASSWORD = 
DB_NAME = 
```

## Setting up an Express Server

This tutorial will feature a Bookstore API to demonstrate combining Knex and MySQL with Node.js.

To create an Express server, first, create an `index.js` file in your project’s root directory and add the code block below to it:

```javascript
//index.js
const express = require("express");
const app = express();
const port = 3000;

app.use(express.json());

app.listen(port, () => {
  console.log(`App listening on port:${port}`);
});
```

The code block above creates a simple express server. It listens on port `3000` and uses the `express.json()` middleware to parse incoming JSON requests.

## Configuring Knex with MySQL

Knex requires a knexfile containing configuration options for connecting to a database, such as a database type, host, port, username, password, and other configuration options.

Run the command below to create a knexfile:

```
knex init
```

This command above will create a `knexfile.js` file in your project’s root directory. By default, the generated `knexfile.js` file will have `sqlite3` as its development database.

To configure Knex to use MySQL, replace the contents of your `knexfile.js` file with the code block below:

```javascript
require("dotenv").config()

/**
 * @type { Object.<string, import("knex").Knex.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",
    }
  }
};
```

The code block above configures Knex to use MySQL as its database driver and specifies the file path where the migration files will be stored.

Next, create a `db` folder in your project directory by running the command below:

```
mkdir db
```

Then, create a `db.js` file in your `db` folder and import `knex` and your `knexFile.js` file.

Like so:

```javascript
//db/db.js
const knex = require("knex");
const knexFile = require("../knexfile.js");
```

Next, add the code block below to your db.js file:

```javascript
//db/db.js
const environment = process.env.NODE_ENV || "development";

module.exports = knex(knexFile[environment]);
```

The code block above sets the `environment` variable to either the `NODE_ENV` environment variable or `development` if `NODE_ENV` is not set. This lets you specify different configurations for different environments, such as development, production, or testing.

## Creating Migration Files

Migration files are files you can use to manage changes to the database schema. When you create a new table, modify an existing table, or add new columns, you must modify the database schema to reflect these changes.

Your Bookstore API will have three database tables: Books, Authors, and Genres. Thus, you will need three migration files.

Run the command below to create your `books` migration file:

```
knex migrate:make books
```

Run the command below to create your `authors` migration file:

```
knex migrate:make authors
```

Run the command below to create your `genres` migration file:

```
knex migrate:make genres
```

Running the commands above will create three migration files in your `knexfile.js` specified file path.

### Writing Migrations

The migration files generated by Knex contain boilerplate code with no connection to the type of application you are building.

Open your `authors` migration file and replace the `up` function with the code block below:

```javascript
exports.up = function(knex) {
  return knex.schema.createTable("authors", (table) => {
    table.increments("id").primary();
    table.string("name").notNullable();
    table.timestamps(true, true);
  })
};
```

The code block above, when executed, creates an “authors” table in your database with the tables specified above.

Next, replace the `down` function with the code block below:

```javascript
exports.down = function(knex) {
  return knex.schema.dropTableIfExists("authors");
};
```

The code block above, when executed, drops the “authors” table in your database, which is the opposite of what the `up` function does.

Next, open your `genres` migration file and replace the `up` function with the code block below:

```javascript
exports.up = function (knex) {
  return knex.schema.createTable("genres", (table) => {
    table.increments("id").primary();
    table.string("name").notNullable();
    table.timestamps(true, true);
  });
};
```

The code block above, when executed, creates a “genres” table in your database with the tables specified above.

Next, replace the `down` function with the code block below:

```javascript
exports.down = function (knex) {
  return knex.schema.dropTableIfExists("genres");
};
```

The code block above, when executed, drops the “genres” table in your database, which is the opposite of what the `up` function does.

Next, open your `books` migration file and replace the `up` function with the code block below:

```javascript
exports.up = function(knex) {
  return knex.schema.createTable("books", (table) => {
    table.increments("id").primary();
    table.string("title").notNullable();
    table.integer('author_id').unsigned().notNullable();
    table.integer('genre_id').unsigned().notNullable();
    table.foreign('author_id').references('authors.id');
    table.foreign('genre_id').references('genres.id');
    table.timestamps(true, true);
  })
};
```

The code block above, when executed, creates a “books” table in your database with the tables specified above. It also defines a many-to-one relationship between genres, authors, and books.

Next, replace the `down` function with the code block below:

```javascript
exports.down = function(knex) {
  return knex.schema.dropTableIfExists("books");
};
```

The code block above, when executed, drops the “books” table in your database, which is the opposite of what the `up` function does.

To run the migrations, run the command below on your terminal:

```
knex migrate:latest
```

The command above goes through all your migration files and runs the `up` function.

To undo the migrations, run the command below:

```
knex migrate:rollback
```

The command above goes through all your migration files and runs the `down` function.

The Books table has two rows that depend on the Genres and Authors tables, respectively. So you have to create the Authors and Genres table before you create the Books table. 

Alternatively, you can run the migrations in the sequence Authors → Genres → Books by running the command below:

```
knex migrate:up <migration_filename>
```


<div style="background-color:#efefef; border-radius:8px; padding:10px; display:block;">
<hr/>
<h3><em>Session Replay for Developers</em></h3> 
<p><em>Uncover frustrations, understand bugs and fix slowdowns like never before with <strong><a href="https://github.com/openreplay/openreplay" target="_blank">OpenReplay</a></strong> — an open-source session replay suite for developers. It can be <strong>self-hosted</strong> in minutes, giving you complete control over your customer data.</em></p>
<img alt="OpenReplay" style="margin-top:5px; margin-bottom:5px;" width="768" height="400" src="https://raw.githubusercontent.com/openreplay/openreplay/main/static/openreplay-git-hero.svg" class="astro-UXNKDZ4E" loading="lazy" decoding="async">
<p><em>Happy debugging! <a href="https://openreplay.com" target="_blank">Try using OpenReplay today.</a></em><p>
<hr/>
</div>


## Seeding Databases with Data

Seeding refers to populating your database with initial data that you can use for development or testing.

To seed your database with initial data, you must create seed files using Knex.

Run the command below to create a seed file for your `author` table:

```
knex seed:make authors
```

The command above generates an “authors” seed file in your `knexfile.js` specified file path.

Next, replace the generated boilerplate code with the code block below:

```javascript
//authors.js
exports.seed = async function(knex) {
  // Deletes ALL existing entries
  await knex('authors').del()
  await knex('authors').insert([
    {id: 1, name: 'Jane Austen'},
    {id: 2, name: 'Ernest Hemingway'},
    {id: 3, name: 'Agatha Christie'}
  ]);
};
```

The code block above, when executed, deletes all existing entries from the `authors` table and inserts the specified entries in the table.

Run the command below to create a seed file for your `genres` table:

```
knex seed:make genres
```

The command above generates a “genres” seed file in your `knexfile.js` specified file path.

Next, replace the generated boilerplate code with the code block below:

```javascript
//genres.js
exports.seed = async function (knex) {
  // Deletes ALL existing entries
  await knex("genres").del();
  await knex("genres").insert([
    { id: 1, name: "Romance" },
    { id: 2, name: "Mystery" },
    { id: 3, name: "Thriller" },
    { id: 4, name: "Science Fiction" },
  ]);
};
```

Run the command below to create a seed file for your `books` table:

```
knex seed:make books
```

Next, replace the generated boilerplate code with the code block below:

```javascript
//books.js
exports.seed = async function (knex) {
  // Deletes ALL existing entries
  await knex("books").del();
  await knex("books").insert([
    {
      id: 1,
      title: "Pride and Prejudice",
      description: "A novel of manners by Jane Austen",
      author_id: 1,
      genre_id: 1,
    },
    {
      id: 2,
      title: "The Old Man and the Sea",
      description: "A short novel by Ernest Hemingway",
      author_id: 2,
      genre_id: 3,
    },
    {
      id: 3,
      title: "Murder on the Orient Express",
      description: "A detective novel by Agatha Christie",
      author_id: 3,
      genre_id: 2,
    },
    {
      id: 4,
      title: "Emma",
      description: "A novel by Jane Austen",
      author_id: 1,
      genre_id: 1,
    },
    {
      id: 5,
      title: "The Sun Also Rises",
      description: "A novel by Ernest Hemingway",
      author_id: 2,
      genre_id: 1,
    },
  ]);
};
```

Like the migrations files, you must run the seed files in the order of Authors → Genres, → Books.

You can execute a seed file by running the command below:

```javascript
knex seed:run --specific=<file_name>
```

## Querying your Database

Create a `routes` folder in your project’s root directory and create a `bookStore.js` file in it. You are going to implement route handlers that:  

- Fetch all the books in the database.
- Fetch books based on their genre.
- Fetch books based on their author.

To implement these routes, import Express, your Knex instance, and set up the Express router.

Like so:

```javascript
const express = require("express");
const router = express.Router();
const db = require("../db/db.js");
```

Next, add the code block below to implement the route handler that fetches all the books in the database:

```javascript
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");
  }
});
```

Next, add the code block below to implement the route handler that fetches books based on their genre:

```javascript
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);

    res.json({ genre: genre[0].name, books });
  } catch (err) {
    console.error(err);
    res.status(500).send("Internal Server Error");
  }
});
```

Next, add the code block below to implement the route handler that fetches books based on their author:

```javascript
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);
    res.json({ author: author[0].name, books });
  } catch (err) {
    console.error(err);
    res.status(500).send("Internal Server Error");
  }
});
```

Next, export your Express router. Like so:

```javascript
module.exports = router;
```

Finally, import your router instance into your `index.js` file and use it as middleware. Like so:

```javascript
const bookRouter = require("./routes/bookStore.js");

app.use("/api", bookRouter);
```

### Testing your Database

To test your application, you can use tools such as Postman to make HTTP requests to your API and verify that it returns the expected results. Alternatively, you can use your browser.

For example, you can request a GET to [http://localhost:3000/api/genres/1/books](http://localhost:3000/api/genres/1/books) by pasting the URL on your browser's search bar and hitting **ENTER**. Your results should be similar to the image below.

![-](https://blog.openreplay.com/images/creating-a-nodejs-api-with-knex-and-mysql/images/9nSRlGO.jpg)



## Conclusion

In this article, you learned how to use Knex.js with MySQL to build a Node.js Bookstore API. You also learned how to configure Knex to use MySQL as its database client, connect to a PostgreSQL database locally and remotely, seed the database, create migrations, and make queries using Knex.

With the knowledge gained in this article, you can now build robust and scalable Node.js applications that leverage the power of MySQL and the simplicity of Knex.js.