Santuy

Santuy is a nodejs framework and database generator from model schema.

You are viewing docs for the v1 of santuy

Features:

• Mysql and Postgresql support
• Sync database from model schema
• Database Relation & include support
• Database Seed support
• Built-in ACID Transaction support (automatic rollback if query fails)
• Built-in CRUD
• Raw query support
• Built-in pagination
• Powerful TypeScript support

ACID Transaction support between PostgreSQL and MySQL

Mysql

1. DML (Yes)
2. DDL (Mysql < 8 (No) / Mysql > 8 (Single statement atomic DDL))

Postgresql

1. DML (Yes)
2. DDL (Yes)

Getting Started

1npm i santuy

Santuy Commands

1init "Generate santuy directory"
2sync "Database sync"
3seed "Database seeder"
4generate "Generate model or seed"

Installation

1-- create database
2CREATE DATABASE `database_name`;
3

1npx santuy init

Generate Model

1npx santuy generate model [model_name]

Models Example

1npx santuy generate model users

[use lowercase underscores for model name and column name]

users.js:

1//model users (file: santuy/models/users.js)
2
3
4const UsersModel = {
5    name: 'users',
6    icon: 'AiFillCaretRight',
7    columns: [
8        {
9            name: 'id',
10            title: 'ID',
11            dataType: 'INT AUTO_INCREMENT PRIMARY KEY',
12            inputType: 'number',
13        },
14        {
15            name: 'username',
16            title: 'Username',
17            dataType: 'VARCHAR(100) NULL',
18            inputType: 'text',
19        },
20        {
21            name: 'password',
22            title: 'Password',
23            dataType: 'VARCHAR(255) NULL',
24            inputType: 'password',
25        },
26        {
27            name: 'name',
28            title: 'Name',
29            dataType: 'VARCHAR(30) NULL',
30            inputType: 'text',
31        },
32        {
33            name: 'avatar',
34            title: 'Avatar',
35            dataType: 'VARCHAR(100) NULL',
36            inputType: 'image',
37        },
38        {
39            name: 'address',
40            title: 'Address',
41            dataType: 'VARCHAR(100) NULL',
42            inputType: 'textarea',
43        },
44    ],
45}
46
47
48module.exports = UsersModel
49
50

1npx santuy generate model categories

categories.js:

1//model categories (file: santuy/models/categories.js)
2
3
4const CategoriesModel = {
5    name: 'categories',
6    icon: 'AiFillCaretRight',
7    columns: [
8        {
9            name: 'id',
10            title: 'ID',
11            dataType: 'INT AUTO_INCREMENT PRIMARY KEY',
12            inputType: 'number',
13        },
14        {
15            name: 'name',
16            title: 'Category Name',
17            dataType: 'VARCHAR(50) NULL',
18            inputType: 'text',
19        },
20    ],
21}
22
23
24module.exports = CategoriesModel
25
26

1npx santuy generate model products

products.js:

1//model products (file: santuy/models/products.js)
2
3
4const ProductsModel = {
5    name: 'products',
6    icon: 'AiFillCaretRight',
7    columns: [
8        {
9            name: 'id',
10            title: 'ID',
11            dataType: 'INT AUTO_INCREMENT PRIMARY KEY',
12            inputType: 'number',
13        },
14        {
15            name: 'category_id',
16            title: 'Category',
17            dataType: 'INT NULL',
18            inputType: 'select',
19            selectData: "categories",
20            relation: {
21                field: 'category_id',
22                reference: 'categories.id',
23                select: 'categories.name'
24            },
25        },
26        {
27            name: 'name',
28            title: 'Item Name',
29            dataType: 'VARCHAR(100) NULL',
30            inputType: 'text',
31        },
32        {
33            name: 'plu',
34            title: 'PLU',
35            dataType: 'VARCHAR(50) NULL',
36            inputType: 'text',
37        },
38        {
39            name: 'unit',
40            title: 'Unit',
41            dataType: 'VARCHAR(30) NULL',
42            inputType: 'text',
43        },
44        {
45            name: 'cost',
46            title: 'Cost',
47            dataType: 'INT NULL',
48            inputType: 'number',
49        },
50        {
51            name: 'price',
52            title: 'Price',
53            dataType: 'INT NULL',
54            inputType: 'number',
55        },
56        {
57            name: 'qty',
58            title: 'Qty',
59            dataType: 'INT NULL',
60            inputType: 'number',
61        },
62    ]
63}
64
65
66module.exports = ProductsModel
67
68

.env file

[mysql] DATABASE_URL="mysql://USER:PASSWORD@HOST:PORT/DATABASE"

[postgresql] DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE"

.env file example mysql

DATABASE_URL="mysql://root:@localhost:3306/database_name"

.env file example postgresql

DATABASE_URL="postgresql://postgres:password@localhost:5432/database_name"

Database Sync

1npx santuy sync

[*if sync fails with relations, try to reorder models list from models/schema.js]

Seed

1npx santuy generate seed [model_name]

Example Seed

1npx santuy generate seed users

users.json

[seed users (file: santuy/seeds/users.json)]

1[
2    {
3        "username": "admin",
4        "password": "admin123",
5        "name": "Admin",
6        "avatar": "https://ui-avatars.com/api/?name=Admin%20Dashboard",
7        "address": "Jl. Ahmad Yani No. 790"
8    }
9]

example seeding users

1npx santuy seed users

