Table Schemas

Learn how to define type-safe database tables and columns using Bunty’s fluent API. The schema system provides full TypeScript type inference and comprehensive MySQL column support.

Basic Table Definition

Use the mysqlTable method to define tables with type-safe columns:

import { MysqlClient, bigint, varchar, timestamp, boolean } from '@bunty/orm';

const db = new MysqlClient(process.env.DATABASE_URL!);

const users = db.mysqlTable('users', {
  id: bigint('id').primaryKey().autoIncrement(),
  name: varchar('name', { size: 100 }).notNullable(),
  email: varchar('email', { size: 255 }).notNullable().unique(),
  emailVerified: boolean('email_verified').default(false),
  createdAt: timestamp('created_at').notNullable(),
  updatedAt: timestamp('updated_at').notNullable()
});

Column Types

Bunty supports all MySQL column types with full TypeScript inference:

Text Columns

import { varchar, char, text, mediumtext, longtext } from '@bunty/orm';

// Variable-length strings with size limit
varchar('name', { size: 255 })
varchar('email', { size: 320 })  // RFC compliant email length

// Fixed-length strings
char('country_code', { size: 2 })    // ISO country codes
char('currency', { size: 3 })        // ISO currency codes

// Text content of various sizes
text('description')           // Up to 65,535 characters
mediumtext('article')        // Up to 16,777,215 characters  
longtext('content')          // Up to 4,294,967,295 characters

Numeric Columns

import { tinyint, smallint, int, bigint, decimal, float, double } from '@bunty/orm';

// Integer types
tinyint('status')           // -128 to 127 (1 byte)
smallint('priority')        // -32,768 to 32,767 (2 bytes)
int('count')               // -2,147,483,648 to 2,147,483,647 (4 bytes)
bigint('user_id')          // Large integers (8 bytes)

// Unsigned integers
tinyint('rating').unsigned()    // 0 to 255
int('views').unsigned()         // 0 to 4,294,967,295

// Auto-increment primary keys
bigint('id').primaryKey().autoIncrement()

// Decimal types for precise calculations
decimal('price', { precision: 10, scale: 2 })    // For money (123456.78)
decimal('latitude', { precision: 10, scale: 8 }) // For coordinates

// Floating point numbers
float('rating')             // Single precision
double('coordinates')       // Double precision

Date and Time

import { date, time, datetime, timestamp, year } from '@bunty/orm';

// Date only (YYYY-MM-DD)
date('birth_date')
date('event_date')

// Time only (HH:MM:SS)
time('start_time')
time('duration')

// Date and time (YYYY-MM-DD HH:MM:SS)
datetime('event_datetime')
datetime('scheduled_at')

// Timestamp with automatic features
timestamp('created_at').notNullable().defaultNow()
timestamp('updated_at').notNullable().onUpdateCurrentTimestamp()

// Year only (YYYY)
year('graduation_year')

Boolean and Enums

import { boolean, enumerable } from '@bunty/orm';

// Boolean values (stored as TINYINT(1))
boolean('is_active').default(true)
boolean('email_verified').default(false)
boolean('is_premium').notNullable()

// Enumerated values
enumerable('status', ['draft', 'published', 'archived'])
enumerable('priority', ['low', 'medium', 'high', 'urgent'])
enumerable('user_role', ['admin', 'editor', 'user', 'guest'])

JSON and Binary Data

import { json, binary, varbinary, blob } from '@bunty/orm';

// JSON data (MySQL 5.7+)
json('metadata')
json('settings')
json('user_preferences')

// Binary data
binary('file_hash', { size: 32 })      // Fixed-size binary
varbinary('token', { size: 255 })      // Variable-size binary
blob('file_data')                      // Binary large object

Column Modifiers

Constraints

Add constraints to ensure data integrity:

// Primary key constraint
.primaryKey()

// Not null constraint
.notNullable()

// Unique constraint
.unique()

// Auto increment for numeric columns
.autoIncrement()

// Unsigned for numeric columns
.unsigned()

// Example: Comprehensive user ID column
bigint('id')
  .primaryKey()
  .autoIncrement()
  .unsigned()
  .notNullable()

Default Values

Set default values for columns:

// Static default values
varchar('status').default('active')
int('attempts').default(0)
boolean('is_verified').default(false)

// MySQL function defaults
timestamp('created_at').defaultNow()                    // CURRENT_TIMESTAMP
timestamp('updated_at').onUpdateCurrentTimestamp()      // ON UPDATE CURRENT_TIMESTAMP

// Null as default (explicit)
varchar('middle_name').nullable().default(null)

