DEV Community: Yogi Saputro

int8 Becomes String in PostgreSQL Node.JS

Yogi Saputro — Fri, 19 Jan 2024 07:39:45 +0000

It is a brief explanation of why int8 data type in PostgreSQL is converted to string instead of number in Node.JS. tl;dr it is a feature, not bug.

But First, Little Story

It can happen to anyone. Business requirement stated a transaction may be worth many billions in certain currencies. So, I needed to update SQL column data type from int4 to int8. That was it? I supposed so. Turned out not. People were furious and they had thrown me into dark pit of debugging.

I used Typescript but somehow it slipped through. I realized when taking a look on log and I saw long string like 8233800739007328003173000. It's enough clue that number addition became string concatenation. The returned query turned out as string.

But how?

I was baffled for few moments, then regained composure. The chaos was fixed by casting those string back to number. After that, business flows as usual, people cheered, and I reclaimed my state of sanity.

Why It's There, and Why It's Not A Bug

The question lingers, though? Why did it turned into string? I did a little research and found out that it is expected behaviour. Yes, PostgreSQL int8 will always be converted to Node.JS string. The culprit here is package called node-postgres or pg.

If you work with PostgreSQL in Node.JS, chances are there's pg in your node_modules. That's one of the packages that hold Node.JS community in one piece. Yet, exactly in this package the decision above was made. It is designed to handle one specific problem: Javascript number. Now, let me explain a bit on number before coming back.

Javascript number data type is 64-bit. Meanwhile, PostgreSQL int8 is also 64-bit. However, they are different. The first one is IEEE 754 double precision, while the later is signed integer. Javascript number has bits dedicated for precision and sacrifices range. Now let's compare.

max value of Javascript number (or Number.MAX_SAFE_INTEGER) is 2^53 - 1 or 9.007.199.254.740.991.
max value of PostgreSQL int8 is 2^63 - 1 or 9.223.372.036.854.775.807

Then, what will happen when the stored value in PostgreSQL is bigger than Javascript number max value? Javascript will read the binary representation and churn out incorrect value.

That is problem. BIG problem.
That's where pg package comes in and saves the day. The author recognized this problem, then decided to cast the result as Javascript string, which does not alter the output value.

For me it is beautiful trade-off. First, it handles all cases. Second, it avoids worst output. Third, it gives simple resolve once the value is safe in Javascript.

So, hats-off for Brian C, the author of pg.

Rerefences

One Product A Month for #DEVresolutions2024

Yogi Saputro — Fri, 12 Jan 2024 02:25:09 +0000

The new year vibe has already settled in and people are getting more drawn into reality. That's when I exactly think this is the best time to make new year resolution. By starting on my own timeline, I know I write this not because of FOMO, but I aim for real progress, instead.

My 2024 Resolution

I managed to simple and ambitious enough goal in one sentence:

Ship one product per end of month.

This goal beautifully encompasses all things I aspire to accomplish.

Open opportunities from various ways
Focusing on small set of tasks instead of overthinking technical stuff that lead to self-inflicted scope creep (that's my bane, yeah)
Experience journey beyond coding
More portfolio is nice bonus as well

Details, Details, and Details

Scope

Then, isn't it daunting and overly ambitious? Yes, when we think about "product" as a set of solutions working seamlessly and displayed beautifully. I define output product below to set expectations:

the product solves one specific real world problem
the product is deployed on internet
the product interacts with human users, and optionally with machine users

This might be close to MVP, but not yet. The goal is personal growth, not user feedback or financial. Even in this setup, I will have total freedom to build with Svelte in January and Solid.js in February, and so on. I do not focus on product valuation. However, it is totally possible that output product from this process is further developed as MVP or, with with sufficient resources and time, marketable product.

How Does This Help DEV Users?

In addition to shipping a prodcut per month, I will also share my journey on DEV about my problem-finding process, my design process, my journey of overcoming obstacles. Hopefully it will help community members.

Wish me luck, and I hope everyone has a blessed and eventful 2024 🎊🎊🍾 🚀

"Breaking Tables, Delivering Features: A Developer's Guide to SQL Normalization"

Yogi Saputro — Tue, 14 Nov 2023 08:58:41 +0000

Your new feature will break sophisticated, well-thought, beautiful, current SQL database structure? Worry not. This guide is here to help fellow developers to handle feature that involves changing database structure. With example, of course.

Please note that this guide is focused on normalizing SQL database. This is not related to programming language, nor this applies to NoSQL database.

Let's dive in.

Database Change is Inevitable

First, a little background. As time passes, product usually grows following business needs. Complexity also grows. You will have more feature tickets awaiting. On one hand, this is good sign. People are using the product, which turns into revenue, which turns into your salary. Hopefully.

On the other hand, you will be pressed to work on new features, and more often than not, require changes on your codebase and database. It is not due to lack of planning. No one could cover all possible cases and put it into code. Therefore, the first tip is to embrace the change and deal with it.

I have encountered quite some breaking changes in my career. The early ones felt frustrating. I thought "They are messing with my desgin? Why didn't they thought about it?". Then sometimes I even doubt myself "Why didn't I thought about it?". That was bad. Then I realized it is just inherent reality of evolving product. After some more time, I finally developed simple guide to deal with it.

Real World Example: NEW Split Bill Payment

Suppose that your product is a restaurant management system that allows customer to see menu, make orders, and pay through app/website while dining-in. It reduces restaurant's staff cost and improve efficiency.

Now the product have implemented order with payment. However, everyone agreed that one order is paid once. In other words, order and payment have 1:1 relation. Few weeks later, the company received feedback. Now people want to split bills. This means 1:1 relation between order and payment turns to 1:N relation.

What do you have to do now? Keep reading and follow these steps

SQL Normalization Guide

1. Identify Impacted Tables and Columns

Once it is known that some table relations will be changed from 1:1 to 1:N, quickly determine which columns are impacted.

In example case above, let's say that existing orders table is structured like this

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_date TIMESTAMP,
  bill_base_amount BIGINT,
  tax_amount BIGINT,
  other_fee_amount BIGINT,
  bill_total_amount BIGINT,
  payment_amount BIGINT,
  payment_type VARCHAR(255),
  payment_timestamp TIMESTAMP,
  payment_completed BOOLEAN,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

This is the table orders with sample data

id	order_date	bill_base_amount	tax_amount	other_fee_amount	bill_total_amount	payment_amount	payment_type	payment_timestamp	payment_completed	created_at	updated_at
1	2023-11-14 12:00:00	5000	1000	200	6200	6200	Credit Card	2023-11-14 12:05:00	true	2023-11-14 12:00:00	2023-11-14 12:05:00

In this case, following columns should be separated as new table

payment_amount
payment_type
payment_timestamp
payment_completed

Why bill_total_amount and some others are not included in new table? Because the information is related to order as a whole. Furthermore, for bill_total_amount it is practical later as checker to make sure sum of all split bills are equal to bill on order.

Once impacted columns are identified, you need to make new table.

2. Create New Table

Now, a new table is needed to store payment data. Such table requires

Identifier --> id
Foreign key to orders --> order_id
Impacted columns above
Timestamps --> just good practice

In SQL, this is one example

CREATE TABLE payments (
  id SERIAL PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  payment_amount INT,
  payment_type VARCHAR(255),
  payment_timestamp TIMESTAMP,
  payment_completed BOOLEAN,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

Now, you have the building blocks in the database. Time to start refactoring.

3. Change Existing Codebase Logic

At this point, you'd know orders and payments has 1:N relation, meaning one order could have at least one payment. You just need to insert data to orders and payments. However, it's still unclear when to insert the data. There are at least 2 options:

Assuming there's order_menu table (it's common sense for dev, right?), you can split by menu price. The splitting process is another part entirely, but from database perspective, all payment records can be generated and inserted together.
Split by amount. This approach relies on amount input. This allow each payment to be generated independently. However, the total amount must be thoroughly checked.

Which one is better? It depends on what product team wants :)

