# Lockness Drizzle

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,
            },
        ])
    }
}
```

Register in `database/seeders/database_seeder.ts`:

```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

1. **Modify schema** in `app/model/*.ts`

2. **Generate migration:**
   ```bash
   deno task cli db:generate
   ```

3. **Review migration** in `database/migrations/`

4. **Apply migration:**
   ```bash
   deno task cli db:migrate
   ```

5. **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