Skip to main content
Hashnoderaulnq

Command Palette

Search for a command to run...

Hono and Drizzle ORM

Updated
•10 min read
Hono and Drizzle ORM
R
Raul Naupari
Part of seriesNodeJs

As modern web applications demand increasingly robust data access patterns, the combination of Hono's lightweight framework with Drizzle ORM's type-safe database operations creates a powerful stack for building performant APIs.

We'll build upon an existing Hono application, implementing a complete data layer using Drizzle ORM with PostgreSQL. By the end, we will understand how to leverage Drizzle's type-safe query builder and schema management to create production-ready APIs.

What is Drizzle?

Drizzle is a TypeScript-first Object-Relational Mapping library designed with performance and developer experience as primary concerns. Unlike traditional ORMs that abstract SQL away entirely, Drizzle takes a different approach: it provides a thin, type-safe layer over SQL that feels natural to developers who understand relational databases. Drizzle follows several core design principles that distinguish it from other ORMs:

  • SQL-like API: Query builders mirror SQL syntax closely rather than hiding it.

  • Type Safety: Full TypeScript type inference from schema to query results.

  • Zero Dependencies: Core package has no runtime dependencies.

  • Database Agnostic Drivers: All database drivers are optional peer dependencies.

  • Thin Abstraction Layer: Minimal runtime overhead, direct SQL generation.

Why Drizzle for Hono?

Hono is designed to be lightweight and fast, making it perfect for edge computing. Drizzle shares this philosophy, providing a minimal footprint while maintaining full type safety. Together, they create an optimal stack for modern API development.

Creating a PostgreSQL Database

We will use Docker to create our local database. Create the docker-compose.yml file with the following content:

services:
  database:
    image: postgres
    ports:
      - '5432:5432'
    environment:
      POSTGRES_DB: mydb
      POSTGRES_USER: myuser
      POSTGRES_PASSWORD: mypassword

Then, add the following scripts to the package.json file:

{
  ...
  "scripts": {
    ...
    "database:up": "docker-compose up database -d",
    "database:down": "docker-compose down database",
    "database:stop": "docker-compose stop database",
  },
...
}

Execute npm run database:up to start up the database.

Creating a Database Connection

Install the following packages:

npm install drizzle-orm 
npm install pg
npm install drizzle-kit

First, we need to configure our database connection string. In the env.ts file, we've already defined the environment schema:

import { config } from 'dotenv';
import { expand } from 'dotenv-expand';
import { ZodError, z } from 'zod';

const ENVSchema = z.object({
  NODE_ENV: z
    .enum(['development', 'production', 'test'])
    .default('development'),
  PORT: z.coerce.number().default(3000),
  DATABASE_URL: z.string(),
  TOKEN: z.string().optional(),
});

expand(config());

try {
  ENVSchema.parse(process.env);
} catch (error) {
  if (error instanceof ZodError) {
    const e = new Error(
      `Environment validation failed:\n ${z.treeifyError(error)}`
    );
    e.stack = '';
    throw e;
  } else {
    console.error('Unexpected error during environment validation:', error);
    throw error;
  }
}

export const ENV = ENVSchema.parse(process.env);

This validates that DATABASE_URL is present at startup, preventing runtime errors from missing configuration. The database client is initialized in the database/client.ts file:

import { drizzle } from 'drizzle-orm/node-postgres';
import * as schema from './schemas.js';
import { ENV } from '@/env.js';
export const client = drizzle(ENV.DATABASE_URL, {
  schema,
  logger: ENV.NODE_ENV === 'development',
});

  • Connection String: We use drizzle-orm/node-postgres, which accepts a connection string directly.

  • Schema Import: All schemas are passed to enable type-safe queries with relations.

  • Query Logging: Enabled in development to see generated SQL queries.

  • Single Instance: This client is exported and reused throughout the application.

Schema Definition

Drizzle schemas define our database structure using TypeScript. They serve as both the source of truth for our database and the foundation for type inference. The Drizzle stores schema is defined in the features/stores/store.ts file:

import { z } from 'zod';
import { varchar, pgSchema, uuid } from 'drizzle-orm/pg-core';

