> ## Documentation Index
> Fetch the complete documentation index at: https://docs.bytebase.com/llms.txt
> Use this file to discover all available pages before exploring further.

# 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](#file-organization)
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

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

### 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:

```sql theme={null}
-- 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:

```sql theme={null}
-- 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:

```sql theme={null}
✅ Correct - Fully qualified names
CREATE TABLE public.users (...);
CREATE INDEX idx_name ON public.users(name);
CREATE FUNCTION public.get_user(...) RETURNS ...;
```

```sql theme={null}
❌ 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:

```sql theme={null}
✅ 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 '%@%')
);
```

```sql theme={null}
❌ 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:

```sql theme={null}
✅ 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)
```

```sql theme={null}
❌ 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:

```sql theme={null}
✅ 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)
);
```

```sql theme={null}
❌ Incorrect - Unqualified reference
CONSTRAINT fk_orders_users FOREIGN KEY (user_id) REFERENCES users(id)
```

### 5. Named Indexes

All indexes must have explicit names:

```sql theme={null}
✅ Correct - Named indexes
CREATE INDEX idx_users_email ON public.users(email);
CREATE UNIQUE INDEX idx_users_username ON public.users(username);
```

```sql theme={null}
❌ 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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
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

```sql theme={null}
-- 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

<CardGroup cols={2}>
  <Card title="SQL Review CI" icon="shield-check" href="/gitops/state-based-workflow/sql-review-ci">
    Set up SDL validation in your CI/CD pipeline
  </Card>

  <Card title="Release" icon="rocket" href="/gitops/state-based-workflow/release">
    Deploy your SDL changes to databases
  </Card>
</CardGroup>