This could be an implementation in Node.js
Example in Node.js

const pgp = require('pg-promise')();
const db = pgp('your_connection_string');

// Sample data for an order
const orderData = {
  order_date: new Date(),
  bill_base_amount: 5000,
  tax_amount: 1000,
  other_fee_amount: 200,
  bill_total_amount: 6200,
  payment_amount: 6200,
  payment_type: 'Credit Card',
};

// Query for inserting into orders
const insertOrderQuery = `INSERT INTO orders -- etc`;
const insertPaymentQuery = `INSERT INTO payments -- etc`;
const mappingToPayments = (order_id) => {
    // do something mapping here
    // should be more complex, but you get the idea
    return paymentList
}

// Function to insert payments for a specific order_id
function insertPayments(order_id) {
  const insertPaymentPromises = mappingToPayments(order_id).map((paymentData) => {
    // Modify paymentData to include the order_id for the query
    const modifiedPaymentData = { ...paymentData, order_id };

    // Execute the query to insert into payments
    return db.none(insertPaymentQuery, modifiedPaymentData);
  });

  // Return a promise that resolves when all payments are inserted
  return Promise.all(insertPaymentPromises);
}

// Execute the first query to insert into orders
db.one(insertOrderQuery, orderData)
  .then((result) => {
    // Use the returned order_id in the function to insert payments
    const order_id = result.id;

    // Call the function to insert payments for the order_id
    return insertPayments(order_id);
  })
  .then(() => {
    console.log('Order and payments inserted successfully.');
  })
  .catch((error) => {
    console.error('Error inserting order and payments:', error);
  })
  .finally(() => {
    pgp.end(); // Close the database connection when done
  });

Also, don't forget to refactor the query to fetch data. Plus, make sure to test your code.

4. Migrate Existing Data

Now that you've taken care of the development. It is time to migrate the data. As you've known, existing data is in orders. After deployment, they better be in payments as well.

Fortunately, there's one-time query for this.

INSERT INTO payments (order_id, payment_amount, payment_type, payment_timestamp, payment_completed, created_at, updated_at)
SELECT
  id AS order_id,
  bill_total_amount AS payment_amount, -- Assuming one payment for one order
  payment_type,
  payment_timestamp,
  true AS payment_completed,  -- Assuming all existing payments are completed
  created_at,
  updated_at
FROM orders
WHERE payment_type IS NOT NULL;  -- Add additional conditions if needed

If there's more complexities (e.g not all payments are completed), you need to handle them first before executing query above.

6. Deploy & Run

This is time where you actually execute all steps above in production environment. Fingers crossed!

That's it! I hope this guide helps you. Comments and feedbacks are always appreciated. Stay sane, developers :)

(Cover image generated by playground.ai)

ChatGPT Already Solved Your Problem. Be A Problem Definer, instead.

Yogi Saputro — Tue, 26 Sep 2023 17:24:13 +0000

Charles Kettering, the Head of Research at General Motors one hundred years ago said, "A problem well-stated is a problem half-solved."
Well, little did he know that a century later, a problem well-defined is problem solved thanks to ChatGPT.

ChatGPT rose to prominence in less than a year and became actual disruption for many people. Some are delighted, some are frightened, others are skeptical. Now the hype is calming down and the dust are settled, we can see ChatGPT from better perspective and use it for our advantage.

When talking about using ChatGPT for my own advantage, I always ask this question: "How smart is ChatGPT actually?"
Forbes said it is smart because it passes exams, creates beautiful essay, etc. However, the term "smart" has many interpretations. Sabrina, the owner of Answer in Progress Youtube channel, has very interesting point of view. You can watch the full video:

