Skip to content

Schema Builder

The Schema Builder in ScyllinX provides a fluent interface for creating and modifying database schemas. It supports both SQL databases and ScyllaDB with their specific features, allowing you to define tables, columns, indexes, and constraints programmatically.

Introduction

The Schema Builder is the foundation for creating and managing database structures in ScyllinX. It provides:

  • Database Agnostic: Works with SQL databases and ScyllaDB
  • Fluent Interface: Chainable methods for readable schema definitions
  • Type Safety: Full TypeScript support with proper type checking
  • Migration Support: Integrates seamlessly with the migration system
  • Advanced Features: Support for indexes, constraints, and database-specific features

Basic Usage

Creating Tables

typescript
import { Schema } from 'scyllinx';

// Get schema instance
const schema = connectionManager.getSchema();

// Create a basic table
await schema.createTable('users', (table) => {
  table.uuid('id').primary();
  table.string('name');
  table.string('email').unique();
  table.timestamps();
});

Table Builder Methods

The table builder provides methods for defining columns, constraints, and indexes:

typescript
await schema.createTable('posts', (table) => {
  // Primary key
  table.uuid('id').primary();
  
  // String columns
  table.string('title', 255);           // VARCHAR(255)
  table.text('content');                // TEXT
  table.char('status', 1);              // CHAR(1)
  
  // Numeric columns
  table.integer('view_count');          // INTEGER
  table.bigInteger('large_number');     // BIGINT
  table.decimal('price', 10, 2);        // DECIMAL(10,2)
  table.float('rating');                // FLOAT
  table.double('precise_value');        // DOUBLE
  
  // Boolean
  table.boolean('published');           // BOOLEAN
  
  // Date and time
  table.date('publish_date');           // DATE
  table.time('publish_time');           // TIME
  table.timestamp('created_at');        // TIMESTAMP
  table.datetime('updated_at');         // DATETIME
  
  // JSON and binary
  table.json('metadata');               // JSON
  table.binary('file_data');            // BINARY/BLOB
  
  // UUID
  table.uuid('user_id');                // UUID
  
  // Foreign keys
  table.foreign('user_id').references('id').on('users');
  
  // Indexes
  table.index('user_id');
  table.index(['published', 'created_at']);
});

Column Types

String Types

typescript
await schema.createTable('string_examples', (table) => {
  // Variable length string with optional length
  table.string('name');                 // VARCHAR(255) - default length
  table.string('title', 100);           // VARCHAR(100) - custom length
  
  // Fixed length string
  table.char('code', 10);               // CHAR(10)
  
  // Text types
  table.text('description');            // TEXT
  table.mediumText('content');          // MEDIUMTEXT (MySQL)
  table.longText('large_content');      // LONGTEXT (MySQL)
  
  // Enum (database-specific)
  table.enum('status', ['active', 'inactive', 'pending']);
});

Numeric Types

typescript
await schema.createTable('numeric_examples', (table) => {
  // Integer types
  table.integer('count');               // INTEGER/INT
  table.bigInteger('big_count');        // BIGINT
  table.smallInteger('small_count');    // SMALLINT
  table.tinyInteger('tiny_count');      // TINYINT (MySQL)
  
  // Auto-increment
  table.increments('id');               // AUTO_INCREMENT PRIMARY KEY
  table.bigIncrements('big_id');        // BIGINT AUTO_INCREMENT PRIMARY KEY
  
  // Decimal types
  table.decimal('price', 10, 2);        // DECIMAL(10,2)
  table.float('rating', 8, 2);          // FLOAT(8,2)
  table.double('precise', 15, 8);       // DOUBLE(15,8)
  
  // Unsigned (MySQL)
  table.integer('positive').unsigned(); // UNSIGNED INT
});

Date and Time Types

