Models & Database

Lockness uses Drizzle ORM for type-safe database operations with PostgreSQL.

Creating Models

Generate a model with all related files:

bash

deno task cli make:model Post -a

The -a flag creates:

app/model/post.ts - Drizzle schema + Zod validation
app/repository/post_repository.ts - Data access layer
app/controller/post_controller.ts - REST API controller
app/seeder/post_seeder.ts - Database seeder

Defining Models

Create a Drizzle table schema:

typescript

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

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

// Validation schemas
export const insertPostSchema = createInsertSchema(posts, {
    title: z.string().min(5).max(200),
    content: z.string().min(10),
})

export const selectPostSchema = createSelectSchema(posts)

// TypeScript types
export type Post = typeof posts.$inferSelect
export type NewPost = typeof posts.$inferInsert

Repositories

Repositories provide a clean data access layer:

typescript

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

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

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

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

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

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

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

Migrations

Generate migrations from your schema:

bash

deno task cli db:generate

This creates migration files in migrations/:

sql

-- migrations/0001_create_posts.sql
CREATE TABLE "posts" (
    "id" serial PRIMARY KEY,
    "title" text NOT NULL,
    "content" text NOT NULL,
    "published" boolean DEFAULT false,
    "created_at" timestamp DEFAULT now(),
    "updated_at" timestamp DEFAULT now()
);

Apply migrations:

bash

deno task cli db:migrate

Seeders

Create seed data for testing:

typescript

// app/seeder/post_seeder.ts
import { Database } from '@lockness/drizzle'
import { posts } from '../model/post.ts'

export class PostSeeder {
    async run(db: Database) {
        await db.instance.insert(posts).values([
            {
                title: 'First Post',
                content: 'This is the content of the first post',
                published: true,
            },
            {
                title: 'Second Post',
                content: 'This is the content of the second post',
                published: false,
            },
        ])
    }
}

Run seeders:

bash

deno task cli db:seed

Relationships

Define relationships between models:

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(),
    authorId: integer('author_id').references(() => users.id),
})

// Define relations
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

const postsWithAuthors = await db.instance.query.posts.findMany({
    with: {
        author: true,
    },
})

Query Building

Use Drizzle's query builder for complex queries:

typescript

import { and, desc, eq, like } from 'drizzle-orm'

// Find published posts by author
const publishedPosts = await db.instance
    .select()
    .from(posts)
    .where(and(
        eq(posts.published, true),
        eq(posts.authorId, userId),
    ))
    .orderBy(desc(posts.createdAt))

// Search posts by title
const searchResults = await db.instance
    .select()
    .from(posts)
    .where(like(posts.title, `%${query}%`))

Drizzle Studio

Launch the visual database browser:

bash

dx drizzle-kit studio

This opens Drizzle Studio at https://local.drizzle.studio where you can:

Browse tables and data
Run queries
Edit records
View relationships
Manage your database visually