export const storeSchema = z.object({
  storeId: z.uuidv7(),
  name: z.string().min(1).max(1024),
  url: z.url().max(2048),
});

export type Store = z.infer<typeof storeSchema>;

const dbSchema = pgSchema('price_tracker');

export const stores = dbSchema.table('stores', {
  storeId: uuid('storeid').primaryKey(),
  name: varchar('name', { length: 1024 }).notNull(),
  url: varchar('url', { length: 2048 }).notNull(),
});

Drizzle core features:

The Drizzle products schema in features/stores/product.ts demonstrates more complex features:

import { z } from 'zod';
import {
  varchar,
  pgSchema,
  uuid,
  numeric,
  timestamp,
} from 'drizzle-orm/pg-core';
import { stores } from '@/features/stores/store.js';

export const productSchema = z.object({
  storeId: z.uuidv7(),
  productId: z.uuidv7(),
  name: z.string().min(1).max(1024),
  url: z.url().max(2048),
  currentPrice: z.number().positive().nullable(),
  priceChangePercentage: z.number().nullable(),
  lastUpdated: z.coerce.date().nullable(),
  currency: z.string().length(3),
});

export type Product = z.infer<typeof productSchema>;

const dbSchema = pgSchema('price_tracker');

export const products = dbSchema.table('products', {
  productId: uuid('productid').primaryKey(),
  storeId: uuid('storeid')
    .notNull()
    .references(() => stores.storeId),
  name: varchar('name', { length: 1024 }).notNull(),
  url: varchar('url', { length: 2048 }).notNull(),
  currentPrice: numeric('currentprice', {
    precision: 10,
    scale: 2,
    mode: 'number',
  }),
  priceChangePercentage: numeric('pricechangepercentage', {
    precision: 5,
    scale: 2,
    mode: 'number',
  }),
  lastUpdated: timestamp('lastupdated', { mode: 'date' }),
  currency: varchar('currency', { length: 3 }).notNull(),
});

export const priceHistorySchema = z.object({
  productId: z.uuidv7(),
  priceHistoryId: z.uuidv7(),
  timestamp: z.coerce.date(),
  price: z.number().positive(),
});

export type PriceHistory = z.infer<typeof priceHistorySchema>;

export const priceHistories = dbSchema.table('price_histories', {
  priceHistoryId: uuid('pricehistoryid').primaryKey(),
  productId: uuid('productid')
    .notNull()
    .references(() => products.productId),
  timestamp: timestamp('timestamp', { mode: 'date' }).notNull(),
  price: numeric('price', {
    precision: 10,
    scale: 2,
    mode: 'number',
  }).notNull(),
});

Drizzle advanced features:

  • Foreign Keys: .references(() => stores.storeId) creates a relationship.

  • Numeric Types: precision and scale define decimal precision.

  • Timestamp Modes: mode: 'date' returns JavaScript Date objects.

  • Number Modes: mode: 'number' returns a number.

  • Nullable Fields: Fields without .notNull() are nullable by default.

All schemas are exported from the database/schemas.ts file:

export { stores } from '@/features/stores/store.js';
export { products, priceHistories } from '@/features/products/product.js';

This centralized export is imported by the database client, enabling Drizzle to understand relationships between tables.

Migrations

Drizzle Kit generates and manages database migrations automatically from our schema definitions. The migration configuration is defined in the drizzle.config.ts file:

import { ENV } from './src/env.js';
import { defineConfig } from 'drizzle-kit';
export default defineConfig({
  out: './src/database/migrations',
  schema: './src/database/schemas.ts',
  dialect: 'postgresql',
  dbCredentials: {
    url: ENV.DATABASE_URL,
  },
  migrations: {
    schema: 'price_tracker',
  },
});

The configuration options are:

  • out: Directory where migration files are generated.

  • schema: Path to our schema definitions.

  • dialect: Database type (postgresql, mysql, sqlite).

  • dbCredentials: Connection information.

  • migrations.schema: PostgreSQL schema name for migrations table.

The package.json file includes scripts for migration management:

{
  ...
  "scripts": {
    ...
    "database:generate": "tsx ./node_modules/drizzle-kit/bin.cjs generate",
    "database:migrate": "tsx ./node_modules/drizzle-kit/bin.cjs migrate",
    "database:studio": "tsx ./node_modules/drizzle-kit/bin.cjs studio"
  },
...
}

