DrawDB Internal Data Model: How Tables, Fields, and Relationships Are Structured

DrawDB stores every diagram as a JSON document validated against JSON-Schema definitions, with tables, fields, and relationships defined as discrete, strongly-typed objects in src/data/schemas.js.

The drawDB database design tool uses a schema-driven architecture to represent entity-relationship diagrams programmatically. Understanding this internal data model is essential for developers building importers, exporters, or automation that interacts with drawDB files.

---

Top-Level Diagram Structure

The root schema (jsonSchema in src/data/schemas.js) defines a diagram as a composite object containing:

• tables — array of table objects
• relationships — array of relationship connectors
• notes — free-form annotations
• subjectAreas — visual grouping regions
• types — custom data type definitions
• enums — enumerated value sets

Every diagram is a single JSON document that conforms to this structure, making the format portable and version-control friendly.

---

Table Schema in drawDB

The tableSchema definition (src/data/schemas.js, lines 1–79) specifies all properties that describe a table entity.

{
  "id": ["integer", "string"],
  "name": "string",
  "x": "number",
  "y": "number",
  "fields": [ { …field objects… } ],
  "indices": [
    {
      "name": "string",
      "unique": true,
      "fields": ["string"]
    }
  ],
  "uniqueConstraints": [
    {
      "name": "string",
      "fields": ["string"]
    }
  ],
  "comment": "string",
  "color": "#RRGGBB",
  "inherits": ["string"],
  "locked": true,
  "hidden": false,
  "collapsed": false
}

Key architectural decisions:

• Flexible identifiers: The id field accepts integers or strings, supporting both auto-incrementing keys and client-generated UUIDs
• Spatial properties: x and y coordinates enable precise canvas positioning
• Inheritance: The inherits array supports table inheritance (primarily for PostgreSQL-style models)
• UI state flags: locked, hidden, and collapsed preserve editor state without affecting the semantic model

---

Field Schema: Column Definitions

Fields are nested objects within a table's fields array. The schema (src/data/schemas.js, lines 9–38) captures complete column metadata:

{
  "id": ["integer", "string"],
  "name": "string",
  "type": "string",
  "default": ["string", "number", "boolean"],
  "check": "string",
  "primary": true,
  "unique": true,
  "notNull": true,
  "increment": true,
  "comment": "string",
  "size": ["string", "number"],
  "values": ["string"]
}

Constraint flags (primary, unique, notNull, increment) are boolean toggles rather than separate objects, keeping the structure flat and serialization-efficient. The values array supports enum-like value sets directly on fields.

---

Relationship Schema: Foreign Key Connections

Relationships in drawDB are independent top-level objects rather than nested table properties. This design allows many-to-many relationships and self-referencing tables without constraint duplication.

{
  "startTableId": ["integer", "string"],
  "startFieldId": ["integer", "string"],
  "endTableId": ["integer", "string"],
  "endFieldId": ["integer", "string"],
  "name": "string",
  "cardinality": "string",
  "updateConstraint": "string",
  "deleteConstraint": "string",
  "id": ["integer", "string"]
}

Endpoint addressing: Relationships reference tables and fields by id, creating a loose coupling that allows tables to be renamed or restructured without breaking foreign key references.

Referential integrity options: updateConstraint and deleteConstraint accept standard SQL action strings (CASCADE, SET NULL, RESTRICT, NO ACTION, SET DEFAULT).

---

Practical JSON Examples

Creating a Users Table

{
  "id": "tbl_users_001",
  "name": "users",
  "x": 150,
  "y": 80,
  "fields": [
    {
      "id": "fld_id_001",
      "name": "id",
      "type": "uuid",
      "primary": true,
      "increment": false,
      "notNull": true
    },
    {
      "id": "fld_email_001",
      "name": "email",
      "type": "varchar",
      "size": 255,
      "unique": true,
      "notNull": true
    }
  ],
  "indices": [],
  "uniqueConstraints": [],
  "comment": "Stores user accounts",
  "color": "#4A90E2",
  "inherits": []
}

Defining a One-to-Many Relationship

{
  "startTableId": "tbl_users_001",
  "startFieldId": "fld_email_001",
  "endTableId": "tbl_orders_002",
  "endFieldId": "fld_customer_email_003",
  "name": "user_orders",
  "cardinality": "1:N",
  "updateConstraint": "CASCADE",
  "deleteConstraint": "SET NULL",
  "id": "rel_001"
}

Minimal Complete Diagram

{
  "title": "E-Commerce Model",
  "database": "postgres",
  "tables": [ /* table objects */ ],
  "relationships": [ /* relationship objects */ ],
  "notes": [],
  "subjectAreas": [],
  "types": [],
  "enums": []
}

---

Persistence Layer: Dexie.js and IndexedDB

DrawDB persists diagram JSON through Dexie.js, a Promise-based wrapper over IndexedDB. The database configuration in src/data/db.js defines a diagrams object store where the complete JSON document is saved as a single record.

This approach provides:

• Offline-first capability — all data local to the browser
• Atomic saves — entire diagram state written transactionally
• Schema evolution — versioned migrations handled by Dexie

---

Key Source Files

File	Purpose
src/data/schemas.js	JSON-Schema definitions for all diagram components
src/data/db.js	Dexie database initialization and persistence logic
src/data/seeds.js	Default templates and sample data
src/utils/dbml/parse.js	DBML-to-JSON parser using these schemas
src/utils/dbml/applyPlan.js	Schema-driven mutation application

---

Summary

• DrawDB's data model is a JSON-Schema-validated document with tables, fields, and relationships as distinct object types
• Tables carry spatial coordinates (x, y), visual state, and nested field arrays
• Fields embed all constraints (primary, unique, notNull, increment) as boolean flags
• Relationships are top-level objects with explicit endpoint IDs, enabling flexible connection patterns
• All identifiers accept integers or strings for UUID compatibility
• Persistence uses Dexie.js over IndexedDB, storing complete diagram JSON atomically

---

Frequently Asked Questions

What format does drawDB use to save diagrams?

DrawDB saves diagrams as JSON documents conforming to schemas defined in src/data/schemas.js. The JSON is stored in the browser's IndexedDB via Dexie.js, not sent to a server by default. This JSON can be exported, version-controlled, and re-imported.

Can drawDB handle composite primary keys?

Yes. The primary flag on fields is boolean, so multiple fields within a table can set primary: true. The schema does not enforce single-column primary keys at the validation level—this matches how drawDB represents composite keys visually and in SQL generation.

How does drawDB represent many-to-many relationships?

Many-to-many relationships use the same relationship object structure, with appropriate cardinality values like "M:N". The relationship references endpoint tables and fields by ID rather than embedding connection data, allowing any cardinality pattern without structural changes.

Are drawDB diagram files portable between databases?

Yes. The internal JSON model is database-agnostic. The database field at the root level ("postgres", "mysql", "sqlite", etc.) drives SQL dialect generation, but the core table-field-relationship structure remains consistent across targets.

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