selfclose-sql-schematic

NodeJS modules is mysql schematic builder, or create table if not exists helper

This package

• forge query string for MySQL / MariaDB
• No dependencies
• Extreme Light

Installation

npm i --save selfclose-sql-schematic

Todo

• add set timestamp option
• add emum type
• ALTER mode (rather update instead of destroy table)

Currently Written:

• MySQL / MariaDB

Table of Contents

• Quick Example

Quick Example

This quick example shows how it's looks and usage.

1const schema = require('selfclose-sql-schematic');
2var s = schema.createTableIfNotExist('post', {
3    comment: 'this is post table'
4});
5s.add('enabled').type(schema.type.TINYINT, 4).notNull().default(1);
6s.add('title').type(schema.type.VARCHAR, 60).notNull();
7s.add('type').type(schema.type.VARCHAR, 60).notNull().default('post');
8s.add('money').type(T.DECIMAL, [10,2]).notNull();
9s.add('permalink').type(schema.type.VARCHAR, 120).notNull().unique();
10console.log('MYSQL', s.toString());

And You'll get

1CREATE TABLE IF NOT EXISTS `post` (`id` INT NOT NULL AUTO_INCREMENT, `enabled` TINYINT(4) NOT NULL DEFAULT 1, `title` VARCHAR(60) NOT NULL, `type` VARCHAR(60) NOT NULL DEFAULT 'post', `permalink` VARCHAR(120) NOT NULL, `created_at` DATETIME NOT NULL,
2`updated_at` DATETIME NOT NULL, PRIMARY KEY (`id`), UNIQUE INDEX `permalink` (`permalink`)) COMMENT='this is post table' COLLATE='utf8_general_ci' ENGINE=InnoDB

Then, Take these query string to mysql query function

sql.query(s.toString());

Methods

First create variable

1var schema = FunctionHere()

Functions

• createTable(table_name, options)
• createTableIfNotExist(table_name, options)

Options

(Values as shown is default)

1{
2    comment: '',
3    id: true, //Auto create id column with auto increment and primary key
4    timestamp: true, //Auto create 'created_at' and 'updated_at'
5    timestamp_type: 'DATETIME', // type can be 'DATETIME' or 'NUMBER'
6    charset: 'utf8_general_ci',
7    engine: 'InnoDB'
8}

Next will be chain functions

Chain Function

Most of theme already mysql syntax

• add(column name)
• type(type name)
• notNull()
• unique()
• default(value)
• comment(column comment)
• primaryKey()
• foreignKey(column, target_table, target_table_column)
• onUpdate(action)
• onDelete(action)
• toString() //End of chain for Return Query String

---

Another Example

Add with Object, With this define you will be easier to reuse variable later

1const schema = require('selfclose-sql-schematic');
2var s = schema.createTableIfNotExist('post', {
3    comment: 'this is post table'
4});
5let model = {
6    enabled: {
7        type: T.TINYINT,
8        length: 4,
9        notNull: true,
10        default: 1
11    },
12    title: {
13        type: T.VARCHAR,
14        length: 60,
15        notNull: true,
16        regex: /^[a-zA-Z]{10,60}$/ //This is not Module's variable, But can be use later
17    },
18    type: {
19        type: T.VARCHAR,
20        length: 60,
21        notNull: true,
22        default: 'post'
23    },
24    permalink: {
25        type: T.VARCHAR,
26        length: 120,
27        notNull: true,
28        unique: true
29    },
30};
31s.add(model);
32console.log('MYSQL', s.toString());
33
34//Reuse variable
35let _title = 'HELLO!';
36if (_title.length > model.title.length) {
37    console.log('-- Title is too long')
38}
39if (!model.title.regex.test(_title)) {
40    console.log('-- Title must be only English Characters!');
41}

Foreign Key

Chain with .foreignKey(targetTable, targetTableColumn, onDelete, onUpdate)

1const T = schema.type;
2const A = schema.action;
3
4var member = schema.createTableIfNotExist('member', {
5    comment: 'member'
6});
7member.add('username').type(T.VARCHAR, 50).notNull().unique();
8member.add('age').type(T.INT, 4).notNull().default(18);
9console.log('MYSQL', member.toString());
10
11var member_type = schema.createTableIfNotExist('member_type', {
12    comment: 'pair with member'
13});
14member_type.add('name').type(T.VARCHAR, 20).notNull().unique().foreignKey('member', 'id', A.CASCADE, A.NO_ACTION);
15
16console.log('MYSQL', member_type.toString());
17

##--WILL CONTINUE WRITE README SOON--