db

Once your database connection is configured, you can query it directly with Kysely, Dream's underlying query builder, instead of writing raw SQL, with TypeScript autocomplete at a low level. A new Psychic app ships a db helper in src/db/index.ts that returns the Kysely instance for a connection, typed against your app's DB types:

// src/app/services/BurpCollaboratorUsers.ts
import db from '#src/db/index.js'

export default class BurpCollaboratorUsers {
  public static async all() {
    return await db()
      .selectFrom('users')
      .selectAll()
      .where('email', 'ilike', '%burpcollaborator%')
      .execute()
  }
}

db() takes the connection type as its first argument, defaulting to 'primary'. Pass 'replica' to read from the replica; when the connection has no replica configured, Dream falls back to the primary credentials:

// src/app/services/ReplicaUsers.ts
import db from '#src/db/index.js'

export default class ReplicaUsers {
  public static async all() {
    return await db('replica').selectFrom('users').selectAll().execute()
  }
}

You can also turn any query built in Dream into a Kysely query by calling toKysely, passing 'select', 'update' or 'delete'. A 'select' builder already selects every column of the model's table and carries the Dream query's conditions, including default scopes such as soft-delete filtering:

// src/app/services/BurpCollaboratorUsersViaDream.ts
import User from '#models/User.js'
import { ops } from '@rvoh/dream'

export default class BurpCollaboratorUsersViaDream {
  public static async all() {
    return await User.where({ email: ops.ilike('%burpcollaborator%') })
      .toKysely('select')
      .execute()
  }
}