Database Schema

Define your database schema using Drizzle ORM with defineTable. Combine schema definition and security configuration in a single TypeScript file.

Quickback uses Drizzle ORM to define your database schema. With defineTable, you combine your schema definition and security configuration in a single file.

Defining Tables with defineTable

Each table gets its own file with schema and config together. Use the Drizzle dialect that matches your target database:

Target Database	Import From	Table Function
Cloudflare D1, Turso, SQLite	`drizzle-orm/sqlite-core`	`sqliteTable`
Supabase, Neon, PostgreSQL	`drizzle-orm/pg-core`	`pgTable`
PlanetScale, MySQL	`drizzle-orm/mysql-core`	`mysqlTable`

// definitions/features/rooms/rooms.ts
// For D1/SQLite targets:
import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';
import { defineTable } from '@quickback/compiler';

export const rooms = sqliteTable('rooms', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  capacity: integer('capacity').notNull().default(10),
  roomType: text('room_type').notNull(),
  isActive: integer('is_active', { mode: 'boolean' }).notNull().default(true),

  // Ownership - for firewall data isolation
  organizationId: text('organization_id').notNull(),
});

export default defineTable(rooms, {
  firewall: { organization: {} },
  guards: {
    createable: ["name", "description", "capacity", "roomType"],
    updatable: ["name", "description", "capacity"],
  },
  crud: {
    list: { access: { roles: ["owner", "admin", "member"] } },
    get: { access: { roles: ["owner", "admin", "member"] } },
    create: { access: { roles: ["owner", "admin"] } },
    update: { access: { roles: ["owner", "admin"] } },
    delete: { access: { roles: ["owner", "admin"] } },
  },
});

export type Room = typeof rooms.$inferSelect;

Column Types

Drizzle supports all standard SQL column types:

Type	Drizzle Function	Example
String	`text()`, `varchar()`	`text('name')`
Integer	`integer()`, `bigint()`	`integer('count')`
Boolean	`boolean()`	`boolean('is_active')`
Timestamp	`timestamp()`	`timestamp('created_at')`
JSON	`json()`, `jsonb()`	`jsonb('metadata')`
UUID	`uuid()`	`uuid('id')`
Decimal	`decimal()`, `numeric()`	`decimal('price', { precision: 10, scale: 2 })`

Column Modifiers

// Required field
name: text('name').notNull()

// Default value
isActive: boolean('is_active').default(true)

// Primary key
id: text('id').primaryKey()

// Unique constraint
email: text('email').unique()

// Default to current timestamp
createdAt: timestamp('created_at').defaultNow()

File Organization

Organize your tables by feature. Each feature directory contains table files:

definitions/
└── features/
    ├── rooms/
    │   ├── rooms.ts              # Main table + config
    │   ├── room-bookings.ts      # Related table + config
    │   └── actions.ts            # Custom actions
    ├── users/
    │   ├── users.ts              # Table + config
    │   └── user-preferences.ts   # Related table
    └── organizations/
        └── organizations.ts

Key points:

Tables with export default defineTable(...) get CRUD routes generated
Tables without a default export are internal (no routes, used for joins/relations)
Route paths are derived from filenames: room-bookings.ts → /api/v1/room-bookings

defineTable vs defineResource

Both functions are available:

defineTable - The standard function for defining tables with CRUD routes
defineResource - Alias for defineTable, useful when thinking in terms of REST resources

// These are equivalent:
export default defineTable(rooms, { /* config */ });
export default defineResource(rooms, { /* config */ });

1 Resource = 1 Security Boundary

Each defineTable() call defines a complete, self-contained security boundary. The security config you write — firewall, access, guards, and masking — is compiled into a single resource file that wraps all CRUD routes for that table.

This is a deliberate design choice. Mixing two resources with different security rules in one configuration would create ambiguity about which firewall, access, or masking rules apply to which table. By keeping it 1:1, there's never any question.

Scenario	What to do
Table needs its own API routes + security	Own file with `defineTable()`
Table is internal/supporting (no direct API)	Extra `.ts` file in the parent feature directory, no `defineTable()`

A supporting table without defineTable() is useful when it's accessed internally — by action handlers, joins, or background jobs — but should never be directly exposed as its own API endpoint.

Internal Tables (No Routes)

For junction tables or internal data structures that shouldn't have API routes, simply omit the defineTable export:

// definitions/features/rooms/room-amenities.ts
import { sqliteTable, text } from 'drizzle-orm/sqlite-core';

// Junction table - no routes needed
export const roomAmenities = sqliteTable('room_amenities', {
  roomId: text('room_id').notNull(),
  amenityId: text('amenity_id').notNull(),
});

// No default export = no CRUD routes generated

These internal tables still participate in the database schema and migrations — they just don't get API routes or security configuration.

The compiler automatically injects audit fields (createdAt, modifiedAt, deletedAt, etc.) and organizationId into child/junction tables when the parent feature is org-scoped. This ensures cascade soft deletes and scoped queries work correctly without manual column definitions.