Comments and Documentation

Add comments to document your schema:

// Column comments
varchar('email', { size: 255 })
  .notNullable()
  .unique()
  .comment('User email address for authentication')

// Multiple columns with comments
const users = db.mysqlTable('users', {
  id: bigint('id')
    .primaryKey()
    .autoIncrement()
    .comment('Unique user identifier'),
    
  email: varchar('email', { size: 255 })
    .notNullable()
    .unique()
    .comment('User email for login and notifications'),
    
  passwordHash: varchar('password_hash', { size: 255 })
    .notNullable()
    .comment('Bcrypt hashed password'),
    
  emailVerifiedAt: timestamp('email_verified_at')
    .nullable()
    .comment('Timestamp when email was verified')
});

Table Configuration

Configure table-level options:

const posts = db.mysqlTable('posts', {
  id: bigint('id').primaryKey().autoIncrement(),
  title: varchar('title', { size: 200 }).notNullable(),
  content: text('content').notNullable(),
  createdAt: timestamp('created_at').notNullable(),
  updatedAt: timestamp('updated_at').notNullable()
})
  .engine('InnoDB')                           // Storage engine
  .charset('utf8mb4')                         // Character set
  .collation('utf8mb4_unicode_ci')           // Collation
  .comment('Blog posts and articles');       // Table comment

Storage Engines

Choose the appropriate storage engine:

// InnoDB (recommended for most use cases)
.engine('InnoDB')    // ACID compliance, foreign keys, row-level locking

// MyISAM (for read-heavy, non-transactional data)
.engine('MyISAM')    // Fast reads, table-level locking

// Memory (for temporary/cache tables)
.engine('MEMORY')    // In-memory storage

Advanced Schema Patterns

Timestamps Pattern

Create a reusable timestamps pattern:

function withTimestamps() {
  return {
    createdAt: timestamp('created_at').notNullable().defaultNow(),
    updatedAt: timestamp('updated_at').notNullable().onUpdateCurrentTimestamp()
  };
}

const users = db.mysqlTable('users', {
  id: bigint('id').primaryKey().autoIncrement(),
  name: varchar('name', { size: 100 }).notNullable(),
  email: varchar('email', { size: 255 }).notNullable().unique(),
  ...withTimestamps()
});

Soft Deletes Pattern

Implement soft deletes with a deleted timestamp:

function withSoftDeletes() {
  return {
    deletedAt: timestamp('deleted_at').nullable().comment('Soft delete timestamp')
  };
}

const posts = db.mysqlTable('posts', {
  id: bigint('id').primaryKey().autoIncrement(),
  title: varchar('title', { size: 200 }).notNullable(),
  content: text('content').notNullable(),
  ...withTimestamps(),
  ...withSoftDeletes()
});

UUID Primary Keys

Use UUIDs instead of auto-increment IDs:

import { char } from '@bunty/orm';

const sessions = db.mysqlTable('sessions', {
  id: char('id', { size: 36 }).primaryKey(),  // UUID v4
  userId: bigint('user_id').notNullable(),
  token: varchar('token', { size: 255 }).notNullable().unique(),
  expiresAt: timestamp('expires_at').notNullable(),
  ...withTimestamps()
});

Real-World Examples

E-commerce Product Table

const products = db.mysqlTable('products', {
  id: bigint('id').primaryKey().autoIncrement(),
  sku: varchar('sku', { size: 50 }).notNullable().unique(),
  name: varchar('name', { size: 255 }).notNullable(),
  description: text('description'),
  price: decimal('price', { precision: 10, scale: 2 }).notNullable(),
  compareAtPrice: decimal('compare_at_price', { precision: 10, scale: 2 }).nullable(),
  cost: decimal('cost', { precision: 10, scale: 2 }).nullable(),
  weight: decimal('weight', { precision: 8, scale: 3 }).nullable(),
  status: enumerable('status', ['draft', 'active', 'archived']).default('draft'),
  isDigital: boolean('is_digital').default(false),
  requiresShipping: boolean('requires_shipping').default(true),
  inventory: int('inventory').unsigned().default(0),
  minInventory: int('min_inventory').unsigned().default(0),
  metadata: json('metadata').nullable(),
  ...withTimestamps()
})
  .engine('InnoDB')
  .comment('Product catalog');

User Authentication Table

