Create a Hapi Server with Knex and MySQL

In this article I am going to show you how to build a simple API server with hapi and MySQL. I am using Knex as a SQL query builder. And in order to pass the database instance to the request object, I use server decorations property. Rest of the code consists of a simple procedure explained here in detail. To see it in action follow the steps below:

1. Setup the project and install dependencies:

mkdir myServer
cd myServer
npm -y init

2. Install dependencies:

npm i @hapi/hapi mysql2 knex

3. Create a file named server.js with the contents below:

const hapi = require("@hapi/hapi");
const knex = require("knex");

// Controller function for '/users' route
function test(req, res) {
  // We can access our database over request object
  return req.database.schema.hasTable("users").then(function (exists) {
    // Check if users table exists in the database and return the JSON object below
    return {
      doesTableExist: exists,
    };
  });
}

async function run() {
  // Define server with its options
  const server = hapi.server({
    port: 3000,
    host: "localhost",
  });

  // Define routes array
  const routes = [
    {
      method: "GET", // Method of the route
      path: "/doesTableExist", // Path of the route
      handler: test, // Handler aka controller of the route
    },
  ];

  server.route(routes);

  // Create a database instance
  const database = knex({
    client: "mysql2", // Database client
    connection: {
      host: "localhost",
      user: "root",
      password: "root",
      database: "myDatabase",
    },
  });

  // Attach database instance to request object under the name of 'database'
  server.decorate("request", "database", database);

  // Initialize the server
  await server.start();

  console.log(`Running`);
}

run();

4. Configure and run your MySQL server and modify database instance with proper values(host, user, password, database). If you want to use another SQL database check out the supported clients here and install the one you need.

5. Run the server with command below:

node server.js

6. Check out the route we defined in our code to see it works:

http://localhost:3000/doesTableExist