How drawDB Handles Table and Field Comments Across Database Systems

DrawDB treats table and field comments as first-class metadata, parsing them during SQL import and generating dialect-specific DDL syntax for MySQL, PostgreSQL, SQLite, MSSQL, OracleSQL, and others.

The open-source diagramming tool drawDB provides comprehensive comment support that persists through the entire workflow—from importing existing schemas to exporting clean, executable SQL. Each database system receives tailored handling based on its native comment syntax, ensuring compatibility without manual intervention.

Comment Storage in drawDB's Data Model

When you import SQL into drawDB, the parsers in src/utils/importSQL/*.js extract comments and assign them to table.comment and field.comment properties. The internal representation remains consistent regardless of source database:

const table = {
  name: "users",
  comment: "Stores application users",
  fields: [
    { name: "id", type: "INT", primary: true, comment: "Primary key" },
    { name: "email", type: "VARCHAR", notNull: true, comment: "User email" },
  ],
};

Empty strings represent absent comments, so downstream logic simply checks field.comment?.trim() to determine whether to emit comment syntax.

MySQL and MariaDB: Inline COMMENT Clauses

MySQL and MariaDB use the most straightforward approach: inline COMMENT keywords appended directly to DDL statements.

In src/utils/migrations/diffToSQL.js, table-level comments are injected via string replacement:

// Table comment for MySQL/MariaDB (lines 47-52)
if ([DB.MYSQL, DB.MARIADB].includes(db) && table.comment?.trim()) {
  createTableSQL = createTableSQL.replace(
    /;\s*$/,
    ` COMMENT='${escapeString(table.comment)}';`
  );
}

Column comments append directly to column definitions in src/utils/exportSQL/mysql.js:

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT NOT NULL PRIMARY KEY COMMENT 'Primary key',
  `email` VARCHAR NOT NULL COMMENT 'User email'
) COMMENT='Stores application users';

PostgreSQL: Separate COMMENT ON Statements

PostgreSQL requires post-creation COMMENT ON statements rather than inline syntax. DrawDB generates these sequentially after the CREATE TABLE block.

From src/utils/migrations/diffToSQL.js (lines 55-66):

// Table comment as separate statement
exportSQL += `COMMENT ON TABLE "${table.name}" IS '${escapeString(table.comment)}';\n`;

// Column comments
fieldsWithComments.forEach(f => {
  exportSQL += `COMMENT ON COLUMN "${table.name}"."${f.name}" IS '${escapeString(f.comment)}';\n`;
});

Generated output preserves column order with inline SQL comments for readability:

CREATE TABLE IF NOT EXISTS "users" (
  -- Primary key
  "id" INT NOT NULL PRIMARY KEY,
  -- User email
  "email" VARCHAR NOT NULL
);
COMMENT ON TABLE "users" IS 'Stores application users';
COMMENT ON COLUMN "users"."id" IS 'Primary key';
COMMENT ON COLUMN "users"."email" IS 'User email';

Microsoft SQL Server: Extended Properties

MSSQL stores descriptions through system stored procedures. DrawDB invokes sp_addextendedproperty with fully qualified object paths.

From src/utils/exportSQL/mssql.js (lines 77-83), the generateAddExtendedPropertySQL helper produces:

CREATE TABLE [users] (
  [id] INT NOT NULL PRIMARY KEY,
  [email] VARCHAR NOT NULL
);
GO
EXEC sys.sp_addextendedproperty 
  @name=N'MS_Description', 
  @value=N'Stores application users',
  @level0type=N'SCHEMA', @level0name=N'dbo',
  @level1type=N'TABLE', @level1name=N'users';
GO
EXEC sys.sp_addextendedproperty 
  @name=N'MS_Description', 
  @value=N'Primary key',
  @level0type=N'SCHEMA', @level0name=N'dbo',
  @level1type=N'TABLE', @level1name=N'users',
  @level2type=N'COLUMN', @level2name=N'id';

Each column requires a separate procedure call with hierarchical level specifications.

SQLite: Block Comment Prefixes

SQLite lacks native comment syntax for schema objects. DrawDB works around this limitation by prepending C-style block comments before the DDL.

In src/utils/exportSQL/sqlite.js (lines 15-19):

if (table.comment?.trim()) {
  exportSQL = `/* ${table.comment} */\n` + exportSQL;
}

Column comments receive similar treatment with inline block comments placed immediately before relevant definitions:

/* Stores application users */
CREATE TABLE IF NOT EXISTS "users" (
  "id" INTEGER NOT NULL PRIMARY KEY,
  "email" TEXT NOT NULL,
  "bio" TEXT
);

OracleSQL: Inline Line Comments

OracleSQL uses standard SQL line comments (--) positioned after closing delimiters for tables and inline for columns.

From src/utils/exportSQL/oraclesql.js (lines 13-18, 34-43):

CREATE TABLE "users" (
  "id" NUMBER NOT NULL PRIMARY KEY, -- Primary key
  "email" VARCHAR2(255) NOT NULL   -- User email
) -- Stores application users;

Oracle's syntax constraints prevent block comments in these positions, making line comments the pragmatic choice.

Generic Fallback Dialect

For databases without dedicated export modules, src/utils/exportSQL/generic.js implements progressive fallback logic (lines 215-230):

• Table comments: Attempt MySQL-style COMMENT='...' syntax; fall back to /* ... */ block prefix
• Field comments: Use COMMENT '...' when supported; otherwise append -- ... inline

This ensures meaningful output even for unsupported or emerging database systems.

Visual Rendering in the Diagram Canvas

Comments affect layout calculations in src/utils/utils.js (lines 76-132). The measureCommentHeight function caches text dimensions to prevent reflow thrashing, while the showComments boolean flag controls visibility. When enabled, comments render beneath field names with appropriate vertical spacing based on the cached measurements.

Summary

• Import: All SQL parsers populate table.comment and field.comment properties with extracted text
• MySQL/MariaDB: Inline COMMENT clauses for both tables and columns
• PostgreSQL: Separate COMMENT ON TABLE/COLUMN statements post-creation
• MSSQL: sp_addextendedproperty procedure calls with hierarchical object references
• SQLite: Block comment prefixes since native metadata syntax doesn't exist
• OracleSQL: Inline line comments (--) positioned after definitions
• Generic fallback: Progressive degradation from native syntax to block or line comments
• UI: Height-cached rendering with toggleable visibility via showComments

Frequently Asked Questions

Does drawDB preserve comments when converting between database systems?

Yes. Comments live in the intermediate JSON representation independently of any database syntax. When you switch export targets, drawDB regenerates appropriate DDL without losing the underlying comment text.

Why does SQLite output differ so much from other databases?

SQLite's engine doesn't support attaching descriptive metadata to tables or columns through SQL syntax. DrawDB chooses block comments as the most compatible workaround— human-readable and preserved in schema dumps without affecting execution.

Can I disable comments in exported SQL?

DrawDB respects non-empty comments by default, but you can clear comment properties in the UI before export. There's no global "strip comments" flag; the tool assumes intentional documentation should persist.

How does drawDB handle special characters in comments?

All export modules use escapeString() utility functions (implementation varies by dialect) to sanitize single quotes, backslashes, and other characters that would break SQL literal syntax.

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