@robot.com/postgres-schema-tools
Advanced tools
> Comprehensive PostgreSQL schema introspection, comparison, and migration toolkit for TypeScript
Comprehensive PostgreSQL schema introspection, comparison, and migration toolkit for TypeScript
Status: Active development. Version 0.0.7 is functional but not recommended for production use yet.
Postgres Schema Tools is a TypeScript library that provides powerful capabilities for working with PostgreSQL database schemas. It bridges the gap between code-defined schemas (like Drizzle ORM) and live databases, enabling comprehensive schema introspection, intelligent comparison, and automated migration generation.
Schema Introspection
Schema Comparison
Migration Generation
Developer Experience
npm install @robot.com/postgres-schema-tools
# or
pnpm add @robot.com/postgres-schema-tools
# or
yarn add @robot.com/postgres-schema-tools
import { fetchSchemaPostgresSQL, generatePushDiffSchema } from '@robot.com/postgres-schema-tools'
import postgres from 'postgres'
// Connect to two databases
const prod = postgres(PROD_URL)
const staging = postgres(STAGING_URL)
// Fetch schemas
const prodSchema = await fetchSchemaPostgresSQL(prod)
const stagingSchema = await fetchSchemaPostgresSQL(staging)
// Generate migration SQL
const migrationBatches = generatePushDiffSchema(prodSchema, stagingSchema)
// Execute migrations
for (const batch of migrationBatches) {
await prod.begin(async (tx) => {
for (const sql of batch) {
await tx.unsafe(sql)
}
})
}
await prod.end()
await staging.end()
PostgreSQL - Connect to any PostgreSQL database
import postgres from 'postgres'
import { fetchSchemaPostgresSQL } from '@robot.com/postgres-schema-tools'
const sql = postgres(process.env.DATABASE_URL!)
const schema = await fetchSchemaPostgresSQL(sql)
PGlite - Embedded PostgreSQL for testing (no Docker)
import { createLocalDatabase, fetchSchemaPgLite } from '@robot.com/postgres-schema-tools'
const db = await createLocalDatabase()
const schema = await fetchSchemaPgLite(db.$client)
Drizzle ORM - Extract schema from Drizzle table definitions
import { fetchSchemaDrizzleORM } from '@robot.com/postgres-schema-tools'
import * as schema from './db/schema'
const localSchema = fetchSchemaDrizzleORM(schema)
Database Objects Supported:
Column Types Supported:
smallint, integer, bigint, numeric(p,s), real, double precisionserial, bigserial (auto-increment sequences)text, varchar(n), char(n)byteadate, time, timestamp, timestamptz, intervalbooleanuuidjson, jsonbinet, cidr, macaddr[] notationSmart Change Detection:
Detailed Reports:
has_changes flag for CI/CD gatesDependency-Aware Ordering:
Handles Complex Scenarios:
The postgres-schema-tools CLI provides comprehensive commands for schema operations.
postgres-schema-tools
├── schema
│ ├── fetch # Extract schema from source
│ ├── diff # Compare two schemas
│ └── push # Apply schema to database
├── migrate
│ └── generate # Generate migration SQL
└── diff-report # [DEPRECATED] Legacy command
# Run directly with npx
npx @robot.com/postgres-schema-tools schema fetch ./schema.ts
# Or install globally
npm install -g @robot.com/postgres-schema-tools
schema fetchFetch schema from a database, Drizzle TypeScript file, or JSON file.
postgres-schema-tools schema fetch <source> [options]
Arguments:
<source> - Database URL, TypeScript file path, or JSON file pathOptions:
--type <type> - Source type: auto | postgres | drizzle | json (default: auto)--output <path> - Output file path (default: stdout)--format <format> - Output format: json | yaml (default: json)Examples:
# Fetch from Drizzle TypeScript schema
postgres-schema-tools schema fetch ./src/db/schema.ts
# Fetch from PostgreSQL database
postgres-schema-tools schema fetch "postgres://user:pass@localhost/mydb"
# Save to file
postgres-schema-tools schema fetch ./schema.ts --output schema.json
# Explicit type (useful if auto-detection fails)
postgres-schema-tools schema fetch ./schema.ts --type drizzle
schema diffCompare two schemas and generate a diff report.
postgres-schema-tools schema diff <sourceA> <sourceB> [options]
Arguments:
<sourceA> - First schema source (database URL or file path)<sourceB> - Second schema source (database URL or file path)Options:
--type-a <type> - Type of sourceA: auto | postgres | drizzle | json (default: auto)--type-b <type> - Type of sourceB: auto | postgres | drizzle | json (default: auto)--output <path> - Output file path (default: stdout)--format <format> - Output format: json | markdown (default: markdown)--fail-on-changes - Exit with code 1 if differences detected (for CI)Examples:
# Compare Drizzle schema with database
postgres-schema-tools schema diff ./src/schema.ts "$DATABASE_URL"
# Compare two databases
postgres-schema-tools schema diff "$PROD_URL" "$STAGING_URL"
# Output JSON report to file
postgres-schema-tools schema diff ./schema.ts "$DATABASE_URL" \
--format json --output diff-report.json
# CI mode - fail if schemas differ
postgres-schema-tools schema diff ./schema.ts "$DATABASE_URL" --fail-on-changes
schema pushPush schema changes to a target database.
postgres-schema-tools schema push <source> <target> [options]
Arguments:
<source> - Source schema (file path or database URL)<target> - Target database URLOptions:
--type <type> - Source type: auto | postgres | drizzle | json (default: auto)--mode <mode> - Push mode: new | diff (default: diff)--dry-run - Generate SQL without executing--output <path> - Save generated SQL to file--yes - Skip confirmation promptsExamples:
# Preview migration (dry-run)
postgres-schema-tools schema push ./schema.ts "$DATABASE_URL" --dry-run
# Generate and save migration SQL
postgres-schema-tools schema push ./schema.ts "$DATABASE_URL" \
--dry-run --output migration.sql
# Execute migration (with confirmation)
postgres-schema-tools schema push ./schema.ts "$DATABASE_URL"
# Execute migration (skip confirmation)
postgres-schema-tools schema push ./schema.ts "$DATABASE_URL" --yes
# Create new schema (no diff, fresh database)
postgres-schema-tools schema push ./schema.ts "$DATABASE_URL" --mode new --yes
migrate generateGenerate migration SQL between two schemas without executing.
postgres-schema-tools migrate generate <from> <to> [options]
Arguments:
<from> - Current/source schema (database URL or file path)<to> - Target schema (database URL or file path)Options:
--type-from <type> - Type of from source (default: auto)--type-to <type> - Type of to source (default: auto)--output <path> - Output SQL file (default: stdout)--format <format> - Output format: sql | batched (default: sql)Examples:
# Generate migration from production to Drizzle schema
postgres-schema-tools migrate generate "$PROD_URL" ./schema.ts
# Save migration to file
postgres-schema-tools migrate generate "$PROD_URL" ./schema.ts --output migration.sql
# Output as JSON batches (for programmatic use)
postgres-schema-tools migrate generate "$PROD_URL" ./schema.ts --format batched
diff-report (Deprecated)Deprecated: Use
schema diffinstead.
Compare two databases and generate a comprehensive report:
postgres-schema-tools diff-report <dbA> <dbB> [options]
Arguments:
<dbA> - First database connection URL<dbB> - Second database connection URLOptions:
--out-dir <directory> - Output directory for reports--fail-on-changes - Exit with code 1 if differences are detected (for CI)Example:
postgres-schema-tools diff-report "$PROD_URL" "$STAGING_URL" --out-dir ./report
This creates:
report/schema1.json - Full schema Areport/schema2.json - Full schema Breport/report.json - Detailed diff reportreport/report.md - Human-readable Markdown report0 - Success (or no changes when using --fail-on-changes)1 - Error occurred or changes detected (with --fail-on-changes)name: Schema Validation
on: [pull_request]
jobs:
validate-schema:
runs-on: ubuntu-latest
steps:
- uses: actions/checkout@v4
- uses: actions/setup-node@v4
- run: npm install
- name: Compare Drizzle schema with production
run: |
npx @robot.com/postgres-schema-tools schema diff \
./src/db/schema.ts \
"${{ secrets.DATABASE_URL }}" \
--format markdown \
--output ./schema-diff.md \
--fail-on-changes
- name: Upload report
if: failure()
uses: actions/upload-artifact@v3
with:
name: schema-diff
path: ./schema-diff.md
Alternative: Compare two databases
- name: Compare staging with production
run: |
npx @robot.com/postgres-schema-tools schema diff \
"${{ secrets.PROD_DATABASE_URL }}" \
"${{ secrets.STAGING_DATABASE_URL }}" \
--fail-on-changes
import {
// Schema fetching
fetchSchemaPostgresSQL,
fetchSchemaPgLite,
fetchSchemaDrizzleORM,
// Migration generation
generatePushNewSchema,
generatePushDiffSchema,
// Reporting
createJsonDiffReport,
createMarkdownReport,
// Testing utilities
createLocalDatabase,
// Types
type RemoteSchema,
type LocalSchema,
type JsonReport,
type TableDefinition,
type ColumnDefinition,
} from '@robot.com/postgres-schema-tools'
import postgres from 'postgres'
import { fetchSchemaPostgresSQL } from '@robot.com/postgres-schema-tools'
const sql = postgres(process.env.DATABASE_URL!)
const schema = await fetchSchemaPostgresSQL(sql, {
ignore: {
tables: ['_drizzle_migrations', '_internal_audit'],
views: ['pg_stat_statements'],
indexes: ['idx_temporary'],
constraints: ['old_check'],
}
})
await sql.end()
Options:
ignore.tables - Array of table names to excludeignore.views - Array of view names to excludeignore.indexes - Array of index names to excludeignore.constraints - Array of constraint names to excludeimport { createLocalDatabase, fetchSchemaPgLite } from '@robot.com/postgres-schema-tools'
// Create local database
const db = await createLocalDatabase({
extensions: ['pg_trgm', 'uuid-ossp']
})
// Create schema
await db.$client.exec(`
CREATE TABLE users (
id serial PRIMARY KEY,
email text NOT NULL UNIQUE,
created_at timestamp DEFAULT NOW()
)
`)
// Fetch schema
const schema = await fetchSchemaPgLite(db.$client)
// Clean up
await db.close()
import { pgTable, serial, text, timestamp } from 'drizzle-orm/pg-core'
import { fetchSchemaDrizzleORM } from '@robot.com/postgres-schema-tools'
const users = pgTable('users', {
id: serial('id').primaryKey(),
email: text('email').notNull().unique(),
createdAt: timestamp('created_at').defaultNow()
})
const localSchema = fetchSchemaDrizzleORM({ users })
import { generatePushNewSchema } from '@robot.com/postgres-schema-tools'
import type { LocalSchema } from '@robot.com/postgres-schema-tools'
const schema: LocalSchema = {
enums: [
{ name: 'user_role', values: ['admin', 'user', 'guest'] }
],
tables: [
{
name: 'users',
columns: [
{ name: 'id', data_type: 'serial', is_nullable: false },
{ name: 'email', data_type: 'text', is_nullable: false },
{ name: 'role', data_type: 'USER-DEFINED', udt_name: 'user_role', default: "'user'" }
],
constraints: [
{ name: 'users_pkey', type: 'PRIMARY KEY', columns: ['id'] },
{ name: 'users_email_unique', type: 'UNIQUE', columns: ['email'] }
],
indexes: [
{ name: 'idx_users_email', columns: [{ name: 'email' }] }
]
}
]
}
const statements = generatePushNewSchema(schema)
// Execute statements
for (const sql of statements) {
await db.execute(sql)
}
import { generatePushDiffSchema } from '@robot.com/postgres-schema-tools'
const oldSchema = await fetchSchemaPostgresSQL(prodDb)
const newSchema = await fetchSchemaPostgresSQL(stagingDb)
// Returns string[][] - array of batches
const batches = generatePushDiffSchema(oldSchema, newSchema)
// Execute each batch as a transaction
for (const batch of batches) {
await prodDb.begin(async (tx) => {
for (const sql of batch) {
await tx.unsafe(sql)
}
})
}
import { createJsonDiffReport } from '@robot.com/postgres-schema-tools'
const schema1 = await fetchSchemaPostgresSQL(db1)
const schema2 = await fetchSchemaPostgresSQL(db2)
const report = createJsonDiffReport(schema1, schema2)
console.log('Has changes:', report.has_changes)
console.log('Tables added:', report.tables.added.length)
console.log('Tables modified:', report.tables.modified.length)
console.log('Enums changed:', report.enums.modified.length)
// Access detailed changes
report.tables.modified.forEach(table => {
console.log(`Table ${table.name}:`)
console.log(` - Columns added: ${table.columns.added.length}`)
console.log(` - Indexes added: ${table.indexes.added.length}`)
})
import { createMarkdownReport } from '@robot.com/postgres-schema-tools'
import { writeFile } from 'node:fs/promises'
const jsonReport = createJsonDiffReport(schema1, schema2)
// Basic markdown
const markdown = createMarkdownReport(jsonReport, 'Production', 'Staging')
// With migration SQL included
const markdownWithSQL = createMarkdownReport(
jsonReport,
'Production',
'Staging',
{ includeMigrationCode: true }
)
await writeFile('schema-changes.md', markdown)
Ensure staging matches production before deployment:
// scripts/validate-schema.ts
import { fetchSchemaPostgresSQL, createJsonDiffReport } from '@robot.com/postgres-schema-tools'
import postgres from 'postgres'
const prod = postgres(process.env.PROD_DATABASE_URL!)
const staging = postgres(process.env.STAGING_DATABASE_URL!)
const prodSchema = await fetchSchemaPostgresSQL(prod, {
ignore: { tables: ['_drizzle_migrations'] }
})
const stagingSchema = await fetchSchemaPostgresSQL(staging, {
ignore: { tables: ['_drizzle_migrations'] }
})
const report = createJsonDiffReport(prodSchema, stagingSchema)
if (report.has_changes) {
console.error('❌ Schema mismatch detected!')
console.error(`Tables added: ${report.tables.added.length}`)
console.error(`Tables removed: ${report.tables.removed.length}`)
console.error(`Tables modified: ${report.tables.modified.length}`)
process.exit(1)
}
console.log('✅ Schemas match!')
await prod.end()
await staging.end()
Apply Drizzle schema changes to production:
// scripts/migrate-from-drizzle.ts
import {
fetchSchemaPostgresSQL,
fetchSchemaDrizzleORM,
generatePushDiffSchema
} from '@robot.com/postgres-schema-tools'
import { localSchemaToRemoteSchema } from '@robot.com/postgres-schema-tools/schema/local'
import postgres from 'postgres'
import * as schema from './db/schema'
const db = postgres(process.env.DATABASE_URL!)
// Get current database schema
const currentSchema = await fetchSchemaPostgresSQL(db)
// Get desired schema from Drizzle
const localSchema = fetchSchemaDrizzleORM(schema)
const desiredSchema = localSchemaToRemoteSchema(localSchema)
// Generate migration
const batches = generatePushDiffSchema(currentSchema, desiredSchema)
console.log('Migration preview:')
batches.forEach((batch, i) => {
console.log(`\n--- Batch ${i + 1} ---`)
batch.forEach(sql => console.log(sql))
})
// Execute with confirmation
const answer = prompt('Execute migration? (yes/no): ')
if (answer === 'yes') {
for (const batch of batches) {
await db.begin(async tx => {
for (const sql of batch) {
await tx.unsafe(sql)
}
})
}
console.log('✅ Migration complete!')
}
await db.end()
Test schema migrations without Docker:
// tests/schema-migrations.test.ts
import { test } from 'node:test'
import { strict as assert } from 'node:assert'
import {
createLocalDatabase,
fetchSchemaPgLite,
generatePushDiffSchema
} from '@robot.com/postgres-schema-tools'
test('adding column generates correct SQL', async () => {
const db = await createLocalDatabase()
// Initial schema
await db.$client.exec(`
CREATE TABLE users (
id serial PRIMARY KEY,
name text NOT NULL
)
`)
const v1 = await fetchSchemaPgLite(db.$client)
// Add column
await db.$client.exec(`
ALTER TABLE users ADD COLUMN email text
`)
const v2 = await fetchSchemaPgLite(db.$client)
// Generate migration
const batches = generatePushDiffSchema(v1, v2)
// Verify
assert.equal(batches.length, 1)
assert(batches[0][0].includes('ADD COLUMN "email"'))
await db.close()
})
Generate schema change documentation for PRs:
// scripts/document-schema-changes.ts
import {
fetchSchemaPostgresSQL,
createJsonDiffReport,
createMarkdownReport
} from '@robot.com/postgres-schema-tools'
import { writeFile } from 'node:fs/promises'
import postgres from 'postgres'
const main = postgres(process.env.MAIN_BRANCH_DB!)
const branch = postgres(process.env.FEATURE_BRANCH_DB!)
const mainSchema = await fetchSchemaPostgresSQL(main)
const branchSchema = await fetchSchemaPostgresSQL(branch)
const jsonReport = createJsonDiffReport(mainSchema, branchSchema)
const markdown = createMarkdownReport(jsonReport, 'Main', 'Feature Branch', {
includeMigrationCode: true
})
await writeFile('./schema-changes.md', markdown)
console.log('📝 Schema changes documented in schema-changes.md')
await main.end()
await branch.end()
Complete database state as fetched from PostgreSQL:
interface RemoteSchema {
schema: string // Always "public"
generated_at: string // ISO 8601 timestamp
enums: EnumDefinition[]
views: ViewDefinition[]
tables: TableDefinition[]
}
interface TableDefinition {
name: string
description: string | null
columns: ColumnDefinition[]
constraints: ConstraintDefinition[]
indexes: IndexDefinition[]
foreign_keys: ForeignKeyDefinition[]
triggers: TriggerDefinition[]
}
interface ColumnDefinition {
name: string
description: string | null
position: number
data_type: string // "integer", "text", "numeric", etc.
is_nullable: boolean
default: string | null // SQL expression
is_generated: boolean
generation_expression: string | null
is_identity: boolean
identity_generation: 'ALWAYS' | 'BY DEFAULT' | null
max_length: number | null // For varchar(N)
numeric_precision: number | null // For numeric(P,S)
numeric_scale: number | null
udt_name: string // Underlying type: "int4", "varchar"
}
Simplified format for defining schemas in code:
interface LocalSchema {
enums?: LocalEnumDefinition[]
views?: LocalViewDefinition[]
tables?: LocalTableDefinition[]
}
interface LocalTableDefinition {
name: string
description?: string | null
columns: LocalColumnDefinition[]
constraints?: LocalConstraintDefinition[]
indexes?: LocalIndexDefinition[]
foreign_keys?: LocalForeignKeyDefinition[]
triggers?: LocalTriggerDefinition[]
}
interface LocalColumnDefinition {
name: string
data_type: string // Required
description?: string | null
is_nullable?: boolean // Optional, defaults
default?: string | null
// ... other optional fields
}
Detailed diff result:
interface JsonReport {
has_changes: boolean
schemas: { from: string; to: string }
generated_at: string
enums: {
added: EnumDefinition[]
removed: EnumDefinition[]
modified: Difference<EnumDefinition>[]
}
views: {
added: ViewDefinition[]
removed: ViewDefinition[]
modified: Difference<ViewDefinition>[]
}
tables: {
added: TableDefinition[]
removed: TableDefinition[]
modified: TableModification[]
}
}
interface TableModification {
name: string
description?: { from: string | null; to: string | null }
columns: {
added: ColumnDefinition[]
removed: ColumnDefinition[]
modified: Difference<ColumnDefinition>[]
}
constraints: { added, removed, modified }
indexes: { added, removed, modified }
foreign_keys: { added, removed, modified }
triggers: { added, removed, modified }
}
Full support for Drizzle ORM schema definitions:
import { pgTable, pgEnum, serial, text, integer, index, foreignKey, check } from 'drizzle-orm/pg-core'
import { sql } from 'drizzle-orm'
import { fetchSchemaDrizzleORM } from '@robot.com/postgres-schema-tools'
// Define enums
const userRole = pgEnum('user_role', ['admin', 'user', 'guest'])
// Define tables
const users = pgTable('users', {
id: serial('id').primaryKey(),
email: text('email').notNull(),
role: userRole('role').default('user')
}, (t) => [
index('idx_users_email').on(t.email),
check('email_format', sql`${t.email} LIKE '%@%'`)
])
const posts = pgTable('posts', {
id: serial('id').primaryKey(),
userId: integer('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
title: text('title').notNull()
})
// Extract schema
const localSchema = fetchSchemaDrizzleORM({ userRole, users, posts })
Supported Drizzle Features:
Automatic sequence generation for serial columns:
const users = pgTable('users', {
id: serial('id').primaryKey(), // Automatically creates sequence
bigId: bigserial('big_id') // Automatically creates bigint sequence
})
const schema = fetchSchemaDrizzleORM({ users })
// Generates: data_type: 'serial' (not 'integer' with default)
Preserves precision and scale for numeric types:
const products = pgTable('products', {
price: numeric('price', { precision: 10, scale: 2 }), // numeric(10, 2)
weight: numeric('weight', { precision: 8 }) // numeric(8)
})
// Generates SQL: price numeric(10, 2), weight numeric(8)
Full support for PostgreSQL array types:
const posts = pgTable('posts', {
tags: text('tags').array().default(sql`'{}'::text[]`),
scores: integer('scores').array()
})
// Generates SQL: tags text[] DEFAULT '{}'::text[], scores integer[]
Support for all index types and features:
const documents = pgTable('documents', {
id: serial('id').primaryKey(),
content: text('content'),
search_vector: text('search_vector'), // tsvector
metadata: jsonb('metadata'),
created_at: timestamp('created_at')
}, (t) => [
// GIN index (full-text search)
index('idx_search').on(t.search_vector).using('gin'),
// Composite index with ordering
index('idx_date_id').on(t.created_at.desc(), t.id.asc()),
// Partial index
index('idx_recent').on(t.created_at).where(sql`created_at > NOW() - INTERVAL '30 days'`),
// JSONB GIN index
index('idx_metadata').on(t.metadata).using('gin')
])
Important: GIN, GIST, BRIN, and hash indexes don't support column ordering (ASC/DESC/NULLS). The library automatically omits ordering for these index types.
# Run all tests
pnpm --filter @robot.com/postgres-schema-tools test
# Run specific test file
pnpm test -- src/tests/fetch.test.ts
# Run with Node.js environment variables
node --env-file .env --import tsx --test './src/**/*.test.ts'
All 108 tests passing (100% pass rate):
Core Functionality:
Database Features:
Edge Cases:
No Docker required! All tests use PGlite for fast, isolated testing:
import { test } from 'node:test'
import { createLocalDatabase, fetchSchemaPgLite } from '@robot.com/postgres-schema-tools'
test('create and fetch schema', async () => {
const db = await createLocalDatabase()
await db.$client.exec(`
CREATE TABLE users (id serial PRIMARY KEY, name text)
`)
const schema = await fetchSchemaPgLite(db.$client)
assert.equal(schema.tables.length, 1)
assert.equal(schema.tables[0].name, 'users')
await db.close()
})
Public Schema Only - Only inspects the public schema. Other schemas are ignored.
Enum Value Removal - Cannot remove values from existing enums (PostgreSQL limitation). Requires manual type recreation.
No Rollback Generation - Only generates forward migrations. For rollbacks, generate diff in reverse or write manually.
Column Reordering Not Supported - Cannot change column order (PostgreSQL limitation). Would require table recreation.
Materialized Views - Treated like regular views. Refresh strategies not captured.
No Partitioned Tables - Partition information not captured or managed.
NULLS NOT DISTINCT - PostgreSQL 15+ feature. Code prepared but commented out for PGlite (PG 14) compatibility.
Schema Fetching:
ignore options to exclude migration tables and system viewsMigration Generation:
Testing:
CI/CD:
diff-report --fail-on-changes to gate deploymentsSingle Query Approach:
Query Structure:
WITH
enums AS (...),
views AS (...),
table_columns AS (...),
table_constraints AS (...),
indexes AS (...),
foreign_keys AS (...),
triggers AS (...)
SELECT jsonb_build_object(
'schema', 'public',
'enums', (SELECT jsonb_agg(...) FROM enums),
'tables', (SELECT jsonb_agg(...) FROM tables)
) AS public_schema_json
426 lines of optimized SQL - See src/schema/remote/query.ts
Efficient Comparison:
Typical Performance:
TypeScript errors with Drizzle:
// ❌ Wrong - missing type imports
import { fetchSchemaDrizzleORM } from '@robot.com/postgres-schema-tools'
// ✅ Correct - import all schema objects
import * as schema from './db/schema'
const localSchema = fetchSchemaDrizzleORM(schema)
Serial columns not auto-incrementing:
// ❌ Wrong - using integer with default
const users = pgTable('users', {
id: integer('id').default(sql`nextval('users_id_seq')`)
})
// ✅ Correct - use serial()
const users = pgTable('users', {
id: serial('id').primaryKey()
})
Failed INSERT increments sequence:
// Be aware: failed INSERTs still consume sequence values
await client.query("INSERT INTO users (email) VALUES ('invalid')") // Fails
await client.query("INSERT INTO users (email) VALUES ('valid@email.com') RETURNING id")
// Returns id=2, not id=1 (sequence was incremented by failed attempt)
// Always use RETURNING to get actual IDs in tests
const result = await client.query("INSERT INTO users (...) VALUES (...) RETURNING id")
const userId = result.rows[0].id
GIN index syntax errors:
// ❌ Wrong - GIN doesn't support ordering
CREATE INDEX idx_search USING gin (search_vector ASC NULLS LAST)
// ✅ Correct - no ordering for GIN
CREATE INDEX idx_search USING gin (search_vector)
// Library handles this automatically!
Contributions are welcome! This package is in active development.
# Clone repository
git clone https://github.com/robot-com/oss.git
cd oss/packages/postgres-schema-tools
# Install dependencies
pnpm install
# Run tests
pnpm test
# Type check
pnpm tsc --noEmit
# Run specific test
pnpm test -- src/tests/fetch.test.ts
src/
├── schema/ # Schema definitions & fetching
│ ├── common/ # Shared types (ConstraintType, etc.)
│ ├── remote/ # Live database schemas
│ │ ├── types.ts # RemoteSchema types
│ │ ├── query.ts # 426-line SQL query
│ │ └── fetch.ts # fetchSchemaPostgresSQL/PgLite
│ ├── local/ # Code-defined schemas
│ │ ├── types.ts # LocalSchema types
│ │ └── to-remote.ts
│ ├── drizzle/ # Drizzle ORM integration
│ │ └── fetch.ts # fetchSchemaDrizzleORM
│ └── push/ # Migration generation
│ ├── diff.ts # generatePushDiffSchema
│ ├── new.ts # generatePushNewSchema
│ └── generators.ts # SQL statement builders
├── report/ # Diff reporting
│ ├── json.ts # createJsonDiffReport
│ └── markdown.ts # createMarkdownReport
├── db/ # Database utilities
│ └── index.ts # createLocalDatabase (PGlite)
├── bin/ # CLI
│ ├── main.ts # CLI entry point
│ ├── commands/ # CLI commands
│ │ ├── schema-fetch.ts
│ │ ├── schema-diff.ts
│ │ ├── schema-push.ts
│ │ └── migrate-generate.ts
│ └── utils/ # CLI utilities
│ ├── drizzle-loader.ts # Dynamic TS loading with jiti
│ └── source-loader.ts # Unified schema loading
└── tests/ # Test suite (108 tests)
See KNOWLEDGE_BASE.md for comprehensive documentation including:
MIT © Robot OSS
Made with ❤️ for the PostgreSQL and TypeScript communities
FAQs
> Comprehensive PostgreSQL schema introspection, comparison, and migration toolkit for TypeScript
We found that @robot.com/postgres-schema-tools demonstrated a healthy version release cadence and project activity because the last version was released less than a year ago. It has 2 open source maintainers collaborating on the project.
Did you know?
Socket for GitHub automatically highlights issues in each pull request and monitors the health of all your open source dependencies. Discover the contents of your packages and block harmful activity before you install or update your dependencies.
Security News
Open source is under attack because of how much value it creates. It has been the foundation of every major software innovation for the last three decades. This is not the time to walk away from it.
Security News
Socket CEO Feross Aboukhadijeh breaks down how North Korea hijacked Axios and what it means for the future of software supply chain security.
Security News
OpenSSF has issued a high-severity advisory warning open source developers of an active Slack-based campaign using impersonation to deliver malware.