Raw SQL report

Use raw SQL for reporting and dashboards where the query is easier to express directly.

import { backend } from "@layeron/core"
import { db, table, text, integer, rawSql } from "@layeron/modules"

const app = backend()

const database = db({
  name: "main",
  schema: {
    posts: table({
      id: text().primaryKey(),
      authorId: text().notNull().index(),
      title: text().notNull(),
      createdAt: integer().notNull().default(rawSql("(unixepoch())")),
    }),
  },
})

app.use(database)

app.get("/reports/posts-by-author", async () => {
  const { results } = await database.sql<{
    authorId: string
    postCount: number
  }>(
    `
      select authorId, count(*) as postCount
      from posts
      group by authorId
      order by postCount desc
    `,
  ).all()

  return Response.json({ results })
})