Relational Schema Generator

0.6.3 · active · verified Wed Apr 22

Relational-schema is a utility package designed to introspect and generate a comprehensive, semantic schema representation of a relational database (currently supporting PostgreSQL and MySQL) into various developer-friendly formats. It outputs schema definitions as JavaScript, TypeScript, CommonJS, or JSON files. Currently at version 0.6.3, the package receives regular updates, primarily bug fixes and feature enhancements, though minor version increments (0.x.x) may introduce breaking changes without a major version bump. Unlike simplified ORM models, it provides a detailed schema including full table definitions, columns with types, default values, nullability, keys, constraints, unique key combinations, and intricate table relations with human-readable aliases, soft-delete identification, and enums. This tool aims to leverage the strictness of relational schemas for building more robust and automated tooling, such as the Gybson query client.

Common errors

```
Error: connect ECONNREFUSED <host>:<port>
```
cause The database server is not running, or the host/port configured in your `relation-config.json` or programmatic settings is incorrect or unreachable from where `relational-schema` is executed.

fix Verify your database server's operational status and confirm the `host` and `port` values in your configuration accurately reflect your database connection.
```
relations: command not found
```
cause The `relational-schema` CLI binary (`relations`) is not installed globally or is not included in your system's PATH environment variable.

fix Install the package globally using `npm install -g relational-schema`, or execute the command directly via `npx relational-schema introspect` to use the local installation.
```
ERROR: permission denied for relation pg_namespace (PostgreSQL example)
```
cause The database user specified in your configuration lacks sufficient permissions to read the necessary system catalog tables required for schema introspection.

fix Grant `SELECT` permissions on the relevant system schemas and tables (e.g., `pg_catalog`, `information_schema`) to the database user used by `relational-schema`.
```
Missing config file: relation-config.json
```
cause The `relations introspect` command was run without a `relation-config.json` file in the current working directory, and no alternative configuration file path was provided.

fix Create a `relation-config.json` file in your project root or current directory, or use the `--config <path/to/config.json>` flag to specify its exact location.

Warnings

breaking As `relational-schema` is currently in `0.x.x` versions, minor version increments (e.g., `0.5.x` to `0.6.x`) may introduce breaking changes to the generated schema structure, programmatic API, or configuration options without adhering strictly to semantic versioning. Always review changelogs when upgrading.
Fix: Pin package versions in your `package.json` to prevent unintended updates, and carefully review the GitHub release notes or changelog before performing any minor version upgrades.
gotcha The database user configured in `relation-config.json` (or programmatic config) must possess sufficient permissions to query the database's information schema (e.g., `information_schema` and `pg_catalog` in PostgreSQL, `information_schema` in MySQL) for `relational-schema` to fully introspect the database. Insufficient permissions will result in an incomplete or failed schema generation.
Fix: Grant `SELECT` permissions on relevant system schemas and tables to the configured database user. For PostgreSQL, this typically includes `pg_catalog` and `information_schema`.
gotcha For both programmatic usage and the CLI, `relational-schema` relies on underlying database drivers (e.g., `pg` for PostgreSQL, `mysql2` for MySQL) to connect. While these are usually internal dependencies, ensuring your environment has compatible native client libraries or that the Node.js drivers are correctly managed is crucial to prevent connection failures.
Fix: Verify that the database client is properly installed and configured on the system where `relational-schema` is executed, and ensure that all connection parameters (host, port, user, password, database) are correct and the database is accessible.
gotcha Version `0.6.3` introduced a bug fix to specifically exclude partition children from introspection results. This change might subtly alter the generated schema for partitioned tables compared to prior versions, aiming for greater accuracy.
Fix: If your database includes partitioned tables, carefully review the generated schema after updating to `0.6.3` or newer to confirm it aligns with your expected database structure. This update is generally a positive refinement.

Install

npm install relational-schema npm
yarn add relational-schema yarn
pnpm add relational-schema pnpm

Imports

introspectSchema

wrong:

const introspectSchema = require('relational-schema').introspectSchema;

correct:

import { introspectSchema } from 'relational-schema';

Primary function for programmatically fetching schema metadata from a connected database.

generateSchema
wrong:
```
const generateSchema = require('relational-schema').generateSchema;
```
correct:
```
import { generateSchema } from 'relational-schema';
```
Used for programmatic generation of schema files (e.g., TypeScript, JSON) based on introspected data.
DatabaseSchema
wrong:
```
import { DatabaseSchema } from 'relational-schema';
```
correct:
```
import type { DatabaseSchema } from 'relational-schema';
```
Import for the TypeScript type definition of the generated database schema structure, enabling type-safe interaction with the schema data.

Quickstart

Demonstrates programmatic database introspection and schema file generation using the library's core functions, including setting environment variables for credentials and handling output files. It also outlines the more common CLI usage.

import { generateSchema, introspectSchema } from 'relational-schema';
import fs from 'node:fs/promises';
import path from 'node:path';

async function runSchemaGeneration() {
  const config = {
    host: process.env.DB_HOST ?? '127.0.0.1',
    client: process.env.DB_CLIENT ?? 'postgres', // or 'mysql'
    port: parseInt(process.env.DB_PORT ?? '5432', 10), // 3306 for mysql
    user: process.env.DB_USER ?? 'postgres',
    password: process.env.DB_PASSWORD ?? 'secure_password',
    database: process.env.DB_NAME ?? 'mydb',
    outdir: './src/schemas',
    format: 'typescript'
  };

  try {
    console.log('Introspecting database schema...');
    const schema = await introspectSchema(config);
    console.log('Schema introspected successfully. Generating files...');

    // The generateSchema function returns an object of { filePath: content }
    const generatedFiles = await generateSchema(schema, config);

    await fs.mkdir(config.outdir, { recursive: true });

    for (const [fileName, content] of Object.entries(generatedFiles)) {
      const fullPath = path.join(config.outdir, fileName);
      await fs.writeFile(fullPath, content);
      console.log(`Generated: ${fullPath}`);
    }
    console.log('Schema generation complete!');
  } catch (error) {
    console.error('Failed to generate schema:', error);
    process.exit(1);
  }
}

// To run this example, ensure you have a database running and set environment variables.
// Example of typical CLI usage:
// 1. Create a `relation-config.json` in your project root:
//    {
//        "host": "127.0.0.1", "client": "postgres", "port": 5432,
//        "user": "postgres", "password": "secure_password",
//        "database": "mydb", "outdir": "src/schemas", "format": "typescript"
//    }
// 2. Run from your terminal:
//    npx relations introspect

runSchemaGeneration();

view raw JSON →