7.3. DDL Writing Standards

7.3.1. CREATE TABLE

-- V1__create_users_table.sql
CREATE TABLE users (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    password    VARCHAR(255) NOT NULL,
    is_active   BOOLEAN NOT NULL DEFAULT true,
    created_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
    updated_at  TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX uniq_users_email ON users (email);

Writing rules:

• Column definitions must be aligned with consistent indentation.
• NOT NULL constraints must be declared explicitly. Nullable columns are only permitted when intentional.
• Columns with default values must include a DEFAULT declaration.
• Indexes must be included in the same migration file.

7.3.2. ALTER TABLE

-- V3__add_phone_to_users.sql
ALTER TABLE users
    ADD COLUMN phone VARCHAR(20);

-- V4__add_not_null_constraint_to_phone.sql
UPDATE users SET phone = '' WHERE phone IS NULL;

ALTER TABLE users
    ALTER COLUMN phone SET NOT NULL;

Caution

When adding a NOT NULL constraint, existing NULL values must be handled first.

7.3.3. Index Creation

-- V5__create_idx_orders_user_id.sql
CREATE INDEX idx_orders_user_id ON orders (user_id);

CREATE INDEX CONCURRENTLY idx_orders_created_at
    ON orders (created_at);

• The CONCURRENTLY option should be used when adding indexes to large tables in production environments.

Caution

CREATE INDEX CONCURRENTLY cannot be executed inside a transaction. To use this statement in a Flyway migration, it must be separated into its own migration file.

7.3.4. Seed Data

Initial data (roles, codes, etc.) must be managed through migration files.

-- V6__insert_initial_roles.sql
INSERT INTO roles (name, description) VALUES
    ('ADMIN', 'System Administrator'),
    ('USER', 'General User'),
    ('MANAGER', 'Manager');

Test data must not be included in migrations. Test data must be generated directly in test code.