Author: abhijit

Ruby Sequel migration – increment column in a group

August 2, 2022 / abhijit

The requirement was to add a column called position to the order_details table.

The order_details table is actually a table with a foreign key of order_id. The requirement is to increment the position starting from 1 in the position column grouped on order_id.

So, before the migration if the records are –

id  order_id
1   1
2   1
3   1
4   2
5   2
6   2

After the migration, the records should be –

id  order_id position
1   1       1
2   1       2
3   1       3
4   2       1
5   2       2
6   2       3

There are two solutions possible. One using window functions and one without.

How to generate SSH Keys and avoid “Invalid privatekey” error

May 31, 2022 / abhijit

I’ve long been a user of DBeaver which works so well across different OSes, so recently I wanted to set up the cloud version called cloudbeaver.

In the course of doing that, I had to generate SSH keys to be able to access remote databases via SSH.

Generate an SSH keypair

The standard way of generating SSH keys is to run this command.

> ssh-keygen -t rsa

This by default creates id_rsa and id_rsa.pub under <home directory>/.ssh folder (both in Windows and Linux)

increase Postgres statement_timeout

May 6, 2022 / abhijit

Sometimes it is required to increase the statement_timeout for Postgres.

The correct way to do this would be to create a .psqlrc in the home directory and add the setting inside there.

$> cat .psqrc
set statement_timeout to 10000;

This is given in milliseconds so the above is for 10 seconds.

This can be verified from within psql command

psql> show statement_timeout;
 statement_timeout
-------------------
 10s
(1 row)

Reference: https://stackoverflow.com/a/24093305/33581

PM2 logrotate

May 6, 2022 / abhijit

One of the things I forgot when I wrote the PM2 post is that PM2 creates logs. And these logs get very big, very fast!!

The pm2-logrotate takes care of this.

To install pm2-logrotate

$> pm2 install pm2-logrotate

The defaults work perfectly fine. It will retain logs for 30 days and rotate the log every day, each with a max-size of 10 MB.

To change any of these settings –

$> pm2 set pm2-logrotate:max_size 1K (1KB)
$> pm2 set pm2-logrotate:compress true (compress logs when rotated)
$> pm2 set pm2-logrotate:rotateInterval '*/1 * * * *' (force rotate every minute)

Installing nvm and node on Linux

May 6, 2022 / abhijit

I’m using PM2 for managing all the server processes. So, installing node and nvm (node version manager) is now one of the first things I do during an application server setup.

node and nvm, just like ruby and rvm (ruby version manager) are always installed under the non-root user account.

Promises and Async / Await

April 27, 2022 / abhijit

I always struggle with understanding how async / await works.

So, I decided to write the code down and refer to this rather than trying to think it though every time!

I’ll start with the simple XMLHttpRequest

var req = new XMLHttpRequest();

req.onreadystatechange = function () {

  req.onload = function () {
    if (req.status == 200) {
      console.info('success')
      var arr = JSON.parse(req.responseText);
      populatePage(arr);
    }
    else {
      console.info('error')
    }
  }
}

req.open('GET', baseURL, true);
req.send();

Here, I’m hitting baseURL (defined outside of this code) and doing something with the received array with the populatePage function outside as well.

I wrote it like this so that I don’t add other code which is not relevant to the call.

Check Ubuntu version without any libraries

April 6, 2022 / abhijit

Every time I need to check the current version of Ubuntu, I have to run

$> lsb_release -a

But what if lsb_release is not installed?

The easiest way is to run

$> cat /etc/*release

This gives me an output like this!

Reference

Hide commands from bash history temporarily

April 4, 2022 / abhijit

There’s been a number of times I’ve wanted to not include some commands that I type into bash from being added to bash_history

The most convenient is to start a subshell by typing in bash again. This ensures that the commands in the subshell don’t get added to the history.

There are additional ways of achieving this as well.

When you face a fork in the road, you always have three choices
Abhijit

Export SQLite table to CSV

January 19, 2022 / abhijit

A number of times I’ve needed to export a specific table from an SQLite database to a CSV file.

The sqlite program itself gives a very clean interface for this.

sqlite3 -header -csv database.sqlite "SELECT * FROM artists;" > ./artists.csv

Conversely, if I need do this from the SQLite prompt, I need to do it slightly differently.