Databases
MySQL
Tables

MySQL Tables

Complete reference for defining MySQL tables in SchemaHero.

Basic Structure

apiVersion: schemas.schemahero.io/v1alpha4
kind: Table
metadata:
  name: users
spec:
  database: myapp
  name: users
  schema:
    mysql:
      defaultCharset: utf8mb4
      collation: utf8mb4_unicode_ci
      primaryKey: [id]
      columns:
        - name: id
          type: int
          attributes:
            autoIncrement: true
        - name: email
          type: varchar(255)
          constraints:
            notNull: true

Table Options

schema:
  mysql:
    defaultCharset: utf8mb4           # Table default character set
    collation: utf8mb4_unicode_ci     # Table collation

Columns

columns:
  - name: id
    type: int                   # Required - MySQL type
    default: null               # Optional
    constraints:
      notNull: true
    attributes:
      autoIncrement: true
    charset: utf8mb4            # Per-column charset
    collation: utf8mb4_bin      # Per-column collation

Column Types

CategoryTypes
Integertinyint, smallint, mediumint, int, bigint
Numericdecimal(p,s), float, double
Stringchar(n), varchar(n), text, mediumtext, longtext
Booleanboolean (alias for tinyint(1))
Date/Timedate, time, datetime, timestamp, year
Binarybinary, varbinary, blob, mediumblob, longblob
JSONjson
Enumenum('a','b','c')
Setset('a','b','c')

Default Values

# String literal (note nested quotes)
- name: status
  type: varchar(20)
  default: "'pending'"
 
# Number
- name: count
  type: int
  default: "0"
 
# Boolean
- name: active
  type: boolean
  default: "1"
 
# Timestamp functions
- name: created_at
  type: timestamp
  default: current_timestamp
 
- name: updated_at
  type: timestamp
  default: current_timestamp on update current_timestamp
 
# UUID (MySQL 8.0+)
- name: id
  type: char(36)
  default: "(uuid())"

Auto-Increment

columns:
  - name: id
    type: int
    attributes:
      autoIncrement: true

Primary Keys

schema:
  mysql:
    primaryKey: [id]              # Single column
    primaryKey: [tenant_id, id]   # Composite key

Indexes

indexes:
  - columns: [email]
    name: idx_users_email
    isUnique: true
    type: btree                   # btree (default) or fulltext
    
  - columns: [last_name, first_name]
    name: idx_users_name
    
  - columns: [bio]
    name: idx_users_bio
    type: fulltext                # Full-text index

Index Types

TypeUse Case
btreeDefault, general purpose
fulltextFull-text search on text columns

Foreign Keys

foreignKeys:
  - columns: [team_id]
    references:
      table: teams
      columns: [id]
    onDelete: CASCADE
    onUpdate: NO ACTION
    name: fk_users_team

Note: Foreign keys require InnoDB storage engine.

Complete Example

apiVersion: schemas.schemahero.io/v1alpha4
kind: Table
metadata:
  name: users
spec:
  database: myapp
  name: users
  schema:
    mysql:
      defaultCharset: utf8mb4
      collation: utf8mb4_unicode_ci
      primaryKey: [id]
      columns:
        - name: id
          type: int
          attributes:
            autoIncrement: true
        - name: email
          type: varchar(255)
          constraints:
            notNull: true
        - name: name
          type: varchar(100)
        - name: team_id
          type: int
        - name: bio
          type: text
        - name: metadata
          type: json
        - name: status
          type: enum('active','inactive','pending')
          default: "'pending'"
        - name: created_at
          type: timestamp
          default: current_timestamp
        - name: updated_at
          type: timestamp
          default: current_timestamp on update current_timestamp
      indexes:
        - columns: [email]
          name: idx_users_email
          isUnique: true
        - columns: [team_id]
          name: idx_users_team
        - columns: [bio]
          name: idx_users_bio
          type: fulltext
      foreignKeys:
        - columns: [team_id]
          references:
            table: teams
            columns: [id]
          onDelete: SET NULL
ConnectingColumn Types