# How drawDB Manages Database Indexes: Creation, Validation, and Export

> Discover how drawDB manages database indexes from creation to export. Learn about its robust lifecycle support for indices within your schema management.

- Repository: [drawDB/drawdb](https://github.com/drawdb-io/drawdb)
- Tags: internals
- Published: 2026-08-14

---

**drawDB treats database indexes as first-class elements of table objects, storing them in an `indices` array that supports full lifecycle management from UI creation to SQL export, import parsing, validation, and migration diffing.**

drawDB is an open-source database diagramming tool that provides comprehensive **drawDB index management** capabilities through its React-based frontend. The application implements a complete index lifecycle that allows users to create, edit, validate, and export index definitions across multiple SQL dialects including PostgreSQL, MySQL, SQLite, and SQL Server.

## Data Structure: Indexes as Table Properties

At the core of drawDB's architecture, every table definition maintains an `indices` array that stores index objects as part of the table's state. When a user creates a table, the in-memory representation follows this structure:

```json
{
  "name": "users",
  "fields": [ ... ],
  "indices": []
}

```

Each index object contains:
- `id`: A unique identifier (typically the array index)
- `name`: The index name following the pattern `{tableName}_index_{n}`
- `unique`: A boolean flag indicating if the index enforces uniqueness
- `fields`: An ordered array of column names included in the index

## Creating Indexes via the UI

### The addIndex Function

When a user clicks **Add → Index** in the table-side panel, the `addIndex` function in [`src/components/EditorSidePanel/TablesTab/TableInfo.jsx`](https://github.com/drawdb-io/drawdb/blob/main/src/components/EditorSidePanel/TablesTab/TableInfo.jsx) creates a fresh index entry and appends it to the table's `indices` array:

```javascript
// Build a new index object and push it onto the table's indices array
updateTable(data.id, {
  indices: [
    ...data.indices,
    {
      id: data.indices.length,
      name: `${data.name}_index_${data.indices.length}`,
      unique: false,
      fields: [],
    },
  ],
});

```

The new index receives:
- A generated `id` based on the current length of the array
- A default `name` using the template `${tableName}_index_${n}`
- `unique: false` and an empty `fields` list, ready for column selection

### Editing with IndexDetails

The UI renders each entry via the **`IndexDetails`** component (rendered inside the `Collapse.Panel`), allowing users to edit the index name, toggle the uniqueness constraint, and select referenced fields.

## Exporting Indexes to SQL and DBML

### SQL Export

When exporting diagrams to SQL, the generators in `src/utils/exportSQL/` transform index objects into appropriate DDL statements. The generic exporter (found in [`src/utils/exportSQL/generic.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportSQL/generic.js)) constructs `CREATE INDEX` clauses:

```javascript
${table.indices
  .map(
    (i) => `CREATE ${i.unique ? "UNIQUE " : ""}INDEX ${i.name} ON ${table.name} (${i.fields.join(
      ", "
    )});`
  )
  .join("\n")}

```

This produces standard SQL output such as:

```sql
CREATE INDEX orders_index_0 ON orders (customer_id);
CREATE UNIQUE INDEX orders_index_1 ON orders (order_date);

```

### DBML Export

For DBML (Database Markup Language) export, the `indexesBlock` helper in [`src/utils/exportAs/dbml.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/exportAs/dbml.js) renders index definitions using DBML syntax:

```javascript
indexes {
  ${table.indices.map(i => `index ${i.name} ${i.unique ? "unique" : ""} (${i.fields.join(", ")});`).join("\n")}
}

```

Both exporters preserve the `name`, `unique` flag, and ordered `fields` list from the in-memory representation.

## Importing Existing Indexes from SQL

When reverse-engineering existing schemas, drawDB's SQL parsers (located in `src/utils/importSQL/`) detect `INDEX` statements and populate the `indices` array.

### SQLite Parser Implementation

In [`src/utils/importSQL/sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/importSQL/sqlite.js), the `parseSingleStatement` function handles index definitions when `e.keyword === "index"`:

```javascript
const index = {
  name: e.index,
  unique: e.index_type === "unique",
  fields: e.index_columns.map(f => f.column),
};
table.indices.push(index);

```

The same parsing pattern exists across PostgreSQL, MySQL, and MSSQL importers, ensuring cross-dialect consistency when importing existing database schemas.

## Validation and Error Detection

Before allowing export or saving, drawDB runs a validation pass through [`src/utils/issues.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/issues.js) to detect common index problems:

- **Duplicate index names** (`duplicate_index`): Checks for conflicting names within a table (lines 110-113)
- **Empty index names** (`empty_index_name`): Validates that every index has a defined name (line 124)
- **Indexes without fields** (`empty_index`): Ensures indexes reference at least one column (line 127)

When issues are detected, the UI surfaces warnings to the user, preventing the generation of broken or invalid DDL.

## Migration Diff Generation

For schema versioning and change tracking, [`src/utils/migrations/diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/migrations/diffToSQL.js) compares index states between schema versions. The diff engine iterates over `table.indices` (line 174) to generate the appropriate migration statements:

- `DROP INDEX` statements for removed indexes
- `CREATE INDEX` statements for newly added indexes
- `DROP INDEX` followed by `CREATE INDEX` for renamed or modified indexes

This enables users to generate migration scripts that accurately reflect index additions, removals, or structural changes.

## Summary

- **drawDB stores indexes as objects** in a table's `indices` array with properties for `id`, `name`, `unique`, and `fields`
- **UI creation** occurs through [`TableInfo.jsx`](https://github.com/drawdb-io/drawdb/blob/main/TableInfo.jsx), which uses the `addIndex` function to generate default index objects
- **SQL and DBML export** modules transform the `indices` array into dialect-specific `CREATE INDEX` statements
- **SQL import** parsers reverse-engineer `INDEX` statements from existing schemas into the internal `indices` format
- **Validation** in [`issues.js`](https://github.com/drawdb-io/drawdb/blob/main/issues.js) prevents duplicates, empty names, and field-less indexes before export
- **Migration support** via [`diffToSQL.js`](https://github.com/drawdb-io/drawdb/blob/main/diffToSQL.js) generates `DROP` and `CREATE` statements for schema changes

## Frequently Asked Questions

### How does drawDB store index definitions internally?

drawDB stores indexes as objects within each table's `indices` array, where every index contains an `id`, `name`, `unique` boolean, and an ordered `fields` array listing the column names. This structure exists in the in-memory state managed by the application's React components.

### Can drawDB export indexes to different SQL dialects?

Yes, drawDB exports indexes to multiple dialects through dedicated generators in `src/utils/exportSQL/`. The generic exporter handles standard `CREATE [UNIQUE] INDEX` syntax, while dialect-specific modules customize output for PostgreSQL, MySQL, SQLite, and SQL Server nuances.

### How does drawDB validate index definitions?

The validation logic in [`src/utils/issues.js`](https://github.com/drawdb-io/drawdb/blob/main/src/utils/issues.js) checks for three specific problems: duplicate index names within a table, empty index names, and indexes that don't reference any fields. These checks run before export to prevent generation of invalid DDL.

### Does drawDB support importing indexes from existing SQL files?

Yes, when importing SQL scripts, the parsers in `src/utils/importSQL/` (including [`sqlite.js`](https://github.com/drawdb-io/drawdb/blob/main/sqlite.js), [`postgres.js`](https://github.com/drawdb-io/drawdb/blob/main/postgres.js), and [`mysql.js`](https://github.com/drawdb-io/drawdb/blob/main/mysql.js)) detect `INDEX` statements and reconstruct the internal `indices` array. The parsers extract the index name, uniqueness flag, and column list to populate the diagram accurately.