Joseph Jude

Consult . Code . Coach

Connecting to SQL db from Hapi using TypeORM


code . nodejs . tsc

This post is part of Learn to build web-applications with Hapi and Typescript

When it comes to databases, Nodejs is often associated with NoSQL dbs like Mongo. Yet, SQL dbs like MySQL, Postgresql have been battle tested in real world scenarios for years.

In this, tutorial, I'm going to show how to connect to Postgres db from Hapi. We will use TypeORM, a data mapper based ORM, to connect to db.

Initialization

Start a new node project with npm init. Let us now install typeorm and pg realated modules.

npm install reflect-metadata typeorm pg --save

We need to create tsconfig.json to compile typescript projects. For this project, our tsconfig.json looks like this:

{
  "compilerOptions": {
    "target": "es5",
      "lib":[
        "es2016",
      "dom"
      ],
      "module": "commonjs",
      "moduleResolution": "node",
      "sourceMap": false,
      "emitDecoratorMetadata": true,
      "experimentalDecorators": true,
      "removeComments": true,
      "noImplicitAny": false
  },
    "exclude": [
      "node_modules"
    ]
}

As I explained in another post, we will use npm scripts to compile typescript files.

"scripts": {
  "start": "./node_modules/nodemon/bin/nodemon.js -e ts  --exec \"npm run compile\"",
    "compile": "tsc --outDir ./build --module commonjs ./src/*.ts && node ./build/server.js"
}

Connecting to DB

Connecting to db is easy. Provide the credentials and db type to connect.

import { createConnection } from "typeorm";

createConnection({
driver: {
type: "postgres",
host: "localhost",
username: "user1",
password: "",
database: "db1",
port: 5432
}
}).then( connection => {
  console.log("connected");
  })

Defining the models

In TypeORM, Entity decorator defines a table, Column decorator defines columns, and PrimaryGeneratedColumn defines a auto generated primary column. Let us define a blog post entity.

import { Entity, PrimaryColumn, Column } from "typeorm";

@Entity()
  export class Entry {

    @PrimaryGeneratedColumn()
      id: number;

    @Column()
      title: string;

    @Column()
      content: string;

  };

This is a simple blog post entity with just title and content as its columns. We need to modify the connection block to include the entities to create.

createConnection({
driver: {
type: "postgres",
host: "localhost",
username: "user1",
password: "",
database: "db1",
port: 5432
},
entities: [
Entry
]
}).then( connection => {
  console.log("connected");
  })

Reading and writing entities

TypeORM provides entityManager and repository to deal with entities. As recommended by TypeORM, we will use repositories to connect to entities.

let entryRepo = connection.getRepository(Entry);
let allEntries = await entryRepo.find();

Now we can loop through allEntries and pick up individual entry for processing. Similarly, entryRepo.persist(newEntry) persists a new entity.

Putting it all together

Now let us put the entire program together. We are going to define two routes. A GET route that fetches all entries, and a POST route that creates a new entry.

"use strict";

import * as hapi from "hapi";
import "reflect-metadata";
import { createConnection, Entity, PrimaryGeneratedColumn, Column } from "typeorm";

const server: hapi.Server = new hapi.Server()
server.connection({ port: 3000 });

@Entity("Entries")
export class Entry {

  @PrimaryGeneratedColumn()
    id: number;

  @Column()
    title: string;

  @Column()
    slug: string;

  @Column()
    content: string;
};

server.route({
method: "GET",
path: "/",
handler: async (request: hapi.Request, reply: hapi.IReply) => {
const entryRepo = server.app.dbConnection.getRepository(Entry);
const entries = await entryRepo.find();
reply(entries);
}
});

server.route({
method: "POST",
path: "/",
handler: async (request: hapi.Request, reply: hapi.IReply) => {

let {title,slug,content} = request.payload;

let newEntry = new Entry();
newEntry.title = title;
newEntry.slug = slug;
newEntry.content= content;

let entryRepo = server.app.dbConnection.getRepository(Entry);
await entryRepo.persist(newEntry);

reply("New post created");
}
});

createConnection({
driver: {
type: "postgres",
host: "localhost",
username: "user1",
password: "",
database: "db1",
port: 5432
},
entities: [
Entry
],
autoSchemaSync: true
}).then(async connection => {
  server.start(err => {
      if (err) {
      throw err;
      }
      // set the connection to server.app so it can be used in methods
      server.app.dbConnection = connection;
      console.log("server running in " + server.info.uri);
      })
  })
.catch(err => console.log(err));

Execute this with npm start. It will compile the typescript file and start the server. Open another terminal and issue the below command to create a new entry.

curl -d '{"title":"Hapi coding","slug":"hapi-coding","content":"Coding in hapi is happy"}' -H "Content-Type: application/json" -X POST http://localhost:3000

After that you can issue curl http://localhost:3000 to fetch the newly created entry.

Got questions? Ask in comments or via twitter

Interested in learning hapi with typescript? Subscribe now, using the below form, to receive each new lesson for free.


Like the post? Retweet it. Got comments? Reply.

Connecting to SQL db from @hapijs using TypeORM by @jjude: https://t.co/gyKPl4XujM

— Joseph Jude (@jjude) February 28, 2017
Share this on: Twitter / /

Comments

comments powered by Disqus