Lockness Drizzle | Lockness Documentation

Lockness Drizzle

LLM DOCS:VIEW

Drizzle ORM integration for Lockness with PostgreSQL support, migration management, and CLI scaffolding.

Overview

@lockness/drizzle provides:

PostgreSQL Integration - Full Drizzle ORM support
Migration Management - Generate and apply migrations
Database Seeding - Populate development/test data
Drizzle Studio - Visual database browser
CLI Scaffolding - Generate models, repositories, controllers
Dependency Injection - Auto-injectable Database service
Type Safety - Full TypeScript support with inference

Installation

Quick Install (Recommended)

bash

deno task cli package:install drizzle

This automatically:

Creates drizzle.config.ts
Sets up directory structure (migrations/, app/model/, etc.)
Creates database_seeder.ts
Adds DATABASE_URL to .env files
Tests database connection

Manual Installation

bash

deno add @lockness/drizzle

Then configure manually (see Configuration).

Configuration

Environment Variables

Add to .env:

env

DATABASE_URL=postgres://user:password@localhost:5432/mydb

Drizzle Config

drizzle.config.ts (auto-generated by installer):

typescript

import { defineConfig } from 'drizzle-kit'

export default defineConfig({
    schema: './app/model/*.ts',
    out: './database/migrations',
    dialect: 'postgresql',
    dbCredentials: {
        url: Deno.env.get('DATABASE_URL')!,
    },
})

Basic Usage

Database Service

typescript

import { Inject, Service } from '@lockness/core'
import { Database } from '@lockness/drizzle'

@Service()
export class UserService {
    @Inject(Database)
    accessor db!: Database

    async getAllUsers() {
        return await this.db.instance.select().from(users)
    }
}

In Controllers

typescript

import { Context, Controller, Get, Inject } from '@lockness/core'
import { Database } from '@lockness/drizzle'
import { users } from '../model/user.ts'

@Controller('/users')
export class UserController {
    @Inject(Database)
    accessor db!: Database

    @Get('/')
    async index(c: Context) {
        const allUsers = await this.db.instance.select().from(users)
        return c.json({ users: allUsers })
    }
}

Model Definition

Define database schema with Drizzle:

typescript

// app/model/user.ts
import { boolean, pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core'
import { createInsertSchema, createSelectSchema } from 'drizzle-zod'
import { z } from 'zod'

export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    email: text('email').notNull().unique(),
    name: text('name').notNull(),
    password: text('password').notNull(),
    emailVerified: boolean('email_verified').default(false),
    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').defaultNow(),
})

// Auto-generated Zod schemas from Drizzle
export const selectUserSchema = createSelectSchema(users)
export const insertUserSchema = createInsertSchema(users, {
    email: z.string().email(),
    name: z.string().min(2).max(100),
    password: z.string().min(8),
})

// TypeScript types
export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert

Relationships

Define relations between tables:

typescript

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

export const users = pgTable('users', {
    id: serial('id').primaryKey(),
    name: text('name').notNull(),
})

export const posts = pgTable('posts', {
    id: serial('id').primaryKey(),
    title: text('title').notNull(),
    content: text('content'),
    authorId: integer('author_id').references(() => users.id),
})

// Define relationships
export const usersRelations = relations(users, ({ many }) => ({
    posts: many(posts),
}))

export const postsRelations = relations(posts, ({ one }) => ({
    author: one(users, {
        fields: [posts.authorId],
        references: [users.id],
    }),
}))

Query with relations:

typescript

// Get users with their posts
const usersWithPosts = await db.instance.query.users.findMany({
    with: {
        posts: true,
    },
})

// Get post with author
const postWithAuthor = await db.instance.query.posts.findFirst({
    where: eq(posts.id, 1),
    with: {
        author: true,
    },
})

Repository Pattern

Create repositories for clean data access:

typescript

// app/repository/user_repository.ts
import { Inject, Service } from '@lockness/core'
import { Database } from '@lockness/drizzle'
import { eq } from 'drizzle-orm'
import { type NewUser, type User, users } from '../model/user.ts'

@Service()
export class UserRepository {
    @Inject(Database)
    accessor db!: Database

    async findAll(): Promise<User[]> {
        return await this.db.instance.select().from(users)
    }

    async findById(id: number): Promise<User | undefined> {
        const result = await this.db.instance
            .select()
            .from(users)
            .where(eq(users.id, id))
        return result[0]
    }

    async findByEmail(email: string): Promise<User | undefined> {
        const result = await this.db.instance
            .select()
            .from(users)
            .where(eq(users.email, email))
        return result[0]
    }

