Back to Learning Hub

Create Table

20 minutes BEGINNER

Learn how to create the Table layer - the database access layer. Master defining database structure, schema constraints, and query methods.

What You'll Learn

Understanding Table

What is the Table layer and its responsibilities

Schema Definition

Define database structure with _type_map and defineSchema()

Table Migration

Migrate table to database using db:migrate

Video Coming Soon...

What is Table Layer?

The Table Layer is the database access layer in GEMVC. It's the fourth and final layer in the 4-layer architecture and provides direct database operations.

  • Database Structure - Defines table structure with properties matching database columns
  • Schema Definition - Defines indexes, unique constraints, foreign keys, and other database constraints
  • Query Methods - Provides custom query methods (e.g., selectById(), selectByName())
  • Database Operations - Uses fluent query builder for database queries

Key Principle: The Table layer defines the database structure and provides query methods. It's the foundation that the Model layer extends to add business logic.

1

Understanding the Structure

Basic Table Structure

Every Table class must extend Table and implement three required components:

  1. Properties - Match database column names exactly
  2. $_type_map - Maps properties to PHP types
  3. getTable() - Returns database table name
  4. defineSchema() - Defines database constraints
app/table/ProductTable.php 
<?php
namespace App\Table;

use Gemvc\Database\Table;
use Gemvc\Database\Schema;

class ProductTable extends Table
{
    // Properties match database columns
    public int $id;
    public string $name;
    public float $price;
    public ?string $description;
    
    // Type mapping for database operations
    protected array $_type_map = [
        'id' => 'int',
        'name' => 'string',
        'price' => 'float',
        'description' => 'string',
    ];
    
    public function __construct()
    {
        parent::__construct();
    }
    
    // Required: Return database table name
    public function getTable(): string
    {
        return 'products';
    }
    
    // Required: Define database schema constraints
    public function defineSchema(): array
    {
        return [
            Schema::primary('id'),
            Schema::autoIncrement('id'),
            Schema::index('name'),
        ];
    }
}

Key Points:

  • ✓ Extends Table base class
  • ✓ Properties match database column names exactly
  • ✓ $_type_map maps all properties to PHP types
  • ✓ getTable() returns the database table name
  • ✓ defineSchema() defines indexes, unique constraints, etc.
2

Properties and TypeMap

Defining Properties

Properties in your Table class must match database column names exactly. Use PHP type hints to define the column types.

app/table/ProductTable.php - Properties Example 
<?php
class ProductTable extends Table
{
    // Properties match database columns exactly
    public int $id;                    // Maps to 'id' column (INT)
    public string $name;               // Maps to 'name' column (VARCHAR)
    public float $price;               // Maps to 'price' column (DOUBLE)
    public ?string $description;       // Maps to 'description' column (VARCHAR NULL)
    protected string $password;        // Protected = not returned in SELECT queries
    
    // Type mapping - MUST include all properties
    protected array $_type_map = [
        'id' => 'int',              // PHP int → SQL INT(11)
        'name' => 'string',         // PHP string → SQL VARCHAR(255)
        'price' => 'float',         // PHP float → SQL DOUBLE
        'description' => 'string',  // PHP string → SQL VARCHAR(255)
        'password' => 'string',     // Include protected properties too
    ];
}

Property Type Mapping:

  • int → SQL INT(11)
  • float → SQL DOUBLE
  • bool → SQL TINYINT(1)
  • string → SQL VARCHAR(255)
  • ?string → SQL VARCHAR(255) NULL (nullable)

Important Rules:

  • ✓ Property names must match column names exactly (case-sensitive)
  • ✓ Include ALL properties in $_type_map (including protected ones)
  • ✓ Use protected for sensitive data (not returned in SELECT queries)
  • ✓ Use ? prefix for nullable types (e.g., ?string)
3

Schema Definition

Defining Database Constraints

The defineSchema() method defines database constraints like primary keys, indexes, unique constraints, and foreign keys.

app/table/ProductTable.php - Schema Example 
<?php
public function defineSchema(): array
{
    return [
        // Primary key with auto increment
        Schema::primary('id'),
        Schema::autoIncrement('id'),
        
        // Unique constraint
        Schema::unique('name'),
        
        // Indexes for performance
        Schema::index('price'),
        Schema::index('description'),
    ];
}

Common Schema Constraints:

  • Schema::primary('id') - Primary key
  • Schema::autoIncrement('id') - Auto increment
  • Schema::unique('email') - Unique constraint
  • Schema::index('name') - Index for performance
  • Schema::foreignKey('category_id', 'categories.id') - Foreign key

Note: You can return an empty array [] if you don't need any constraints, but it's recommended to at least define a primary key and indexes for better performance.

4

Query Methods

Custom Query Methods

You can create custom query methods using the fluent query builder. These methods are typically used by the Model layer.

app/table/ProductTable.php - Query Methods Example 
<?php
// Select product by ID
public function selectById(int $id): null|static
{
    $result = $this->select()->where('id', $id)->limit(1)->run();
    return $result[0] ?? null;
}

// Select product by name
public function selectByName(string $name): null|static
{
    $result = $this->select()->where('name', $name)->limit(1)->run();
    return $result[0] ?? null;
}

