# Lockness Database & Models

Type-safe PostgreSQL with Drizzle ORM.

## Define Model

```typescript
// src/model/user.ts
import { 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(),
    name: text('name').notNull(),
    email: text('email').notNull().unique(),
    password: text('password').notNull(),
    createdAt: timestamp('created_at').defaultNow(),
    updatedAt: timestamp('updated_at').defaultNow(),
})

// Auto-generate Zod schemas
export const insertUserSchema = createInsertSchema(users, {
    email: z.string().email(),
    password: z.string().min(8),
})
export const selectUserSchema = createSelectSchema(users)

export type User = typeof users.$inferSelect
export type NewUser = typeof users.$inferInsert
```

## Repository Pattern

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

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

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

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

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

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

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

## CLI Commands

```bash
# Create model
deno task cli make:model Post           # Just model
deno task cli make:model Post -r        # + Repository
deno task cli make:model Post -s        # + Seeder
deno task cli make:model Post -c        # + Controller
deno task cli make:model Post -a        # All of above

# Database operations
deno task cli db:generate               # Generate migrations
deno task cli db:migrate                # Run migrations
deno task cli db:push                   # Push schema changes
deno task cli db:seed                   # Run seeders
deno task cli db:studio                 # Open Drizzle Studio
deno task cli db:fresh                  # Drop all & remigrate
```

## Configuration

```typescript
// drizzle.config.ts
import { defineConfig } from 'drizzle-kit'

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

```env
# .env
DATABASE_URL=postgresql://user:pass@localhost:5432/db
```