Build a Staff Directory Application

In this tutorial, you will learn how to use D1 to build a staff directory. This application will allow users to access information about an organization's employees and give admins the ability to add new employees directly within the app. To do this, you will first need to set up a D1 database to manage data seamlessly, then you will develop and deploy your application using the HonoX Framework ↗ and Cloudflare Pages.

Prerequisites

Before moving forward with this tutorial, make sure you have the following:

• A Cloudflare account, if you do not have one, sign up ↗ before continuing.
• A recent version of npm ↗ installed.

If you do not want to go through with the setup now, view the completed code ↗ on GitHub.

1. Install HonoX

In this tutorial, you will use HonoX ↗, a meta-framework for creating full-stack websites and Web APIs to build your application. To use HonoX in your project, run the hono-create command.

To get started, run the following command:

npm create hono@latest

During the setup process, you will be asked to provide a name for your project directory and to choose a template. When making your selection, choose the x-basic template.

2. Initialize your HonoX application

Once your project is set up, you can see a list of generated files as below. This is a typical project structure for a HonoX application:

.
├── app
│   ├── global.d.ts // global type definitions
│   ├── routes
│   │   ├── _404.tsx // not found page
│   │   ├── _error.tsx // error page
│   │   ├── _renderer.tsx // renderer definition
│   │   ├── about
│   │   │   └── [name].tsx // matches `/about/:name`
│   │   └── index.tsx // matches `/`
│   └── server.ts // server entry file
├── package.json
├── tsconfig.json
└── vite.config.ts

The project includes directories for app code, routes, and server setup, alongside configuration files for package management, TypeScript, and Vite.

3. Create a database

To create a database for your project, use the Cloudflare CLI tool, Wrangler, which supports the wrangler d1 command for D1 database operations. Create a new database named staff-directory with the following command:

npx wrangler d1 create staff-directory

After creating your database, you will need to set up a binding in the Wrangler configuration file to integrate your database with your application.

This binding enables your application to interact with Cloudflare resources such as D1 databases, KV namespaces, and R2 buckets. To configure this, create a wrangler.toml file in your project's root directory and input the basic setup information:

name = "staff-directory"
compatibility_date = "2023-12-01"

Next, add the database binding details to your wrangler.toml file. This involves specifying a binding name (in this case, DB), which will be used to reference the database within your application, along with the database_name and database_id provided when you created the database:

[[d1_databases]]
binding = "DB"
database_name = "staff-directory"
database_id = "f495af5f-dd71-4554-9974-97bdda7137b3"

You have now configured your application to access and interact with your D1 database, either through the command line or directly within your codebase.

You will also need to make adjustments to your Vite config file in vite.config.js. Add the following config settings to ensure that Vite is properly set up to work with Cloudflare bindings in local environment:

import adapter from "@hono/vite-dev-server/cloudflare";

export default defineConfig(({ mode }) => {
  if (mode === "client") {
    return {
      plugins: [client()],
    };
  } else {
    return {
      plugins: [
        honox({
          devServer: {
            adapter,
          },
        }),
        pages(),
      ],
    };
  }
});

4. Interact with D1

To interact with your D1 database, you can directly issue SQL commands using the wrangler d1 execute command:

wrangler d1 execute staff-directory --command "SELECT name FROM sqlite_schema WHERE type ='table'"

The command above allows you to run queries or operations directly from the command line.

For operations such as initial data seeding or batch processing, you can pass a SQL file with your commands. To do this, create a schema.sql file in the root directory of your project and insert your SQL queries into this file:

CREATE TABLE locations (
    location_id INTEGER PRIMARY KEY AUTOINCREMENT,
    location_name VARCHAR(255) NOT NULL
);

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY AUTOINCREMENT,
    department_name VARCHAR(255) NOT NULL
);

CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(255) NOT NULL,
    position VARCHAR(255) NOT NULL,
    image_url VARCHAR(255) NOT NULL,
    join_date DATE NOT NULL,
    location_id INTEGER REFERENCES locations(location_id),
    department_id INTEGER REFERENCES departments(department_id)
);

INSERT INTO locations (location_name) VALUES ('London, UK'), ('Paris, France'), ('Berlin, Germany'), ('Lagos, Nigeria'), ('Nairobi, Kenya'), ('Cairo, Egypt'), ('New York, NY'), ('San Francisco, CA'), ('Chicago, IL');

INSERT INTO departments (department_name) VALUES ('Software Engineering'), ('Product Management'), ('Information Technology (IT)'), ('Quality Assurance (QA)'), ('User Experience (UX)/User Interface (UI) Design'), ('Sales and Marketing'), ('Human Resources (HR)'), ('Customer Support'), ('Research and Development (R&D)'), ('Finance and Accounting');

