Develop

Create SDL files that define your complete database schema in a declarative format.

Getting Started

To use state-based workflow, you must start by exporting your database schema. Bytebase provides a built-in export feature that generates SDL files in the multi-file format, ready to commit to your Git repository.

Quick Start Workflow

1. Export - Click Export Schema → Multi-File (ZIP) in Bytebase database detail page
2. Extract - Unzip the downloaded file to your Git repository
3. Verify - Check the exported structure follows the multi-file format
4. Commit - Add files to Git and push to your repository
5. Make Changes - Edit the SDL files to modify your schema
6. Deploy - Commit changes to trigger deployment via GitOps

The exported ZIP contains a complete, ready-to-use schema structure. You can immediately commit it to Git without any manual organization.

How to Export Schema

1. Navigate to Database Detail Page - Go to your database in Bytebase
2. Click “Export Schema” Button - Find it in the action buttons at the top
3. Choose Export Format:
   • Multi-File (ZIP) - Recommended for GitOps workflow
     • Downloads organized schema as a ZIP file
     • Each object in its own file (e.g., schemas/public/tables/users.sql)
     • Ready to extract and commit to Git
   • Single File - All objects in one SQL file
     • Useful for quick review or legacy workflows
     • Requires manual splitting if you want multi-file format

What Gets Exported

The multi-file export automatically includes:

• Tables - With inline indexes, comments, and owned sequences
• Views - Regular views with their definitions
• Materialized Views - With their indexes and refresh settings
• Functions & Procedures - User-defined functions and stored procedures
• Sequences - Independent sequences consolidated per schema
• Comments - All object and column comments

File Organization

Bytebase uses a multi-file format where each database object is stored in a separate file, organized by schema and object type. This structure provides clear organization and makes code reviews easier.

Directory Structure

schemas/
├── public/
│   ├── tables/
│   │   ├── users.sql
│   │   ├── orders.sql
│   │   └── products.sql
│   ├── views/
│   │   ├── active_users.sql
│   │   └── order_summary.sql
│   ├── materialized_views/
│   │   └── sales_summary.sql
│   ├── functions/
│   │   └── get_user_count.sql
│   ├── procedures/
│   │   └── update_user_status.sql
│   └── sequences.sql
├── analytics/
│   ├── tables/
│   │   └── events.sql
│   └── views/
│       └── daily_stats.sql
└── internal/
    └── tables/
        └── audit_logs.sql

Organization Rules

• One object per file - Each table, view, function, etc. has its own file
• Schema-based grouping - Objects are organized under their schema directory
• Type-based subdirectories - Within each schema, objects are grouped by type (tables/, views/, etc.)
• Sequences consolidated - Independent sequences are grouped in a single sequences.sql file per schema
• Related objects bundled - Table files include their indexes, comments, and owned sequences

File Naming

Files are named after the database object they define:

• Tables: schemas/{schema}/tables/{table_name}.sql
• Views: schemas/{schema}/views/{view_name}.sql
• Materialized Views: schemas/{schema}/materialized_views/{materialized_view_name}.sql
• Functions: schemas/{schema}/functions/{function_name}.sql
• Procedures: schemas/{schema}/procedures/{procedure_name}.sql
• Sequences: schemas/{schema}/sequences.sql (consolidated)

Example: Table File Content

A table file includes the table definition and all related objects:

-- schemas/public/tables/users.sql

CREATE TABLE public.users (
    id INTEGER NOT NULL DEFAULT nextval('public.users_id_seq'::regclass),
    username TEXT NOT NULL,
    email TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_username_key UNIQUE (username),
    CONSTRAINT users_email_key UNIQUE (email)
);

COMMENT ON TABLE public.users IS 'Application users';
COMMENT ON COLUMN public.users.username IS 'Unique username for login';

CREATE INDEX idx_users_email ON public.users(email);
CREATE INDEX idx_users_created_at ON public.users(created_at);

COMMENT ON INDEX idx_users_email IS 'Index for email lookups';

-- Owned sequence
CREATE SEQUENCE public.users_id_seq;
ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;

Example: Materialized View File Content

Materialized view files include the view definition and its indexes:

-- schemas/public/materialized_views/sales_summary.sql

CREATE MATERIALIZED VIEW public.sales_summary AS
SELECT
    DATE_TRUNC('day', created_at) AS sale_date,
    COUNT(*) AS total_orders,
    SUM(amount) AS total_amount
FROM public.orders
GROUP BY sale_date;

COMMENT ON MATERIALIZED VIEW public.sales_summary IS 'Daily sales aggregation';

CREATE INDEX idx_sales_summary_date ON public.sales_summary(sale_date);

COMMENT ON INDEX idx_sales_summary_date IS 'Index on sale date for fast lookups';

SDL Syntax Requirements

SDL enforces strict conventions to ensure maintainability:

1. Schema Qualification Required

All objects must include schema prefix:

✅ Correct - Fully qualified names
CREATE TABLE public.users (...);
CREATE INDEX idx_name ON public.users(name);
CREATE FUNCTION public.get_user(...) RETURNS ...;

❌ Incorrect - Missing schema
CREATE TABLE users (...);
CREATE INDEX idx_name ON users(name);

2. Table-Level Constraints

PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK constraints must be defined at table level with explicit names:

✅ Correct - Table-level with names
CREATE TABLE public.users (
    id INTEGER NOT NULL,                    -- NOT NULL allowed at column level
    email TEXT NOT NULL,                    -- NOT NULL allowed at column level
    created_at TIMESTAMP DEFAULT NOW(),     -- DEFAULT allowed at column level
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_email_key UNIQUE (email),
    CONSTRAINT users_email_check CHECK (email LIKE '%@%')
);

❌ Incorrect - Column-level constraints
CREATE TABLE public.users (
    id INTEGER PRIMARY KEY,           -- Must be at table level
    email TEXT UNIQUE,                -- Must be at table level
    age INTEGER CHECK (age >= 0)      -- Must be at table level
);

Allowed at column level:

• NOT NULL
• DEFAULT
• GENERATED ALWAYS AS
• SERIAL / BIGSERIAL

3. Named Constraints

All table constraints require explicit names:

✅ Correct - Named constraints
CONSTRAINT users_pkey PRIMARY KEY (id)
CONSTRAINT users_email_key UNIQUE (email)
CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES public.users(id)
CONSTRAINT check_positive_age CHECK (age > 0)

❌ Incorrect - Unnamed constraints
PRIMARY KEY (id)
UNIQUE (email)
FOREIGN KEY (user_id) REFERENCES public.users(id)
CHECK (age > 0)

Naming conventions:

• Primary keys: {table}_pkey
• Unique constraints: {table}_{column}_key
• Foreign keys: fk_{table}_{referenced_table}
• Check constraints: check_{description} or {table}_{column}_check

4. Foreign Key References

Foreign keys must use fully qualified table names:

✅ Correct - Fully qualified reference
CREATE TABLE public.orders (
    id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    CONSTRAINT orders_pkey PRIMARY KEY (id),
    CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES public.users(id)
);

❌ Incorrect - Unqualified reference
CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id)

5. Named Indexes

All indexes must have explicit names:

✅ Correct - Named indexes
CREATE INDEX idx_users_email ON public.users(email);
CREATE UNIQUE INDEX idx_users_username ON public.users(username);

❌ Incorrect - Unnamed index
CREATE INDEX ON public.users(email);

Supported Statements

SDL files support these PostgreSQL statements:

• CREATE TABLE
• CREATE INDEX / CREATE UNIQUE INDEX
• CREATE VIEW
• CREATE SEQUENCE
• CREATE FUNCTION
• ALTER SEQUENCE ... OWNED BY (for serial columns)

Not Allowed:

• ALTER TABLE, DROP (Bytebase generates these)
• INSERT, UPDATE, DELETE (use migration-based workflow)
• Transaction control (BEGIN, COMMIT)

Complete Multi-File SDL Example

schemas/public/tables/users.sql

CREATE TABLE public.users (
    id INTEGER NOT NULL DEFAULT nextval('public.users_id_seq'::regclass),
    username TEXT NOT NULL,
    email TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status TEXT NOT NULL DEFAULT 'active',
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_username_key UNIQUE (username),
    CONSTRAINT users_email_key UNIQUE (email),
    CONSTRAINT check_email_format CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$'),
    CONSTRAINT check_status_values CHECK (status IN ('active', 'inactive', 'suspended'))
);

COMMENT ON TABLE public.users IS 'Application users';
COMMENT ON COLUMN public.users.email IS 'User email address (unique)';

CREATE INDEX idx_users_email ON public.users(email);
CREATE INDEX idx_users_created_at ON public.users(created_at);
CREATE INDEX idx_users_status ON public.users(status) WHERE status != 'inactive';

-- Owned sequence for id column
CREATE SEQUENCE public.users_id_seq;
ALTER SEQUENCE public.users_id_seq OWNED BY public.users.id;

schemas/public/tables/user_profiles.sql

CREATE TABLE public.user_profiles (
    user_id INTEGER NOT NULL,
    bio TEXT,
    avatar_url TEXT,
    CONSTRAINT user_profiles_pkey PRIMARY KEY (user_id),
    CONSTRAINT fk_user_profiles_users FOREIGN KEY (user_id) REFERENCES public.users(id) ON DELETE CASCADE
);

COMMENT ON TABLE public.user_profiles IS 'Extended user profile information';

schemas/public/views/active_users.sql

CREATE VIEW public.active_users AS
SELECT id, username, email, created_at
FROM public.users
WHERE status = 'active';

COMMENT ON VIEW public.active_users IS 'View of all active users';

schemas/public/functions/get_user_count.sql

CREATE FUNCTION public.get_user_count()
RETURNS INTEGER
LANGUAGE sql
AS $$
    SELECT COUNT(*) FROM public.users;
$$;

COMMENT ON FUNCTION public.get_user_count() IS 'Returns total count of users';

schemas/public/sequences.sql

-- Independent sequences (not owned by any table column)
CREATE SEQUENCE public.custom_id_seq
    START WITH 1000
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

COMMENT ON SEQUENCE public.custom_id_seq IS 'Custom sequence for special IDs';

---

Next Steps

SQL Review CI

Set up SDL validation in your CI/CD pipeline

Release

Deploy your SDL changes to databases