Drizzle Kit cannot properly handle path aliases or file extensions (in certain TypeScript configurations) because it uses esbuild-register. This library has limited module resolution capabilities compared to the full TypeScript compiler. Due to this issue, we use tsx to run Drizzle-kit instead of the regular command drizzle-kit generate and drizzle-kit migrate.

The workflow to use Drizzle-kit could be something like:

  • Define or modify schemas in our TypeScript files.

  • Generate migration: npm run database:generate.

  • Review the SQL in the generated migration file.

  • Apply migration: npm run database:migrate.

drizzle-kit studio command spins up a server for Drizzle Studio(SQL database explorer) hosted on https://local.drizzle.studio

Generated migrations are stored in the database/migrations folders. Drizzle Kit also maintains metadata in the database/migrations/meta folder to track migration history and generate incremental changes.

Querying with Drizzle

In Drizzle ORM, we have two query styles.

SQL-like queries

These are explicit, low-level, SQL-shaped queries. They map very closely to real SQL and give us full control over joins, filters, grouping, and projections.

const result = await db
  .select({
    userId: users.id,
    userName: users.name,
    postTitle: posts.title,
  })
  .from(users)
  .leftJoin(posts, eq(posts.userId, users.id))
  .where(eq(users.active, true));

I want to write SQL, but safely.

Relational queries

Relational queries are higher-level and schema-aware. We define relations once in our schema, and Drizzle automatically figures out what to do.

const result = await db.query.users.findMany({
  where: (users, { eq }) => eq(users.active, true),
  with: {
    posts: {
      columns: {
        id: true,
        title: true,
      },
    },
  },
});

I want objects. Drizzle figures out the joins.

Let's see how to integrate Drizzle in our application using the low-level API.

Insert Statement

Update the file features/stores/add-store.ts file with the following content:

import { Hono } from 'hono';
import { v7 } from 'uuid';
import { StatusCodes } from 'http-status-codes';
import { stores, storeSchema } from './store.js';
import { zValidator } from '@/utils/validation.js';
import { client } from '@/database/client.js';

const schema = storeSchema.omit({ storeId: true });

export const addRoute = new Hono().post(
  '/',
  zValidator('json', schema),
  async c => {
    const data = c.req.valid('json');
    const [store] = await client
      .insert(stores)
      .values({ ...data, storeId: v7() })
      .returning();
    return c.json(store, StatusCodes.CREATED);
  }
);

  • Insert Operation: The .insert(stores) method initiates an insert query on the stores table.

  • Values Method: .values() accepts an object matching the table schema, providing type safety.

  • UUID Generation: Uses UUID v7 for time-sortable unique identifiers.

  • Returning Clause: .returning() is a PostgreSQL feature that returns the inserted record, eliminating the need for a separate SELECT query.

  • Type Safety: TypeScript ensures that the data object contains all required fields with correct types.

Select Statement

Update the file feature/stores/get-store.ts file with the following content:

import { Hono } from 'hono';
import { stores, storeSchema } from './store.js';
import { StatusCodes } from 'http-status-codes';
import { zValidator } from '@/utils/validation.js';
import { createResourceNotFoundPD } from '@/utils/problem-document.js';
import { client } from '@/database/client.js';
import { eq } from 'drizzle-orm';

const schema = storeSchema.pick({ storeId: true });

export const getRoute = new Hono().get(
  '/:storeId',
  zValidator('param', schema),
  async c => {
    const { storeId } = c.req.valid('param');
    const [store] = await client
      .select()
      .from(stores)
      .where(eq(stores.storeId, storeId))
      .limit(1);
    if (!store) {
      return c.json(
        createResourceNotFoundPD(c.req.path, `Store ${storeId} not found`),
        StatusCodes.NOT_FOUND
      );
    }
    return c.json(store, StatusCodes.OK);
  }
);

  • Selective Operation: .select() without arguments retrieves all columns, but Drizzle supports selective column retrieval.

  • Primary Key Lookup: Using eq() on the primary key ensures an index is used, providing O(1) lookup performance.

  • Limit Clause: .limit(1) tells the database to stop scanning after finding the first match.

  • Null Handling: The query returns an array; checking if (!store) handles the not-found case gracefully.