const users = db.mysqlTable('users', {
  id: bigint('id').primaryKey().autoIncrement(),
  email: varchar('email', { size: 320 }).notNullable().unique(),
  passwordHash: varchar('password_hash', { size: 255 }).notNullable(),
  firstName: varchar('first_name', { size: 100 }).notNullable(),
  lastName: varchar('last_name', { size: 100 }).notNullable(),
  displayName: varchar('display_name', { size: 200 }).nullable(),
  avatar: varchar('avatar', { size: 500 }).nullable(),
  timezone: varchar('timezone', { size: 50 }).default('UTC'),
  locale: varchar('locale', { size: 10 }).default('en'),
  role: enumerable('role', ['admin', 'editor', 'user']).default('user'),
  isActive: boolean('is_active').default(true),
  emailVerifiedAt: timestamp('email_verified_at').nullable(),
  lastLoginAt: timestamp('last_login_at').nullable(),
  passwordChangedAt: timestamp('password_changed_at').nullable(),
  ...withTimestamps(),
  ...withSoftDeletes()
})
  .engine('InnoDB')
  .comment('User accounts and authentication');

Blog Posts with Categories

const categories = db.mysqlTable('categories', {
  id: bigint('id').primaryKey().autoIncrement(),
  name: varchar('name', { size: 100 }).notNullable(),
  slug: varchar('slug', { size: 150 }).notNullable().unique(),
  description: text('description').nullable(),
  parentId: bigint('parent_id').nullable(),
  sortOrder: int('sort_order').unsigned().default(0),
  isActive: boolean('is_active').default(true),
  ...withTimestamps()
});

const posts = db.mysqlTable('posts', {
  id: bigint('id').primaryKey().autoIncrement(),
  authorId: bigint('author_id').notNullable(),
  categoryId: bigint('category_id').nullable(),
  title: varchar('title', { size: 255 }).notNullable(),
  slug: varchar('slug', { size: 300 }).notNullable().unique(),
  excerpt: varchar('excerpt', { size: 500 }).nullable(),
  content: longtext('content').notNullable(),
  featuredImage: varchar('featured_image', { size: 500 }).nullable(),
  status: enumerable('status', ['draft', 'published', 'scheduled', 'archived']).default('draft'),
  publishedAt: timestamp('published_at').nullable(),
  scheduledAt: timestamp('scheduled_at').nullable(),
  viewCount: int('view_count').unsigned().default(0),
  commentCount: int('comment_count').unsigned().default(0),
  metadata: json('metadata').nullable(),
  ...withTimestamps(),
  ...withSoftDeletes()
})
  .engine('InnoDB')
  .comment('Blog posts and articles');

TypeScript Integration

The schema system provides full TypeScript type inference:

// TypeScript automatically infers the types
type User = {
  id: number;
  email: string;
  passwordHash: string;
  firstName: string;
  lastName: string;
  displayName: string | null;
  role: 'admin' | 'editor' | 'user';
  isActive: boolean;
  emailVerifiedAt: Date | null;
  createdAt: Date;
  updatedAt: Date;
};

// Use with query results
const user: User = await db.select().from(users).where('id', '=', 1).first();

Best Practices

Naming Conventions

// Use snake_case for database columns
// Use camelCase for TypeScript properties (auto-converted)

const users = db.mysqlTable('users', {
  id: bigint('id').primaryKey().autoIncrement(),
  firstName: varchar('first_name', { size: 100 }),  // first_name in DB, firstName in TS
  lastName: varchar('last_name', { size: 100 }),    // last_name in DB, lastName in TS
  emailAddress: varchar('email_address', { size: 255 }) // email_address in DB, emailAddress in TS
});

Index Considerations

// Primary keys automatically get clustered indexes
// Unique constraints automatically get unique indexes
// Consider adding indexes for frequently queried columns

const users = db.mysqlTable('users', {
  id: bigint('id').primaryKey().autoIncrement(),      // Clustered index
  email: varchar('email', { size: 255 }).unique(),   // Unique index
  status: enumerable('status', ['active', 'inactive']) // Consider index if filtering frequently
});

Data Type Selection

Choose appropriate data types for performance and storage:

// Use the smallest appropriate data type
tinyint('age')              // vs int('age') - saves 3 bytes per row
varchar('title', { size: 200 }) // vs text('title') - better performance for known max length

// Use unsigned for non-negative values
int('view_count').unsigned()    // Doubles the positive range

// Use appropriate decimal precision
decimal('price', { precision: 10, scale: 2 })  // $99,999,999.99 max
decimal('tax_rate', { precision: 5, scale: 4 }) // 9.9999% max rate

This comprehensive guide covers all aspects of defining robust, type-safe database schemas with the Bunty ORM. The schema system provides the foundation for your application’s data layer with full TypeScript support.