pg-schema-gen

CLI tool to generate simple readable TypeScript, Zod and other schema files based on SQL schema files.

pg-schema-gen uses pgsql-parser to parse SQL files as an abstract syntax tree then generates schema files based on the syntax tree.

pg-schema-gen is used by the Convo-Make generative build system to create schema a type files based on generated database schemas.

Arguments:

| name | value | multi | description | |-------------------------|---------|-------|-------------------------------------------------------------| | --sql | sql | Y | SQL statement | | --sql-file | path | Y | SQL file to load | | --type-map-file | path | Y | Type map JSON file | | --clear-type-map | boolean | | Clear default type mapping | | --barrel-base | path | | base path used by schema barrel to import | | --disable-schema-barrel | boolean | | Disables the default schema barrel | | --import-ext | string | | Sets the import extension used with TypeScript files | | --insert-suffix | suffix | | Suffix added to insert type | | --silent | boolean | | Silence console logging | | --verbose | boolean | | Enable verbose output | | --out | path | Y | Path to directory to written all outputs to | | --ts-out | path | Y | Path to write TypeScript type | | --zod-out | path | Y | Path to write Zod schema | | --convo-out | path | Y | Path to write Convo-Lang struct | | --type-map-out | path | Y | Path to write computed type map | | --table-map-out | path | Y | Path to write table map as JSON | | --ts-table-map-out | path | Y | Path to write table map as exported JSON object | | --type-list-out | path | Y | Path to write type list as JSON array | | --type-list-short-out | path | Y | Path to write shortened type list as JSON array | | --ts-type-def-out | path | Y | Path to write type definitions to | | --ts-schema-barrel-out | path | Y | Path to write a barrel file that exports all other ts files | | --parsed-sql-out | path | Y | Path to write parsed SQL |

(multi) arguments can be specified multiple times.

The --out argument provides provides a default path for all other output arguments.

Metadata Comments

Special metadata can be defined in comments using a format of {@TAG[:LANG] VALUE} where LANG is optional. Metadata can be used to override property types or to add source code to outputs.

Type Overrides

Property types for output languages can be overwritten using a type metadata comment when applied to a table column. This can be useful when the type within application can is more specific than the type used in the database and is commonly used with jsonb columns that implement a specific interface or type in TypeScript. The source metadata comment can be used in combination with a type metadata comment to define where the custom property type should be imported from.

In the following example the value property ExampleType TypeScript output will have a type of ExampleValueType and will be imported from ../lib/example

-- {@source:ts import { ExampleValueType } from '../lib/example'; }

create table example_type (
    id uuid not null default gen_random_uuid (),
    name text not null,
    -- {@type:ts ExampleValueType}
    value jsonb not null
)

TypeScript output:

import { ExampleValueType } from '../lib/example';

export interface ExampleType{
    id:string;
    name:string;
    value:ExampleValueType;
}

Example

Write all schema files to the src/schema directory

npx pg-schema-gen --sql-file schema.sql --out src/schema

Write TypeScript type and Zod Schemas to custom paths:

npx pg-schema-gen --sql-file schema.sql --ts-out src/types.ts --zod-out src/zod-schemas.ts

Source SQL: schema.sql

-- A cool dude
create table public.user (
  -- Id of the user
  id uuid not null default gen_random_uuid (),

  -- Time stamp when the user was created
  created_at timestamp with time zone not null default now(),
  name text not null,

  -- Valid email
  email text not null,
  account_id uuid not null,
  data jsonb not null,
  constraint user_pkey primary key (id),
  constraint user_account_id_fkey foreign KEY (account_id) references account (id) on delete CASCADE
);

-- extra data for stuff
create table public.data (
  id uuid not null default gen_random_uuid (),
  account_id uuid not null,
  created_at timestamp with time zone not null default now(),
  last_modified_at timestamp with time zone not null default now(),
  created_by_user_id uuid null,
  modified_by_user_id uuid null,
  owner_user_id uuid null,
  public boolean not null default false,
  data jsonb not null,
  "table" text not null,
  constraint data_pkey primary key (id),
  constraint data_account_id_fkey foreign KEY (account_id) references account (id) on delete CASCADE,
  constraint data_created_by_user_id_fkey foreign KEY (created_by_user_id) references data (id) on delete set null,
  constraint data_modified_by_user_id_fkey foreign KEY (modified_by_user_id) references "user" (id) on delete set null,
  constraint data_owner_user_id_fkey foreign KEY (owner_user_id) references "user" (id) on delete set null
);

