Automatic CRUD endpoints with automatic creation of zod validators and typing from Drizzle-orm schema (drizzle-orm/pg-core)

[Meess]

As a long time Django (Python MVC framework) dev I've always liked django-admin and DRF (Django Rest Framework) which work very closely with their ORM to manage data and automatically create endpoints and authorization.

Now using hono I really liked the hono/client (type inference from the API to the frontend something that's not possible when using Python atm), but I prefer to not do things manually when they can be automated. As Drizzle provides proper typing (and attributes from which you can derive how the schema looks), I wanted to automatically create validators for a defined schema that are directly reflected in the client typings (no more mismatch between backend and frontend typings, or manual updating).
let
user/schema.ts

import { pgTable, text } from 'drizzle-orm/pg-core'

export const userTable = pgTable('user', {
    id: text('id').primaryKey(),
    username: text('username').unique().notNull(),
    passwordHash: text('passwordHash').notNull(),
})

With two lines of code all crud endpoints can be created, which are Zod validated via zValidator and thus the types of the request body are directly reflected in the client (via hono/client).

// create crud endpoints for the userTable, but leave out 'id' when creating the zod schema. 
const userCRUDEndpoints = createCRUDRoutes('user', userTable, 'id')
const app = new Hono().route('/api/user', userCRUDEndpoints.routes)

// If inspecting the routes (e.g. via `showRoutes(app, { verbose: true,}`) it will show the following output:

// GET     /api/user
//          [handler]
// GET     /api/user/:id
//          [handler]
// POST    /api/user
//          [handler]
// PUT     /api/user/:id
//          [handler]
// DELETE  /api/user/:id
//          [handler]

And when looking in the client:

import { hc } from 'hono/client'
import type { AppType } from '../backend/index'

export const client = hc<AppType>()
// The following will give a type error, exactly as we wanted:
// Argument of type '{}' is not assignable to parameter of type '{ json: { username: string; passwordHash: string; }; }'.
client.api.user.$post({}) // not OK

// Passes type checking:
client.api.user.$post({json:{username:'test', passwordHash: 'x'}}) // OK

It's just a POC to see if we could harness the power of normally tRPC but lightweight with automated validation through zValidator, with hono/client to directly reflect it in the client 💪. Curious what other people's thoughts on it are.

Code that you can simply copy and use, only have to change the db import (side note, now only focussed on postgres):

// crud.ts
import { Hono } from 'hono'
import db from '../../db/db'
import { count, eq } from 'drizzle-orm'
import { type ExtractColumnsType, tableToZodSchema } from './tableToZodSchema'
import { zValidator } from '@hono/zod-validator'
import { PgTableWithColumns } from 'drizzle-orm/pg-core'

// TODO: filter out primary key from schema
// TODO: if column hasDefault: true, make it optional in schema
// TODO: if column notNull: true, make it required in schema
// TODO; exclude is auto increment, for pg it's if columnType: "PgSerial"
// TODO: blacklist - allow exclusion of multiple columns
// TODO: whitelist - allow inclusion of multiple columns

export const createCRUDRoutes = <
    T extends PgTableWithColumns<ExtractColumnsType<T>>,
    U extends string,
    V extends keyof T['_']['columns'],
>(
    name: U,
    table: T,
    exclude: V,
) => {
    const schema = tableToZodSchema(table, exclude)
    const validatorSchema = zValidator('json', schema)
    const router = new Hono()

        // List items with pagination
        .get(`/${name}`, async (c) => {
            const page = parseInt(c.req.query('page') || '10', 10)
            const limit = parseInt(c.req.query('limit') || '10', 10)
            const offset = (page - 1) * limit

            const total = await db.select({ count: count() }).from(table)
            const totalResults = total[0].count
            const result = await db
                .select()
                .from(table)
                .limit(limit)
                .offset(offset)

            return c.json({
                result,
                total,
                page,
                pages: Math.ceil(totalResults / limit),
            })
        })

        // Get single item by ID
        .get(`/${name}/:id`, async (c) => {
            // TODO: find something to get rid of ! and still maintain correct type inference
            const id = c.req.param('id')!
            const item = await db
                .select()
                .from(table)
                .where(eq(table.id, id))
            if (!item) return c.json({ message: 'Item not found' }, 404)
            return c.json(item)
        })

        // Create new item
        .post(`/${name}`, validatorSchema, async (c) => {
            const data = await c.req.json()
            const newItem = await db.insert(table).values(data)
            return c.json(newItem, 201)
        })

        // Update item by ID
        .put(`/${name}/:id`, validatorSchema, async (c) => {
            // TODO: find something to get rid of ! and still maintain correct type inference
            const id = c.req.param('id')!
            const data = await c.req.json()
            await db.update(table).set(data).where(eq(table.id, id))
            const updatedItem = await db
                .select()
                .from(table)
                .where(eq(table.id, id))
            return c.json(updatedItem)
        })

        // Delete item by ID
        .delete(`/${name}/:id`, async (c) => {
            // TODO: find something to get rid of ! and still maintain correct type inference
            const id = c.req.param('id')!
            await db.delete(table).where(eq(table.id, id))
            return c.json({ message: 'Item deleted' })
        })
    return {
        name,
        router,
    }
}

And the part that is not Hono related, but extracts the zod schema's from the Drizzle pg table:

// tabletoZodSchema.ts
import type { PgColumn, PgTableWithColumns } from 'drizzle-orm/pg-core'
import z from 'zod'

const drizzleDataTypeToZodType = {
    string: z.string(),
    number: z.number(),
    boolean: z.boolean(),
    date: z.date(),
    bigint: z.number(),
    // TODO: still need proper z validation type for these ->
    array: z.string(),
    buffer: z.string(),
    json: z.string(),
    custom: z.string(),
}

export const tableToZodSchema = <
    T extends PgTableWithColumns<ExtractColumnsType<T>>,
    U extends keyof T['_']['columns'],
>(
    table: T,
    exclude: U,
) => {
    const columnKeyWithValueDataType = mapColumnsToKeysWithValue(
        table as typeof table._.columns,
        exclude,
    )

    const zodObject = mapKeysToValue(
        columnKeyWithValueDataType,
        drizzleDataTypeToZodType,
    )
    return z.object(zodObject)
}

function mapColumnsToKeysWithValue<
    T extends Record<string, PgColumn>,
    U extends keyof T,
>(columns: T, exclude: U) {
    type resulstType = Omit<KeysWithValue<T>, U>;
    const result = {} as resulstType
    for (const key in columns) {
        // TODO: hacky way to get type of key correct
        const key2 = key as keyof T
        if (key2 === exclude) continue
        
        if (columns.hasOwnProperty(key)) {
            const dataType = columns[key].dataType as ExtractDataType<
                T[typeof key2]
            >
            const tkey = key as keyof resulstType
            result[tkey] = dataType
        }
    }
    return result
}

function mapKeysToValue<T extends Record<string, keyof V>, V extends ValueMap>(
    keysWithValue: T,
    value: V,
) {
    const result = {} as { [K in keyof T]: V[T[K]] }
    for (const key in keysWithValue) {
        if (keysWithValue.hasOwnProperty(key)) {
            const valueType = keysWithValue[key]
            result[key] = value[valueType]
        }
    }
    return result
}

// types
export type ExtractColumnsType<T> =
    T extends PgTableWithColumns<infer U> ? U : never

type ValueMap = { [key: string]: z.ZodTypeAny }

type ExtractDataType<T> = T extends PgColumn<infer U> ? U['dataType'] : never

type KeysWithValue<T> = {
    [K in Extract<keyof T, string>]: ExtractDataType<T[K]>
}

[Meess]

It's better to replace my experimental "tableToZodSchema" with the drizzle extension drizzle-zod, createInsertSchema. i.e.:

import { createInsertSchema } from 'drizzle-zod'

const schema = createInsertSchema(table)

So the whole tableToZodSchema.ts can be skipped

[yusukebe]

Hi @Meess

Interesting! I've also tried to use Drizzle and Zod stack: https://github.com/yusukebe/testing-d1-app-with-types

That was a good experience; it will automatically add the validator and the client types if the database schema is set.

[yusukebe]

With that example, we have to create endpoints ourselves. But it would be great and useful if CRUD is created.

[Meess]

Yes that would indeed be great, the downside of auto generating CRUD is that you've to commit to an ORM (e.g. in this case Drizzle), or make a very complex adapter that can support other ORM's (although that could be quite a maintenance burden). Not sure if Hono wants to go that route or just keep it as guides in the docs with the helper functions in a different library. Curious what the stance on this if for the Hono future, bind itself to some other packages (e.g. ORM) to create a full batteries included framework, or stay with what it does at it's core and let the community develop tools around it (which will be less structured, so harder to have a consistent and cohesive way of working with the framework).

For now I'll keep fiddling with this POC, add error handling, logging, extensibility, ability to do authorization, and see if I found it robust enough to use in production.

[yusukebe]

I am the type of guy who is not happy unless I make it myself :) So, before we(Hono) decide on the route, I'll try to create my PoC, referring you to your PoC! First, I intend to use the below stack as the same as your example is a good choice because a good DX and work on any runtimes:

