Xano Schema Design

Xano Schema Design

PostgreSQL schema design best practices adapted for Xano's database layer.

Data Format Selection

Decision Criteria

Default recommendation: Standard SQL format for production applications.

Normalization Principles

When to Normalize

Normalize when:

• Data is repeated across records
• Updates would need to change multiple rows
• Referential integrity is important
• Relationships are clearly defined

Denormalize when:

• Read performance is critical
• Data rarely changes
• Avoiding joins is more important than data consistency

Normalization Example

Before (Denormalized)

orders table:
| id | customer_name | customer_email | product_name | product_price |

Problems:

• Customer data duplicated per order
• Product data duplicated per order
• Updates require changing multiple rows

After (Normalized)

customers table:
| id | name | email |

products table:
| id | name | price |

orders table:
| id | customer_id | product_id | quantity | created_at |

Benefits:

• Single source of truth
• Updates affect one row
• Referential integrity via foreign keys

Xano UI Steps (Create Foreign Key)

1. Database → orders table → Schema
2. Add field: customer_id (Integer)
3. Click the link icon next to the field
4. Select: Table Reference → customers → id
5. Save

Data Type Selection

Recommended Data Types

Data Type Anti-Patterns

// Storing money as float - precision errors
price DECIMAL(10,2)  // Good
price FLOAT          // Bad - 0.1 + 0.2 = 0.30000000000000004

// Storing dates as strings
created_at TIMESTAMP  // Good - enables date functions
created_at TEXT       // Bad - no date operations

// Using text for booleans
is_active BOOLEAN     // Good
is_active TEXT        // Bad - "true", "True", "1", "yes"...

Constraints

NOT NULL Constraints

Apply NOT NULL to required fields:

Xano UI:
1. Database → [table] → Schema
2. Edit field
3. Enable "Required" toggle
4. Save

Unique Constraints

For fields that must be unique (email, slug, etc.):

Xano UI:
1. Database → [table] → Indexes
2. Create Index
3. Select field (e.g., email)
4. Type: Unique
5. Save

Check Constraints (Direct Query)

For custom validation rules:

-- Via Direct Database Query
ALTER TABLE products 
ADD CONSTRAINT positive_price CHECK (price > 0);

ALTER TABLE orders
ADD CONSTRAINT valid_quantity CHECK (quantity >= 1);

Foreign Key Constraints

Xano UI:
1. Database → [table] → Schema
2. Click link icon on reference field
3. Select referenced table and field
4. Configure ON DELETE behavior:
   - CASCADE: Delete related records
   - SET NULL: Set reference to NULL
   - RESTRICT: Prevent deletion if referenced
5. Save

Index Design

Index Types in Xano

Composite Index Guidelines

Order columns by:

1. Equality conditions first (status = ?)
2. Range conditions last (created_at > ?)
3. Most selective columns first

// Query pattern
WHERE user_id = ? AND status = ? AND created_at > ?

// Index order
(user_id, status, created_at)

Partial Indexes (Direct Query)

Index only relevant rows:

-- Only index active users
CREATE INDEX idx_users_active_email 
ON users(email) 
WHERE status = 'active';

-- Only index pending orders
CREATE INDEX idx_orders_pending 
ON orders(created_at) 
WHERE status = 'pending';

Benefit: Smaller index, faster updates, faster queries on subset.

Index Maintenance

Indexes need occasional maintenance:

-- Check index usage (Direct Query)
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Remove unused indexes (0 scans = unused)
DROP INDEX IF EXISTS unused_index_name;

Table Design Patterns

Timestamps Pattern

Always include audit timestamps:

Table schema:
| id | ... | created_at | updated_at |

Xano automatically manages created_at. For updated_at, use a trigger or set in API logic.

Soft Delete Pattern

Instead of deleting records:

Table schema:
| id | ... | deleted_at | is_deleted |

// Soft delete
db.raw "UPDATE users SET deleted_at = NOW(), is_deleted = true WHERE id = ?" as $result

// Query only active records
db.query user {
  filter = "is_deleted = ?", false
}

Benefits:

• Audit trail
• Easy restore
• Referential integrity preserved

Status Enum Pattern

Use consistent status fields:

Recommended statuses for orders:
| pending | processing | completed | cancelled |

Table schema:
| id | ... | status |

Create index on status for filtering:

Indexes:
- status (Index type)
- (status, created_at) composite for sorted queries

Multi-Tenant Pattern

For SaaS applications:

All tables include:
| id | tenant_id | ... |

// Always filter by tenant
db.query order {
  filter = "tenant_id = ?", $auth.tenant_id
}

Important: Create composite indexes starting with tenant_id:

(tenant_id, status)
(tenant_id, created_at)
(tenant_id, user_id)

JSONB Field Usage

When to Use JSONB Fields

Even in Standard SQL format, JSONB fields are useful for:

• User preferences
• Metadata
• Flexible attributes
• API response caching

JSONB Indexing

-- GIN index for containment queries
CREATE INDEX idx_users_preferences ON users USING GIN (preferences);

-- Supports: preferences @> '{"theme": "dark"}'

JSONB Query Patterns

// Filter by JSONB field (Direct Query)
db.raw "SELECT * FROM users WHERE preferences @> '{\"theme\": \"dark\"}'" as $dark_mode_users

// Extract JSONB value
db.raw "SELECT id, preferences->>'theme' as theme FROM users" as $themes

Migration Patterns

Adding Non-Null Column

-- Step 1: Add nullable column
ALTER TABLE users ADD COLUMN phone TEXT;

-- Step 2: Backfill data
UPDATE users SET phone = 'unknown' WHERE phone IS NULL;

-- Step 3: Add NOT NULL constraint
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Renaming Column

ALTER TABLE users RENAME COLUMN fname TO first_name;

Note: Update all XanoScript and API references after rename.

Changing Data Type

-- Safe type change (compatible types)
ALTER TABLE products ALTER COLUMN price TYPE NUMERIC(12,2);

-- Type change with cast
ALTER TABLE orders ALTER COLUMN quantity TYPE BIGINT USING quantity::BIGINT;

Schema Review Checklist

Before deploying schema changes:

• All tables have appropriate primary keys
• Foreign keys defined for relationships
• Indexes on frequently filtered columns
• NOT NULL on required fields
• Unique constraints on unique fields (email, slug)
• Appropriate data types chosen
• Timestamps (created_at, updated_at) included
• Multi-tenant filter columns indexed

Related Skills

• xano-database-best-practices - Format selection overview
• xano-query-performance - Index optimization
• xano-security - RLS and access control
• xano-data-access - Query patterns

Resources

• Xano Database Docs: https://docs.xano.com/the-database/database-basics/using-the-xano-database
• Direct Database Query: https://docs.xano.com/the-function-stack/functions/database-requests/direct-database-query
• PostgreSQL Data Types: https://www.postgresql.org/docs/current/datatype.html

Converted and distributed by TomeVault — claim your Tome and manage your conversions.