Schema

Use schema when you want typed rows and generated SQL migrations.

Declare Tables

import {
  db,
  table,
  text,
  integer,
  boolean,
  json,
  rawSql,
} from "@layeron/modules"

const database = db({
  name: "main",
  schema: {
    posts: table({
      id: text().primaryKey(),
      title: text().notNull(),
      body: text().notNull(),
      published: boolean().notNull().default(false),
      metadata: json<Record<string, unknown>>(),
      createdAt: integer().notNull().default(rawSql("(unixepoch())")),
    }),
  },
})

Layeron turns this into create table if not exists ... SQL and makes database.table("posts") typed.

Column Types

Builder	TypeScript value	SQLite storage
text()	string	text
integer()	number	integer
real()	number	real
boolean()	boolean	integer
json<T>()	T	text
blob()	ArrayBuffer	blob

Column Modifiers

table({
  id: text().primaryKey(),
  email: text().notNull().unique(),
  accountId: text().notNull().index(),
  createdAt: integer().notNull().default(rawSql("(unixepoch())")),
  authorId: text().references("users.id"),
})

Supported modifiers:

• primaryKey()
• notNull()
• unique()
• index()
• references("table.column")
• default(value)

Use rawSql(...) for SQLite expressions such as unixepoch() or CURRENT_TIMESTAMP.

Table Indexes

Use index(...) and uniqueIndex(...) for multi-column indexes:

import { index, uniqueIndex } from "@layeron/modules"

const database = db({
  name: "main",
  schema: {
    posts: table(
      {
        id: text().primaryKey(),
        authorId: text().notNull(),
        slug: text().notNull(),
        createdAt: integer().notNull(),
      },
      {
        indexes: [
          index(["authorId", "createdAt"], { name: "posts_author_created_idx" }),
          uniqueIndex(["authorId", "slug"], { name: "posts_author_slug_unique" }),
        ],
      },
    ),
  },
})

Insert and Update Types

Schema controls Table API types:

await database.table("posts").insert({
  id: crypto.randomUUID(),
  title: "Hello",
  body: "Layeron Database",
  published: false,
})

await database.table("posts").update({
  published: true,
}).where({ id })

Required insert fields come from non-null columns without defaults. Columns with defaults and nullable columns are optional.

Schema and SQL Together

You can append custom SQL migrations after the generated schema migration:

const database = db({
  name: "main",
  schema: {
    posts: table({
      id: text().primaryKey(),
      title: text().notNull(),
    }),
  },
  sql: [
    {
      name: "002_add_posts_search_index",
      sql: "create index if not exists posts_title_idx on posts(title);",
    },
  ],
})

Use this when the schema builder handles the common shape and SQL expresses a specialized migration more clearly.