Schema Definition

The Extended PowerSync Table system lets you define your database schema once, then automatically generate forms, validation, migrations, and CRUD operations.

Basic Schema

Basic Schema Definition

Compare ZyraForm schema definition with Zod validation, Drizzle schema, and SwiftData

🍎 Swift / ZyraForm🛡️ Zod Validation🌧️ Drizzle Schema📱 SwiftData

import Foundation
import PowerSync
import ZyraForm

let UsersSchema = ExtendedTable(
  name: "(AppConfig.dbPrefix)users",
  primaryKey: "id",
  defaultOrderBy: "created_at DESC",
  columns: [
      .text("email").email().notNull(),
      .text("name").minLength(2).maxLength(50).notNull(),
      .text("age").int().positive().intMin(18).intMax(120).nullable()
  ]
)

import Foundation
import PowerSync
import ZyraForm

let UsersSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)users",
    primaryKey: "id",
    defaultOrderBy: "created_at DESC",
    columns: [
        .text("email").email().notNull(),
        .text("name").minLength(2).maxLength(50).notNull(),
        .text("age").int().positive().intMin(18).intMax(120).nullable()
    ]
)

Column Types

Text Columns

.text("name")                    // Basic text
.text("email").email()          // Email validation
.text("website").url()          // URL validation
.text("user_id").uuid()        // UUID validation
.text("description").nullable() // Optional text

Integer Columns

.integer("age")                  // Integer type
.integer("count").positive()    // Must be positive
.integer("score").intMin(0).intMax(100) // Range validation

Boolean Columns

.text("is_active").bool()        // Boolean stored as text
.text("is_verified").bool().default(true) // With default

Real/Double Columns

.real("price")                   // Double/Float type
.real("rating").double().minimum(0.0).maximum(5.0) // Range

Column Modifiers

Nullability

.text("name").notNull()         // Required field
.text("bio").nullable()         // Optional field

Encryption

.text("name").encrypted()       // Encrypted storage
.text("api_key").encrypted().notNull()

Default Values

.text("status").default("active")           // String default
.integer("count").default(0)                // Integer default
.text("is_active").bool().default(true)    // Boolean default
.text("created_at").default(.now)          // Timestamp default
.text("updated_at").defaultSQL("NOW()")    // SQL expression

Foreign Keys

.text("user_id").uuid().references("\(AppConfig.dbPrefix)users")
.text("org_id").references("\(AppConfig.dbPrefix)organizations",
                           onDelete: .setNull,
                           onUpdate: .cascade)

Enum Types

let ProjectStatusEnum = DatabaseEnum(
    name: "\(AppConfig.dbPrefix)project_status",
    values: ["draft", "active", "archived", "deleted"]
)

.text("status").enum(ProjectStatusEnum).notNull()

Validation Rules

String Validations

.text("email").email()                    // Email format
.text("website").url()                    // URL format
.text("api_endpoint").httpUrl()          // HTTP/HTTPS only
.text("user_id").uuid()                   // UUID format
.text("username").minLength(3).maxLength(20) // Length
.text("code").exactLength(6)             // Exact length
.text("domain").startsWith("https://")   // Starts with
.text("slug").endsWith(".com")           // Ends with
.text("password").includes("!")          // Contains substring
.text("username").lowercase()            // Must be lowercase
.text("api_key").uppercase()             // Must be uppercase
.text("phone").regex("^\\+?[1-9]\\d{1,14}$") // Regex pattern

Number Validations

.integer("age").positive()               // Must be positive
.integer("balance").negative()           // Must be negative
.integer("page").even()                   // Must be even
.integer("row").odd()                     // Must be odd
.integer("score").intMin(0).intMax(100)  // Range
.real("price").minimum(0.01).maximum(9999.99) // Double range

Custom Validation

.text("price").double()
    .positive()
    .custom("Price must be at least 0.01") { value in
        if let price = value as? Double {
            return price >= 0.01
        }
        return false
    }

