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

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:

{
  "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 creates a fresh index entry and appends it to the table's indices array:

// 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) constructs CREATE INDEX clauses:

${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:

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 renders index definitions using DBML syntax:

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, the parseSingleStatement function handles index definitions when e.keyword === "index":

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 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 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, 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 prevents duplicates, empty names, and field-less indexes before export
• Migration support via 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 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, postgres.js, and 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.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}