typescript
await schema.createTable('datetime_examples', (table) => {
  // Date types
  table.date('birth_date');             // DATE
  table.time('start_time');             // TIME
  table.datetime('event_datetime');     // DATETIME
  table.timestamp('created_at');        // TIMESTAMP
  
  // Timestamps with timezone
  table.timestampTz('created_at_tz');   // TIMESTAMP WITH TIME ZONE
  
  // Auto-managed timestamps
  table.timestamps();                   // created_at, updated_at
  table.timestamps(true, true);         // with timezone
  
  // Soft delete timestamp
  table.softDeletes();                  // deleted_at
  table.softDeletesTz();                // deleted_at with timezone
});

JSON and Binary Types

typescript
await schema.createTable('json_binary_examples', (table) => {
  // JSON types
  table.json('settings');               // JSON
  table.jsonb('preferences');           // JSONB (PostgreSQL)
  
  // Binary types
  table.binary('file_data');            // BINARY/BLOB
  table.longBlob('large_file');         // LONGBLOB (MySQL)
  
  // UUID
  table.uuid('id');                     // UUID
  table.uuid('user_id').index();        // UUID with index
});

ScyllaDB-Specific Types

Basic ScyllaDB Types

typescript
await schema.createTable('scylla_basic', (table) => {
  // UUID types
  table.uuid('id');                     // UUID
  table.timeuuid('time_id');           // TIMEUUID
  
  // Network types
  table.inet('ip_address');            // INET
  
  // Counter type
  table.counter('view_count');         // COUNTER
  
  // Blob
  table.blob('binary_data');           // BLOB
  
  // Variable integer
  table.varint('big_integer');         // VARINT
});

Collection Types

typescript
await schema.createTable('scylla_collections', (table) => {
  table.uuid('id').primary();
  
  // List (ordered collection, allows duplicates)
  table.list('tags', 'text');          // LIST<TEXT>
  table.list('scores', 'int');         // LIST<INT>
  
  // Set (unordered collection, no duplicates)
  table.set('categories', 'text');     // SET<TEXT>
  table.set('user_ids', 'uuid');       // SET<UUID>
  
  // Map (key-value pairs)
  table.map('attributes', 'text', 'text');     // MAP<TEXT, TEXT>
  table.map('counters', 'text', 'counter');    // MAP<TEXT, COUNTER>
  
  // Frozen collections (immutable, can be used in WHERE clauses)
  table.frozenList('frozen_tags', 'text');
  table.frozenSet('frozen_categories', 'text');
  table.frozenMap('frozen_attrs', 'text', 'text');
});

User-Defined Types

typescript
// First, create a user-defined type
await schema.createType('address', (type) => {
  type.text('street');
  type.text('city');
  type.text('state');
  type.text('zip_code');
  type.text('country');
});

// Use the UDT in a table
await schema.createTable('users_with_address', (table) => {
  table.uuid('id').primary();
  table.text('name');
  
  // Use the user-defined type
  table.userType('address', 'address');
  
  // Frozen UDT (immutable)
  table.frozenUserType('billing_address', 'address');
  
  // Collection of UDTs
  table.list('previous_addresses', 'frozen<address>');
});

Tuple Types

typescript
await schema.createTable('scylla_tuples', (table) => {
  table.uuid('id').primary();
  
  // Tuple type (ordered, fixed-size collection)
  table.tuple('coordinates', ['double', 'double']);  // TUPLE<DOUBLE, DOUBLE>
  table.tuple('name_age', ['text', 'int']);          // TUPLE<TEXT, INT>
  
  // Frozen tuple
  table.frozenTuple('frozen_coords', ['double', 'double']);
});

Column Modifiers

Constraints and Properties

typescript
await schema.createTable('modified_columns', (table) => {
  // Primary key
  table.uuid('id').primary();
  
  // Nullable/Not nullable
  table.string('required_field').notNull();
  table.string('optional_field').nullable();
  
  // Default values
  table.boolean('is_active').default(true);
  table.integer('count').default(0);
  table.string('status').default('pending');
  table.timestamp('created_at').defaultTo(schema.now());
  
  // Unique constraints
  table.string('email').unique();
  table.string('username').unique('unique_username_idx');
  
  // Auto-increment (SQL databases)
  table.increments('auto_id');
  
  // Unsigned (MySQL)
  table.integer('positive_number').unsigned();
  
  // Comments
  table.string('documented_field').comment('This field stores user preferences');
});