Update the features/stores/list-stores.ts file with the following content:

import { Hono } from 'hono';
import { stores } from './store.js';
import { StatusCodes } from 'http-status-codes';
import { paginationSchema, createPage } from '@/types/pagination.js';
import { z } from 'zod';
import { zValidator } from '@/utils/validation.js';
import { client } from '@/database/client.js';
import { like, count, SQL, and } from 'drizzle-orm';

const schema = paginationSchema.extend({
  name: z.string().optional(),
});

export const listRoute = new Hono().get(
  '/',
  zValidator('query', schema),
  async c => {
    const { pageNumber, pageSize, name } = c.req.valid('query');
    const filters: SQL[] = [];
    const offset = (pageNumber - 1) * pageSize;
    if (name) filters.push(like(stores.name, `%${name}%`));

    const [{ totalCount }] = await client
      .select({ totalCount: count() })
      .from(stores)
      .where(and(...filters));

    const items = await client
      .select()
      .from(stores)
      .where(and(...filters))
      .limit(pageSize)
      .offset(offset);
    return c.json(
      createPage(items, totalCount, pageNumber, pageSize),
      StatusCodes.OK
    );
  }
);

  • Aggregate Functions: count() provides SQL COUNT functionality with type safety.

  • LIKE Operator: The like() function enables pattern matching for text search.

  • Filtering: Building a filter array allows conditional query construction.

  • Logical Operators: and(...filters) combines multiple conditions with AND logic.

  • Pagination: .limit() and .offset() implement cursor-based pagination.

  • Two-Query Pattern: Separate queries for count and data retrieval ensure accurate pagination metadata.

Update Statement

Navigate to the features/stores/edit-store.ts file and update the content with:

import { Hono } from 'hono';
import { StatusCodes } from 'http-status-codes';
import { stores, storeSchema } from './store.js';
import { zValidator } from '@/utils/validation.js';
import { createResourceNotFoundPD } from '@/utils/problem-document.js';
import { client } from '@/database/client.js';
import { eq } from 'drizzle-orm';

const paramSchema = storeSchema.pick({ storeId: true });
const bodySchema = storeSchema.pick({ name: true, url: true });

export const editRoute = new Hono().put(
  '/:storeId',
  zValidator('param', paramSchema),
  zValidator('json', bodySchema),
  async c => {
    const { storeId } = c.req.valid('param');
    const data = c.req.valid('json');
    const existing = await client
      .select()
      .from(stores)
      .where(eq(stores.storeId, storeId))
      .limit(1);

    if (existing.length === 0) {
      return c.json(
        createResourceNotFoundPD(c.req.path, `Store ${storeId} not found`),
        StatusCodes.NOT_FOUND
      );
    }
    const [store] = await client
      .update(stores)
      .set(data)
      .where(eq(stores.storeId, storeId))
      .returning();
    return c.json(store, StatusCodes.OK);
  }
);

  • Select Query: .select().from(stores) creates a SELECT statement with full type inference.

  • Where Clauses: The eq() operator provides type-safe equality comparisons.

  • Existence Check: Queries the database to verify the resource exists before attempting updates.

  • Update Operation: .update(stores).set(data) initiates an update query.

  • Conditional Updates: The .where() clause ensures only the specific record is modified.

This endpoint follows a check-then-update pattern. While this involves two database round-trips, it provides better error handling and user feedback.

Drizzle ORM provides a type-safe, lightweight solution for database operations in Hono applications. Its SQL-like API, excellent TypeScript integration, and minimal runtime overhead make it ideal for modern web applications. You can find all the code here. Thanks, and happy coding.

#nodejs#hono#drizzleorm#postgresql
28 views

NodeJs

Part 4 of 19
Up next

Hono: Error Handling and Security

Building production-ready APIs requires robust error handling and security mechanisms. This article explores how to implement comprehensive error handling using the RFC 7807 Problem Details standard and secure our Hono application using built-in midd...

More from this blog

raulnq

170 posts

Somebody who likes to code