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 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

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

# 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

Once done downloading and installing, you'll see something like this.

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.

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).

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).

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

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

# 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

{   
   "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 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.

// 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

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!!

In short (for details, checkout above link),
0. install packages

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)

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.

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

Wanna know more about me? Come here!
SilvenLEAF.github.io

Blog

Automate creating DB Schemas by SilvenLEAF

SilvenLEAF