// Select products by price range
public function selectByPriceRange(float $min, float $max): array
{
    return $this->select()
        ->where('price', '>=', $min)
        ->where('price', '<=', $max)
        ->orderBy('price', 'ASC')
        ->run();
}

Query Builder Methods:

  • select() - Start SELECT query
  • where('column', 'value') - Add WHERE condition
  • where('column', '>=', 'value') - Comparison operators
  • orderBy('column', 'ASC') - Order results
  • limit(10) - Limit results
  • run() - Execute query and return results

Return Types: Query methods should return null|static for single records or array for multiple records.

5

Complete Example

Full ProductTable Example

Here's a complete example of a Table class with all required components:

app/table/ProductTable.php - Complete Example 
<?php
namespace App\Table;

use Gemvc\Database\Table;
use Gemvc\Database\Schema;

class ProductTable extends Table
{
    // Properties match database columns
    public int $id;
    public string $name;
    public float $price;
    public ?string $description;
    
    // Type mapping - MUST include all properties
    protected array $_type_map = [
        'id' => 'int',
        'name' => 'string',
        'price' => 'float',
        'description' => 'string',
    ];
    
    public function __construct()
    {
        parent::__construct();
    }
    
    // Required: Return database table name
    public function getTable(): string
    {
        return 'products';
    }
    
    // Required: Define database schema constraints
    public function defineSchema(): array
    {
        return [
            Schema::primary('id'),
            Schema::autoIncrement('id'),
            Schema::unique('name'),
            Schema::index('price'),
        ];
    }
    
    // Custom query methods
    public function selectById(int $id): null|static
    {
        $result = $this->select()->where('id', $id)->limit(1)->run();
        return $result[0] ?? null;
    }
    
    public function selectByName(string $name): null|static
    {
        $result = $this->select()->where('name', $name)->limit(1)->run();
        return $result[0] ?? null;
    }
}

Required Components Checklist:

  • ✓ Extends Table class
  • ✓ Properties match database column names
  • ✓ $_type_map includes all properties
  • ✓ getTable() returns table name
  • ✓ defineSchema() returns schema array
  • ✓ Custom query methods (optional but recommended)

Important Notes

  • Property Names Must Match: Property names must match database column names exactly (case-sensitive).
  • Include All Properties in TypeMap: Every property that maps to a database column must be included in $_type_map.
  • Use Protected for Sensitive Data: Properties marked as protected are not returned in SELECT queries (e.g., passwords).
  • Naming Convention: Use PascalCase + "Table" suffix (e.g., ProductTable.php, UserTable.php).
6

Create Your Table

Manual Creation

To create a Table class, simply create a new PHP class file in the app/table/ directory. For example, to create a Product Table:

File Location:

Create app/table/ProductTable.php

The class name should match the filename (e.g., ProductTable.php → class ProductTable)

Example: Create a Product Table

app/table/ProductTable.php 
<?php
namespace App\Table;

use Gemvc\Database\Table;
use Gemvc\Database\Schema;

class ProductTable extends Table
{
    public int $id;
    public string $name;
    public float $price;
    public ?string $description;
    
    protected array $_type_map = [
        'id' => 'int',
        'name' => 'string',
        'price' => 'float',
        'description' => 'string',
    ];
    
    public function __construct()
    {
        parent::__construct();
    }
    
    public function getTable(): string
    {
        return 'products';
    }
    
    public function defineSchema(): array
    {
        return [
            Schema::primary('id'),
            Schema::autoIncrement('id'),
            Schema::unique('name'),
            Schema::index('price'),
        ];
    }
    
    public function selectById(int $id): null|static
    {
        $result = $this->select()->where('id', $id)->limit(1)->run();
        return $result[0] ?? null;
    }
}

✨ Table Structure:

The Table class defines the database structure. Once created, you can migrate it to the database to create the actual table.

Alternative: Use CLI Command

You can also use the GEMVC CLI command to generate the Table file automatically:

Terminal 
gemvc create:table Product
7

Migrate Table to Database

Creating the Database Table

After creating your Table class, you need to migrate it to the database. This will create the actual database table with all the columns, indexes, and constraints you defined.

Migration Command:

Use the gemvc db:migrate command to create the table in your database:

Terminal 
gemvc db:migrate ProductTable

Note: Use the class name (e.g., ProductTable), not the file name.

What Happens During Migration:

  • ✓ Creates the database table if it doesn't exist
  • ✓ Creates all columns based on properties and $_type_map
  • ✓ Creates primary keys, indexes, and unique constraints from defineSchema()
  • ✓ Updates existing tables if columns or constraints changed

Migration Flags:

  • --force - Remove columns not in class definition
  • --sync-schema - Sync schema constraints (indexes, unique, etc.)
  • --default="value" - Set default value for new columns

Example: gemvc db:migrate ProductTable --force --sync-schema

✅ Your CRUD API is Complete!

All 4 layers are now created and the database table is migrated.

What You've Built:

app/api/Product.php - API Service
app/controller/ProductController.php - Controller
app/model/ProductModel.php - Model
app/table/ProductTable.php - Table
products - Database table created

🎉 CRUD API Complete!

Congratulations! You've successfully created all 4 layers and migrated the table. Your Product CRUD API is now ready to use!

Back to CRUD Hub Previous: Create Model Learning Hub