Skip to content

A javascript friendly schema generator for relational databases.

License

Notifications You must be signed in to change notification settings

MattGson/relational-schema

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

87 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

npm GitHub tag Github last commit Pull Requests Welcome Commitizen friendly

Easily work with relational databases in Javascript and Typescript

Relational-schema is a utility package to generate a semantic, complete schema of a relational database (PostgreSQL, MySQL) in Javascript, JSON and other formats.

Works with MySQL and PostgreSQL. SQLite coming soon.

Full docs here

Why Relational-schema?

One of the key benefits of working with relational databases is the strict schema. Often in our applications we fail to take advantage of this, instead we rely on handwritten simplifications such as ORM models.

Relational-schema gives TS/JS developers a complete database schema including some nice additions (see below for specs). The schema can be output in json, es6, typescript and commonJS to suit multiple use-cases.

This schema can be used for building better, more automated tooling for working with relational databases in JS.

Projects using Relational-schema

  • Gybson - an auto generated, typesafe, flexible query client for working with relational databases in typescript.

Usage

npm install relational-schema

Create a config file relation-config.json

{
    "host": "127.0.0.1",
    "client": "mysql",
    "port": 3306,
    "user": "root",
    "password": "secure",
    "database": "mydb",
    "outdir": "src/schemas",
    "format": "json"
}
relations introspect

The schema will be generated in outdir in the chosen format.


Key features:

Full table definitions including:

  • columns (names, types, default values, nullability...)
  • keys and constraints
  • unique-ness
  • Typescript type mappings
  • table relations (including direction)
  • human readable relation alias'
  • soft-delete idenfication
  • enums

Example output:

{
    "database": "tests",
    "schema": "tests",
    "generatedAt": "2021-04-21T08:41:31.747Z",
    "tables": {
        "teams": {
            "primaryKey": {
                "constraintName": "teams_pkey",
                "constraintType": "PRIMARY KEY",
                "columnNames": ["team_id"]
            },
            "keys": [{ "constraintName": "teams_pkey", "constraintType": "PRIMARY KEY", "columnNames": ["team_id"] }],
            "uniqueKeyCombinations": [["team_id"]],
            "relations": [
                {
                    "toTable": "team_members",
                    "alias": "team_members",
                    "joins": [{ "fromColumn": "team_id", "toColumn": "team_id" }],
                    "type": "hasMany"
                }
            ],
            "columns": {
                "team_id": {
                    "dbType": "int4",
                    "columnDefault": "nextval('teams_team_id_seq'::regclass)",
                    "nullable": false,
                    "columnName": "team_id",
                    "tsType": "number"
                },
                "name": {
                    "dbType": "varchar",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "name",
                    "tsType": "string"
                },
                "deleted": {
                    "dbType": "bool",
                    "columnDefault": "false",
                    "nullable": true,
                    "columnName": "deleted",
                    "tsType": "boolean"
                }
            },
            "softDelete": {
                "dbType": "bool",
                "columnDefault": "false",
                "nullable": true,
                "columnName": "deleted",
                "tsType": "boolean"
            },
            "enums": {
                "teams_subscription_level": {
                    "enumName": "teams_subscription_level",
                    "values": ["BRONZE", "GOLD", "SILVER"],
                    "columnName": ""
                },
                "teams_permissions": { "enumName": "teams_permissions", "values": ["ADMIN", "USER"], "columnName": "" }
            }
        },
        "team_members": {
            "primaryKey": {
                "constraintName": "team_members_pkey",
                "constraintType": "PRIMARY KEY",
                "columnNames": ["team_id", "user_id"]
            },
            "keys": [
                {
                    "constraintName": "team_members_member_post_id_foreign",
                    "constraintType": "FOREIGN KEY",
                    "columnNames": ["member_post_id"]
                },
                {
                    "constraintName": "team_members_pkey",
                    "constraintType": "PRIMARY KEY",
                    "columnNames": ["team_id", "user_id"]
                },
                {
                    "constraintName": "team_members_team_id_foreign",
                    "constraintType": "FOREIGN KEY",
                    "columnNames": ["team_id"]
                },
                {
                    "constraintName": "team_members_user_id_foreign",
                    "constraintType": "FOREIGN KEY",
                    "columnNames": ["user_id"]
                }
            ],
            "uniqueKeyCombinations": [["team_id", "user_id"]],
            "relations": [
                {
                    "toTable": "posts",
                    "alias": "member_post",
                    "joins": [{ "fromColumn": "member_post_id", "toColumn": "post_id" }],
                    "type": "belongsTo"
                },
                {
                    "toTable": "teams",
                    "alias": "team",
                    "joins": [{ "fromColumn": "team_id", "toColumn": "team_id" }],
                    "type": "belongsTo"
                },
                {
                    "toTable": "users",
                    "alias": "user",
                    "joins": [{ "fromColumn": "user_id", "toColumn": "user_id" }],
                    "type": "belongsTo"
                },
                {
                    "toTable": "team_members_positions",
                    "alias": "team_members_position",
                    "joins": [
                        { "fromColumn": "team_id", "toColumn": "team_id" },
                        { "fromColumn": "user_id", "toColumn": "user_id" }
                    ],
                    "type": "hasOne"
                }
            ],
            "columns": {
                "team_id": {
                    "dbType": "int4",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "team_id",
                    "tsType": "number"
                },
                "user_id": {
                    "dbType": "int4",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "user_id",
                    "tsType": "number"
                },
                "member_post_id": {
                    "dbType": "int4",
                    "columnDefault": null,
                    "nullable": true,
                    "columnName": "member_post_id",
                    "tsType": "number"
                },
                "deleted": {
                    "dbType": "bool",
                    "columnDefault": "false",
                    "nullable": true,
                    "columnName": "deleted",
                    "tsType": "boolean"
                }
            },
            "softDelete": {
                "dbType": "bool",
                "columnDefault": "false",
                "nullable": true,
                "columnName": "deleted",
                "tsType": "boolean"
            },
            "enums": {
                "team_members_subscription_level": {
                    "enumName": "team_members_subscription_level",
                    "values": ["BRONZE", "GOLD", "SILVER"],
                    "columnName": ""
                },
                "team_members_permissions": {
                    "enumName": "team_members_permissions",
                    "values": ["ADMIN", "USER"],
                    "columnName": ""
                }
            }
        },
        "team_members_positions": {
            "primaryKey": {
                "constraintName": "team_members_positions_pkey",
                "constraintType": "PRIMARY KEY",
                "columnNames": ["team_id", "user_id"]
            },
            "keys": [
                {
                    "constraintName": "team_members_positions_position_manager_unique",
                    "constraintType": "UNIQUE",
                    "columnNames": ["manager", "position"]
                },
                {
                    "constraintName": "team_members_positions_pkey",
                    "constraintType": "PRIMARY KEY",
                    "columnNames": ["team_id", "user_id"]
                },
                {
                    "constraintName": "team_members_positions_team_id_user_id_foreign",
                    "constraintType": "FOREIGN KEY",
                    "columnNames": ["team_id", "user_id"]
                }
            ],
            "uniqueKeyCombinations": [
                ["manager", "position"],
                ["team_id", "user_id"]
            ],
            "relations": [
                {
                    "toTable": "team_members",
                    "alias": "team_member",
                    "joins": [
                        { "fromColumn": "team_id", "toColumn": "team_id" },
                        { "fromColumn": "user_id", "toColumn": "user_id" }
                    ],
                    "type": "hasOne"
                }
            ],
            "columns": {
                "team_id": {
                    "dbType": "int4",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "team_id",
                    "tsType": "number"
                },
                "user_id": {
                    "dbType": "int4",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "user_id",
                    "tsType": "number"
                },
                "position": {
                    "dbType": "varchar",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "position",
                    "tsType": "string"
                },
                "manager": {
                    "dbType": "varchar",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "manager",
                    "tsType": "string"
                },
                "verified": {
                    "dbType": "bool",
                    "columnDefault": "false",
                    "nullable": true,
                    "columnName": "verified",
                    "tsType": "boolean"
                }
            },
            "softDelete": null,
            "enums": {
                "team_members_positions_subscription_level": {
                    "enumName": "team_members_positions_subscription_level",
                    "values": ["BRONZE", "GOLD", "SILVER"],
                    "columnName": ""
                },
                "team_members_positions_permissions": {
                    "enumName": "team_members_positions_permissions",
                    "values": ["ADMIN", "USER"],
                    "columnName": ""
                }
            }
        },
        "users": {
            "primaryKey": {
                "constraintName": "users_pkey",
                "constraintType": "PRIMARY KEY",
                "columnNames": ["user_id"]
            },
            "keys": [
                {
                    "constraintName": "users_best_friend_id_foreign",
                    "constraintType": "FOREIGN KEY",
                    "columnNames": ["best_friend_id"]
                },
                { "constraintName": "users_email_unique", "constraintType": "UNIQUE", "columnNames": ["email"] },
                { "constraintName": "users_token_unique", "constraintType": "UNIQUE", "columnNames": ["token"] },
                { "constraintName": "users_pkey", "constraintType": "PRIMARY KEY", "columnNames": ["user_id"] }
            ],
            "uniqueKeyCombinations": [["email"], ["token"], ["user_id"]],
            "relations": [
                {
                    "toTable": "users",
                    "alias": "best_friend",
                    "joins": [{ "fromColumn": "best_friend_id", "toColumn": "user_id" }],
                    "type": "belongsTo"
                },
                {
                    "toTable": "posts",
                    "alias": "author_posts",
                    "joins": [{ "fromColumn": "user_id", "toColumn": "author_id" }],
                    "type": "hasMany"
                },
                {
                    "toTable": "posts",
                    "alias": "co_author_posts",
                    "joins": [{ "fromColumn": "user_id", "toColumn": "co_author" }],
                    "type": "hasMany"
                },
                {
                    "toTable": "team_members",
                    "alias": "team_members",
                    "joins": [{ "fromColumn": "user_id", "toColumn": "user_id" }],
                    "type": "hasMany"
                },
                {
                    "toTable": "users",
                    "alias": "users",
                    "joins": [{ "fromColumn": "user_id", "toColumn": "best_friend_id" }],
                    "type": "hasMany"
                }
            ],
            "columns": {
                "user_id": {
                    "dbType": "int4",
                    "columnDefault": "nextval('users_user_id_seq'::regclass)",
                    "nullable": false,
                    "columnName": "user_id",
                    "tsType": "number"
                },
                "best_friend_id": {
                    "dbType": "int4",
                    "columnDefault": null,
                    "nullable": true,
                    "columnName": "best_friend_id",
                    "tsType": "number"
                },
                "email": {
                    "dbType": "varchar",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "email",
                    "tsType": "string"
                },
                "first_name": {
                    "dbType": "varchar",
                    "columnDefault": null,
                    "nullable": true,
                    "columnName": "first_name",
                    "tsType": "string"
                },
                "last_name": {
                    "dbType": "varchar",
                    "columnDefault": null,
                    "nullable": true,
                    "columnName": "last_name",
                    "tsType": "string"
                },
                "password": {
                    "dbType": "varchar",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "password",
                    "tsType": "string"
                },
                "token": {
                    "dbType": "varchar",
                    "columnDefault": null,
                    "nullable": true,
                    "columnName": "token",
                    "tsType": "string"
                },
                "permissions": {
                    "dbType": "permissions",
                    "columnDefault": "'USER'::permissions",
                    "nullable": true,
                    "columnName": "permissions",
                    "tsType": "users_permissions"
                },
                "subscription_level": {
                    "dbType": "subscription_level",
                    "columnDefault": null,
                    "nullable": true,
                    "columnName": "subscription_level",
                    "tsType": "users_subscription_level"
                },
                "deleted_at": {
                    "dbType": "timestamptz",
                    "columnDefault": null,
                    "nullable": true,
                    "columnName": "deleted_at",
                    "tsType": "Date"
                }
            },
            "softDelete": {
                "dbType": "timestamptz",
                "columnDefault": null,
                "nullable": true,
                "columnName": "deleted_at",
                "tsType": "Date"
            },
            "enums": {
                "users_subscription_level": {
                    "enumName": "users_subscription_level",
                    "values": ["BRONZE", "GOLD", "SILVER"],
                    "columnName": ""
                },
                "users_permissions": { "enumName": "users_permissions", "values": ["ADMIN", "USER"], "columnName": "" }
            }
        },
        "posts": {
            "primaryKey": {
                "constraintName": "posts_pkey",
                "constraintType": "PRIMARY KEY",
                "columnNames": ["post_id"]
            },
            "keys": [
                {
                    "constraintName": "posts_author_id_foreign",
                    "constraintType": "FOREIGN KEY",
                    "columnNames": ["author_id"]
                },
                {
                    "constraintName": "posts_co_author_foreign",
                    "constraintType": "FOREIGN KEY",
                    "columnNames": ["co_author"]
                },
                { "constraintName": "posts_pkey", "constraintType": "PRIMARY KEY", "columnNames": ["post_id"] }
            ],
            "uniqueKeyCombinations": [["post_id"]],
            "relations": [
                {
                    "toTable": "users",
                    "alias": "author_",
                    "joins": [{ "fromColumn": "author_id", "toColumn": "user_id" }],
                    "type": "belongsTo"
                },
                {
                    "toTable": "users",
                    "alias": "co_author_",
                    "joins": [{ "fromColumn": "co_author", "toColumn": "user_id" }],
                    "type": "belongsTo"
                },
                {
                    "toTable": "team_members",
                    "alias": "team_members",
                    "joins": [{ "fromColumn": "post_id", "toColumn": "member_post_id" }],
                    "type": "hasMany"
                }
            ],
            "columns": {
                "post_id": {
                    "dbType": "int4",
                    "columnDefault": "nextval('posts_post_id_seq'::regclass)",
                    "nullable": false,
                    "columnName": "post_id",
                    "tsType": "number"
                },
                "author": {
                    "dbType": "varchar",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "author",
                    "tsType": "string"
                },
                "author_id": {
                    "dbType": "int4",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "author_id",
                    "tsType": "number"
                },
                "co_author": {
                    "dbType": "int4",
                    "columnDefault": null,
                    "nullable": true,
                    "columnName": "co_author",
                    "tsType": "number"
                },
                "message": {
                    "dbType": "varchar",
                    "columnDefault": null,
                    "nullable": false,
                    "columnName": "message",
                    "tsType": "string"
                },
                "rating_average": {
                    "dbType": "float4",
                    "columnDefault": "'0'::real",
                    "nullable": true,
                    "columnName": "rating_average",
                    "tsType": "number"
                },
                "created": {
                    "dbType": "timestamptz",
                    "columnDefault": "CURRENT_TIMESTAMP",
                    "nullable": true,
                    "columnName": "created",
                    "tsType": "Date"
                },
                "deleted": {
                    "dbType": "bool",
                    "columnDefault": "false",
                    "nullable": true,
                    "columnName": "deleted",
                    "tsType": "boolean"
                }
            },
            "softDelete": {
                "dbType": "bool",
                "columnDefault": "false",
                "nullable": true,
                "columnName": "deleted",
                "tsType": "boolean"
            },
            "enums": {
                "posts_subscription_level": {
                    "enumName": "posts_subscription_level",
                    "values": ["BRONZE", "GOLD", "SILVER"],
                    "columnName": ""
                },
                "posts_permissions": { "enumName": "posts_permissions", "values": ["ADMIN", "USER"], "columnName": "" }
            }
        }
    }
}

Relational-schema is built on top of trusted open-source projects:

About

A javascript friendly schema generator for relational databases.

Resources

License

Code of conduct

Stars

Watchers

Forks

Packages

No packages published

Contributors 4

  •  
  •  
  •  
  •