relational-schema
Advanced tools
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
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 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.
{
"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:
FAQs
Generate a js schema for a relational database
The npm package relational-schema receives a total of 897 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
CISOs are racing to adopt AI for cybersecurity, but hurdles in budgets and governance may leave some falling behind in the fight against cyber threats.
Research
Security News
Socket researchers uncovered a backdoored typosquat of BoltDB in the Go ecosystem, exploiting Go Module Proxy caching to persist undetected for years.
Security News
Company News
Socket is joining TC54 to help develop standards for software supply chain security, contributing to the evolution of SBOMs, CycloneDX, and Package URL specifications.