The above queries will create three tables: Locations, Departments, and Employees. To populate these tables with initial data, use the INSERT INTO command. After preparing your schema file with these commands, you can apply it to the D1 database. Do this by using the --file flag to specify the schema file for execution:

wrangler d1 execute staff-directory --file=./schema.sql

To execute the schema locally and seed data into your local directory, pass the --local flag to the above command.

5. Create SQL statements

After setting up your D1 database and configuring the wrangler.toml file as outlined in previous steps, your database is accessible in your code through the DB binding. This allows you to directly interact with the database by preparing and executing SQL statements. In the following step, you will learn how to use this binding to perform common database operations such as retrieving data and inserting new records.

Retrieve data from database

export const findAllEmployees = async (db: D1Database) => {
  const query = `
      SELECT employees.*, locations.location_name, departments.department_name
      FROM employees
      JOIN locations ON employees.location_id = locations.location_id
      JOIN departments ON employees.department_id = departments.department_id
      `;
  const { results } = await db.prepare(query).all();
  const employees = results;
  return employees;
};

Insert data into the database

export const createEmployee = async (db: D1Database, employee: Employee) => {
  const query = `
      INSERT INTO employees (name, position, join_date, image_url, department_id, location_id)
      VALUES (?, ?, ?, ?, ?, ?)`;

  const results = await db
    .prepare(query)
    .bind(
      employee.name,
      employee.position,
      employee.join_date,
      employee.image_url,
      employee.department_id,
      employee.location_id,
    )
    .run();
  const employees = results;
  return employees;
};

For a complete list of all the queries used in the application, refer to the db.ts ↗ file in the codebase.

6. Develop the UI

The application uses hono/jsx for rendering. You can set up a Renderer in app/routes/_renderer.tsx using the JSX-rendered middleware, serving as the entry point for your application:

import { jsxRenderer } from 'hono/jsx-renderer'
import { Script } from 'honox/server'

export default jsxRenderer(({ children, title }) => {
  return (
    <html lang="en">
      <head>
        <meta charset="utf-8" />
        <meta name="viewport" content="width=device-width, initial-scale=1.0" />
        <title>{title}</title>
        <Script src="/app/client.ts" async />
      </head>
      <body>{children}</body>
    </html>
  )
})

Add the bindings defined earlier in global.d.ts file where the global type definitions for TypeScript is defined ensuring type consistency across your application:

declare module "hono" {
  interface Env {
    Variables: {};
    Bindings: {
      DB: D1Database;
    };
  }
}

This application uses Tailwind CSS ↗ for styling. To use Tailwind CSS, refer to the TailwindCSS documentation ↗, or follow the steps provided on GitHub ↗.

To display a list of employees, invoke the findAllEmployees function from your db.ts file and call that within the routes/index.tsx file. The createRoute() function present in the file serves as a helper function for defining routes that handle different HTTP methods like GET, POST, PUT, or DELETE.

import { css } from 'hono/css'
import { createRoute } from 'honox/factory'
import Counter from '../islands/counter'

const className = css`
  font-family: sans-serif;
`

export default createRoute((c) => {
  const name = c.req.query('name') ?? 'Hono'
  return c.render(
    <div class={className}>
      <h1>Hello, {name}!</h1>
      <Counter />
    </div>,
    { title: name }
  )
})

The existing code within the file includes a placeholder that uses the Counter component. You should replace this section with the following code block:

import { createRoute } from 'honox/factory'
import type { FC } from 'hono/jsx'
import type { Employee } from '../db'
import { findAllEmployees, findAllDepartments, findAllLocations } from '../db'

const EmployeeCard: FC<{ employee: Employee }> = ({ employee }) => {
  const { employee_id, name, image_url, department_name, location_name } = employee;
  return (
    <div className="max-w-sm bg-white border border-gray-200 rounded-lg shadow-md">
      <a href={`/employee/${employee_id}`}>
        <img className="bg-indigo-600 p-4 rounded-t-lg" src={image_url} alt={name} />
        //...
      </a>
    </div>
  );
};

export const GET = createRoute(async (c) => {
  const employees = await findAllEmployees(c.env.DB)
  const locations = await findAllLocations(c.env.DB)
  const departments = await findAllDepartments(c.env.DB)
  return c.render(
    <section className="flex-grow">
      <h1 className="mb-4 text-3xl font-extrabold text-gray-900 dark:text-white md:text-5xl lg:text-6xl mt-12">
        <span className="text-transparent bg-clip-text bg-gradient-to-r to-blue-600 from-sky-400">{`Directory `}</span>
      </h1>
      //...
      </section>
      <section className="flex flex-wrap -mx-4">
        {employees.map((employee) => (
          <div className="w-full sm:w-1/2 md:w-1/3 lg:w-1/4 px-2 mb-4">
            <EmployeeCard employee={employee} />
          </div>
        ))}
      </section>
    </section>
  )
})