ScyllaDB-Specific Modifiers

typescript
await schema.createTable('scylla_modifiers', (table) => {
  // Partition keys (required for ScyllaDB)
  table.uuid('user_id');
  table.text('category');
  
  // Clustering keys (optional, determines sort order)
  table.timestamp('created_at');
  table.uuid('id');
  
  // Regular columns
  table.text('content');
  table.counter('view_count');
  
  // Define partition and clustering keys
  table.partitionKey(['user_id', 'category']);
  table.clusteringKey(['created_at', 'id']);
  
  // Static columns (shared across partition)
  table.text('user_name').static();
  table.text('user_email').static();
});

Indexes

Basic Indexes

typescript
await schema.createTable('indexed_table', (table) => {
  table.uuid('id').primary();
  table.string('email');
  table.string('username');
  table.timestamp('created_at');
  table.boolean('active');
  
  // Simple index
  table.index('email');
  
  // Named index
  table.index('username', 'idx_username');
  
  // Composite index
  table.index(['active', 'created_at'], 'idx_active_created');
  
  // Unique index
  table.unique('email', 'unique_email');
  table.unique(['username', 'active'], 'unique_username_active');
});

// Add indexes after table creation
await schema.alterTable('users', (table) => {
  table.index('last_login');
  table.index(['status', 'created_at'], 'idx_status_created');
});

Advanced Index Types

typescript
// PostgreSQL specific indexes
await schema.createTable('postgres_indexes', (table) => {
  table.uuid('id').primary();
  table.string('title');
  table.text('content');
  table.json('metadata');
  table.boolean('published');
  
  // Partial index
  table.index('title', 'idx_published_titles')
    .where('published = true');
  
  // Expression index
  table.index('lower(title)', 'idx_lower_title');
  
  // GIN index for JSON
  table.index('metadata', 'idx_metadata_gin')
    .using('gin');
  
  // Full-text search index
  table.index('to_tsvector(\'english\', title || \' \' || content)', 'idx_fulltext')
    .using('gin');
});

// MySQL specific indexes
await schema.createTable('mysql_indexes', (table) => {
  table.increments('id');
  table.string('title');
  table.text('content');
  
  // Full-text index
  table.fulltext(['title', 'content'], 'ft_title_content');
  
  // Spatial index (for geometry columns)
  table.geometry('location');
  table.spatialIndex('location', 'spatial_location');
});

ScyllaDB Secondary Indexes

typescript
// Create table first
await schema.createTable('scylla_indexed', (table) => {
  table.uuid('user_id');
  table.timestamp('created_at');
  table.uuid('id');
  table.text('status');
  table.text('category');
  table.list('tags', 'text');
  
  table.partitionKey(['user_id']);
  table.clusteringKey(['created_at', 'id']);
});

// Add secondary indexes
await schema.createIndex('scylla_indexed', 'status', 'idx_status');
await schema.createIndex('scylla_indexed', 'category', 'idx_category');

// Index on collection
await schema.createIndex('scylla_indexed', 'tags', 'idx_tags');

// SASI index for advanced text search
await schema.raw(`
  CREATE CUSTOM INDEX IF NOT EXISTS idx_status_sasi 
  ON scylla_indexed (status) 
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = {
    'mode': 'CONTAINS',
    'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
    'case_sensitive': 'false'
  }
`);

Constraints

Foreign Key Constraints

typescript
await schema.createTable('posts', (table) => {
  table.uuid('id').primary();
  table.string('title');
  table.uuid('user_id');
  table.uuid('category_id').nullable();
  
  // Basic foreign key
  table.foreign('user_id').references('id').on('users');
  
  // Named foreign key with cascade options
  table.foreign('category_id', 'fk_posts_category')
    .references('id')
    .on('categories')
    .onDelete('SET NULL')
    .onUpdate('CASCADE');
});

// Add foreign keys to existing table
await schema.alterTable('comments', (table) => {
  table.foreign('post_id')
    .references('id')
    .on('posts')
    .onDelete('CASCADE');
});

