Multi-table read

Use raw SQL when one response needs data from more than one table.

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

const app = backend()

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

app.use(database)

app.get("/feed", async () => {
  const { results: posts } = await database.sql<{
    id: string
    title: string
    authorName: string
  }>(
    `
      select posts.id, posts.title, users.name as authorName
      from posts
      join users on users.id = posts.authorId
      order by posts.createdAt desc
      limit ?
    `,
    [20],
  ).all()

  return Response.json({ posts })
})