Db-modeling-markdown NPM

db-modeling-markdown README

Now, you may define your database tables in markdown files, and this utility will use them to generate .sql files.

API Doc

Release Notes

2.0.2

Samples

Define a markdown file(.db.md) like：

# Table: departments

## `Table`

| `Name`      | `Comment`             | `Character Set` | `Collation`        | `Engine` |
| ----------- | --------------------- | --------------- | ------------------ | -------- |
| departments | The department table. | utf8mb4         | utf8mb4_general_ci | InnoDB   |

## `Primary Key`

| `Columns`    |
| ------------ |
| DepartmentID |

## `Indexes`

## `Foreign Keys`

| `Columns` | `Ref Table` | `Ref Columns` | `Options` |
| --------- | ----------- | ------------- | --------- |
| ManagerID | employees   | EmployeeID    |           |
| ParentID  | departments | DepartmentID  |           |

## `Columns`

| `Label`         | `Name`         | `Type`                                 | `Nullable` | `Default`           | `Comment`            |
| --------------- | -------------- | -------------------------------------- | ---------- | ------------------- | -------------------- |
| Department ID   | DepartmentID   | int auto_increment                     | `No`       |                     | Department ID        |
| Department Name | DepartmentName | varchar(50)                            | `No`       |                     | Department Name      |
| Parent ID       | ParentID       | int                                    | `Yes`      |                     | Parent Department    |
| Manager ID      | ManagerID      | int                                    | `Yes`      |                     | Manager              |
| Created By      | CreatedBy      | varchar(255)                           | `No`       | ''                  | Created By User Name |
| Create Time     | CreateTime     | datetime                               | `No`       | current_timestamp() | Created Time         |
| Update By       | UpdateBy       | varchar(255)                           | `No`       | ''                  | Updated By User Name |
| Update Time     | UpdateTime     | datetime on update current_timestamp() | `No`       | current_timestamp() | Updated Time         |

You will get SQL scripts:

-- Create table 'departments'
CREATE TABLE `departments` (
    `DepartmentID` int auto_increment NOT NULL COMMENT 'Department ID'
  , `DepartmentName` varchar(50) NOT NULL COMMENT 'Department Name'
  , `ParentID` int COMMENT 'Parent Department'
  , `ManagerID` int COMMENT 'Manager'
  , `CreatedBy` varchar(255) NOT NULL DEFAULT '' COMMENT 'Created By User Name'
  , `CreateTime` datetime NOT NULL DEFAULT current_timestamp() COMMENT 'Created Time'
  , `UpdateBy` varchar(255) NOT NULL DEFAULT '' COMMENT 'Updated By User Name'
  , `UpdateTime` datetime on update current_timestamp() NOT NULL DEFAULT current_timestamp() COMMENT 'Updated Time'
  , PRIMARY KEY (DepartmentID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='The department table.'
;

Sample Code

import * as path from 'path'

import { DatabaseTypes, DbmOptions, DbmService } from '../src'

const options: DbmOptions = new DbmOptions()
options.database = DatabaseTypes.mariadb

// Generate database modeling markdown files (.db.md) from a database
options.sourceFolder = path.join(__dirname, `../../temp/tables/v1`)
await DbmService.fromDatabase(
	{
		databaseType: options.database,
		host: '127.0.0.1',
		port: 3306,
		user: 'root',
		password: 'password',
		database: 'my-db',
	},
	options.sourceFolder,
	true,
)

// Read table definition markdow files(.db.md), and produce a sql file with create-table-if-not-exists scripts
options.sourceFolder = path.join(__dirname, '../tests/data/mariadb/tables')
options.targetPath = path.join(__dirname, '../temp/createTableIf.sql')
DbmService.toCreateTableIfNotExistsSql(options)

// Read table definition markdow files(.db.md), and produce a sql file with create-table scripts
options.targetPath = path.join(__dirname, '../temp/createTable.sql')
DbmService.toCreateTableSql(options)

// Read table definition markdow files(.db.md), and produce a sql file with drop-table scripts
options.targetPath = path.join(__dirname, '../temp/dropTable.sql')
DbmService.toDropTableSql(options)

// Read table definition markdow files(.db.md), and produce a sql file with drop-then-create-table scripts
options.targetPath = path.join(__dirname, '../temp/dropThenCreateTable.sql')
DbmService.toDropThenCreateTableSql(options)

// Read table definition markdow files(.db.md), and produce a sql file with upgrade scripts
options.previousVersionFolder = path.join(__dirname, `../tests/data/mariadb/tables/v1`)
options.targetPath = path.join(__dirname, '../temp/upgrade.sql')
DbmService.toUpgradeSql(options)

// Read table definition markdow files(.db.md), and produce a json file
options.targetPath = path.join(__dirname, '../temp/model.json')
DbmService.toJson(options)

// Read table definition markdow files(.db.md), and produce a model object
options.targetPath = ''
const model = DbmService.toModel(options)
// eslint-disable-next-line no-console
console.log(JSON.stringify(model, null, 2))

// Generate database modeling markdown files (.db.md) from a model
DbmService.toMarkdown(model, path.join(__dirname, '../temp/tables'), true)

// Generate index.md from a model
DbmService.genIndexFile(model, options.sourceFolder)

Features

Support Databases
- DB2
- MariaDB
- MS SQL Server
- MySQL
- Oracle Database
- PostgreSQL
- SQLite
Support generate SQL scripts of create tables.
Support generate SQL scripts of create tables if not exists.
Support generate SQL scripts of drop then create tables.
Support generate SQL scripts of drop tables.
Support generate SQL scripts of upgrade a database (mariadb/mysql only).
Support generate database definition markdown files from a database (mariadb/mysql only).
Support generate a json file of the database model.
Support generate an model object of the database model.
Support generate database definition markdown files from a database model (json).
Support generate index.md for a database model.