Complete Schema Example

let ProjectStatusEnum = DatabaseEnum(
    name: "\(AppConfig.dbPrefix)project_status",
    values: ["draft", "active", "archived"]
)

let ProjectsSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)projects",
    primaryKey: "id",
    defaultOrderBy: "created_at DESC",
    columns: [
        .text("name").encrypted().notNull(),
        .text("description").encrypted().nullable(),
        .text("status").enum(ProjectStatusEnum).notNull(),
        .text("owner_id").uuid().references("\(AppConfig.dbPrefix)users"),
        .text("org_id").uuid().nullable().references("\(AppConfig.dbPrefix)organizations"),
        .text("created_at").default(.now).notNull(),
        .text("updated_at").default(.now).notNull()
    ]
)

Auto-Included Columns

ExtendedTable automatically includes standard columns for you, eliminating repetitive code:

Automatic Columns

let UsersSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)users",
    columns: [
        .text("email").email().notNull(),
        .text("name").notNull()
        // That's it! id, created_at, updated_at are added automatically
    ]
)

What’s automatically added:

1. id - Primary key (text/UUID)

   • Added if not specified
   • Uses your primaryKey parameter if provided

2. created_at - Timestamp with NOW() default

   • Automatically set to TIMESTAMPTZ NOT NULL DEFAULT NOW()
   • No manual timestamp management needed

3. updated_at - Timestamp with NOW() default + auto-update trigger

   • Automatically set to TIMESTAMPTZ NOT NULL DEFAULT NOW()
   • Auto-update trigger generated automatically
   • Updates on every row UPDATE - no manual code needed!

Result: You only define your business columns. Standard fields are handled automatically!

Code Generation

ExtendedTable generates complete, production-ready code for multiple platforms:

SQL Migration Generation

Generates complete PostgreSQL migration SQL with:

Basic Table with Auto Columns

let UsersSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)users",
    columns: [
        .text("email").email().notNull(),
        .text("name").minLength(2).maxLength(50).notNull()
    ]
)

let sql = UsersSchema.generateCreateTableSQL()

Generated SQL:

CREATE TABLE "myApp-users" (
    id TEXT PRIMARY KEY,
    email TEXT NOT NULL,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Function to update updated_at timestamp
CREATE OR REPLACE FUNCTION devspace_users_update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to automatically update updated_at on row update
CREATE TRIGGER devspace_users_updated_at_trigger
BEFORE UPDATE ON "myApp-users"
FOR EACH ROW
EXECUTE FUNCTION devspace_users_update_updated_at();

With Enums

let ProjectStatusEnum = DatabaseEnum(
    name: "\(AppConfig.dbPrefix)project_status",
    values: ["draft", "active", "archived", "deleted"]
)

let ProjectsSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)projects",
    columns: [
        .text("name").notNull(),
        .text("status").enum(ProjectStatusEnum).notNull()
    ]
)

Generated SQL:

-- Create enum type first
CREATE TYPE "myApp-project_status" AS ENUM ('draft', 'active', 'archived', 'deleted');

CREATE TABLE "myApp-projects" (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    status "myApp-project_status" NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Auto-update trigger for updated_at
CREATE OR REPLACE FUNCTION devspace_projects_update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER devspace_projects_updated_at_trigger
BEFORE UPDATE ON "myApp-projects"
FOR EACH ROW
EXECUTE FUNCTION devspace_projects_update_updated_at();

With Foreign Key Relations

let ProjectsSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)projects",
    columns: [
        .text("name").notNull(),
        .text("owner_id").uuid().references("\(AppConfig.dbPrefix)users"),
        .text("org_id").uuid().nullable().references("\(AppConfig.dbPrefix)organizations",
                                                      onDelete: .setNull),
        .text("team_id").uuid().nullable().references("\(AppConfig.dbPrefix)teams",
                                                       onDelete: .cascade)
    ]
)