• Hono
• Zod
• Drizzle

[Meess]

Great to hear, really think that's a road that can fill an empty space in the current Node ecosystem. Keep me updated, curious to follow if you're building something, and what trade-offs you'll make ss your knowledge about Hono is far deeper than mine, probably you can make some way nicer abstractions from which I can learn.

[yusukebe]

By the way, I was not a Node.js user. The interesting thing is that I had never used JavaScript for the backend before starting to create Hono. Though I'm not a Rails or Django programmer, I was using modern "Perl" to create server-side applications (I love the amazing framework "Mojolicious"!). So, my experience working on the classical backend will be affected in the future to fill the space in the current JavaScript world.

[flipvh]

Nice work @Meess, I am interested in trying to help figure out how this can help the community! I am working on a template with hono in it, and also using zod, drizzle, pg. Beyond that we also use zod-openapi and scalar to generate docs like this: https://api.cellajs.com/docs. Cella could benefit from having an easy way to add CRUD endpoints for sure! Would love to know if you would be interested in adapting this for more complex scenarios: so that it's also usable in that way that cella uses hono zod-openapi middleware? And secondly, how to allow for customization of the business logic as you inevitably always want to customize endpoints eventually? Here is a link to one of our endpoints and you see that we also have the ability to add endpoint specific middleware for each route: That said, Maybe I am making it too complicated and larger applications are not the target you want to focus on, but just something to consider perhaps.

