or run

npx @tessl/cli init

Version

Tile

Overview

Evals

Files

docs

cli-interface.md configuration-management.md data-seeding.md database-interface.md database-operations.md index.md migration-management.md

tile.json

Database Interface

Database interface methods available within migration and seed files for database schema management and data manipulation. These methods provide a unified API across different database drivers (MySQL, PostgreSQL, SQLite3, MongoDB).

Capabilities

DDL Operations (Data Definition Language)

Schema operations for creating, modifying, and dropping database structures.

Create Table

Create a new database table with specified columns and constraints.

/**
 * Create a new database table
 * @param tableName - Name of the table to create
 * @param options - Table schema definition object
 * @param callback - Completion callback (function)
 * @returns Promise
 */
createTable(tableName, options, callback): Promise;

Usage Examples:

// In migration up() method
exports.up = function(db) {
  return db.createTable('users', {
    id: { 
      type: 'int', 
      primaryKey: true, 
      autoIncrement: true 
    },
    name: { 
      type: 'string', 
      length: 255, 
      notNull: true 
    },
    email: { 
      type: 'string', 
      length: 255, 
      unique: true 
    },
    age: { 
      type: 'int' 
    },
    created_at: { 
      type: 'datetime', 
      defaultValue: 'CURRENT_TIMESTAMP' 
    }
  });
};

// With foreign key
exports.up = function(db) {
  return db.createTable('posts', {
    id: { type: 'int', primaryKey: true, autoIncrement: true },
    title: { type: 'string', length: 255, notNull: true },
    user_id: { 
      type: 'int', 
      notNull: true,
      foreignKey: {
        name: 'posts_user_id_fk',
        table: 'users',
        rules: {
          onDelete: 'CASCADE',
          onUpdate: 'RESTRICT'
        }
      }
    }
  });
};

Drop Table

Remove an existing database table.

/**
 * Drop an existing database table
 * @param tableName - Name of the table to drop
 * @param callback - Completion callback (function)
 * @returns Promise
 */
dropTable(tableName, callback): Promise;

Usage Example:

// In migration down() method
exports.down = function(db) {
  return db.dropTable('users');
};

Rename Table

Rename an existing database table.

/**
 * Rename an existing database table
 * @param tableName - Current table name
 * @param newTableName - New table name
 * @param callback - Completion callback (function)
 * @returns Promise
 */
renameTable(tableName, newTableName, callback): Promise;

Usage Example:

exports.up = function(db) {
  return db.renameTable('old_users', 'users');
};

Column Operations

Add Column

Add a new column to an existing table.

/**
 * Add a new column to an existing table
 * @param tableName - Name of the table
 * @param columnName - Name of the new column
 * @param columnSpec - Column specification object
 * @param callback - Completion callback (function)
 * @returns Promise
 */
addColumn(tableName, columnName, columnSpec, callback): Promise;

Usage Examples:

exports.up = function(db) {
  return db.addColumn('users', 'middle_name', {
    type: 'string',
    length: 100
  });
};

// Add column with constraints
exports.up = function(db) {
  return db.addColumn('users', 'status', {
    type: 'string',
    length: 20,
    notNull: true,
    defaultValue: 'active'
  });
};

Remove Column

Remove an existing column from a table.

/**
 * Remove an existing column from a table
 * @param tableName - Name of the table
 * @param columnName - Name of the column to remove
 * @param callback - Completion callback (function)
 * @returns Promise
 */
removeColumn(tableName, columnName, callback): Promise;

Usage Example:

exports.down = function(db) {
  return db.removeColumn('users', 'middle_name');
};

Rename Column

Rename an existing column in a table.

/**
 * Rename an existing column in a table
 * @param tableName - Name of the table
 * @param oldColumnName - Current column name
 * @param newColumnName - New column name
 * @param callback - Completion callback (function)
 * @returns Promise
 */
renameColumn(tableName, oldColumnName, newColumnName, callback): Promise;

Usage Example:

exports.up = function(db) {
  return db.renameColumn('users', 'name', 'full_name');
};

Change Column

Modify the specification of an existing column.

/**
 * Modify the specification of an existing column
 * @param tableName - Name of the table
 * @param columnName - Name of the column to modify
 * @param columnSpec - New column specification object
 * @param callback - Completion callback (function)
 * @returns Promise
 */
changeColumn(tableName, columnName, columnSpec, callback): Promise;

Usage Example:

exports.up = function(db) {
  return db.changeColumn('users', 'name', {
    type: 'string',
    length: 500,  // Increased from 255
    notNull: true
  });
};

Index Operations

Add Index

Create an index on one or more columns.

/**
 * Create an index on one or more columns
 * @param tableName - Name of the table
 * @param indexName - Name of the index
 * @param columns - Column name (string) or array of column names
 * @param callback - Completion callback (function)
 * @returns Promise
 */
addIndex(tableName, indexName, columns, callback): Promise;

Usage Examples:

// Single column index
exports.up = function(db) {
  return db.addIndex('users', 'users_email_idx', 'email');
};

