Joseph Jude

Consult . Code . Coach

Export Postgresql data to text files using nodejs


code . nodejs . tsc

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

Most web-applications need to store and retrieve data. One of the option is to use to relational db like postgresql. In this post, let us connect to an existing postgresql db, read all rows, and store the data in individual files.

We will use TypeORM, a typescript compatible ORM that can connect to all the popular relational dbs - MySQL, Postgresql, MariaDB, sqlite and so on.

Initialisation

Let us start by creating a new project with npm init. We will use gulp for compiling typescript files. So let us install the required gulp related modules.

npm install typescript nodemon gulp-typescript gulp-nodemon gulp @types/node --save-dev

Need to understand gulp? Refer my post on gulp.

Let us now install typeorm related modules to connect to postgresql.

npm install reflect-metadata typeorm pg --save

After installing all these dependencies our package.json file should look like this:

"devDependencies": {
    "@types/node": "7.0.5",
    "gulp": "3.9.1",
    "gulp-nodemon": "2.2.1",
    "gulp-typescript": "3.1.4",
    "nodemon": "1.11.0",
    "typescript": "2.1.5"
  },
  "dependencies": {
    "pg": "6.1.2",
    "reflect-metadata": "0.1.9",
    "typeorm": "0.0.8"
  }

TypeORM works through decorators. For this to work, we need to enable certain settings in tsconfig.json. Create a tsconfig.json file with the following contents:

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

In the earlier post, I didn't use tsconfig.json. We will change gulpfile.js to use the this configuration file.

Our gulpfile.js should look like this:

"use strict";

let gulp = require('gulp');
let ts = require("gulp-typescript")
let nodemon = require("gulp-nodemon");

let tsProject = ts.createProject("tsconfig.json")

gulp.task("default", ["serve"]);

gulp.task("watch", () => {
  gulp.watch('src/**/*.ts', ["compile"]);
});

gulp.task("compile", () => {
  console.log("compiling files")
  let tsResult = gulp.src(['src/**/*.ts'])
    .pipe(tsProject())
    return tsResult.js
    .pipe(gulp.dest('build'))
})

gulp.task("serve", ["compile", "watch"], () => {
  nodemon({
      script: "build/index.js",
      env: {
        "NODE_ENV": "development"
      }
    })
    .on("restart", () => {
      console.log("restarted");
    })
})

Notice that we are creating a project with let tsProject = ts.createProject("tsconfig.json") and we use this project to compile typescript files.

Now that we have initialised everything, let us use TypeORM to connect to the db.

Connecting to DB

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

import { createConnection } from "typeorm";

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

Defining Models

In TypeORM, Entity decorator defines a table, Column decorator defines columns, and PrimaryColumn defines a primary column. So let us define a blog post entity.

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

@Entity()
export class Entry {

  @PrimaryColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  slug: string;

  @Column()
  category: string;

  @Column()
  excerpt: string;

  @Column()
  content: string;

};

We can define as many entities as needed. For this introductory tutorial, let us keep it with just this simple table.

We need to modify the connection block to include the entities.

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

Reading from db & Writing to files

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 do whatever we want with the individual entry.

The complete program is listed for reference:

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

@Entity("Entries")
export class Entry {

  @PrimaryColumn()
  id: number;

  @Column()
  title: string;

  @Column()
  slug: string;

  @Column()
  category: string;

  @Column()
  excerpt: string;

  @Column()
  content: string;
};

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

  let entryRepo = connection.getRepository(Entry);

  let allEntries = await entryRepo.find();

  for (let entry of allEntries) {
    let fileName = "posts/" + entry.slug + ".txt";
    let content = `title: ${entry.title}
slug: ${entry.slug}
excerpt: ${entry.excerpt}
---
${entry.content}
`
    await fs.writeFile(fileName, content, "utf8");

  }

  console.log("completed");
})

You should use the stable version of node (6.9) to execute this. If you use the latest 7.0, it will throw errors.

Things to improve

As an introductory post, I have kept this post simple. It doesn't deal with errors; it doesn't deal with relations or complex data types. Refer TypeORM site to learn them.

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


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

Export Postgresql data to text files using nodejs and #typeorm by @jjude: https://t.co/HuVvLdOAik

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

Comments

comments powered by Disqus