The code snippet demonstrates how to import the findAllEmployees, findAllLocations, and findAllDepartments functions from the db.ts file, and how to use the binding c.env.DB to invoke these functions. With these, you can retrieve and display the fetched data on the page.

Add an employee

Use the export POST route to create a new employee through the /admin page:

import { createRoute } from "honox/factory";
import type { Employee } from "../../db";
import { getFormDataValue, getFormDataNumber } from "../../utils/formData";
import { createEmployee } from "../../db";

export const POST = createRoute(async (c) => {
  try {
    const formData = await c.req.formData();
    const imageFile = formData.get("image_file");
    let imageUrl = "";

    // TODO: process image url with R2

    const employeeData: Employee = {
      employee_id: getFormDataValue(formData, "employee_id"),
      name: getFormDataValue(formData, "name"),
      position: getFormDataValue(formData, "position"),
      image_url: imageUrl,
      join_date: getFormDataValue(formData, "join_date"),
      department_id: getFormDataNumber(formData, "department_id"),
      location_id: getFormDataNumber(formData, "location_id"),
      location_name: "",
      department_name: "",
    };

    await createEmployee(c.env.DB, employeeData);
    return c.redirect("/", 303);
  } catch (error) {
    return new Response("Error processing your request", { status: 500 });
  }
});

Store images in R2

During the process of creating a new employee, the image uploaded can be stored in an R2 bucket prior to being added to the database.

To store an image in an R2 bucket:

1. Create an R2 bucket.
2. Upload the image to this bucket.
3. Obtain a public URL for the image from the bucket. This URL is then saved in your database, linking to the image stored in the R2 bucket.

Use the wrangler r2 bucket create command to create a bucket:

wrangler r2 bucket create employee-avatars

Once the bucket is created, add the R2 bucket binding to your wrangler.toml file:

[[r2_buckets]]
binding = "MY_BUCKET"
bucket_name = "employee-avatars"

Pass the R2 binding to the global.d.ts file:

declare module "hono" {
  interface Env {
    Variables: {};
    Bindings: {
      DB: D1Database;
      MY_BUCKET: R2Bucket;
    };
  }
}

To store the uploaded image in the R2 bucket, you can use the put() method provided by R2. This method allows you to upload the image file to your bucket:

if (imageFile instanceof File) {
  const key = `${new Date().getTime()}-${imageFile.name}`;
  const fileBuffer = await imageFile.arrayBuffer();

  await c.env.MY_BUCKET.put(key, fileBuffer, {
    httpMetadata: {
      contentType: imageFile.type || "application/octet-stream",
    },
  });
  console.log(`File uploaded successfully: ${key}`);
  imageUrl = `https://pub-8d936184779047cc96686a631f318fce.r2.dev/${key}`;
}

Refer to GitHub ↗ for the full codebase.

7. Deploy your HonoX application

With your application ready for deployment, you can use Wrangler to build and deploy your project to the Cloudflare Network. Ensure you are logged in to your Cloudflare account by running the wrangler whoami command. If you are not logged in, Wrangler will prompt you to login by creating an API key that you can use to make authenticated requests automatically from your computer.

After successful login, confirm that your wrangler.toml file is configured similarly to the code block below:

name = "staff-directory"
compatibility_date = "2023-12-01"

[[r2_buckets]]
binding = "MY_BUCKET"
bucket_name = "employee-avatars"

[[d1_databases]]
binding = "DB"
database_name = "staff-directory"
database_id = "f495af5f-dd71-4554-9974-97bdda7137b3"

Run wrangler deploy to deploy your project to Cloudflare. After deployment you can test your application is working by accessing the deployed URL provided for you. Your browser should display your application with the base frontend you created. If you do not have any data populated in your database, go to the /admin page to add a new employee, and this should return a new employee in your home page.

Conclusion

In this tutorial, you built a staff directory application where users can view all employees within an organization. Refer to the Staff directory repository ↗ for the full source code.

Was this helpful?

What did you like?

Accurate

Easy to understand

Solved my problem

Helped me decide to use the product

Other

What went wrong?

Hard to understand

Incorrect information

Missing the information

Other

Thank you for helping improve Cloudflare's documentation!