schema-dsl
  • English

      • PostgreSQL exporter documentation

      Overview

      PostgreSQLExporter Convert the JSON Schema generated by schema-dsl into PostgreSQL DDL statements, supporting rich PostgreSQL features.

      Core functions

      • ✅ Generate CREATE TABLE DDL statement
      • ✅ Automatic type mapping (supports JSONB, UUID, etc.)
      • ✅ Automatically generate CHECK constraints
      • ✅ Supports COMMENT of tables and columns
      • ✅ Supports multiple index types (btree, hash, gin, gist)
      • ✅ Support PostgreSQL schema namespace

      quick start

      import { s, exporters } from 'schema-dsl/pure';

// 1. Define Schema
const userSchema = s({
  id: 'uuid!',
  username: 'string:3-32!',
  email: 'email!',
  age: 'number:18-120',
  status: 'active|inactive'
});

// 2. Create exporter
const exporter = new exporters.PostgreSQLExporter();

// 3. Export to PostgreSQL DDL
const ddl = exporter.export('users', userSchema);
console.log(ddl);

      Output:

      CREATE TABLE public.users (
  id UUID NOT NULL,
  username VARCHAR(32) NOT NULL CHECK (LENGTH(username) BETWEEN 3 AND 32),
  email VARCHAR(255) NOT NULL,
  age DOUBLE PRECISION CHECK (age BETWEEN 18 AND 120),
  status VARCHAR(255) CHECK (status IN ('active', 'inactive')),
  PRIMARY KEY (id)
);

      API reference

      Constructor

      new PostgreSQLExporter(options)

      Parameters:

      Parametertypedefault valueDescription
      options.schemastring'public'PostgreSQL schema name

      method

      export(tableName, jsonSchema)

      Generates a PostgreSQL CREATE TABLE statement.

      const ddl = exporter.export('users', userSchema);

      Parameters:

      • tableName (string): table name
      • jsonSchema (Object): JSON Schema object (must be of object type)

      Return Value:

      • string: PostgreSQL DDL statements (including COMMENT statements)

      generateIndex(tableName, columnName, options)

      Generate index creation statements.

      const indexDdl = exporter.generateIndex('users', 'email', {
  unique: true,
  method: 'btree'
});
console.log(indexDdl);
// CREATE UNIQUE INDEX idx_users_email ON public.users USING btree (email);

      Parameters:

      • tableName (string): table name
      • columnName (string): column name
      • options.name (string): index name (optional)
      • options.unique (boolean): Is it a unique index?
      • options.method (string): Index method (btree/hash/gin/gist)

      PostgreSQLExporter.export(tableName, jsonSchema) (static method)

      Fast export without instantiation.

      const ddl = exporters.PostgreSQLExporter.export('users', userSchema);

      Configuration options

      Schema namespace

      // Use custom schema
const exporter = new exporters.PostgreSQLExporter({
  schema: 'myapp'
});

const ddl = exporter.export('users', userSchema);
// CREATE TABLE myapp.users (...);

      Complete example

      User table DDL generation

      import { s, exporters } from 'schema-dsl/pure';

//Define user Schema (with description)
const userSchema = s({
  id: 'uuid!',
  username: s('string:3-32!').description('User login name, 3-32 characters'),
  email: s('email!').description('User email address'),
  password: 'string:8-64!',
  age: 'number:18-120',
  profile: {
    bio: 'string:500',
    avatar: 'url'
  },
  status: 'active|inactive|banned',
  createdAt: 'datetime!'
});

//Add table description
userSchema.description = 'User information table';

// Generate DDL
const exporter = new exporters.PostgreSQLExporter({ schema: 'app' });
const ddl = exporter.export('users', userSchema);

console.log(ddl);

// Generate index
console.log(exporter.generateIndex('users', 'email', { unique: true }));
console.log(exporter.generateIndex('users', 'username', { unique: true }));
console.log(exporter.generateIndex('users', 'profile', { method: 'gin' }));

      Output:

      CREATE TABLE app.users (
  id UUID NOT NULL,
  username VARCHAR(32) NOT NULL CHECK (LENGTH(username) BETWEEN 3 AND 32),
  email VARCHAR(255) NOT NULL,
  password VARCHAR(64) NOT NULL CHECK (LENGTH(password) BETWEEN 8 AND 64),
  age DOUBLE PRECISION CHECK (age BETWEEN 18 AND 120),
  profile JSONB,
  status VARCHAR(255) CHECK (status IN ('active', 'inactive', 'banned')),
  createdAt TIMESTAMP NOT NULL,
  PRIMARY KEY (id)
);

COMMENT ON TABLE app.users IS 'User information table';

COMMENT ON COLUMN app.users.username IS 'User login name, 3-32 characters';
COMMENT ON COLUMN app.users.email IS 'User email address';

CREATE UNIQUE INDEX idx_users_email ON app.users USING btree (email);
CREATE UNIQUE INDEX idx_users_username ON app.users USING btree (username);
CREATE INDEX idx_users_profile ON app.users USING gin (profile);

      type mapping

      JSON Schema typeformat/constraintsPostgreSQL type
      string-VARCHAR(255)
      stringmaxLength: 50VARCHAR(50)
      stringmaxLength: 500TEXT
      stringformat: emailVARCHAR(255)
      stringformat: uuidUUID
      stringformat: dateDATE
      stringformat: date-timeTIMESTAMP
      integermaximum: 32767SMALLINT
      integermaximum: 2147483647INTEGER
      integer-BIGINT
      number-DOUBLE PRECISION
      boolean-BOOLEAN
      object-JSONB
      array-JSONB

      CHECK constraints

      PostgreSQLExporter automatically generates CHECK statements for the following constraints:

      String length constraints

      username: 'string:3-32!'
// CHECK (LENGTH(username) BETWEEN 3 AND 32)

      Numeric range constraints

      age: 'number:18-120'
// CHECK (age BETWEEN 18 AND 120)

      enumeration constraints

      status: 'active|inactive|banned'
// CHECK (status IN ('active', 'inactive', 'banned'))

      Index type

      methoduseExample
      btree (default)Universal index, supports sorting and range queriesPrimary key, foreign key, sort field
      hashEquivalence queryExact match query
      ginJSON/array indexJSONB fields, full text search
      gistGeometry data, range typeGeolocation, IP range
      // GIN index for JSONB fields
exporter.generateIndex('users', 'metadata', { method: 'gin' });

      Export restrictions

      ⚠️ IMPORTANT NOTE: Although PostgreSQL supports CHECK constraints, some features cannot be exported.

      Features not supported by PostgreSQL:

      • ❌ Regular expression constraints (pattern)
      • ❌ Nested object constraints (exported as JSONB, internal constraints are lost)
      • ❌ Conditional validation logic (s.match(), s.if())

      Detailed instructions: Please read Export restrictions document

      Corresponding sample file

      Example entry: postgresql-exporter.ts Description: Covers PostgreSQL DDL export, CHECK constraint expression, and generateIndex() generates gin index.