Check Constraints

typescript
await schema.createTable('products', (table) => {
  table.uuid('id').primary();
  table.string('name');
  table.decimal('price', 10, 2);
  table.integer('quantity');
  table.string('status');
  
  // Check constraints
  table.check('price > 0', 'check_positive_price');
  table.check('quantity >= 0', 'check_non_negative_quantity');
  table.check('status IN (\'active\', \'inactive\', \'discontinued\')', 'check_valid_status');
});

Unique Constraints

typescript
await schema.createTable('users', (table) => {
  table.uuid('id').primary();
  table.string('email');
  table.string('username');
  table.string('phone').nullable();
  
  // Single column unique
  table.unique('email');
  table.unique('username', 'unique_username');
  
  // Multi-column unique
  table.unique(['email', 'phone'], 'unique_email_phone');
});

Table Options

SQL Database Options

typescript
await schema.createTable('mysql_options', (table) => {
  table.increments('id');
  table.string('name');
  table.timestamps();
}, {
  // MySQL specific options
  engine: 'InnoDB',
  charset: 'utf8mb4',
  collate: 'utf8mb4_unicode_ci',
  comment: 'User data table'
});

await schema.createTable('postgres_options', (table) => {
  table.uuid('id').primary();
  table.string('name');
  table.timestamps();
}, {
  // PostgreSQL specific options
  tablespace: 'fast_ssd',
  withOids: false,
  inherits: 'base_table'
});

ScyllaDB Table Options

typescript
await schema.createTable('scylla_options', (table) => {
  table.uuid('user_id');
  table.timestamp('created_at');
  table.uuid('id');
  table.text('content');
  
  table.partitionKey(['user_id']);
  table.clusteringKey(['created_at', 'id']);
  
  // ScyllaDB specific table options
  table.withOptions({
    // Clustering order
    clusteringOrder: [
      ['created_at', 'DESC'],
      ['id', 'ASC']
    ],
    
    // Compaction strategy
    compaction: {
      class: 'SizeTieredCompactionStrategy',
      max_threshold: 32,
      min_threshold: 4
    },
    
    // Compression
    compression: {
      sstable_compression: 'LZ4Compressor',
      chunk_length_kb: 64
    },
    
    // Garbage collection grace seconds
    gcGraceSeconds: 864000, // 10 days
    
    // Default TTL
    defaultTimeToLive: 2592000, // 30 days
    
    // Bloom filter false positive chance
    bloomFilterFpChance: 0.01,
    
    // Caching
    caching: {
      keys: 'ALL',
      rows_per_partition: 'NONE'
    },
    
    // Comment
    comment: 'User events table with time-based partitioning'
  });
});

Altering Tables

Adding Columns

typescript
await schema.alterTable('users', (table) => {
  // Add single column
  table.string('phone').nullable();
  
  // Add multiple columns
  table.date('birth_date').nullable();
  table.boolean('is_verified').default(false);
  table.json('preferences').nullable();
  
  // Add column with index
  table.string('status').default('active').index();
  
  // Add foreign key column
  table.uuid('manager_id').nullable();
  table.foreign('manager_id').references('id').on('users');
});

Modifying Columns

typescript
await schema.alterTable('posts', (table) => {
  // Change column type
  table.text('content').alter(); // Change from string to text
  
  // Change nullable
  table.string('title').notNull().alter();
  
  // Change default value
  table.boolean('published').default(true).alter();
  
  // Rename column
  table.renameColumn('old_name', 'new_name');
});

Dropping Columns and Constraints

typescript
await schema.alterTable('users', (table) => {
  // Drop columns
  table.dropColumn('old_field');
  table.dropColumns(['field1', 'field2']);
  
  // Drop indexes
  table.dropIndex('email');
  table.dropIndex('idx_custom_name');
  
  // Drop foreign keys
  table.dropForeign('user_id');
  table.dropForeign('fk_custom_name');
  
  // Drop unique constraints
  table.dropUnique('email');
  table.dropUnique('unique_custom_name');
});

Views

Creating Views