[Meess]

This of course just a POC to test if it worked with simple DX. As for an auto generated admin panel you don't want to have the write all endpoints and validation yourself but have it automated and allow you to configure which data is shown, authorization, and who can change what (e.g. like payload-cms but less focussed on next.js and more focussed on extensibility, building blocks etc).

A side-effect of that would be that it's really easy to create your own CRUD endpoint for a table.

In your code you've already made a few abstractions I see, e.g. the authorization middleware and openapi spec etc. It seems very easy to change the "UsersRoutesConfig" into a generic "CrudRoutesConfig" that takes in e.g. a drizzle table and a name, and then you've got your generic class which gives you instant CRUD endpoints.

Although my preference is composition, and therefore not using classes as done in the cella codebase with UsersRoutesConfig, but more a functional approach. Also I wouldn't want a full-blown solution that can mainly be used as full-blown solution, but you should be able to incrementally adopt (like authorization, or openapi spec) it or degrade it while keeping full control. It should really be building blocks that can be clicked together, reused and configured, customised and plugged in again.

I've been building enterprise applications for years, but just quit to see if I can get some of my own ideas up and running quickly and therefore playing around with multiple frameworks to see what's possible in Node land (compared to four years ago). And while Hono is not the full-stack framework like Django, Spring boot or Ruby on Rails yet it does have a nice premise of performance and full type safety through the application, and plays well with a SPA. This allows me to quickly make applications, with great DX, and less prone to errors and boilerplate code. Although I still think the Node frameworks are lacking behind on the "batteries included" part compared to these decade old robust frameworks. But let's see where it goes, I like it so far.

[flipvh]

Hi @Meess I like it. Sounds a bit like Platformatic but for hono?! Let me know if I can help. As you say, cella could perhaps also benefit from some abstraction on CRUD and a bit more composability. We chose not to give it too much focus because you often get a template that still require 4 months of work to get to an 'enterprise level'. Also, imo, user and organization endpoints do not allow for very easy composability due to their prominence in many parts of an app (authorization, authentication, stats, billing etc), hence wanted to focus for cella on better composability especially on the remaining endpoints. For less opinionated fullstack frameworks - and probably more composable - in Node you only have Nest I think? In any case good luck!

PS: I also prefer functional, the classes are just for a more clean way to import them if you have many exports. (perhaps I need to look for another solution for that :D)

[MathurAditya724]

Hey everyone, so for the past few months I was also working on a solution like this in a private repo. I just open-sourced it, you can check it out here - https://github.com/rhinobase/honohub and the package is honohub. This is a cleaned-up version of the one I was working on before. Here is the list of current features -

1. Automatic CRUD API generation using drizzle-orm schema, with limit, offset, and sorting functions too. I will be adding more soon.
2. Automatic zod schema creation and validation
3. Plugins support for both app and collections. I have already created a plugin for graphQL support, you can check it out here - @honohub/graphql

Feature planned, these are things that I am still cleaning up / building and will be adding to the package soon -

1. Hooks support
2. File Storage Plugin (Blob Storage)
3. Auth Plugin
4. Dashboard Plugin
5. Docs

I am planning to finish these by mid-July.

The API is still quite fresh and a lot of trial and error is required to make it a robust lib, but it's a start. Give it a try if you can (I know I have to work on the docs more but there is an example - https://github.com/rhinobase/honohub/blob/main/apps/sandbox/src/index.ts) and let me know what are your thoughts on this and how can we improve it.

[Meess]

🚀 great to hear, was also implementing limit, offset, sorting, error handling etc yesterday. Definitely going to check out your repo soon and see if I can borrow some ideas, or see if I can collaborate on it! Would love to see this evolving into more

[MathurAditya724]

Amazing, is your repo also open source? mind sharing the link? But yeah do let me know if you face any issues or have any ideas. Looking forward to collaborating more on this.

[grmkris]

Nice work. I did similar with trpc and even auto ui generator on tanstack router. But was too hard to have something prod ready. Can share if interested

if you can extend the code to work with hono openapi zod it would be even better