Query · NuxtHub

Now that you have your database schema and migrations set up, you can start querying your database.

The hub:db module provides access to the database through a Drizzle ORM instance.

import { db } from 'hub:db'

db is auto-imported on server-side, you can directly use it without importing it from hub:db.

SQL Select

server/api/users.get.ts

import { db, schema } from 'hub:db'

export default eventHandler(async (event) => {
  return await db.query.users.findMany()
  // or
  return await db.select().from(schema.users)
})

Learn more about Drizzle ORM select on the Drizzle documentation.

SQL Insert

server/api/users.post.ts

import { db, schema } from 'hub:db'

export default eventHandler(async (event) => {
  const { name, email } = await readBody(event)

  return await db
    .insert(schema.users)
    .values({
      name,
      email,
      createdAt: new Date()
    })
    .returning()
})

Learn more about Drizzle ORM insert on the Drizzle documentation.

SQL Update

server/api/users/[id].patch.ts

import { db, schema } from 'hub:db'

export default eventHandler(async (event) => {
  const { id } = getRouterParams(event)
  const { name } = await readBody(event)

  return await db
    .update(schema.users)
    .set({ name })
    .where(eq(tables.users.id, Number(id)))
    .returning()
})

Learn more about Drizzle ORM update on the Drizzle documentation.

SQL Delete

server/api/users/[id].delete.ts

import { db, schema } from 'hub:db'

export default eventHandler(async (event) => {
  const { id } = getRouterParams(event)

  const deletedUser = await db
    .delete(schema.users)
    .where(eq(schema.users.id, Number(id)))
    .returning()

  if (!deletedUser) {
    throw createError({
      statusCode: 404,
      message: 'User not found'
    })
  }

  return { deleted: true }
})

Learn more about Drizzle ORM delete on the Drizzle documentation.