typorm multiple connectionsexpressjs_nodejs

CRUD with Nodejs, expressjs, typeOrm, postgres

we are going to write crud operations for the RestAPI with nodejs and expressjs . We will use Typeorm which is Object relational mapper. we will connect to postgres. As we are using typeorm the database code will be always same and we can change the database connection based on our usability.

In future, If you want to change database just change the connection type , no need to change code

Prerequisites:

  1. Setup nodejs, expressjs running locally, if you dont have check out here how to setup.
  2. Install Postgres

You can get complete code from my github https://github.com/MDPuneethReddy/typeorm_postgres_nodejs.

SetUp:

  1. Install typeorm,pg,reflect-metadata

yarn add typeorm pg reflect-metadata

Here pg is the postgres database, you can use any other database.

To install other databases please checkout official documentation here.

package.json

{
  "name": "typeorm-postgresql-nodejs",
  "version": "1.0.0",
  "main": "index.ts",
  "license": "MIT",
  "scripts": {
    "start": "nodemon index.ts"
  },
  "dependencies": {
    "@types/cors": "^2.8.10",
    "@types/express": "^4.17.9",
    "cors": "^2.8.5",
    "express": "^4.17.1",
    "nodemon": "^2.0.7",
    "pg": "^8.5.1",
    "reflect-metadata": "^0.1.13",
    "ts-node": "^9.1.1",
    "typeorm": "^0.2.29",
    "typescript": "^4.1.3"
  }
}

The Initial Express nodejs app will look like this

import * as express from "express"
import * as cors from "cors"
const app=express()
app.use(cors())
app.use(express.json())
const server=app.listen(3000,()=>{
    console.log("server running at 3000....")
})
app.get("/api",(req,res)=>{
    res.send("Welcome to API")
})

Add Entity:

First we need to create entities , here entities are the structure of the tables which we are going to store in the database. In this example we will have users entity.

So create a seperate folder for entities and create a seperate file for users entity and name as users.ts

import {Entity, Column, PrimaryGeneratedColumn} from "typeorm";
@Entity()
export class users {

    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @Column()
    email: string;
}

Here we created a class, we know class is a blueprint. So we create a class how the table users should be. Here we 3 columns id which is autogenerated, and other two email and name are user details.

You will see warnings for the typeorm decorators, so add this two lines in tsconfig file

"emitDecoratorMetadata": true,
"experimentalDecorators": true,

Next we need to create a connection between database and typeorm

Create Connection:

Now we need to Create connection between database and typeorm. So, first create a folder connection and create a file connection.ts in that folder.

Here we can create multiple connections, for now we create a single connection.

import { users } from "../entities/users";
import { createConnection } from "typeorm";

export const connection = createConnection({
  type: "postgres" ,
  host: "localhost",
  port: 5432,
  username: "postgres",
  password: "1234",
  database: "demo",
  entities: [users],
  synchronize: true,
  logging: false
});

Add Connection:

Now we need to add connection to expressjs App, we already saw the basic code of the expressjs application above,

next we need to add this connection.

so, first import connection and call connection.

Inside connection to access users table typeorm have a method getRepository, if connection is set then we have access to all the entities of that connection, so we access the users entity and store it in a variable

import * as express from "express"
import { connection } from "./connection/connection"

import * as cors from "cors"
const app=express()
app.use(cors())
app.use(express.json())
const server=app.listen(3000,()=>{
    console.log("server running at 3000....")
})
app.get("/api",(req,res)=>{
    res.send("Welcome to API")
})
connection.then(
    async connection=>{
        console.log("connected")
      
  const usersRepository = connection.getRepository(users);
    }
).catch(error=>{
    console.log(error)
})

Add CRUD Operations:

Add CRUD operations inside connection and we can access users table by usersRepository, so we will do all operations on usersRepository

GET:

we added get path to get all the users, here find() will get all users from the table and you can write conditions with find to get the desired outputs. so now we will get all the users and send that response, check the code below

app.get("/api/users",async (req,res)=>{
            const users=await usersRepository.find()
            res.send(users)
        })

if you want to get based on ID or based on params, we can use where command to find based on condition.

You can use find or findone, based on the required scenario, then we will use the condition to filter out, we can get prameters from params or query or body.

app.get("/api/users/:id",async(req,res)=>{

          const user=await usersRepository.findOne({where: { id: req.params.id }})
          res.json({
              message:"success",
              payload: user
          })
      })

POST:

If the post items is sent in req.body, so we will create a user based on req.body, we saw earlier as users table as a class, so basically we will create a object based on the class with values from req.body and save the user .

check code below

app.post("/api/users",async (req,res)=>{
            
            console.log("body",req.body)
            const user=await usersRepository.create(req.body)
            const results = await usersRepository.save(user);
            
      res.json({
        message: "success",
        payload: results
      });
    })

DELETE:

To delete user based on id, we will use delete command and pass the unique paramater to delete that row from the table.

app.delete("/api/users/:id",async(req,res)=>{
        const user=await usersRepository.delete(req.params.id)
        res.json({
            message:"success",
        })
    })

PUT:

PUT command is used to change the item inside the table, so basically we need to find that item and need to change with new values.

Here we will merge old with new values and save back the user again

app.put("/api/users/:id",async(req,res)=>{
        const user=await usersRepository.findOne(req.params.id)
        usersRepository.merge(user, req.body);
        const result = await usersRepository.save(user);
        res.json({
            message:"success",
            payload:result
        })
  
    })

This is how we will use the CRUD operations to change or retrieve things from database.

check out the complete code below for the CRUD operations

import * as express from "express"
import { connection } from "./connection/connection"
import { users } from "./entities/users"
import * as cors from "cors"
const app=express()
app.use(cors())
app.use(express.json())
const server=app.listen(3000,()=>{
    console.log("server running at 3000....")
})
app.get("/api",(req,res)=>{
    res.send("Welcome to API")
})
connection.then(
    async connection=>{
        console.log("connected")
        const usersRepository = connection.getRepository(users);
        app.get("/api/users",async (req,res)=>{
            const users=await usersRepository.find()
            res.send(users)
        })
        app.post("/api/users",async (req,res)=>{
            
            console.log("body",req.body)
            const user=await usersRepository.create(req.body)
            const results = await usersRepository.save(user);
            
      res.json({
        message: "success",
        payload: results
      });
    })
      app.get("/api/users/:id",async(req,res)=>{
        console.log("called")
          console.log(req.params.id)
          const user=await usersRepository.findOne({where: { id: req.params.id }})
          res.json({
              message:"success",
              payload: user
          })
      })
      app.delete("/api/users/:id",async(req,res)=>{
        const user=await usersRepository.delete(req.params.id)
        res.json({
            message:"success",
        })
    })
    app.put("/api/users/:id",async(req,res)=>{
        const user=await usersRepository.findOne(req.params.id)
        usersRepository.merge(user, req.body);
        const result = await usersRepository.save(user);
        res.json({
            message:"success",
            payload:result
        })
  
    })
        
    }
).catch(error=>{
    console.log(error)
})

Conclusion:

Here we discussed how to add typeorm to RestAPI along with setup and complete code. Then we added how to create entites, connection between typeorm and database.

Next we connected typeorm, postgres with nodejs, express with CRUD operations with explanation and code.

If you have liked this article please share with others, if you want to look more like these articles, please checkout my blog https://mdpuneethreddy.com/

About the author

puneethrdy

View all posts