API SETUP

Express JS example

[routes/index]

1var express = require('express')
2var router = express.Router()
3const { models } = require("../santuy/schema")
4const { get, detail, create, update, remove, restore, raw } = require("santuy")
5
6
7/* GET DATA. */
8/* GET: http://localhost:3000/?model=users */
9router.get('/', async function (req, res, next) {
10  const modelName = req.query.model
11  let page = req.query.page ?? ""
12  let limit = req.query.limit ?? "10"
13  const model = models[modelName]
14  page = parseInt(page)
15  limit = parseInt(limit)
16  let payload = {
17    model,
18    paginate: page ? {
19      page,
20      limit
21    } : null
22  }
23  const response = await get(payload)
24  if (response) {
25    res.send(response)
26
27  } else {
28    res.send({ message: "error" })
29  }
30})
31
32/* GET DETAIL DATA. */
33/* GET: http://localhost:3000/1/?model=users */
34router.get('/:id', async function (req, res, next) {
35  let id = req.params.id ?? ""
36  const modelName = req.query.model
37  const model = models[modelName]
38  let payload = {
39    model,
40    id
41  }
42  const response = await detail(payload)
43  if (response) {
44    res.send(response)
45
46  } else {
47    res.send({ message: "error" })
48  }
49})
50
51/* CREATE DATA. */
52/* POST: http://localhost:3000/?model=users */
53router.post('/', async function (req, res, next) {
54  const modelName = req.query.model
55  const model = models[modelName]
56  const data = req.body
57  let payload = {
58    model,
59    data
60  }
61  const response = await create(payload)
62  if (response) {
63    res.send(response)
64
65  } else {
66    res.send({ message: "error" })
67  }
68})
69
70/* UPDATE DATA. */
71/* PUT: http://localhost:3000/1/?model=users */
72router.put('/:id', async function (req, res, next) {
73  let id = req.params.id ?? ""
74  const modelName = req.query.model
75  const model = models[modelName]
76  const data = req.body
77  let payload = {
78    model,
79    data,
80    id
81  }
82  const response = await update(payload)
83  if (response) {
84    res.send(response)
85
86  } else {
87    res.send({ message: "error" })
88  }
89})
90
91/* DELETE DATA. */
92/* DELETE: http://localhost:3000/1/?model=users */
93router.delete('/:id', async function (req, res, next) {
94  let id = req.params.id ?? ""
95  const modelName = req.query.model
96  const model = models[modelName]
97  let payload = {
98    model,
99    id
100  }
101  const response = await remove(payload)
102  if (response) {
103    res.send(response)
104
105  } else {
106    res.send({ message: "error" })
107  }
108})
109
110/* RESTORE DATA. */
111/* PUT: http://localhost:3000/restore/1/?model=users */
112router.put('/restore/:id', async function (req, res, next) {
113  let id = req.params.id ?? ""
114  const modelName = req.query.model
115  const model = models[modelName]
116  let payload = {
117    model,
118    id
119  }
120  const response = await restore(payload)
121  if (response) {
122    res.send(response)
123
124  } else {
125    res.send({ message: "error" })
126  }
127})
128
129module.exports = router
130

[routes/query]

1var express = require('express')
2var router = express.Router()
3const { raw } = require("santuy")
4
5/* RAW QUERY. */
6/* GET: http://localhost:3000/query/raw/ */
7router.get('/raw', async function (req, res, next) {
8
9  const response = await raw("SELECT * FROM users")
10  if (response) {
11    res.send(response)
12
13  } else {
14    res.send({ message: "error" })
15  }
16})
17
18module.exports = router
19

Types

1export interface DatabaseType {
2    host: string | 'localhost';
3    user: string | 'root';
4    password: string;
5    port: number | 3306;
6    database: string;
7}
8
9export interface ModelType {
10    name: string;
11    icon?: string;
12    columns: Array<ColumnType>;
13    includes?: Array<IncludeType>;
14}
15
16export interface ColumnType {
17    name: 'id' | string | ModelType;
18    title: string;
19    dataType?: string;
20    inputType?: InputType;
21    selectData?: string | Array<string>;
22    relation?: RelationType;
23}
24
25type InputType = 'text' | 'number' | 'password' | 'email' | 'select' | 'textarea' | 'file' | 'image' | 'hidden' | 'checkbox';
26export interface IncludeType {
27    model: ModelType;
28    relation: string;
29}
30
31export interface RelationType {
32    field: string;
33    reference: string;
34    select: string;
35}
36
37export interface SyncType {
38    models: any;
39}
40
41export interface SeedType {
42    model: ModelType;
43    path: string;
44}
45
46export interface GetType {
47    model: ModelType;
48    paginate?: PaginateType | null;
49}
50
51export interface DetailType {
52    model: ModelType;
53    id: number | string;
54}
55
56export interface CreateType {
57    model: ModelType;
58    data: any;
59}
60
61export interface UpdateType {
62    model: ModelType;
63    data: any;
64    id: number | string;
65}
66
67export interface RemoveType {
68    model: ModelType;
69    id: number | string;
70}
71
72export interface RestoreType {
73    model: ModelType;
74    id: number | string;
75}
76
77export interface PaginateType {
78    page: number;
79    limit: number;
80}
81
82export interface ResultType {
83    data: Array<Object | null> | null | false | undefined;
84    page?: number;
85    limit?: number;
86    total?: number;
87}
88