// Multi-column index
exports.up = function(db) {
  return db.addIndex('posts', 'posts_user_created_idx', ['user_id', 'created_at']);
};

// Unique index
exports.up = function(db) {
  return db.addIndex('users', 'users_username_unique', 'username', true);
};

Remove Index

Drop an existing index.

/**
 * Drop an existing index
 * @param tableName - Name of the table
 * @param indexName - Name of the index to remove
 * @param callback - Completion callback (function)
 * @returns Promise
 */
removeIndex(tableName, indexName, callback): Promise;

Usage Example:

exports.down = function(db) {
  return db.removeIndex('users', 'users_email_idx');
};

Foreign Key Operations

Add Foreign Key

Create a foreign key constraint between tables.

/**
 * Create a foreign key constraint between tables
 * @param tableName - Name of the table to add foreign key to
 * @param referencedTableName - Name of the referenced table
 * @param keyName - Name of the foreign key constraint
 * @param fieldMapping - Object mapping local to foreign columns
 * @param rules - Constraint rules (onDelete, onUpdate)
 * @param callback - Completion callback (function)
 * @returns Promise
 */
addForeignKey(tableName, referencedTableName, keyName, fieldMapping, rules, callback): Promise;

Usage Examples:

// Simple foreign key
exports.up = function(db) {
  return db.addForeignKey('posts', 'users', 'posts_user_fk', {
    'user_id': 'id'
  }, {
    onDelete: 'CASCADE',
    onUpdate: 'RESTRICT'
  });
};

// Multiple column foreign key
exports.up = function(db) {
  return db.addForeignKey('order_items', 'products', 'order_items_product_fk', {
    'product_id': 'id',
    'product_variant_id': 'variant_id'
  }, {
    onDelete: 'CASCADE'
  });
};

Remove Foreign Key

Drop an existing foreign key constraint.

/**
 * Drop an existing foreign key constraint
 * @param tableName - Name of the table
 * @param keyName - Name of the foreign key constraint to remove
 * @param callback - Completion callback (function)
 * @returns Promise
 */
removeForeignKey(tableName, keyName, callback): Promise;

Usage Example:

exports.down = function(db) {
  return db.removeForeignKey('posts', 'posts_user_fk');
};

SQL Execution

Run SQL

Execute raw SQL statements.

/**
 * Execute raw SQL statements
 * @param sql - SQL statement to execute
 * @param params - Optional parameter array for prepared statements
 * @param callback - Completion callback (function)
 * @returns Promise
 */
runSql(sql, params, callback): Promise;

Usage Examples:

// Simple SQL execution
exports.up = function(db) {
  return db.runSql('CREATE EXTENSION IF NOT EXISTS "uuid-ossp"');
};

// SQL with parameters
exports.up = function(db) {
  return db.runSql(
    'INSERT INTO settings (name, value) VALUES (?, ?)',
    ['app_version', '1.0.0']
  );
};

// Complex SQL operation
exports.up = function(db) {
  return db.runSql(`
    CREATE TRIGGER update_users_updated_at 
    BEFORE UPDATE ON users 
    FOR EACH ROW 
    SET NEW.updated_at = CURRENT_TIMESTAMP
  `);
};

All (Query)

Execute a query and return all results.

/**
 * Execute a query and return all results
 * @param sql - SQL query to execute
 * @param params - Optional parameter array for prepared statements
 * @param callback - Completion callback (function)
 * @returns Promise<Array> - Query results
 */
all(sql, params, callback): Promise<Array>;

Usage Example:

exports.up = function(db) {
  return db.all('SELECT id, name FROM users WHERE active = ?', [true])
    .then(function(users) {
      console.log('Found', users.length, 'active users');
      // Process results...
    });
};

Data Manipulation (Seeds Only)

These methods are available in seed files for data manipulation:

Insert

Insert data into a table.

/**
 * Insert data into a table
 * @param tableName - Name of the table
 * @param data - Data object or array of objects to insert
 * @param callback - Completion callback (function)
 * @returns Promise
 */
insert(tableName, data, callback): Promise;

Usage Examples:

// Single record insert
exports.up = function(db) {
  return db.insert('users', {
    name: 'John Doe',
    email: 'john@example.com'
  });
};

// Multiple records insert
exports.up = function(db) {
  return db.insert('categories', [
    { name: 'Electronics', slug: 'electronics' },
    { name: 'Books', slug: 'books' },
    { name: 'Clothing', slug: 'clothing' }
  ]);
};

Update

Update existing data in a table.

/**
 * Update existing data in a table
 * @param tableName - Name of the table
 * @param data - Data object with new values
 * @param where - WHERE clause specification object
 * @param callback - Completion callback (function)
 * @returns Promise
 */
update(tableName, data, where, callback): Promise;

Usage Example:

exports.up = function(db) {
  return db.update('users', 
    { status: 'verified' },
    { email: 'john@example.com' }
  );
};

Delete

Delete data from a table.

