PostgreSQL Schema Manager
Code module for AI agents to introspect, migrate, and seed PostgreSQL databases with safe, reversible migrations.
Code is provided "as is". Review and test before production use. Terms
Built by Thomas
@thomas
PostgreSQL schema introspection, migration, and seeding toolkit. Provides functions to list tables, inspect column/index details, run SQL migrations inside transactions with auto-rollback, insert seed data, take full schema snapshots, and diff snapshots to detect changes.
- Introspect PostgreSQL table schemas including columns, types, and indexes
- Run SQL migrations wrapped in transactions with automatic rollback on error
- Seed database tables with provided data rows
- Take schema snapshots and diff them to detect structural changes
Step 1: Install the pg dependency
npm install pgValidation: pg should appear in package.json dependencies
Step 2: Import SchemaManager and connect
File: your database utility file
import { SchemaManager } from "./src/index";
const db = new SchemaManager(process.env.DATABASE_URL!);Step 3: Use schema introspection
const tables = await db.listTables();
const schema = await db.getTableSchema("users");Validation: Should return table names and column details
Step 4: Run migrations with transaction safety
await db.migrate("ALTER TABLE users ADD COLUMN avatar_url TEXT;");Validation: Column should appear in getTableSchema() result
Step 5: Close the pool when done
await db.close();SchemaManagernew SchemaManager(connectionString: string, options?: PoolConfig)Creates a new schema manager connected to a PostgreSQL database
const db = new SchemaManager(process.env.DATABASE_URL!);listTableslistTables(): Promise<string[]>Returns all table names in the public schema
const tables = await db.listTables();getTableSchemagetTableSchema(table: string): Promise<TableSchema>Returns columns (name, type, nullable, default, isPrimaryKey) and indexes for a single table
const schema = await db.getTableSchema("users");migratemigrate(sql: string): Promise<void>Executes SQL inside a transaction. Automatically rolls back on error.
await db.migrate("ALTER TABLE users ADD COLUMN bio TEXT;");seedseed(table: string, rows: Record<string, unknown>[]): Promise<number>Inserts provided rows into the specified table. Uses ON CONFLICT DO NOTHING to skip duplicates. Returns the number of rows inserted. Validates table and column names.
await db.seed("users", [{email: "a@b.com", name: "Alice"}]);snapshotsnapshot(): Promise<Record<string, TableSchema>>Returns the full schema of all public tables as a JSON object keyed by table name
const snap = await db.snapshot();diffdiff(prev, current): { added: string[]; removed: string[]; changed: string[] }Compares two schema snapshots and returns which tables were added, removed, or changed
const changes = db.diff(before, after);closeclose(): Promise<void>Closes the connection pool
await db.close();- Do not use diff() with a single snapshot — it requires two snapshots (before and after) to compare
- Do not pass untrusted SQL to migrate() — it executes arbitrary SQL within a transaction
- Do not use seed() to generate fake data — it only inserts rows you provide
- Only introspects the public schema — does not support custom schemas
- migrate() executes arbitrary SQL — caller is responsible for SQL safety
- seed() inserts provided data only — does not generate fake or random data
- diff() compares table-level structure — does not detect row-level data changes
DATABASE_URLRequiredSensitivePostgreSQL connection stringFindings (7)
- -getTableSchema() signature in docs claims to return TableSchema with columns containing (name, type, nullable, default, isPrimaryKey), but the actual query joins with table_constraints and uses constraint_type which may return NULL for non-primary-key columns. The isPrimaryKey field will be NULL/FALSE for most columns, not a boolean as documented.
- -Documentation claims seed() returns 'the number of rows inserted', but it actually returns rowCount from the INSERT query, which with ON CONFLICT DO NOTHING will return the number of rows that were actually inserted (skipping conflicts). The behavior is correct but the docs are slightly misleading about what 'inserted' means in presence of duplicates.
- -migrate() executes arbitrary SQL without any validation or escaping. While documented as a limitation that 'caller is responsible for SQL safety', the function provides no guardrails against SQL injection if called with untrusted input. Consider adding a warning or validation layer.
- -seed() uses string concatenation to build the INSERT query table and column names, relying only on regex validation. While the regex check is present, this approach is less robust than parameterized identifiers. The quoted identifier approach is safer but still relies on input validation.
- -getTableSchema() query has a complex LEFT JOIN structure that may produce duplicate rows if a column belongs to multiple constraints. The GROUP BY clause is missing, which could cause issues.
- +2 more findings
Suggestions (6)
- -Fix getTableSchema() query: The LEFT JOIN to table_constraints may return multiple rows per column if it belongs to multiple constraints. Add GROUP BY or use a subquery to ensure one row per column.
- -Add a whitelist or stricter validation for SQL passed to migrate(). Consider adding a comment about SQL injection risks or a linting function.
- -Clarify seed() return value: does it count rows that were actually inserted, or attempted inserts? Document the ON CONFLICT behavior explicitly.
- +3 more suggestions