tldr: ChatGPT is smart when dealing with common or standardized problems and less so when facing problems that require ingenuity, creativity, or critical thinking.

So, the better we are at providing standardized problem to ChatGPT, the better the output.

This aligns with core principle of decomposition in programming. Think program as list complex instructions that can be broken down into modules, that can be broken down again into functions, and so on. For me, being a problem definer means being excellent problem decomposer.

This is general question submitted to ChatGPT.

Me: show me best implementation of integer sorting in javascript

ChatGPT: *proceeds to show Quick sort, Merge sort, and Radix sort*
*puts some code*
*puts disclaimer that the performance depends on your use case*

Now I added constraints and optimization goal.

Me: I have an array of positive integers. The integer elements are not necessarily unique. I have between 0 and 2000 in array length, inclusive. The integer elements are random with long-tail distribution. I want to minimize space complexity and maintain adequate time complexity. How can I optimize sorting algorithm for such case in javascript?

ChatGPT: Use counting sort. It's good for your case.
*puts counting sort*
*puts advantages related to constraints*
*does NOT put evasive disclaimer*

I was blown away by that realization and adjust my coding style. I tell ChatGPT MY abstraction, MY structure, MY constraints, and it responded better than asking generic programming questions. Surely it can be improved and refined. It was weird though, like having a junior programmer with staff knowledge.

So let's be a better problem definer :)

NB:
I was dormant for almost a year for various reasons, and this is my first comeback post. It is just humble opinion from me and I know this community has more exciting use case. Please let me know if you have any other way of gaining advantage with ChatGPT.
Cheers 🍻

Dealing with Enum Type in PostgreSQL

Yogi Saputro — Wed, 15 Dec 2021 06:14:42 +0000

This is a brief guide for effectively understanding and working with enum type in Postgres: Why does it exist? What does it do? How to work with it?

Why Does Enum Type Exist?

There is boolean data type to define a binary column in PostgreSQL. Kind of yes/no switch. For example, is_active column is commonly used to describe whether a record is active.

But what if there are more than two options? Another data type is needed to handle the use case. That data type also has to be strict to prevent other values being written. Then enum comes to the rescue.

What Does Enum Type Do Well?

Enum type is best solution to describe state data. It basically define states of a record in PostgreSQL. For example, an e-commerce platform needs to track package delivery status. The package is either in packaging process, waiting for pick-up, in delivery, in transit, or already delivered to customer. It can be formalized as five states: PACKAGING, WAITING_PICKUP, IN_DELIVERY, IN_TRANSIT, and DELIVERED. Enum can handle this, in a way that will be described below.

Enum type has following behaviours:

It is treated as type in PostgreSQL
It applies to all tables in same schema
It has static values
Enum value is case-sensitive
Enum value size is 4 bytes
Each enum value is unique (duplicate is not allowed)
Adding enum value requires ALTER operation
Order of enum values matter when querying

When to Use Enum

Use enum if:

Cheap constraint is preferrable
Enum values won't change a lot in the future
Enum values aren't too many. Personally, I'd say less than 10 as rule of thumb.
Enum values have different priority
Avoiding invalid value is necessary

Otherwise, other solutions like reference table or even simple integer type will suffice.

Enum Operations

Create Enum Type

In this article, delivery status example above will be used. Below is general query syntax to create enum type.



CREATE TYPE delivery_status 
AS ENUM ('PACKAGING', 'WAITING_PICKUP', 
'IN_DELIVERY', 'IN_TRANSIT', 'DELIVERED');

Rename Enum Type

One can rename enum type by using following query:



ALTER TYPE delivery_status RENAME TO delivery_status_1;

Get Enum Values

To get list of enum values, use following query



SELECT UNNEST(enum_range(null::delivery_status)) 
AS delivery_status;

This is the result after creating delivery_status enum above

Add Enum Value

As the product evolves, one realizes that package can be lost, broken, stolen, etc. New package state is needed. Let's name it OTHERS to describe unexpected situations that may happen to a package.

As of version 9.1, PostgreSQL support adding enum value to do just that. Just execute query below



ALTER TYPE delivery_status ADD VALUE 'OTHERS';

It is also possible to add value before or after specific value.



ALTER TYPE delivery_status ADD VALUE 'OTHERS' BEFORE 'DELIVERED';
ALTER TYPE delivery_status ADD VALUE 'OTHERS' AFTER 'IN_TRANSIT';

The result is the same, new value in enum type.

Prior to version 9.1, things were more complicated. A popular workaround consists of three steps:

Rename enum type to something else
Create new enum with correct type name and value
Delete old enum type.



ALTER TYPE delivery_status RENAME TO delivery_status_old;
CREATE TYPE delivery_status 
AS ENUM ('PACKAGING', 'WAITING_PICKUP', 
'IN_DELIVERY', 'IN_TRANSIT', 'DELIVERED', 'OTHERS');
DROP TYPE delivery_status_old;

Change or delete enum value

As of version 10, PostgreSQL support renaming enum value



ALTER TYPE delivery_status RENAME VALUE 'PACKAGING' TO 'PREPARING';

Other than that, PostgreSQL doesn't support changing or deleting specific enum value. The workaround is the same as above: rename old type, create new and correct type, and delete old type.



-- change PACKAGING to PREAPRING
ALTER TYPE delivery_status RENAME TO delivery_status_old;
CREATE TYPE delivery_status 
AS ENUM ('PREPARING', 'WAITING_PICKUP', 
'IN_DELIVERY', 'IN_TRANSIT', 'DELIVERED', 'OTHERS');
DROP TYPE delivery_status_old;