typescript
// Simple view
await schema.createView('active_users', `
  SELECT id, name, email, created_at 
  FROM users 
  WHERE active = true
`);

// View with joins
await schema.createView('user_post_counts', `
  SELECT 
    u.id,
    u.name,
    u.email,
    COUNT(p.id) as post_count
  FROM users u
  LEFT JOIN posts p ON u.id = p.user_id
  GROUP BY u.id, u.name, u.email
`);

// Materialized view (PostgreSQL)
await schema.createMaterializedView('popular_posts', `
  SELECT 
    p.id,
    p.title,
    p.view_count,
    u.name as author_name
  FROM posts p
  JOIN users u ON p.user_id = u.id
  WHERE p.view_count > 1000
  ORDER BY p.view_count DESC
`);

ScyllaDB Materialized Views

typescript
// Create base table
await schema.createTable('user_events', (table) => {
  table.uuid('user_id');
  table.timestamp('event_time');
  table.uuid('event_id');
  table.text('event_type');
  table.text('data');
  
  table.partitionKey(['user_id']);
  table.clusteringKey(['event_time', 'event_id']);
});

// Create materialized view for querying by event_type
await schema.createMaterializedView('events_by_type', 'user_events', (view) => {
  view.select('*');
  view.partitionKey(['event_type']);
  view.clusteringKey(['event_time', 'user_id', 'event_id']);
  view.where('user_id IS NOT NULL')
      .where('event_time IS NOT NULL')
      .where('event_id IS NOT NULL')
      .where('event_type IS NOT NULL');
});

Raw Queries

Executing Raw SQL

typescript
// Execute raw DDL
await schema.raw('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');

// Create custom index
await schema.raw(`
  CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email_lower 
  ON users (LOWER(email))
`);

// Create trigger (PostgreSQL)
await schema.raw(`
  CREATE OR REPLACE FUNCTION update_modified_column()
  RETURNS TRIGGER AS $$
  BEGIN
    NEW.updated_at = now();
    RETURN NEW;
  END;
  $$ language 'plpgsql'
`);

await schema.raw(`
  CREATE TRIGGER update_users_modtime 
  BEFORE UPDATE ON users 
  FOR EACH ROW EXECUTE FUNCTION update_modified_column()
`);

ScyllaDB Raw CQL

typescript
// Create keyspace
await schema.raw(`
  CREATE KEYSPACE IF NOT EXISTS my_app 
  WITH REPLICATION = {
    'class': 'SimpleStrategy',
    'replication_factor': 3
  }
`);

// Create custom index with SASI
await schema.raw(`
  CREATE CUSTOM INDEX IF NOT EXISTS idx_content_sasi 
  ON posts (content) 
  USING 'org.apache.cassandra.index.sasi.SASIIndex'
  WITH OPTIONS = {
    'mode': 'CONTAINS',
    'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.StandardAnalyzer',
    'case_sensitive': 'false'
  }
`);

// Create user-defined function
await schema.raw(`
  CREATE OR REPLACE FUNCTION state_group_and_count(state map<text, int>)
  CALLED ON NULL INPUT
  RETURNS map<text, int>
  LANGUAGE java AS '
    if (state.isEmpty()) {
      state.put("count", 1);
    } else {
      Integer count = (Integer) state.get("count");
      state.put("count", count + 1);
    }
    return state;
  '
`);

Schema Introspection

Checking Schema Information

typescript
// Check if table exists
const hasUsersTable = await schema.hasTable('users');

// Check if column exists
const hasEmailColumn = await schema.hasColumn('users', 'email');

// Get table information
const tableInfo = await schema.getTableInfo('users');
console.log(tableInfo.columns);
console.log(tableInfo.indexes);
console.log(tableInfo.foreignKeys);

// Get column information
const columnInfo = await schema.getColumnInfo('users', 'email');
console.log(columnInfo.type);
console.log(columnInfo.nullable);
console.log(columnInfo.defaultValue);

// List all tables
const tables = await schema.getAllTables();

// List all indexes for a table
const indexes = await schema.getTableIndexes('users');

Database-Specific Introspection

