Querying and CRUD

Morphis gives you two layers for database access:

use Model methods for normal application CRUD
use the underlying Drizzle instance for more custom queries when the built-in model helpers are not enough

For most projects, put query logic in a service and let the controller call the service.

Example filenames

Use a structure like this:

src/models/Post.ts
src/services/PostService.ts
src/controllers/PostController.ts

1. Model file

src/models/Post.ts

import { Model } from 'morphis'
import type { ConnectionName } from '../config/database'
 
export class Post extends Model {
  static connection: ConnectionName = 'default'
  static tableName = 'posts'
 
  declare id: number
  declare content: string
  declare createdAt: string
}

This model points to the posts table. Because Morphis normalizes application keys, your code can use createdAt while the database column stays created_at.

2. Query and CRUD in a service

src/services/PostService.ts

import { eq } from 'drizzle-orm'
import { NotFoundError } from 'morphis'
import { Post } from '../models/Post'
 
export class PostService {
  async list(limit = 20, offset = 0) {
    return Post.findAll({ limit, offset })
  }
 
  async get(id: number) {
    const post = await Post.findByPk(id)
    if (!post) {
      throw new NotFoundError('Post not found')
    }
    return post
  }
 
  async create(input: { content: string }) {
    return Post.create(input)
  }
 
  async update(id: number, input: { content: string }) {
    const post = await this.get(id)
    await post.update(input)
    return post
  }
 
  async remove(id: number) {
    const post = await this.get(id)
    await post.destroy()
    return post
  }
 
  async findWithDrizzle(id: number) {
    await Post.initialize()
    const query = await Post.find()
    const rows = await query.where(eq(Post.table.id, id)).limit(1)
    return rows[0] ?? null
  }
}
 
export const postService = new PostService()

What each method is doing

Post.findAll() reads many rows
Post.findByPk() reads one row by primary key
Post.create() inserts one row
post.update() updates the current row instance
post.destroy() deletes the current row instance
Post.find() gives you a raw Drizzle query builder for advanced filtering and joins

3. Controller calling the service

src/controllers/PostController.ts

import {
  Controller,
  Delete,
  Get,
  NotFoundError,
  Post as HttpPost,
  Put,
  Request,
} from 'morphis'
import { postService } from '../services/PostService'
 
@Controller('posts')
export class PostController {
  @Get()
  async list(req: Request) {
    const limit = Number(req.query.limit ?? 20)
    const offset = Number(req.query.offset ?? 0)
    return postService.list(limit, offset)
  }
 
  @Get(':id')
  async get(req: Request) {
    const id = Number(req.params.id)
    if (!Number.isFinite(id)) {
      throw new NotFoundError('Post not found')
    }
    return postService.get(id)
  }
 
  @HttpPost()
  async create(req: Request) {
    return postService.create(req.body as { content: string })
  }
 
  @Put(':id')
  async update(req: Request) {
    return postService.update(
      Number(req.params.id),
      req.body as { content: string },
    )
  }
 
  @Delete(':id')
  async remove(req: Request) {
    return postService.remove(Number(req.params.id))
  }
}

This keeps the controller thin. The controller handles HTTP input and output, while the service owns the database logic.

4. Transaction example in a service

Morphis supports explicit transactions directly on the model layer.

src/services/PostService.ts

import { ConnectionManager, NotFoundError } from 'morphis'
import { AuditLog } from '../models/AuditLog'
import { Post } from '../models/Post'
 
export class PostService {
  async createThenRewrite(content: string) {
    const transaction = await ConnectionManager.getTransaction()
 
    try {
      const created = await Post.create({ content }, transaction)
      if (!created) {
        throw new NotFoundError('Post was not created')
      }
 
      const updated = await Post.update(
        { content: `${created.content} (rewritten)` },
        { where: { id: created.id }, transaction },
      )
 
      await AuditLog.create(
        { message: `rewrote post ${created.id}` },
        { transaction },
      )
 
      await transaction.commit()
 
      return updated[0] ?? created
    } catch (error) {
      await transaction.rollback()
      throw error
    }
  }
 
  async previewDrafts() {
    const transaction = await ConnectionManager.getTransaction()
 
    try {
      await Post.create({ content: 'draft' }, { transaction })
 
      const rows = await Post.findAll({ transaction })
      await transaction.commit()
 
      return rows
    } catch (error) {
      await transaction.rollback()
      throw error
    }
  }
}

Transaction notes

call await ConnectionManager.getTransaction() to get a transaction on the default connection
pass a connection name such as await ConnectionManager.getTransaction('analytics') when you need a non-default database
the same transaction can be reused across multiple Morphis models and tables on that same connection
using that transaction with a model on a different connection throws an error
finish every transaction with await transaction.commit() or await transaction.rollback()
you can pass the transaction directly, for example Post.create(data, transaction)
or pass it inside options, for example Post.findAll({ where: { id: 1 }, transaction })
instance methods also support transactions, for example await post.update(data, transaction) and await post.destroy({ transaction })
find, findAll, findOne, findByPk, create, bulkCreate, update, destroy, count, findAndCountAll, and upsert all accept transactions
explicit transactions are not supported for Cloudflare D1 bindings; local D1 via SQLite storage is supported

5. When to use which layer

Use model methods when:

you are doing straightforward CRUD
you want explicit transaction control with Morphis models
you want the Morphis key normalization between camelCase and snake_case
you want simple, readable service code

Use raw Drizzle access when:

you need joins or more custom SQL composition
you want direct access to the query builder beyond the built-in model helpers