Database Management Commands

This page documents the database management operations available through the Light CLI tool, focusing primarily on the db:install command used to install and update database schemas. For information about code generation from the database schema, see Code Generation. For details on the overall CLI tool structure, see CLI Overview.

Purpose and Scope

The database management commands provide automated schema installation, migration, and validation based on the declarative schema definition in db.json. These commands translate the JSON schema definition into executable SQL DDL statements, create or update database tables, establish foreign key constraints, and ensure the database structure matches the defined schema.

Database Management Architecture

Sources: bin/light1-24 db.json1-676

Schema Definition Format (db.json)

The database schema is defined declaratively in the db.json file located at the project root. This JSON file contains an array of table definitions that drive both DDL generation and model code generation.

Table Definition Structure

Each table definition in db.json follows this structure:

Property	Type	Required	Description
name	string	Yes	Table name (e.g., "User", "Role")
columns	array	Yes	Array of column definitions
primary_key	array	Yes	Column names forming the primary key
unique_keys	array	No	Array of unique constraint definitions
keys	array	No	Array of index definitions
foreign_keys	array	No	Array of foreign key constraint definitions

Sources: db.json1-676

Column Definition Format

Example column definitions from the User table:

Sources: db.json6-10 db.json13-16 db.json58-63

Foreign Key Constraints

Foreign keys are defined with full referential integrity control:

Property	Description	Values
columns	Local columns	Array of column names
reference_table	Target table	Table name string
reference_columns	Target columns	Array of column names
on_delete	Delete action	CASCADE, SET NULL, RESTRICT
on_update	Update action	CASCADE, SET NULL, RESTRICT

Example from UserRole table:

This configuration ensures that when a User is deleted, all associated UserRole records are automatically deleted (CASCADE), preserving referential integrity.

Sources: db.json218-230

Index Definitions

The schema supports both unique constraints and regular indexes:

Unique Keys (enforce uniqueness):

Regular Indexes (improve query performance):

Sources: db.json131-138 db.json204-217

db:install Command

The db:install command is the primary tool for database schema management, registered in the CLI application at bin/light17

Command Registration

Sources: bin/light15 bin/light17

Command Invocation

The command is invoked from the command line:

This command:

1. Reads the schema definition from db.json
2. Connects to the database using credentials from environment variables
3. Compares existing schema with defined schema
4. Generates and executes DDL statements to create or modify tables
5. Creates indexes and foreign key constraints
6. Reports the installation status

Schema Installation Process

Sources: bin/light6 bin/light17 db.json1-676

Core Database Tables

The Light framework defines 11 core tables in db.json that support the framework's functionality:

Authentication and Authorization Tables

Table	Primary Key	Purpose	Foreign Keys
User	user_id	Identity management, credentials, profile	None
Role	role_id	Role definitions, hierarchy	None
UserRole	user_role_id	User-to-role mapping	→ User.user_id
Permission	permission_id	Role-to-permission mapping	None
UserLog	userlog_id	Session tracking, login history	→ User.user_id

Sources: db.json3-138 db.json141-177 db.json180-231 db.json477-499 db.json265-336

Configuration and Audit Tables

Table	Primary Key	Purpose	Foreign Keys
Config	config_id	Key-value configuration store	None
EventLog	eventlog_id	Audit trail, revision history	→ User.user_id (SET NULL)
MailLog	maillog_id	Email dispatch tracking	None

Sources: db.json233-263 db.json338-418 db.json420-475

Utility Tables

Table	Primary Key	Purpose
SystemValue	systemvalue_id	System-wide key-value pairs
Translate	translate_id	Internationalization strings
CustomField	custom_field_id	Dynamic schema extension

Sources: db.json501-549 db.json551-605 db.json607-675

Foreign Key Cascade Rules

The schema defines specific cascade behaviors for referential integrity:

Key Design Decisions:

1. UserRole and UserLog use CASCADE DELETE: When a user account is removed, their role assignments and login history are also deleted as they have no independent value.

2. EventLog uses SET NULL ON DELETE: When a user is deleted, their audit trail entries remain in the database with user_id set to NULL. This preserves the complete audit history even after account deletion.

Sources: db.json218-230 db.json324-335 db.json405-417

JSON Data Type Usage

Several tables use MySQL's JSON column type for flexible schema:

Table	Column	Purpose
User	setting	User preferences (theme, notifications)
User	credential	WebAuthn credentials storage
User	menu	Custom menu configuration
User	style	Custom styling preferences
EventLog	source	Pre-modification state snapshot
EventLog	target	Post-modification state snapshot
CustomField	options	Field configuration options
CustomField	default_value	Default field value

JSON columns provide flexibility without requiring schema migrations for preference additions or audit data structures.

Sources: db.json85-87 db.json93-95 db.json120-126 db.json364-370 db.json641-652

Schema Migration Workflow

Migration Safety: The migration tool (mysql-schema-migrate) handles dependencies between tables, ensuring foreign key constraints are created only after both tables exist. It processes table creation in topological order based on foreign key relationships.

Sources: bin/light6 bin/light17 db.json1-676

Database Connection Configuration

The db:install command uses database credentials from environment variables:

Environment Variable	Purpose	Example
DATABASE_HOST	Database server hostname	localhost, 127.0.0.1
DATABASE_USER	Database username	root, app_user
DATABASE_PASSWORD	Database password	(secure password)
DATABASE_NAME	Database name	light, app_db
DATABASE_PORT	Database port	3306
DATABASE_CHARSET	Character set	utf8mb4

These variables should be defined in the .env file at the project root. For deployment details, see Environment Configuration.

Sources: Based on standard Light framework configuration patterns shown in architecture diagrams.

Command Output

When executing db:install, the command provides feedback on the installation process:

• Lists tables being created or modified
• Reports SQL statements executed (in verbose mode)
• Indicates successful table creation
• Reports any errors encountered during installation
• Confirms foreign key constraint creation
• Verifies index creation

The command exits with status code 0 on success, non-zero on failure, allowing integration into deployment scripts and CI/CD pipelines.

Sources: bin/light1-24