-- delete IN_TRANSIT value
ALTER TYPE delivery_status RENAME TO delivery_status_old;
CREATE TYPE delivery_status 
AS ENUM ('PREPARING', 'WAITING_PICKUP', 
'IN_DELIVERY', 'DELIVERED', 'OTHERS');
DROP TYPE delivery_status_old;

After changing enum value PACKAGING to PREPARING

After removing IN_TRANSIT value

Delete enum type

To delete enum type and all of it values, execute following query



DROP TYPE delivery_status;

Enum type is useful tool to ensure type safety in PostgreSQL. I personally use it to make sure system flow doesn't mess up. I find this short article is useful to remind me important things about enum in PostgreSQL. I hope it helps you as well, dear readers.

If you find other tips to deal with enum types in PostgreSQL, please let me know in comments.

Stay sharp and curious.

Data Wrangling with VSCode (Yes, Really!)

Yogi Saputro — Tue, 17 Aug 2021 23:39:34 +0000

In this article, I'm going to show techniques and tips for transforming raw text data to standard format using VSCode. It's effective, quick, and dirty. Suitable for one-time wrangling.

People who works around data sometimes get strange one-time requests.

"Hey, can you get this data real quick? Shouldn't be too hard, right? Appreciate it".

It happended to me (as full-stack dev) few times, say: list of banks, list of loan rates, and so on. It's good thing that the data is available. It's bad thing that they are in PDF or HTML table. My system only works with CSV or JSON. So it is wrangling time!

There are many tools available for wrangling data. Nearly all of them are sophisticated and packed with features. Too many features means too many resource needed. I don't want to open Jupyter Notebooks or Tableau just to add commas or parse words. I need my resource for that 10 tabs of Stack Overflow, you know.

Finally, I resort to VSCode. It is software used by me and millions of people, has powerful editing features, and has various extensions. I can tailor it to wrangle data quickly.

Now, I will show how to wrangle data from PDF or HTML and transform it to CSV or JSON, using only VSCode.

Skills

Wrangling data in VSCode comes with a caveat: you need to understand regular expressions a.k.a regex. Don't be intimidated. Treat regex as a friend. Play around with regex tester here, or check out the documentation and cheat-sheet.

Extensions

Before going further, I recommend installing at least three extensions on VSCode.

Rainbow CSV : it highlights columns by colors, detects inconsistency in data, and provides RBQL (basically SQL for CSV file).
JSON to CSV : it converts JSON format to CSV and vice-versa.
Prettify JSON : it makes JSON easier to read.

Data Source

PDF

I'm going to use list of registered lending fintech companies in Indonesia, downloadable here.

HTML

I choose Wikipedia list of national heroes of Indonesia as example. Available both in English and Bahasa Indonesia.

From HTML to CSV