/**
 * Delete data from a table
 * @param tableName - Name of the table
 * @param where - WHERE clause specification object
 * @param callback - Completion callback (function)
 * @returns Promise
 */
delete(tableName, where, callback): Promise;

Usage Example:

exports.down = function(db) {
  return db.delete('users', { 
    email: 'john@example.com' 
  });
};

Lookup

Query data from a table.

/**
 * Query data from a table
 * @param tableName - Name of the table
 * @param column - Column name to query
 * @param value - Value to search for
 * @param callback - Completion callback (function)
 * @returns Promise<Array> - Query results
 */
lookup(tableName, column, value, callback): Promise<Array>;

Usage Example:

exports.up = function(db) {
  return db.lookup('users', 'email', 'admin@example.com')
    .then(function(users) {
      if (users.length === 0) {
        return db.insert('users', {
          name: 'Admin',
          email: 'admin@example.com',
          role: 'admin'
        });
      }
    });
};

Truncate

Remove all data from a table while preserving structure.

/**
 * Remove all data from a table while preserving structure
 * @param tableName - Name of the table to truncate
 * @param callback - Completion callback (function)
 * @returns Promise
 */
truncate(tableName, callback): Promise;

Usage Example:

exports.down = function(db) {
  return db.truncate('temp_data');
};

Column Specification Options

When creating or modifying columns, use these specification options:

/**
 * Column specification object for table operations
 */
interface ColumnSpec {
  /** Column data type */
  type: string;
  /** Column length/size */
  length?: number;
  /** Precision for decimal types */
  precision?: number;
  /** Scale for decimal types */
  scale?: number;
  /** Whether column allows NULL values */
  notNull?: boolean;
  /** Default value for the column */
  defaultValue?: any;
  /** Whether column is primary key */
  primaryKey?: boolean;
  /** Whether column auto-increments */
  autoIncrement?: boolean;
  /** Whether column values must be unique */
  unique?: boolean;
  /** Foreign key specification */
  foreignKey?: {
    name: string;
    table: string;
    rules?: {
      onDelete?: 'CASCADE' | 'RESTRICT' | 'SET NULL';
      onUpdate?: 'CASCADE' | 'RESTRICT' | 'SET NULL';
    };
  };
}

Data Types

Available data types for column specifications:

/**
 * Standard data types available across database drivers
 */
interface DataTypes {
  STRING: 'string';           // VARCHAR
  TEXT: 'text';              // TEXT/LONGTEXT
  INTEGER: 'int';            // INTEGER
  BIGINT: 'bigint';          // BIGINT
  DECIMAL: 'decimal';        // DECIMAL/NUMERIC
  FLOAT: 'real';             // FLOAT/REAL
  DATE: 'date';              // DATE
  TIME: 'time';              // TIME
  DATETIME: 'datetime';      // DATETIME/TIMESTAMP
  BOOLEAN: 'boolean';        // BOOLEAN/TINYINT(1)
  BINARY: 'binary';          // BINARY/VARBINARY
  BLOB: 'blob';              // BLOB
}

Usage Example:

exports.up = function(db) {
  return db.createTable('products', {
    id: { type: 'int', primaryKey: true, autoIncrement: true },
    name: { type: 'string', length: 255, notNull: true },
    description: { type: 'text' },
    price: { type: 'decimal', precision: 10, scale: 2 },
    in_stock: { type: 'boolean', defaultValue: true },
    created_at: { type: 'datetime', defaultValue: 'CURRENT_TIMESTAMP' }
  });
};

Database-Specific Considerations

MySQL

Supports all DDL and DML operations
Foreign keys require InnoDB engine
Index names must be unique per table
Column length required for STRING/VARCHAR types

PostgreSQL

Supports all DDL and DML operations
Schema-aware operations supported
Advanced data types available (JSON, arrays, etc.)
Case-sensitive identifiers

SQLite3

Limited ALTER TABLE support
No DROP COLUMN support in older versions
Foreign keys must be enabled explicitly
Limited data type enforcement

MongoDB

Schema operations create collections and indexes
Data types mapped to BSON types
Limited relational constraints
Document-based operations for data manipulation

Error Handling

Database interface methods can throw various errors:

exports.up = function(db) {
  return db.createTable('users', tableSpec)
    .catch(function(error) {
      if (error.message.includes('already exists')) {
        console.log('Table already exists, skipping...');
        return Promise.resolve();
      }
      throw error;
    });
};

Types

/**
 * Database interface callback function signature
 */
type DatabaseCallback = (error?: Error, result?: any) => void;

/**
 * WHERE clause specification for queries
 */
interface WhereClause {
  [columnName: string]: any;
}

/**
 * Field mapping for foreign keys
 */
interface FieldMapping {
  [localColumn: string]: string; // foreign column
}

/**
 * Foreign key constraint rules
 */
interface ForeignKeyRules {
  onDelete?: 'CASCADE' | 'RESTRICT' | 'SET NULL';
  onUpdate?: 'CASCADE' | 'RESTRICT' | 'SET NULL';
}

Version

Tile

Files