    async create(data: NewUser): Promise<User> {
        const result = await this.db.instance
            .insert(users)
            .values(data)
            .returning()
        return result[0]
    }

    async update(id: number, data: Partial<NewUser>): Promise<User> {
        const result = await this.db.instance
            .update(users)
            .set({ ...data, updatedAt: new Date() })
            .where(eq(users.id, id))
            .returning()
        return result[0]
    }

    async delete(id: number): Promise<void> {
        await this.db.instance.delete(users).where(eq(users.id, id))
    }
}

CLI Commands

Model Scaffolding

Generate complete model with all files:

bash

deno task cli make:model Post -a

Generates:

app/model/post.ts - Schema and types
app/repository/post_repository.ts - Repository
app/controller/post_controller.ts - Controller
database/seeders/post_seeder.ts - Seeder

Flags:

-r, --repository - Generate repository only
-s, --seeder - Generate seeder only
-c, --controller - Generate controller only
-a, --all - Generate everything

Migration Commands

bash

# Generate migration from schema changes
deno task cli db:generate

# Apply pending migrations
deno task cli db:migrate

# Check migration status
deno task cli db:status

# Drop all tables and re-migrate
deno task cli db:fresh

# Push schema without migrations (dev only)
deno task cli db:push

Database Commands

bash

# Test database connection
deno task cli db:check

# Run seeders
deno task cli db:seed

# Launch Drizzle Studio
dx drizzle-kit studio

Advanced Queries

Filtering

typescript

import { and, eq, gt, gte, ilike, like, lt, lte, ne, or } from 'drizzle-orm'

// Single condition
const activeUsers = await db.instance
    .select()
    .from(users)
    .where(eq(users.emailVerified, true))

// Multiple conditions (AND)
const recentActiveUsers = await db.instance
    .select()
    .from(users)
    .where(and(
        eq(users.emailVerified, true),
        gt(users.createdAt, new Date('2024-01-01')),
    ))

// OR conditions
const specialUsers = await db.instance
    .select()
    .from(users)
    .where(or(
        eq(users.email, 'admin@example.com'),
        eq(users.role, 'admin'),
    ))

// Pattern matching
const searchResults = await db.instance
    .select()
    .from(users)
    .where(ilike(users.name, `%${query}%`))

Sorting and Pagination

typescript

import { asc, desc } from 'drizzle-orm'

// Sort by creation date (newest first)
const users = await db.instance
    .select()
    .from(users)
    .orderBy(desc(users.createdAt))

// Pagination
const page = 2
const perPage = 20
const paginatedUsers = await db.instance
    .select()
    .from(users)
    .limit(perPage)
    .offset((page - 1) * perPage)

Joins

typescript

// Left join
const postsWithAuthors = await db.instance
    .select({
        postId: posts.id,
        postTitle: posts.title,
        authorName: users.name,
        authorEmail: users.email,
    })
    .from(posts)
    .leftJoin(users, eq(posts.authorId, users.id))

// Inner join
const publishedPostsWithAuthors = await db.instance
    .select()
    .from(posts)
    .innerJoin(users, eq(posts.authorId, users.id))
    .where(eq(posts.published, true))

Aggregations

typescript

import { avg, count, max, min, sum } from 'drizzle-orm'

// Count users
const [{ value: userCount }] = await db.instance
    .select({ value: count() })
    .from(users)

// Group by and count
const postsByAuthor = await db.instance
    .select({
        authorId: posts.authorId,
        postCount: count(),
    })
    .from(posts)
    .groupBy(posts.authorId)

Transactions

Execute multiple operations atomically:

typescript

await db.instance.transaction(async (tx) => {
    // Create user
    const [user] = await tx.insert(users).values({
        email: 'new@example.com',
        name: 'New User',
        password: hashedPassword,
    }).returning()

    // Create first post
    await tx.insert(posts).values({
        title: 'Hello World',
        authorId: user.id,
    })

    // If any operation fails, all are rolled back
})

Database Seeding

Create seed data for development:

typescript

// database/seeders/user_seeder.ts
import { Database } from '@lockness/drizzle'
import { users } from '../../app/model/user.ts'
import * as bcrypt from 'bcrypt'

export class UserSeeder {
    async run(db: Database) {
        const hashedPassword = await bcrypt.hash('password123', 10)

        await db.instance.insert(users).values([
            {
                email: 'alice@example.com',
                name: 'Alice Smith',
                password: hashedPassword,
                emailVerified: true,
            },
            {
                email: 'bob@example.com',
                name: 'Bob Johnson',
                password: hashedPassword,
                emailVerified: true,
            },
        ])
    }
}

typescript