typescript
// PostgreSQL specific
const pgSchema = schema as PostgreSQLSchema;

// Get table size
const tableSize = await pgSchema.getTableSize('users');

// Get index usage statistics
const indexStats = await pgSchema.getIndexStats('users');

// ScyllaDB specific
const scyllaSchema = schema as ScyllaDBSchema;

// Get keyspace information
const keyspaceInfo = await scyllaSchema.getKeyspaceInfo();

// Get table schema
const tableSchema = await scyllaSchema.getTableSchema('users');
console.log(tableSchema.partitionKeys);
console.log(tableSchema.clusteringKeys);

Advanced Schema Patterns

Polymorphic Tables

typescript
// Create polymorphic relationship table
await schema.createTable('comments', (table) => {
  table.uuid('id').primary();
  table.text('content');
  table.uuid('user_id');
  
  // Polymorphic columns
  table.string('commentable_type'); // 'Post', 'Video', etc.
  table.uuid('commentable_id');     // ID of the related record
  
  table.timestamps();
  
  // Indexes for polymorphic queries
  table.index(['commentable_type', 'commentable_id']);
  table.index('user_id');
  
  table.foreign('user_id').references('id').on('users');
});

Audit Tables

typescript
// Create audit table for tracking changes
await schema.createTable('user_audits', (table) => {
  table.uuid('id').primary();
  table.uuid('user_id');
  table.string('action'); // 'CREATE', 'UPDATE', 'DELETE'
  table.json('old_values').nullable();
  table.json('new_values').nullable();
  table.uuid('changed_by').nullable();
  table.timestamp('changed_at').defaultTo(schema.now());
  
  table.index(['user_id', 'changed_at']);
  table.index('action');
  
  table.foreign('user_id').references('id').on('users');
  table.foreign('changed_by').references('id').on('users');
});

Time-Series Tables (ScyllaDB)

typescript
// Time-series table optimized for ScyllaDB
await schema.createTable('metrics', (table) => {
  // Partition by metric name and time bucket
  table.text('metric_name');
  table.date('date_bucket'); // Daily buckets
  
  // Clustering by timestamp for time-series queries
  table.timestamp('timestamp');
  table.uuid('id');
  
  // Metric data
  table.double('value');
  table.map('tags', 'text', 'text');
  
  table.partitionKey(['metric_name', 'date_bucket']);
  table.clusteringKey(['timestamp', 'id']);
  
  table.withOptions({
    clusteringOrder: [['timestamp', 'DESC']],
    defaultTimeToLive: 2592000, // 30 days
    compaction: {
      class: 'TimeWindowCompactionStrategy',
      compaction_window_unit: 'DAYS',
      compaction_window_size: 1
    }
  });
});

Testing Schema Changes

Schema Testing

typescript
// __tests__/schema/users.test.ts
describe('Users Table Schema', () => {
  let schema: Schema;
  
  beforeAll(async () => {
    schema = connectionManager.getSchema();
  });
  
  beforeEach(async () => {
    // Clean up before each test
    await schema.dropTableIfExists('users');
  });
  
  test('should create users table with correct structure', async () => {
    await schema.createTable('users', (table) => {
      table.uuid('id').primary();
      table.string('name');
      table.string('email').unique();
      table.timestamps();
    });
    
    // Verify table exists
    expect(await schema.hasTable('users')).toBe(true);
    
    // Verify columns
    expect(await schema.hasColumn('users', 'id')).toBe(true);
    expect(await schema.hasColumn('users', 'name')).toBe(true);
    expect(await schema.hasColumn('users', 'email')).toBe(true);
    expect(await schema.hasColumn('users', 'created_at')).toBe(true);
    expect(await schema.hasColumn('users', 'updated_at')).toBe(true);
    
    // Verify indexes
    const indexes = await schema.getTableIndexes('users');
    expect(indexes.some(idx => idx.columns.includes('email'))).toBe(true);
  });
  
  test('should handle column modifications', async () => {
    // Create initial table
    await schema.createTable('users', (table) => {
      table.uuid('id').primary();
      table.string('name');
    });
    
    // Add column
    await schema.alterTable('users', (table) => {
      table.string('email').nullable();
    });
    
    expect(await schema.hasColumn('users', 'email')).toBe(true);
    
    // Modify column
    await schema.alterTable('users', (table) => {
      table.string('email').notNull().alter();
    });
    
    const columnInfo = await schema.getColumnInfo('users', 'email');
    expect(columnInfo.nullable).toBe(false);
  });
});

