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 file in your project's root directory and input the basic setup information:

wrangler.jsonc

{
  "name": "staff-directory",
  "compatibility_date": "2023-12-01"
}

wrangler.toml

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

Next, add the database binding details to your Wrangler 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:

wrangler.jsonc

{
  "d1_databases": [
    {
      "binding": "DB",
      "database_name": "staff-directory",
      "database_id": "f495af5f-dd71-4554-9974-97bdda7137b3"
    }
  ]
}

wrangler.toml

[[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 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>
  )
})

Otomatik - 104.16.6.189 CloudFlare DNS Türk Telekom DNS Google DNS Open DNS

OSZAR »

  )})">