Guide
Database
Configure PostgreSQL with Drizzle ORM
UNuxt uses PostgreSQL with Drizzle ORM for type-safe database operations.
Configure the database connection
Set your database URL in the environment:
.env
DATABASE_URL=postgresql://user:password@localhost:5432/unuxt
Define your schema
Create Drizzle schemas in the db package:
packages/db/src/schema/users.ts
import { pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core'
export const users = pgTable('users', {
id: uuid('id').primaryKey().defaultRandom(),
email: text('email').notNull().unique(),
name: text('name'),
image: text('image'),
createdAt: timestamp('created_at').defaultNow(),
updatedAt: timestamp('updated_at').defaultNow()
})
Run database migrations
Use the built-in scripts to manage your database:
# Generate migration files from schema changes
pnpm db:generate
# Apply migrations to the database
pnpm db:migrate
# Push schema changes directly (development)
pnpm db:push
# Open Drizzle Studio for database inspection
pnpm db:studio
Query the database
Use Drizzle's query builder for type-safe queries:
server/api/users/index.get.ts
import { db } from '@unuxt/db'
import { users } from '@unuxt/db/schema'
import { eq } from 'drizzle-orm'
export default defineEventHandler(async (event) => {
const allUsers = await db.select().from(users)
return allUsers
})
Create records
Insert new records with full type safety:
server/api/users/index.post.ts
import { db } from '@unuxt/db'
import { users } from '@unuxt/db/schema'
export default defineEventHandler(async (event) => {
const body = await readBody(event)
const [newUser] = await db.insert(users).values({
email: body.email,
name: body.name
}).returning()
return newUser
})
Update records
Update existing records:
server/api/users/[id].patch.ts
import { db } from '@unuxt/db'
import { users } from '@unuxt/db/schema'
import { eq } from 'drizzle-orm'
export default defineEventHandler(async (event) => {
const id = getRouterParam(event, 'id')
const body = await readBody(event)
const [updated] = await db
.update(users)
.set({ name: body.name, updatedAt: new Date() })
.where(eq(users.id, id))
.returning()
return updated
})
Handle relationships
Define relationships between tables:
packages/db/src/schema/posts.ts
import { pgTable, text, timestamp, uuid } from 'drizzle-orm/pg-core'
import { users } from './users'
export const posts = pgTable('posts', {
id: uuid('id').primaryKey().defaultRandom(),
title: text('title').notNull(),
content: text('content'),
authorId: uuid('author_id').references(() => users.id),
createdAt: timestamp('created_at').defaultNow()
})
Query with relations using Drizzle's relational queries:
server/api/posts/index.get.ts
import { db } from '@unuxt/db'
export default defineEventHandler(async () => {
const posts = await db.query.posts.findMany({
with: {
author: true
}
})
return posts
})