Loading data into Postgresql using Nodejs and Minio
Tags: node, postgres, backend, s3
Published: 2019-03-12 22:31:51

---
title: Loading data into Postgresql using Nodejs and Minio
published: true
description: 
tags: #nodejs #postgres #backend #s3
---

I was recently loading a lot of files into PostgreSQL, and i had the need of doing it without many resources and to be fast.

The requirements I had were the following:

1. Low footprint, not reading all lines of a file at once
2. Easy to load data
3. Write data efficiently into Postgres using "COPY TO" sentence

With these requirements, I came up with the idea of doing with streams in NodeJS because:

1. It's efficient
2. It's simple

Let's say I have the following file
```csv
first_name, last_name, country
Karie,Zorn,Ukraine
Thebault,Flicker,France
Danya,Atcock,Canada
Lorne,Westmorland,Russia
Page,Greeve,Canada
Rene,Riccardini,Serbia
Adair,Mullin,Philippines
Della,Gumb,China
Charlie,Swadlinge,Cambodia
Celka,Karlowicz,Canada
```
And the following table in PostgreSQL:
```sql
CREATE EXTENSION "uuid-ossp";	
CREATE TABLE PERSON(
    id uuid default uuid_generate_v4(),
    first_name varchar(30) NOT NULL,
    last_name varchar(30) NOT NULL,
    country varchar(30) NOT NULL
);
```
As we're going to use curl in the PostgreSQL sentence in order to get the file, we're going to extend the Postgresql Docker image with the following:
```bash
FROM postgres:10.4
RUN apt-get update && apt-get install -y curl
```

I named this file postgresql.Dockerfile, when you have that file, we're going to get all running with the following commands:
```bash
docker build -f postgresql.Dockerfile -t postgres-with-curl .
docker run --restart always --name postgres --network host -d postgres-with-curl
docker run --network host -e MINIO_ACCESS_KEY=user -e MINIO_SECRET_KEY=password --restart  always --name minio -d minio/minio:RELEASE.2018-06-22T23-48-46Z server /data
```
So let's do it, first we need to create the connections for PostgreSQL and Minio:
```javascript
const pg = require("pg")
const minio = require("minio")

const minioClient = new minio.Client({
	accessKey: "user",
	secretKey: "password",
	secure: false,
	endPoint: "localhost",
	port: 9000,
	region: "us-east-1",
})
const pgPool = new pg.Pool({
	user: "postgres",
	password: "postgres",
	host: "localhost",
	port: 5432,
	database: "postgres",
})
```

Then we're going to do basically 3 things:

1. Read the file
2. Upload to minio
3. Execute SQL in PostgreSQL instance
```javascript
const fs = require("fs")
const path = require("path")

const TABLE_NAME = "public.people"

async function main() {
	const input = fs.createReadStream(path.join(__dirname, "people.csv"), {
		encoding: "utf-8",
	})
	const bucketName = "tmp"
	const objectName = "new.csv"
	await minioClient.putObject(bucketName, objectName, input)
	const csvUrl = await minioClient.presignedGetObject(
		bucketName,
		objectName,
		10000, // duration in seconds of the url
	)
	const copyDataSql = `
  COPY ${TABLE_NAME} (FIRST_NAME, LAST_NAME, COUNTRY)
    FROM PROGRAM 'curl "${csvUrl}"'
    WITH (FORMAT CSV , HEADER, QUOTE '"', DELIMITER ',' );`
	const client = await pgPool.connect()
	await client.query(copyDataSql)
	await client.release()
	await pgPool.end()
}

main()
```
If we execute the following code in nodejs we should see the rows in the database with the following query:
```sql
SELECT * FROM PUBLIC.PEOPLE;
```

The script can fail because of the following reasons:

- Minio is not up
- Postgresql is not up
- Postgresql table has not been created
- Minio bucket "tmp" has not been created

This technique can be useful when we have no control over the disk in postgres and we have to perform a batch operation.

Thanks for reading.