Audit Fields

Quickback automatically adds and manages these audit fields on all tables in a feature (including child/junction tables without defineTable) - you don't need to define them:

Field	Type	Description
`createdAt`	timestamp	Set when record is created
`createdBy`	text	User ID who created the record
`modifiedAt`	timestamp	Updated on every change
`modifiedBy`	text	User ID who last modified
`deletedAt`	timestamp	Set on soft delete (optional)
`deletedBy`	text	User ID who deleted (optional)

The soft delete fields (deletedAt, deletedBy) are only added if your resource uses soft delete mode.

Disabling Audit Fields

To disable automatic audit fields for your project:

// quickback.config.ts
export default defineConfig({
  // ...
  compiler: {
    features: {
      auditFields: false,  // Disable for entire project
    }
  }
});

Protected System Fields

These fields are always protected and cannot be set by clients, even with guards: false:

id (when generateId is not false)
createdAt, createdBy
modifiedAt, modifiedBy
deletedAt, deletedBy

Ownership Fields

For the firewall to work, include the appropriate ownership columns:

// For organization-scoped data (most common)
organizationId: text('organization_id').notNull()

// For user-owned data (personal data)
ownerId: text('owner_id').notNull()

// For team-scoped data
teamId: text('team_id').notNull()

Display Column

When a table has foreign key columns (e.g., roomTypeId referencing roomTypes), Quickback can automatically resolve the human-readable label in GET and LIST responses. This eliminates the need for frontend lookup calls.

Auto-Detection

Quickback auto-detects the display column by checking for common column names in this order:

name → title → label → headline → subject → code → displayName → fullName → description

If your roomTypes table has a name column, it's automatically used as the display column. No config needed.

Explicit Override

Override the auto-detected column with displayColumn:

export default defineTable(accountCodes, {
  displayColumn: 'code',  // Use 'code' instead of auto-detected 'name'
  firewall: { organization: {} },
  crud: {
    list: { access: { roles: ["member", "admin"] } },
    get: { access: { roles: ["member", "admin"] } },
  },
});

How Labels Appear in Responses

For any FK column ending in Id, the API adds a _label field with the resolved display value:

{
  "id": "rm_abc",
  "name": "Main Conference Room",
  "roomTypeId": "rt_xyz",
  "roomType_label": "Conference",
  "accountCodeId": "ac_123",
  "accountCode_label": "Revenue"
}

The pattern is {columnWithoutId}_label. The frontend can find all labels with:

Object.keys(record).filter(k => k.endsWith('_label'))

Label resolution works within the same feature (tables in the same feature directory). System columns (organizationId, createdBy, modifiedBy) are never resolved.

For LIST endpoints, labels are batch-resolved efficiently — one query per FK column, not per record.

Relations (Optional)

Drizzle supports defining relations for type-safe joins:

import { relations } from 'drizzle-orm';
import { organizations } from '../organizations/organizations';
import { bookings } from './room-bookings';

export const roomsRelations = relations(rooms, ({ one, many }) => ({
  organization: one(organizations, {
    fields: [rooms.organizationId],
    references: [organizations.id],
  }),
  bookings: many(bookings),
}));

Database Configuration

Configure database options in your Quickback config:

// quickback.config.ts
export default defineConfig({
  name: 'my-app',
  providers: {
    database: defineDatabase('cloudflare-d1', {
      generateId: 'prefixed',          // 'uuid' | 'cuid' | 'nanoid' | 'prefixed' | 'serial' | false
      namingConvention: 'snake_case',   // 'camelCase' | 'snake_case'
      usePlurals: false,                // Auth table names: 'users' vs 'user'
    }),
  },
  compiler: {
    features: {
      auditFields: true,           // Auto-manage audit timestamps
    }
  }
});

Choosing Your Dialect

Use the Drizzle dialect that matches your database provider:

SQLite (D1, Turso, better-sqlite3)

import { sqliteTable, text, integer } from 'drizzle-orm/sqlite-core';

export const posts = sqliteTable('posts', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  metadata: text('metadata', { mode: 'json' }),  // JSON stored as text
  isPublished: integer('is_published', { mode: 'boolean' }).default(false),
  organizationId: text('organization_id').notNull(),
});

PostgreSQL (Supabase, Neon)

import { pgTable, text, serial, jsonb, boolean } from 'drizzle-orm/pg-core';

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  metadata: jsonb('metadata'),           // Native JSONB
  isPublished: boolean('is_published').default(false),
  organizationId: text('organization_id').notNull(),
});

Key Differences

Feature	SQLite	PostgreSQL
Boolean	`integer({ mode: 'boolean' })`	`boolean()`
JSON	`text({ mode: 'json' })`	`jsonb()` or `json()`
Auto-increment	`integer().primaryKey()`	`serial()`
UUID	`text()`	`uuid()`

Next Steps

Configure the firewall for data isolation
Set up access control for CRUD operations
Define guards for field modification rules
Add custom actions for business logic

On this page