create index IF not exists data_table_idx on public.data using btree ("table");

create table public.account (
  id uuid not null default gen_random_uuid (),
  name text not null,
  created_at timestamp with time zone not null default now(),
  data jsonb not null,
  constraint account_pkey primary key (id)
);

TypeScript types: src/types.ts

/**
 * @table account
 * @schema public
 */
export interface Account
{
    id:string;
    name:string;
    created_at:string;
    data:Record<string,any>;
}

/**
 * @insertFor Account
 * @table account
 * @schema public
 */
export interface Account_insert
{
    id?:string;
    name:string;
    created_at?:string;
    data:Record<string,any>;
}

/**
 * extra data for stuff
 * @table data
 * @schema public
 */
export interface Data
{
    id:string;
    account_id:string;
    created_at:string;
    last_modified_at:string;
    created_by_user_id?:string;
    modified_by_user_id?:string;
    owner_user_id?:string;
    public:string;
    data:Record<string,any>;
    table:string;
}

/**
 * @insertFor Data
 * @table data
 * @schema public
 */
export interface Data_insert
{
    id?:string;
    account_id:string;
    created_at?:string;
    last_modified_at?:string;
    created_by_user_id?:string;
    modified_by_user_id?:string;
    owner_user_id?:string;
    public?:string;
    data:Record<string,any>;
    table:string;
}

/**
 * @table user
 * @schema public
 */
export interface User
{
    /**
     * Id of the user
     */
    id:string;
    /**
     * Time stamp when the user was created
     */
    created_at:string;
    name:string;
    /**
     * Valid email
     */
    email:string;
    account_id:string;
    data:Record<string,any>;
}

/**
 * @insertFor User
 * @table user
 * @schema public
 */
export interface User_insert
{
    id?:string;
    created_at?:string;
    name:string;
    email:string;
    account_id:string;
    data:Record<string,any>;
}

Zod Schemas: src/zod-schemas.ts

import { z } from "zod";

/**
 * Zod schema for the "Account" interface
 * @table account
 * @schema public
 */
export const AccountSchema=z.object({
    id:z.string(),
    name:z.string(),
    created_at:z.string(),
    data:z.record(z.string(),z.any()),
});

/**
 * Zod schema for the "Account_insert" interface
 * @insertFor Account
 * @table account
 * @schema public
 */
export const Account_insertSchema=z.object({
    id:z.string().optional(),
    name:z.string(),
    created_at:z.string().optional(),
    data:z.record(z.string(),z.any()),
});

/**
 * Zod schema for the "Data" interface
 * @table data
 * @schema public
 */
export const DataSchema=z.object({
    id:z.string(),
    account_id:z.string(),
    created_at:z.string(),
    last_modified_at:z.string(),
    created_by_user_id:z.string().optional(),
    modified_by_user_id:z.string().optional(),
    owner_user_id:z.string().optional(),
    public:z.string(),
    data:z.record(z.string(),z.any()),
    table:z.string(),
}).describe("extra data for stuff");

/**
 * Zod schema for the "Data_insert" interface
 * @insertFor Data
 * @table data
 * @schema public
 */
export const Data_insertSchema=z.object({
    id:z.string().optional(),
    account_id:z.string(),
    created_at:z.string().optional(),
    last_modified_at:z.string().optional(),
    created_by_user_id:z.string().optional(),
    modified_by_user_id:z.string().optional(),
    owner_user_id:z.string().optional(),
    public:z.string().optional(),
    data:z.record(z.string(),z.any()),
    table:z.string(),
});

/**
 * Zod schema for the "User" interface
 * @table user
 * @schema public
 */
export const UserSchema=z.object({
    id:z.string().describe("Id of the user"),
    created_at:z.string().describe("Time stamp when the user was created"),
    name:z.string(),
    email:z.string().describe("Valid email"),
    account_id:z.string(),
    data:z.record(z.string(),z.any()),
});

/**
 * Zod schema for the "User_insert" interface
 * @insertFor User
 * @table user
 * @schema public
 */
export const User_insertSchema=z.object({
    id:z.string().optional(),
    created_at:z.string().optional(),
    name:z.string(),
    email:z.string(),
    account_id:z.string(),
    data:z.record(z.string(),z.any()),
});