relational-schema
Advanced tools
Readme
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.
Relational-schema is built on top of trusted open-source projects:
Works with MySQL and PostgreSQL. SQLite coming soon.
Full docs here
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
npm install relational-schema
Create a config file introspect-config.json
{
"host": "127.0.0.1",
"port": 3306,
"user": "root",
"password": "secure",
"database": "mydb",
"outdir": "src/schemas",
"format": "json"
}
relational-schema introspect
The schema will be generated in outdir in the chosen format.
{
"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": "" }
}
}
}
}
FAQs
Generate a js schema for a relational database
The npm package relational-schema receives a total of 476 weekly downloads. As such, relational-schema popularity was classified as not popular.
We found that relational-schema demonstrated a not healthy version release cadence and project activity because the last version was released a year ago. It has 1 open source maintainer 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
A hospital in Mobile, Alabama, agreed to a settlement in a landmark ransomware death lawsuit, but is now reportedly reconsidering the agreement and refusing to pay.
Security News
A new report explores how advancements in LLMs are enhancing cyber threats, including polymorphic malware, personalized spearphishing, and the risk of hijacking customer service bots.
Security News
ESLint has approved an RFC that adds support for TypeScript configuration files, which is aimed at improving the developer experience and recognizing changes in the evolving JavaScript ecosystem.