First, note that the first output format is always CSV because of its similarity with natural structure of table (e.g columns, rows, header).
Open the HTML data source. Then, copy text from HTML. On this case, I will copy the whole table. It is also useful to note which column is needed.
On VSCode, create new untitled file ctrl + N
Paste the text. Now we have nice list. Next, we want to remove unnecessary lines. Things like title, page number might get copied accidentally. It can be removed.
Now we need to remove unusable columns. For this case, column Ref(s) is not unusable and meaningless. So we need to get rid of it. If you need to filter columns, there is a way to do it later.
Access replace text menu ctrl + H, then press alt + R for regex search.
Now, what do these refs have in common? They have square brackets. Then, inside square bracket is number or letter. There are some possible regex. Try to approach it. The picture above with \[[0-9]\] regex works for single digit, but failed for two or more digits and letters. I also prefer to remove trailing whitespaces. Going further, I acknowledged that \[[0-9a-z]*\] works. \t?\[..?.?\] also works (it removes whitespace as well). Then, replace with '' a.k.a nothing.
Replace all occurences : ctrl + alt + enter. Now the refs are no more.
Replace characters that confuses CSV, like comma (,) or double-tick ("). It is important to avoid mistakes when parsing CSV. No need for regex now.
- Replace text ctrl + H
- Use text : , , replace with ; or leave it empty
- Replace all occurences : ctrl + alt + enter
- Repeat on other characters
Setup header manually to match standard CSV format. This is my recommendation:
- avoid spaces
- use snake_case
- add commas manually
Add proper separator, in this case: comma.
- For this data, I am a bit lucky. Each data is consistently separated by tab or whitespace. Just need to convert whitespace to comma. This is also why removing unnecessary whitespace on step 7 is important. Extra commas will be confusing.
- In some cases, data is separated by spaces. It can be confusing, especially when the column is string with spaces. There are strategies to overcome it below.
- Replace text ctrl + H, then press alt + R for regex search option
- Use text : \t , replace with ,
- Replace all occurences : ctrl + alt + enter
et voila! Data should be ready by now. Save it as csv file. Rainbow CSV should automatically highlight it afterwards.

From PDF to CSV

The basic is similar to HTML data source

Copy all data from PDF
Paste into VSCode workspace
Remove unnecessary lines
Remove unusable columns
Regex search and replace
Remove special characters (commas, double-ticks, etc.)
Setup header manually
Add proper separators
Save as CSV

Let's dive into action.

PDF data often returns a mess. Unnecessary lines, inconsistent line breaks, space separators, are all present here. It is better to observe this pile of mess for a while. Also check with original PDF file to figure out "how did this line ended up here?"
Check header and footer of each PDF page. Something like page number, repeated header, or repeated title might slip in. Clean them all.
Nomor or row number column has no value, so it can be removed. Now there's a little trick. Normal number regex like [0-9. ]+ is not accurate since there are matches at other places. But I know number column is always at the beginning of row. So I add newline \n to my number regex, then replace it with \n.
A lot of regex find-and-replace are required.
- Replace Android\ndan iOS with Android dan iOS
- Replace -\n (it means web based fintech, not mobile-based) with Web\n
- Replace Konvensional\ndan Syariah with Konvensional dan Syariah
Fortunately, there's no character that might confuse CSV. Move on to next step.
Handling header might require domain knowledge. So I basically change the header into this: platform_name, website, company_name, official_register, date_of_registration, lending_type, platform_type.
Now here comes the fun part of adding separators. Remember that we cannot change data, and regex find-and-replace always replace pattern to constant value. Thus, the key is identifying constant values.
- Many URLs begin with either http, https, or www. So, I can replace http with ,http and www (spaced) with ,www. Just add comma before replaced value. Now that's progress.
- Some URLs have no prefix, but have .id suffix. Find it with [a-z]+\.id regex, then press alt + enter to have multiple cursors working on them. Move to leftmost string with ctrl + ←, then add commas manually.
- All companies in the list begin with PT. So, replace PT (spaced) with ,PT
- All official registers begin with KEP or S. There are some inconsistencies about spacing and dash/minus character. So, replace KEP ?-?–? with ,KEP-. Also replace S ?[-?–?]+ with ,S-.
- Lending type is either 'Konvensional', 'Syariah', or 'Konvensional dan Syariah'. Replace Konvensional with ,Konvensional,. Replace Syariah with ,Syariah,. Then replace Konvensional,dan,Syariah with Konvensional dan Syariah
- Official register ends with year. So I can replace /2021 to /2021, and /2020 to /2020, and so on.
- Use CSV Lint from ctrl + shift + P or menu at bottom of VSCode window to check any incorrect row.
Save as csv file, and we're done!

Extra Miles

Select Specific CSV Columns

Rainbow CSV has RBQL feature, which works like SQL. It can be used to select specific columns if needed.

Click on query menu, a new tab will open.
Select columns like in SQL query, then click Run
The result appears in new tab. Save as new CSV using ctrl + shift + s

Save as JSON

Here's the tips to get JSON from CSV file, completely within VSCode.

Use JSON to CSV extension. Open the CSV file, then press F1 > Convert CSV to JSON
Use Prettify JSON extension. Press F1 > Prettify JSON. Now it looks good!
On Windows, there are trailing whitespace \r. Simply find \r and replace with empty string ``
Save as JSON file. Do not save directly using ctrl + S, it will overwrite the CSV file.

Handle Separator Placing

To summarize, here are tips and trick to handle difficult separator placing:

Utilize constant value
Utilize newline
Utilize multiple cursors like alt + enter when searching
Utilize manual multiple cursors with ctrl + alt + ↑ or ctrl + alt + ↓. Note that only maximum of 10000 parallel cursors are allowed on VSCode.
Utilize CSV Lint

Summary

I've been using this method quite often on various data source. Once, I wrangle 80-page PDF file and transform it to 40000-row CSV in about 20 minutes. Just keep in mind these three things:

Follow these steps in order:
- Copy all data then paste it into VSCode workspace
- Remove unnecessary lines
- Remove unusable columns
- Regex search and replace
- Remove special characters (commas, double-ticks, etc.)
- Setup header manually
- Add proper separators
- Save as CSV
- Add or remove column (optional)
- Convert to JSON (optional)
Be nimble on regex and its behaviors
Don't be afraid to undo your steps when things get messy. Maybe your regex wasn't accurate enough. Go back and fix your regex. It will save you time rather than handling data manually. Manual wrangling should be exception, not mandatory step.

I am Yogi Saputro, a full stack developer at Pinjammodal.id. I’m also passionate in building system, pipeline, and provide value to businesses. Check out my Github and portfolio as well.

Optimizing Conditional Bulk Insert in Node.js + PostgreSQL

Yogi Saputro — Sun, 01 Aug 2021 08:25:32 +0000

It is my journey of improving a bulk insert algorithm. It's a mouthful title, but definitely worthwhile read for this specific niche.

Intro

If you're either :

Working on ETL pipeline from various sources
Scraping websites
Aggregating data

chances are you will need conditional bulk insert.

I described term conditional bulk insert as an operation where many records need to be inserted to DB at once, while ensuring there's no conflict on unique identifiers.

In this article, I'm going to compare three approaches of conditional bulk insert for Node.JS and PostgreSQL:

Naive approach
Filtered approach
ON CONFLICT approach

Basic Implementation

For the purpose of demonstration, let's say there is a system for benefits program registration in country X. Data collection process is carried offline by field agents. Then, each agent upload applicants data to system at any particular time. Suppose that the system must check duplicate national ID of X. Citizen of X can only be registered once by single national ID. For simplicity, we will only store first data with unique national ID. If a duplicate exists later, it will neither update or get inserted to database.

Now we will determine best algorithm to insert data in such requirements.

Software requirements:

node at least v12.0
npm at least v6.0
PostgreSQL at least v9.5

Project Setup:

make new project folder, for example mkdir bulk_insert_demo
go to directory: cd bulk_insert_demo
create new Node project: npm init -y
Install necessary packages: npm install pg-promise faker
Create new file index.js
Create new table benefit_recipients with query below:

-- table definition
CREATE TABLE IF NOT EXISTS benefit_recipients (
  id serial NOT NULL PRIMARY KEY,
  national_id VARCHAR UNIQUE NOT NULL,
  person_name VARCHAR DEFAULT NULL,
  city VARCHAR DEFAULT NULL,
  benefit_amount INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT NOW() NOT NULL,
  updated_at TIMESTAMP DEFAULT NULL
);

1. Naive Approach

Naive approach states that first we check for ID of a record. If an ID exists, we do nothing. Otherwise, we insert record with new, unique ID.

Translating to Node.JS with pg-promise package is simple. I use ES6 async-await style. Checking for national_id is possible using SELECT COUNT(*). INSERT process is also straightforward. I put them all in classic for because it works well for async function. Finally, I added some console for information.

/**
 * Naive approach: for every row, check if national_id exists in DB. If it doesn't, insert the row
 * @param {*} client pg-promise client
 * @param {*} bulkData data to insert as array of object
 */
const naiveBulkInsert = async (client, bulkData) => {
  try {
    let counter = 0;
    for (let i = 0; i < bulkData.length; i++) {
      const checkIdExists = await client.one('SELECT COUNT(*) FROM benefit_recipients WHERE national_id = $1', [bulkData[i].national_id]);
      if (checkIdExists.count == 0) { // needed loose 0, not strict 0
        await client.any(`
          INSERT INTO benefit_recipients (national_id, person_name, city, benefit_amount) 
          VALUES ($1, $2, $3, $4)`,[bulkData[i].national_id, bulkData[i].person_name, bulkData[i].city,
          bulkData[i].benefit_amount]);
        counter++;
        process.stdout.write(`Parsing ${counter} products...\r`);
      }
    }
    process.stdout.write(`\nSuccessfully insert ${counter} records.\n`);
  } catch (error) {
    console.log(error);    
  }
}

Advantages

I have to admit, this is the kind of algorithm I wrote when I'm early in my career. It popped to mind directly, simple, and readable. These are the advantages of naive approach:

Easy to implement
Easy to add custom validation logic
Can handle duplicates within data source as well as between source - database

Disadvantages

Runs a lot of queries (between N and 2N where N is data count). We'll see the performance later.
Not ACID compliant, unless wrapped by transaction.

2. Filtered Approach

Naive approach has drawback because it uses many queries. It is like using one truck to send only one box, then tell the truck to go back and forth to send all boxes. Send all boxes on one truck would be more sensible.

That is the basic idea of filtered approach. Instead of checking N times and inserting 0 to N times, why not create one query for all checking and one for inserting? More precisely, one search query for duplicates between data source and database, then one insert query for data source that is not in duplicates.

The diagram below explains what I wish to achieve.

For duplicate search, a SELECT with WHERE ... IN (...) clause will do. Then, the query result will serve as filtering criteria for data source. I did some map() and filter() here. The methods can be chained for sure, if that is preferrable. After that, I used helper from pg-promise method for inserting multiple records.

I found out later that this approach didn't resolve duplicates within data sources, for example if a benefit applicant register more than once. So I fixed it, making internal duplicate check as first step.

So, this is full method for filtered approach.

const pgp = require('pg-promise')();

/**
 * Filtered approach:
 * - Remove internal duplicates 
 * - Search intersection of national_id between bulk data & DB.
 * - Then, filter out duplicates
 * - Finally, insert the rest into DB
 * 
 * @param {*} client pg-promise client
 * @param {*} bulkData data to insert as array of object
 */
const filterBulkInsert = async (client, bulkData) => {
  try {
    // Resolve duplicates in bulkData. Can be more complex than this.
    const uniqueBulkData = bulkData.filter((value,idx,arr)=>arr.findIndex(el=>(el.national_id === value.national_id))===idx);

    const inputNationalIds = uniqueBulkData.map((item) => {return item.national_id});
    const listDuplicates = await client.any('SELECT national_id FROM benefit_recipients WHERE national_id IN ($1:csv)',[inputNationalIds]);
    const duplicatesArray = listDuplicates.map((item) => {return item.national_id});
    const dataToInsert = uniqueBulkData.filter((item) => !duplicatesArray.includes(item.national_id));
    const columns = Object.keys(bulkData[0]).map((str) => str.trim());
    const setTable = new pgp.helpers.ColumnSet(columns , {table: 'benefit_recipients'});
    const insert = pgp.helpers.insert(dataToInsert, setTable);
    await client.any(insert);
    console.log(`Successfully insert ${dataToInsert.length} records.`);
  } catch (error) {
    console.log(error);
  }
}

Advantages

Run with only two queries
INSERT method is ACID compliant

Disadvantages

Doesn't handle duplicates within data source by default
Has a lot of array manipulation (may be substituted with more complex queries)

3. `ON CONFLICT` Approach

I kept pushing forward to improve the query. It turned out that simple PostgreSQL clause ON CONFLICT(...) DO NOTHING is the answer I need. It get rid of SELECT query and array manipulation from previous approach. The code became simpler and more concise. However, note that columns in ON CONFLICT(...) must be declared as UNIQUE in table definition.

The algorithm for third approach is as follows:

const pgp = require('pg-promise')();

const onConflictBulkInsert = async (client, bulkData) => {
  try {
    const columns = Object.keys(bulkData[0]).map((str) => str.trim());
    const setTable = new pgp.helpers.ColumnSet(columns , {table: 'benefit_recipients'});
    const onConflict = ' ON CONFLICT(national_id) DO NOTHING RETURNING *';
    const insertOnConflict = pgp.helpers.insert(bulkData, setTable) + onConflict;
    const result = await client.any(insertOnConflict);
    console.log(`Successfully insert ${result.length} records.`);
  } catch (error) {
    console.log(error);
  }
}

Advantages

Run with only ONE query
ACID compliant
Can handle duplicates within data source as well as between source - database

Disadvantages

none so far

Testing Setup

For the demonstration purpose, I define some testing strategies:

national_id length and bulk data size are parameterized to ensure duplicate values. For example, suppose national_id is a three digit number with 1000 possible values (0-999). If I create 750 samples, then according to birthday problem principle there's >99% chance of national_id duplicates.
Tests are run iteratively in various scenarios. First, each test is run independently for small samples. Then, sample size will be increased accordingly. After that, there will also be test to run three algorithms sequentialy in random order to further measure performance.

For testing purpose, I created some functions:
generateSample() to create array of object with two parameters : sample size and national_id length

const faker = require('faker');  

const generateSample = (size, idLength) => {
  let sample = [];
  for (let i = 0; i < size; i++) {
    sample.push({
      // national_id, center of this test, is defined with specific idLength
      national_id : faker.finance.account(idLength),
      person_name : faker.name.firstName() + ' ' + faker.name.lastName(),
      city : faker.address.cityName(),
      benefit_amount : faker.finance.amount(100, 500, 0) * 10000
    });
  }
  return sample;
}

cleanTable() is used to delete all records from benefit_recipients. It is used after inserting to database. Otherwise, there would be all duplicates, right?

const cleanTable = async (client) => {
  // I don't use TRUNCATE because I will display number of deleted records
  const deleteRecords = await client.any(`DELETE FROM benefit_recipients
  WHERE created_at > (current_timestamp - interval '1 day')
  RETURNING *`);
  console.log(`Deleted ${deleteRecords.length} records.`);
}

runNaiveTest(), runFilteredTest(), and runOnConflictTest(), each consists of timing, sample generation, and INSERT execution.

const runNaiveTest = async (client, sampleSize, idLength) => {
  try {
    console.time('naive_approach');
    console.log('executing naive approach...');
    console.log(`creating ${sampleSize} samples...`);
    let sample1 = generateSample(sampleSize, idLength);
    await naiveBulkInsert(client, sample1);
    console.timeEnd('naive_approach');
  } catch (error) {
    console.log(error);
  }
}

const runFilteredTest = async (client, sampleSize, idLength) => {
  try {
    console.time('filter_approach');
    console.log('executing filter approach...');
    console.log(`creating ${sampleSize} samples...`);
    let sample2 = generateSample(sampleSize, idLength);
    await filterBulkInsert(client, sample2);
    console.timeEnd('filter_approach');
  } catch (error) {
    console.log(error);
  }
}

const runOnConflictTest = async (client, sampleSize, idLength) => {
  try {
    console.time('on_conflict_approach');
    console.log('executing on conflict approach...');
    console.log(`creating ${sampleSize} samples...`);
    let sample3 = generateSample(sampleSize, idLength);
    await onConflictBulkInsert(client, sample3);
    console.timeEnd('on_conflict_approach');
  } catch (error) {
    console.log(error);
  }
}

And finally, an adjustable main() function. It runs specific test with defined iterations, sample size, national_id length, and PostgreSQL client. In the example below, I will run sequential test of naive, filtered, and ON CONFLICT approach, 10 times.
If I want to test each approach independently, I can adjust it here and re-run the test.

const main = async () => {
  const dbClient = pgp(process.env.DB_CLIENT);
  const iteration = 10;
  // to create >99% duplicate guarantee, I use formula (samples) / (10^idLength) = 0.75
  const samples = 750;
  const idLength = 3;
  // adjust desired test here
  for (let idx = 0; idx < iteration; idx++) {
    await runNaiveTest(dbClient,samples,idLength);
    await runOnConflictTest(dbClient,samples,idLength);
    await runFilteredTest(dbClient,samples,idLength);
    await cleanTable(dbClient);
  }
}

main();

All of codes above can be written as one index.js file like I did, or spread into separate files with proper import. Then run
DB_CLIENT=postgres://theusername:thepassword@localhost:5432/db_name node index.js, change DB_CLIENT value to correct connection string.

Testing Result

First, I tested each approach independently with 750 samples.
This is the result.

Well, I expected naive approach to be slow, but not THAT slow. It takes almost 2000 times slower than filtered and ON CONFLICT approach. That number might vary due to various reasons. My database is on remote server, so network latency is a factor. One thing is certain: naive approach is much slower than others. First lesson learned: Never use naive approach. From this point on, I'll exclude naive approach from testing.

On a side note, filtered approach is a bit faster than ON CONFLICT approach. We'll see more from them.

This time, I increase sample size to 7500 and adjust national_id to 4 digits. This is the performance result.

For 7500 samples, filtered approach is about 33% faster than 'ON CONFLICT' approach.

Then, I increase sample size to 75000. This time, there's a surprise.

Filtered approach takes much longer this time. It also didn't increase linearly from previous sample. On the other hand, ON CONFLICT approach seems to scale well linearly as its execution time increase is consistent.

This might be caused by array manipulation. On filtered approach, array manipulation is carried by Node.JS. filter() and map() functions, especially in nested fashion, is expensive. Plus, javascript is not considered fast. On bigger sample size, it breaks. That is not the case for ON CONFLICT approach. The heavy lifting is done by PostgreSQL internal which has been optimized for relational algebra stuff.

Conclusion

Key takeaways

Don't use naive approach. It is really slow.
Filtered approach breaks on bigger INSERT size.
ON CONFLICT approach is fast, scales well, ACID-compliant, and fulfill the requirements well. Use it.

Credit: Photo by Kendall Henderson on Unsplash

Axios Async/Await with Retry

Yogi Saputro — Tue, 09 Mar 2021 04:15:58 +0000

If you have been coding javascript for a while, you'd probably have known about Axios. It is a famous JS library for making HTTP request. Whether you are back-end or front-end developer, Axios is essential to access API services out there.

Alas, reliability is scarce even when we're in 2020s. Things happen, and your HTTP request might get stalled. So what do we do? In some cases, we will try again until we get the data we need. In those cases, equipping Axios with retry capability is necessary.

In this post, I'm going to show how to equip Axios with retry capability i.e resend HTTP request when server doesn't answer. First, the native way. Then, we're going to use some libraries.

I feel the need to write this tutorial since most examples of Axios are written in .then pattern. I'm accustomed to async/await pattern since it feels more familiar (especially if you learn other OOP languages). Performance-wise, both are internally equal.

Anyway, let's dive in to our code. I'm going to make a Node.JS project. Feel free to use whatever javascript stuff to suit your needs. Also, make sure your dependencies are fulfilled. I already installed Node.JS and NPM for this tutorial.

First, make new project using Terminal.

$ mkdir request-retry
$ cd request-retry
$ npm init -y
$ touch index.js

Then, install axios package.

$ npm install axios

Now, we're going to edit index.js using code editor. I'm going to make HTTP request to https://mock.codes and see if it responds.

const axios = require('axios')

const myRequest = async () => {
  try {
    const myConfig = {
      headers: {
        Authorization: 'Basic lorem12345'
      }
    }

    const req = await axios.get('https://mock.codes/200', myConfig);
    console.log(req.data);
  } catch (error) {
    console.log(error.response.data);
  }
}

myRequest();

Now, run index.js

$ node index.js

and we will get this result

{ statusCode: 200, description: 'OK' }

Things are okay, right? Now, I'm going to rig this HTTP request by setting an unreasonably low timeout. To do that, add timeout in the request config. Let's check index.js again and edit myConfig so it looks like this.

    const myConfig = {
      headers: {
        Authorization: 'Basic lorem12345'
      },
      timeout: 10 // 10ms timeout so servers hate you
    }

If I run $ node index.js again, I'll probably get something like this.

UnhandledPromiseRejectionWarning: TypeError: Cannot read property 'data' of undefined

What happened? Due to unfinished request, req doesn't get data from server. Therefore, its value is undefined. We can't get keys from undefined, hence the error.

It is time to implement Axios with retry capability. But before going any further, I want to make clear when referring to retry, mostly we want to have control over two things:

How many times we want to retry
how long we want to wait for each trial

There are two main ways to do this:

Implement directly
Use package

Direct Implementation

This option means doing everything from scratch. It's not too difficult, though. It is suitable option when we just need few types of request in our code and adding package would burden the app.

One simple approach is enveloping every request with loop. Now let's say I'm willing to retry 3 times and 50 miliseconds for each request. This is the example of working code.

const axios = require('axios');

const myRequest = async () => {
  try {
    const retries = 3 // amount of retries we're willing to do
    const myConfig = {
      headers: {
        Authorization: 'Basic lorem12345'
      },
      // we're willing to wait 50ms, servers still hate you
      timeout: 50 
    }
    for (var i=0; i<retries; i++) {
      try {
        const req = await axios.get('https://mock.codes/200', myConfig);
        if (req) {
          console.log(req.data);
          break;
        } else {
          console.log('cannot fetch data');
        }
      } catch (error) {
        console.log('cannot fetch data');
      }
    }
  } catch (e) {
    console.log(e);
  }

  myRequest();
}

It's quite long, but if we're not doing it often across one project, this solution fits nicely.

Use Package

There are times when we have to connect to many endpoints with different characteristics. In such circumstance, using package is justified.

There are 3 famous packages that satisfy our needs:

retry, a general purpose retry operation.
axios-retry, most popular retry add-on for Axios
retry-axios, second most popular retry add-on for Axios

I will use retry-axios since it provides easier implementation on async/await pattern. Now don't forget to read on its documentation. Also, don't forget to install it using Terminal.

$ npm install retry-axios

This is an example of working code.

const rax = require('retry-axios');
const axios = require('axios');

rax.attach();
const myRequest = async () => {
  try {
    const myConfig = {
      raxConfig: {
        retry: 5, // number of retry when facing 4xx or 5xx
        noResponseRetries: 5, // number of retry when facing connection error
        onRetryAttempt: err => {
          const cfg = rax.getConfig(err);
          console.log(`Retry attempt #${cfg.currentRetryAttempt}`); // track current trial
        }
      },
      timeout: 50 // don't forget this one
    }
    const req = await axios.get('https://mock.codes/200', myConfig);
    console.log(req.data);
  } catch (error) {
    console.log(error);
  }
}

myRequest();

Instead of doing loop, I basically attach retry-axios object to Axios. My part is handling config for the request. It is much simpler and delivers same result as direct implementation.

That's it! Now we can make Axios more reliable in sending HTTP request.

Do you find it useful, informative, or insightful ?
Do you find mistakes in my tutorial ?
Do you have any questions?
Feel free to comment below 👇 and let me know.

DEV Community: Yogi Saputro

int8 Becomes String in PostgreSQL Node.JS

But First, Little Story

Why It's There, and Why It's Not A Bug

Rerefences

One Product A Month for #DEVresolutions2024

My 2024 Resolution

Details, Details, and Details

Scope

How Does This Help DEV Users?

"Breaking Tables, Delivering Features: A Developer's Guide to SQL Normalization"

Database Change is Inevitable

Real World Example: NEW Split Bill Payment

SQL Normalization Guide

1. Identify Impacted Tables and Columns

2. Create New Table

3. Change Existing Codebase Logic

4. Migrate Existing Data

6. Deploy & Run

ChatGPT Already Solved Your Problem. Be A Problem Definer, instead.

Dealing with Enum Type in PostgreSQL

Why Does Enum Type Exist?

What Does Enum Type Do Well?

When to Use Enum

Enum Operations

Create Enum Type

Rename Enum Type

Get Enum Values

Add Enum Value

Change or delete enum value

Delete enum type

Data Wrangling with VSCode (Yes, Really!)

Skills

Extensions

Data Source

PDF

HTML

From HTML to CSV

From PDF to CSV

Extra Miles

Select Specific CSV Columns

Save as JSON

Handle Separator Placing

Summary

Optimizing Conditional Bulk Insert in Node.js + PostgreSQL

Intro

Basic Implementation

1. Naive Approach

Advantages

Disadvantages

2. Filtered Approach

Advantages

Disadvantages

3. ON CONFLICT Approach

Advantages

Disadvantages

Testing Setup

Testing Result

Conclusion

Axios Async/Await with Retry

Direct Implementation

Use Package

3. `ON CONFLICT` Approach