Automate creating DB Schemas by SilvenLEAF
Tags: javascript, typescript, node, silvenleaf
Published: 2021-11-03 15:53:40

Ahoy there! Here we are, automating some tedious boring stuff! Today we'll be automating the entire DB Schema generations. Who's excited?

We'll learn a bit of bash scripting on the way too! I'm really excited! Here we'll be using PostgreSQL, but you can use any SQL DB. We'll also be using Sequelize.

### Step 0: Project Setup
Assuming you already have a dummy project set up for this, if not, check this blog out **[How to use PostgreSQL with NodeJS](https://dev.to/silvenleaf/how-to-use-postgresql-with-nodejs-by-silvenleaf-1aef)** and finish your normal DB setup. Here I used PostgreSQL, but you can use any SQL DB you like! Once done, let the adventure begin!!

### Step 1: Create our automation base structure
Create a **autoGen** folder with this following structure
```
ROOT_PROJECT_DIRECTORY
├── autoGen (it is a folder)
│   ├── models (it is a folder) // we will have all our autogenerated models here
│   ├── autoGen.sh (it is a file) // it is our bash script
│   ├── rename.js (it is a file) // an optional file to do some custom renamings
│
├── node_modules
├── package.json
├── .gitignore
└── your other project stuff
```
Now that's done, let's move on the fun part, let's automate!

### Step 2: Understanding the automation logic
Before jumping into the meaty delicious stuff, first let's understand what we want to do.

First, we will autogenerate our models and schemas and will store it inside the **models** folder which is inside **autoGen** folder. Then if we want, we'll do some custom renamings or other conversions stuff. Later we will copy the generated final models and schemas in our **specific location**. Every time we do this, at the beginning we'll clear up the **final location models** folder and **autoGen/models** folders, just to make sure everything is clear.

So now finally let's learn some bash scripting.

### Step 3: Let's learn some bash scripting
But first, run the following command
```bash
npm i -D sequelize-auto
```
(**"npm i -D X"** is the short-hand for **"npm install --save-dev X"**)

Anyway, now that's out of the way. Let's begin the fun stuff.

In the **autoGen.sh** bash file, write the following contents
**Read the comments to understand what each word and each line does**
```bash
# By @SilvenLEAF
# command 1:
rm ../database/models/* # if you followed the blog link to setup the project, then you know that, this is our main root db models folder where we want to copy all our autoGenerated models because this is where we'll be using them from. But if you are using your own project or setup, then replace the path (after rm) with the path of your main "models" folder which you'll be using
# rm X command means remove or delete. X/* symbol means everything inside X. 
# So this command means, remove everything from our "database/models" folder, make it empty and clean



# command 2:
rm models/*
# Same as the previous command, remove everything from our models folder which is in the same level as this bash script
# I mean autoGen > models folder because it is in the same level as **autoGen.sh** file



# command 3:
echo "Started auto-generating the models"
# echo X command is the bash script version of "console.log(X)



# command 4:
../node_modules/.bin/sequelize-auto sequelize-auto -d YOUR_DATABASE_NAME -h YOUR_DATABASE_HOST -p YOUR_DATABASE_PORT -u YOUR_DATABASE_USER -x YOUR_DATABASE_PASSWORD -e TYPE_OF_YOUR_SQL_DATABASE -s YOUR_DATABASE_SCHEMA --cm p --cp c --sg -l es5_OR_es6_OR_esm_OR_ts
# If you globally installed sequelize-auto, then you could have run it like this
# sequelize-auto -d YOUR_DATABASE_NAME -h YOUR_DATABASE_HOST -p YOUR_DATABASE_PORT -u YOUR_DATABASE_USER -x YOUR_DATABASE_PASSWORD -e TYPE_OF_YOUR_SQL_DATABASE -s YOUR_DATABASE_SCHEMA --cm p --cp c --sg -l es5_OR_es6_OR_esm_OR_ts
# But since we didn't do that, we provided it's location, which is obviously from node_modules folder
# Just don't think too much, confirm the location path of node modules, everything else after that are same

# To Know More about this command 4, read below
# -------------------------------------------------------------
# The command 4, auto-generates the models

# Let's imagine our
# DATABASE_NAME = demo
# DATABASE_HOST = localhost 
# DATABASE_USER = silvenleaf
# DATABASE_PASSWORD = 12345678
# TYPE_OF_OUR_SQL_DATABASE = postgres
# DATABASE_SCHEMA = main

# And now we want to generate models written in TypeScript, so use this command 
# ../node_modules/.bin/sequelize-auto sequelize-auto -d demo -h localhost -p 5432 -u silvenleaf -x 12345678 -e postgres -s main --cm p --cp c --sg -l ts

# But if you want to generate models written in JavaScript, then use this command
# ../node_modules/.bin/sequelize-auto sequelize-auto -d demo -h localhost -p 5432 -u silvenleaf -x 12345678 -e postgres -s main --cm p --cp c --sg -l es6

# The final -l X decides whether we want JavaScript or TypeScript models, if not specified, it'll create JavaScript models, but if you specify "-l ts", it'll create TypeScript models
# -------------------------------------------------------------



# command 5: (Optional)
node rename.js
# This is the optional rename.js file. If you want to do some custom renamings, then run this file. Otherwise, remove this command



# command 6:
cp -R models/* ../database/models/
# cp command means copy. cp X/* Y/ means, copy everything from X to Y. Remember what this * means? * means everything :)
# Here the -R flag means to copy stuff recursively



# command 7:
ls models
# ls X command lists down all the contents (files and folders) inside X
# So here it'll list out all the models that were auto-generated
```
Great!! Now let's test it out

### Step 4: Testing the bash Script

#### Sub-step 1: Creating a demo database
First let's create our demo db so that we can test it, if you already have a db created with some tables, then skip this sub-step and go to the next one to run and test. Anyway, let's go.

First download **PgAdmin** (Only if you are using postgres for this tutorial, if not download a similar GUI software for your SQL database). It is a GUI for Postgres Database. But if you are using any other database or other GUIs, then that'd work too. Our main purpose is to create a demo db and some dummy tables. It doesn't matter if you create it from GUI or CLI or any place.

For keeping everything simple, I'm using GUI. If you don't have **PgAdmin** and want to download it, you can download it from [here](https://www.pgadmin.org/download/pgadmin-4-windows/)

Once done downloading and installing, you'll see something like this.
![Open PgAdmin](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z01m4cmrw7pdpdj06rhk.png)

Expand **Servers** and right click on **Databases** and select **Create > Database**. You'll see something like this. Give a name for your database (I gave **demo**) and click **save**.
![Create Database](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/4270pplui9kmislw4q0n.png)

Now, expand **Databases** and you'll see your **demo** database there. Expand it and click on **Schemas** and select **Create > Schema**. Give a name and click **save** (I gave **main**).
![Create Schema](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/sp49ughp3p6jr4pqyz9b.png)

Now, expand the **main** schema and click on **tables** and select **Create > Table**. And you know what to do right? Let's create a table. First give it a name (I gave **users**).
![Create Table](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tkseo3mccpafcsmdxzxk.png)

Then click on the **columns** tab. Then click on the **+** button. Give the column a name, choose it's data type etc etc. I'm creating only these 4 columns: **user_id, username, age, is_female**. You can create as much as you like. When done, click **save**
![Create Columns](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vp85h6z5r5mm23o7s2k7.png)

Great! Now we have a database and a table to test our automation script! Now let's go back to our project folder and update the command 4 of the bash script. Here, put your database name, host, user, password, and schema. If you followed me on the database creation then your command will look something like this
```bash
# command 4:
../node_modules/.bin/sequelize-auto sequelize-auto -d demo -h localhost -p 5432 -u YOUR_USER_NAME -x YOUR_USER_PASSWORD -e postgres -s main --cm p --cp c --sg -l ts

# Here I put "-l ts" to create TypeScript models. If you want JavaScript models, replace it with "-l es6" or "-l es5"
```

Now let's run this file. But before that, one IMPORTANT NOTE. If you are using TypeScript, and you have a **tsconfig.json** file. Then make sure your target is **es6**. Because Sequelize imports do not work with **es5**. So inside your **tsconfig.json**, make the target as **es6**
```json
{   
   "target": "es6",
   // other configs
}
```
#### Sub-Step 2: Run the bash Script and test
Now go inside that **/autoGen** folder and run this bash script. To run it, type the following command
```bash
bash autoGen.sh
```
**(bash X.sh runs the X.sh bash file)**
Hurray!! Magically all your Schemas are generated and also copied to the mentioned root location **(In this case, "../database/models")**. Congrats! You just automated the DB Schema generations!

Now create as much tables as you want, and update them as much as you want in the Database. And when you run this bash file, it'll automatically create all your updated Schemas and models! Yay!!

### Step 5: MORE on rename.js
One Important note about the **command 4**, the name of the autogenerated model is always in singular. So if your table name is **users** or **user**, both will be converted and the generated model name will be **user**. 

So, what if you want to change the generated names. Or customize other stuff or contents. That's where we'll be using that **rename.js** file. One example is below.

Suppose we had a table named as **usermeta**, then the generated model will be named as **usermetum** because **metum** is singular of **meta**. 

So if we want to change the file name and replace all **Usermetum** mentioned in it and inside **init-models** file as **Usermeta**, then we can use the rename.js file. You can write the logic like this with the help of **fs** module.
```javascript
// Use this script to rename any variables generated in models folder

const fs = require('fs');

let file = fs.readFileSync('./models/usermetum.ts', { encoding: 'utf-8' });
file = file.replace(/usermetum/g, 'usermeta');
file = file.replace(/Usermetum/g, 'Usermeta');
fs.writeFileSync('./models/usermeta.ts', file);
fs.unlink('./models/usermetum.ts', (err) => { //delete the original usermetum file
  if (err) throw err;
});



let file2 = fs.readFileSync('./models/init-models.ts', { encoding: 'utf-8' });
file2 = file2.replace(/usermetum/g, 'usermeta');
file2 = file2.replace(/Usermetum/g, 'Usermeta');
fs.writeFileSync('./models/init-models.ts', file2);
```

## Bonus Step: Creating a Server and REST APIs
### Sub-Step 1: Exporting all DB models dynamically
Assuming you have followed my last blog on how to set up the Database with Sequelize, create a **DBmodels.ts** file in the root directory. It'll have these contents
```typescript
import { Sequelize } from "sequelize";


const database = {
  host: 'yourDatabaseHost',
  user: 'yourDatabaseUser',
  password: 'yourDatabasePassword', 
  name: 'yourDatabaseName', 
};


// __________set up sequelize connection (just like mongoose.connect())
const sequelize = new Sequelize(
  database.name, database.user, database.password,
  {
    host: database.host,
    dialect: 'postgres',
  }
);


import { initModels } from "./database/models/init-models"; // from the root models folder, import the init-models file, this is NOT imported from './autoGen/models' but from the root models folder (if you followed my last blog, it'll be './database/models')
const DBmodels = initModels(sequelize);
export default DBmodels;
```

#### Sub-Step 2: Create the Server and API
Let's create the APIs to do some CRUD stuff to test out our DB. You can use anything, restify, hapi, loopback, express or anything. I'm goin' with **express**. Why? Because it's so express-y haha!

For those who are a bit lost on how to create an express server with TypeScript, check this one out
[How to Create a TypeScript Project with ExpressJS the Simplest Way!!](https://dev.to/silvenleaf/how-to-create-a-typescript-project-with-expressjs-the-simplest-way-578a)

In short (for details, checkout above link),
**0. install packages**
```bash
npm i typescript ts-node express @types/node @types/express
```
**1. init tsconfig** (make sure you already have typescript globally installed, if not type **npm i -g typescript**. And don't get it confused with the previous normal **npm i typescript etc etc**)
```bash
tsc --init
```
**create server on our app.ts file**
Create an **app.ts** file in the root level of our project directory and write the following contents.
```typescript
import express, { Request, Response } from 'express';
import DBmodels from './DBmodels'
const { User } = DBmodels; // this DBmodels object has all models dynamically inside it. So you can also find amazing autocomplete if you are using TypeScript



// -------------------firing express app
const app = express();
app.use(express.json());
app.use(express.urlencoded({extended:false}));




// -------------------routes
// Create record
app.post('/users', async (request: Request, response: Response)=>{
  const createdUser = await User.create({ username: 'SilvenLEAF', age: new Date().getFullYear() - 2001, isFemale: false });
  response.json(createdUser)
});

// Read/Get record
app.get('/users', async (request: Request, response: Response)=>{
  const allUsers = await User.findAll({});
  response.json(allUsers)
});

// Update record
app.put('/users/:userId', async (request: Request, response: Response)=>{
  const { userId } = request.query || {};
  const updatedUser = await User.update({ username: 'SilvenLEAF (Manash Sarma)' }, { where: { userId }});
  response.json(updatedUser)
});

// Delete record
app.delete('/users/:userId', async (request: Request, response: Response)=>{
  const { userId } = request.query || {};
  await User.destroy({ where: { userId } });
  response.json({ message: 'Record deleted!'})
});





// --------------------Listen
const PORT = process.env.PORT || 5000;
app.listen(PORT, ()=>{
  console.log(`Server running on PORT ${ PORT }`);
})
```
Now, to test out our server, run **ts-node app.ts** and make those http requests. Congrats!! We learnt how to automate all DB Schema and model generations, we also learnt about bash scripting and much more! Did you enjoy it? If yes, let me know in the comments!

# What's NEXT?
#### 1. Improved AI BOT that can do anything
#### 2. Insane stuff with JavaScript/TypeScript
#### 3. Debugging TypeScript with VS Code Debugger
#### 4. How to automate anything
#### 5. Sequelize Hooks
#### 6. How to deploy in the Cloud (Azure VMs)
#### 7. How to create an Android APP with NO XP
(including apk generating)


# Got any doubt? 
Drop a comment or Feel free to reach out to me @SilveLEAF on Twitter or [Linkedin](https://www.linkedin.com/in/silvenleaf/)

Wanna know more about me? Come here! 
[SilvenLEAF.github.io](https://SilvenLEAF.github.io)