Best Practices

1. Use Appropriate Data Types

typescript
// ✅ Good: Use appropriate types
await schema.createTable('products', (table) => {
  table.uuid('id').primary();           // UUID for distributed systems
  table.string('name', 255);            // Reasonable length limit
  table.decimal('price', 10, 2);        // Precise decimal for money
  table.integer('quantity');            // Integer for counts
  table.boolean('active');              // Boolean for flags
  table.timestamp('created_at');        // Timestamp for dates
});

// ❌ Bad: Inappropriate types
await schema.createTable('products', (table) => {
  table.string('id');                   // String for ID (inefficient)
  table.text('name');                   // Text for short strings (wasteful)
  table.float('price');                 // Float for money (imprecise)
  table.string('quantity');             // String for numbers (wrong type)
  table.string('active');               // String for boolean (confusing)
});

2. Design for Your Database

typescript
// ✅ Good: ScyllaDB optimized
await schema.createTable('user_events', (table) => {
  table.uuid('user_id');               // Partition key
  table.timestamp('event_time');       // Clustering key
  table.uuid('event_id');              // Clustering key
  table.text('event_type');
  
  table.partitionKey(['user_id']);
  table.clusteringKey(['event_time', 'event_id']);
});

// ✅ Good: SQL optimized
await schema.createTable('orders', (table) => {
  table.increments('id');              // Auto-increment PK
  table.integer('user_id').index();    // Foreign key with index
  table.decimal('total', 10, 2);
  table.timestamps();
  
  table.foreign('user_id').references('id').on('users');
});

3. Use Indexes Wisely

typescript
// ✅ Good: Strategic indexing
await schema.createTable('posts', (table) => {
  table.uuid('id').primary();
  table.string('title');
  table.text('content');
  table.uuid('user_id');
  table.boolean('published');
  table.timestamp('created_at');
  
  // Index frequently queried columns
  table.index('user_id');              // For author queries
  table.index(['published', 'created_at']); // For published posts by date
  table.index('title');                // For title searches
});

// ❌ Bad: Over-indexing
await schema.createTable('posts', (table) => {
  table.uuid('id').primary();
  table.string('title').index();       // Every column indexed
  table.text('content').index();       // Text indexing (expensive)
  table.uuid('user_id').index();
  table.boolean('published').index();
  table.timestamp('created_at').index();
  table.timestamp('updated_at').index();
});

4. Plan for Growth

typescript
// ✅ Good: Scalable design
await schema.createTable('user_activities', (table) => {
  // Partition by user and time bucket for even distribution
  table.uuid('user_id');
  table.date('activity_date');
  table.timestamp('activity_time');
  table.uuid('activity_id');
  
  table.partitionKey(['user_id', 'activity_date']);
  table.clusteringKey(['activity_time', 'activity_id']);
  
  // TTL for automatic cleanup
  table.withOptions({
    defaultTimeToLive: 7776000 // 90 days
  });
});

5. Document Your Schema

typescript
// ✅ Good: Well-documented schema
await schema.createTable('users', (table) => {
  table.uuid('id').primary()
    .comment('Unique identifier for the user');
    
  table.string('email').unique()
    .comment('User email address, used for login');
    
  table.string('name')
    .comment('User display name');
    
  table.boolean('email_verified').default(false)
    .comment('Whether the user has verified their email address');
    
  table.timestamp('last_login').nullable()
    .comment('Timestamp of the user\'s last login');
    
  table.timestamps();
});

The Schema Builder is a powerful tool for managing your database structure in ScyllinX. By understanding its capabilities and following best practices, you can create efficient, scalable database schemas that work well with both SQL databases and ScyllaDB.

Released under the MIT License.