schema-consistency-checker

Schema Consistency Checker

Enforce schema consistency and best practices across your database.

Consistency Rules

1. Naming Conventions

// naming-rules.ts
export const NAMING_RULES = {
  tables: {
    pattern: /^[A-Z][a-zA-Z0-9]*$/, // PascalCase
    examples: ["User", "OrderItem", "ProductCategory"],
  },
  columns: {
    pattern: /^[a-z][a-zA-Z0-9]*$/, // camelCase
    examples: ["id", "firstName", "createdAt"],
  },
  indexes: {
    pattern: /^idx_[a-z_]+$/, // idx_table_column
    examples: ["idx_users_email", "idx_orders_user_id"],
  },
  foreignKeys: {
    pattern: /^fk_[a-z_]+$/, // fk_table_column
    examples: ["fk_orders_user_id", "fk_products_category_id"],
  },
  constraints: {
    pattern: /^(chk|unq)_[a-z_]+$/, // chk_ or unq_prefix
    examples: ["chk_age_positive", "unq_users_email"],
  },
};

2. Type Consistency

-- ❌ Bad: Inconsistent types for IDs
CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  email TEXT
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,  -- ❌ Different ID type
  user_id TEXT            -- ❌ Wrong type for FK
);

-- ✅ Good: Consistent types
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email TEXT
);

CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT REFERENCES users(id)
);

3. Nullability Patterns

-- ❌ Bad: Inconsistent NULL handling
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email TEXT,              -- ❌ No NOT NULL on critical field
  name TEXT,               -- ❌ Should be NOT NULL
  phone TEXT NULL,         -- ⚠️ Explicit NULL unnecessary
  created_at TIMESTAMP     -- ❌ Missing NOT NULL
);

-- ✅ Good: Clear nullability
CREATE TABLE users (
  id BIGINT PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  phone TEXT,              -- Optional field
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

4. Missing Constraints

-- ❌ Bad: Missing constraints
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,          -- ❌ Missing FK
  status TEXT,             -- ❌ No CHECK constraint
  total DECIMAL(10,2),     -- ❌ No CHECK for positive
  created_at TIMESTAMP
);

-- ✅ Good: Proper constraints
CREATE TABLE orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  status TEXT NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'delivered')),
  total DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  created_at TIMESTAMP NOT NULL DEFAULT NOW()
);