Generated SQL:

CREATE TABLE "myApp-projects" (
    id TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    owner_id TEXT NOT NULL,
    org_id TEXT,
    team_id TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    CONSTRAINT myApp-projects_owner_id_fkey FOREIGN KEY (owner_id)
        REFERENCES "myApp-users" (id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT myApp-projects_org_id_fkey FOREIGN KEY (org_id)
        REFERENCES "myApp-organizations" (id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT myApp-projects_team_id_fkey FOREIGN KEY (team_id)
        REFERENCES "myApp-teams" (id) ON UPDATE CASCADE ON DELETE CASCADE
);

-- Auto-update trigger
CREATE OR REPLACE FUNCTION devspace_projects_update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER devspace_projects_updated_at_trigger
BEFORE UPDATE ON "myApp-projects"
FOR EACH ROW
EXECUTE FUNCTION devspace_projects_update_updated_at();

With Nested Objects and Arrays

// Define nested object schema
let addressSchema: [String: ColumnBuilder] = [
    "street": .text("street").notNull(),
    "city": .text("city").notNull(),
    "zip": .text("zip").regex("^\\d{5}$"),
    "country": .text("country").notNull()
]

let UsersSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)users",
    columns: [
        .text("email").email().notNull(),
        .text("address").object(addressSchema), // Nested object
        .text("tags").array(.text("tag"))       // Array of strings
    ]
)

Generated SQL:

CREATE TABLE "myApp-users" (
    id TEXT PRIMARY KEY,
    email TEXT NOT NULL,
    address JSONB NOT NULL,  -- Stored as JSONB for nested objects
    tags JSONB NOT NULL,     -- Stored as JSONB for arrays
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Auto-update trigger
CREATE OR REPLACE FUNCTION devspace_users_update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER devspace_users_updated_at_trigger
BEFORE UPDATE ON "myApp-users"
FOR EACH ROW
EXECUTE FUNCTION devspace_users_update_updated_at();

Drizzle Schema Generation (TypeScript)

Generates complete Drizzle ORM schema code with:

Basic Table

let UsersSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)users",
    columns: [
        .text("email").email().notNull(),
        .text("name").notNull()
    ]
)

let drizzleSchema = UsersSchema.generateDrizzleSchema()

Generated Drizzle:

// users table
export const users = createTable(
  "users",
  (d) => ({
    id: d.text().primaryKey(),
    email: d.text().notNull(),
    name: d.text().notNull(),
    created_at: d.timestamp({ withTimezone: true }).default(sql`NOW()`).notNull(),
    updated_at: d.timestamp({ withTimezone: true })
      .default(sql`NOW()`)
      .notNull()
      .$onUpdate(() => new Date()) // Auto-update rule
  })
);

With Enums

let ProjectStatusEnum = DatabaseEnum(
    name: "\(AppConfig.dbPrefix)project_status",
    values: ["draft", "active", "archived"]
)

let ProjectsSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)projects",
    columns: [
        .text("status").enum(ProjectStatusEnum).notNull()
    ]
)

Generated Drizzle:

// Enum definition
export const projectStatus = pgEnum("myApp-project_status", [
  "draft",
  "active",
  "archived"
]);

// Table with enum
export const projects = createTable(
  "projects",
  (d) => ({
    id: d.text().primaryKey(),
    status: projectStatus.notNull(),
    created_at: d.timestamp({ withTimezone: true }).default(sql`NOW()`).notNull(),
    updated_at: d.timestamp({ withTimezone: true })
      .default(sql`NOW()`)
      .notNull()
      .$onUpdate(() => new Date())
  })
);

With Foreign Key Relations

let ProjectsSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)projects",
    columns: [
        .text("owner_id").uuid().references("\(AppConfig.dbPrefix)users"),
        .text("org_id").uuid().nullable().references("\(AppConfig.dbPrefix)organizations")
    ]
)

Generated Drizzle:

export const projects = createTable(
  "projects",
  (d) => ({
    id: d.text().primaryKey(),
    owner_id: d.text(),
    org_id: d.text(),
    created_at: d.timestamp({ withTimezone: true }).default(sql`NOW()`).notNull(),
    updated_at: d.timestamp({ withTimezone: true })
      .default(sql`NOW()`)
      .notNull()
      .$onUpdate(() => new Date())
  }),
  (t) => [
    foreignKey({
      columns: [t.owner_id],
      foreignKeys: [users({ columns: [users.id] })]
    }),
    foreignKey({
      columns: [t.org_id],
      foreignKeys: [organizations({ columns: [organizations.id] })]
    })
  ]
);

With Nested Objects and Arrays

let addressSchema: [String: ColumnBuilder] = [
    "street": .text("street").notNull(),
    "city": .text("city").notNull()
]

let UsersSchema = ExtendedTable(
    name: "\(AppConfig.dbPrefix)users",
    columns: [
        .text("address").object(addressSchema),
        .text("tags").array(.text("tag"))
    ]
)

Generated Drizzle:

export const users = createTable(
  "users",
  (d) => ({
    id: d.text().primaryKey(),
    address: d.jsonb().notNull(), // Nested object as JSONB
    tags: d.jsonb().notNull(),    // Array as JSONB
    created_at: d.timestamp({ withTimezone: true }).default(sql`NOW()`).notNull(),
    updated_at: d.timestamp({ withTimezone: true })
      .default(sql`NOW()`)
      .notNull()
      .$onUpdate(() => new Date())
  })
);

Complete Schema Generation

Generate entire schema with proper ordering:

let appSchema = ExtendedSchema(
    tables: [
        UsersSchema,
        ProjectsSchema,
        OrganizationsSchema
    ],
    enums: [ProjectStatusEnum]
)

// Generate complete migration SQL (handles ordering automatically)
let migrationSQL = appSchema.generateMigrationSQL()

// Generate complete Drizzle schema
let drizzleSchema = appSchema.generateDrizzleSchema()

// Generate all Swift models
let swiftModels = appSchema.generateAllSwiftModels()

What’s included:

• ✅ Proper enum creation order
• ✅ Table creation with dependency resolution
• ✅ Foreign key constraints
• ✅ Auto-update triggers
• ✅ Complete TypeScript types
• ✅ Complete Swift models

Benefits of Code Generation

1. Single Source of Truth: Define once, generate everywhere
2. No Manual SQL: Migrations generated automatically
3. Type Safety: Generated code matches your schema exactly
4. Consistency: Same schema, same structure across platforms
5. Less Code: No manual model definitions needed
6. Auto-Updates: updated_at handled automatically in SQL and Drizzle

Schema Collections

Group related tables into a schema:

let appSchema = ExtendedSchema(
    tables: [
        UsersSchema,
        ProjectsSchema,
        OrganizationsSchema
    ],
    enums: [ProjectStatusEnum]
)

// Generate complete migration SQL
let migrationSQL = appSchema.generateMigrationSQL()

Using Schema Config

Generate field configuration for CRUD operations:

let config = UsersSchema.toTableFieldConfig()
// Returns:
// - allFields: Array of all field names
// - encryptedFields: Array of encrypted field names
// - integerFields: Array of integer field names
// - booleanFields: Array of boolean field names
// - defaultOrderBy: Default sort order

Performance

✅ Zero Runtime Overhead: Schema definitions are computed once at initialization (~0.1ms per table)
✅ Compile-Time Optimized: Builder methods are inlined by Swift compiler
✅ Memory Efficient: ~200-500 bytes per table definition (negligible compared to actual data)
✅ Query Performance: Identical to manual PowerSync implementations - uses native PowerSync.Table instances

Next Steps

• Form Components - Use your schema in forms
• Validation - Learn more about validation rules
• CRUD Operations - Perform database operations