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

Change Log

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.

Contributing

Please see Contributing

Enjoy!