import { UserSeeder } from './user_seeder.ts'
import { PostSeeder } from './post_seeder.ts'

export const seeders = [
    UserSeeder,
    PostSeeder,
]

Run seeders:

bash

deno task cli db:seed

Drizzle Studio

Visual database browser:

bash

dx drizzle-kit studio

Features:

Browse and edit tables
Run SQL queries
View relationships
Manage data visually
Real-time updates

Migration Workflow

Modify schema in app/model/*.ts
Generate migration:
bash
```
deno task cli db:generate
```
Review migration in database/migrations/
Apply migration:
bash
```
deno task cli db:migrate
```
Commit migration to version control

Best Practices

Use repositories for data access (don't query directly in controllers)
Define Zod schemas with drizzle-zod for validation
Use transactions for related operations
Create migrations for all schema changes (never use db:push in production)
Use seeders for test/development data
Leverage TypeScript inference from Drizzle schemas
Add indexes for frequently queried columns
Use relations for cleaner queries with joins
Validate input with Zod before inserting/updating
Handle unique constraint violations gracefully

Common Patterns

Soft Deletes

typescript

export const posts = pgTable('posts', {
    id: serial('id').primaryKey(),
    title: text('title').notNull(),
    deletedAt: timestamp('deleted_at'),
})

// Soft delete
await db.instance
    .update(posts)
    .set({ deletedAt: new Date() })
    .where(eq(posts.id, id))

// Query non-deleted
const activePosts = await db.instance
    .select()
    .from(posts)
    .where(isNull(posts.deletedAt))

Timestamps

typescript

export const posts = pgTable('posts', {
    id: serial('id').primaryKey(),
    title: text('title').notNull(),
    createdAt: timestamp('created_at').defaultNow().notNull(),
    updatedAt: timestamp('updated_at').defaultNow().notNull(),
})

// Update with timestamp
await db.instance
    .update(posts)
    .set({
        title: 'New Title',
        updatedAt: new Date(),
    })
    .where(eq(posts.id, id))

UUID Primary Keys

typescript

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

export const users = pgTable('users', {
    id: uuid('id').defaultRandom().primaryKey(),
    email: text('email').notNull().unique(),
    name: text('name').notNull(),
})

Complete Example

typescript

// app/model/post.ts
import {
    boolean,
    integer,
    pgTable,
    serial,
    text,
    timestamp,
} from 'drizzle-orm/pg-core'
import { relations } from 'drizzle-orm'
import { users } from './user.ts'

export const posts = pgTable('posts', {
    id: serial('id').primaryKey(),
    title: text('title').notNull(),
    content: text('content'),
    published: boolean('published').default(false),
    authorId: integer('author_id').references(() => users.id),
    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').defaultNow(),
})

export const postsRelations = relations(posts, ({ one }) => ({
    author: one(users, {
        fields: [posts.authorId],
        references: [users.id],
    }),
}))

export type Post = typeof posts.$inferSelect
export type NewPost = typeof posts.$inferInsert

// app/repository/post_repository.ts
import { Inject, Service } from '@lockness/core'
import { Database } from '@lockness/drizzle'
import { eq } from 'drizzle-orm'
import { type NewPost, type Post, posts } from '../model/post.ts'

@Service()
export class PostRepository {
    @Inject(Database)
    accessor db!: Database

    async findPublished(): Promise<Post[]> {
        return await this.db.instance
            .select()
            .from(posts)
            .where(eq(posts.published, true))
    }

    async findByIdWithAuthor(id: number) {
        return await this.db.instance.query.posts.findFirst({
            where: eq(posts.id, id),
            with: { author: true },
        })
    }

    async create(data: NewPost): Promise<Post> {
        const [post] = await this.db.instance
            .insert(posts)
            .values(data)
            .returning()
        return post
    }
}

// app/controller/post_controller.ts
import { Context, Controller, Get, Inject } from '@lockness/core'
import { PostRepository } from '../repository/post_repository.ts'

@Controller('/posts')
export class PostController {
    @Inject(PostRepository)
    accessor postRepo!: PostRepository

    @Get('/')
    async index(c: Context) {
        const posts = await this.postRepo.findPublished()
        return c.json({ posts })
    }

    @Get('/:id')
    async show(c: Context) {
        const id = Number(c.req.param('id'))
        const post = await this.postRepo.findByIdWithAuthor(id)

        if (!post) {
            return c.json({ error: 'Not found' }, 404)
        }

        return c.json({ post })
    }
}

Dependencies

drizzle-orm - ORM library
drizzle-kit - CLI tools
drizzle-zod - Zod schema generation
postgres - PostgreSQL client

Edit this page