Forem: Rizky Zhang

Golang sqlx PostgreSQL Get Last Inserted Row ID

Rizky Zhang — Mon, 28 Nov 2022 13:15:04 +0000

Sometime after inserting a row, we might need to get the id usually for creating referenced table with foreign key of id. We can try to use db.NamedExec or db.Exec to do the operation since it will return a sql.Result interface which have LastInsertId() method inside it. The problem since we are using PostgreSQL with pgx driver, this method is not supported as stated in the documentation: In MySQL, for instance, LastInsertId() will be available on inserts with an auto-increment key, but in PostgreSQL, this information can only be retrieved from a normal row cursor by using the RETURNING clause..

The workaround is to take advantage of RETURNING clause that returns the updated row and combine it with db.GetContext or db.QueryRowx to get the query result, I will use db.GetContext here.

For example, we have a productPayload struct that we are going to insert.

type productPayload struct {
    UID         string                  `db:"uid" json:"uid"`
    Name        string                  `db:"name" json:"name"`
    Slug        string                  `db:"slug" json:"slug"`
    SKU         string                  `db:"sku" json:"sku"`
    Description string                  `db:"description" json:"description"`
    Image       string                  `db:"image" json:"image"`

    CreatedAt time.Time `db:"created_at" json:"created_at"`
    UpdatedAt time.Time `db:"updated_at" json:"updated_at"`
}

And here is the full code:

query, args, err := db.BindNamed(`
    INSERT INTO products (uid, name, slug, sku, description, image, created_at, updated_at)
    VALUES (:uid, :name, :slug, :sku, :description, :image, :created_at, :updated_at)
    RETURNING id;
    `, productPayload)
if err != nil {
    return err
}

var productID int64
err = db.GetContext(ctx, &productID, query, args...)
if err != nil {
    return err
}

The usage of db.BindNamed is optional, I use it because I want to convert my productPayload struct into slice of positioned arguments, so I don't need to manually pass it to the db.GetContext.

I hope this article is useful for you, if it is please share it with your friends, thank you so much for reading to the end and see you in the next article!

Fix TP-Link TL-WN722N Not Working on Linux

Rizky Zhang — Wed, 10 Aug 2022 15:27:09 +0000

This issue has been bugging me since last year. It started when I am still running Linux Mint, suddenly after an update, my wireless adapter stop working suddenly. I try to find a way to fix it on Google, but did not found any working solutions. Because I am quite busy at that time, I simply used USB tethering to get around it, and soon forget about the problem.

Early this year, I decided to try MX Linux because it seems to be more lightweight and stable (once a year release compared to monthly release which mean less crash) than Linux Mint. I did notice the performance is noticeably better, and I never have a crash or bug anymore after update, overall it is very stable, but the wireless adapter problem still persist, so clearly the problem is not the OS.

Tonight I remembered about the issue and because I am still curious about it, I decided to research about the issues again with a fresh approach. The first thing I do is running lsusb command on the terminal after I connected my wireless adapter. This is the output:

Gotcha! I found the needed driver version for my wireless adapter which is RTL8188EUS. I then run lsmod | grep 8188 to check whether the driver is loaded or not on the kernel, turn out it is not installed yet.

After a brief google search for the driver, I found it on https://github.com/aircrack-ng/rtl8188eus. I tried the instruction there, but sadly it still doesn't work. Suddenly I got an idea to try this command echo "blacklist r8188eu" | sudo tee /etc/modprobe.d/r8188eu.conf and restart the computer. Finally, it's working! To spare you from wasting time like me, I will give you the precise step here:

Connect your wireless adapter
Open your terminal and run lsusb to find your driver model, if it is not the same as mine, you can modify this tutorial by searching the driver model that match yours on Google and modify the last command to echo "blacklist <driver-model>" | sudo tee/etc/modprobe.d/<driver-model>.conf
Disconnect your wireless adapter and connect to internet with USB tethering from your phone
sudo apt-get install mokutil && mokutil --sb-state, if it output SecureBoot disabled you can proceed to the next step, otherwise you need to turn off the secure boot option from the BIOS because it can possibly interfere with the driver installation
git clone https://github.com/aircrack-ng/rtl8188eus
cd rtl8188eus
make && sudo make install
echo "blacklist r8188eu" | sudo tee /etc/modprobe.d/r8188eu.conf
Restart computer

Important note: you might need to repeat the steps again after updating your Linux because the driver might got replaced.

I hope my article is useful to you, see you in the next article!

Setup Multiple SSH Keys for Multiple GitHub Accounts

Rizky Zhang — Wed, 10 Aug 2022 15:26:50 +0000

Imagine this scenario, you have 3 GitHub accounts, each have their own purposes. Account A for full time work, account B for portfolio projects and account C for personal projects. Whenever you are going to switch between repo in different accounts, you will need to use a different SSH keys for each account.

Changing SSH config every time you are switching is not good use of your time. Now you might start to think it is possible to have multiple SSH keys for each account and let SSH automatically manage it? Yeah, it is possible, but it can be quite tricky because there is a gotcha which I will explain further in this article.

I am assuming you are running Linux and already have OpenSSH installed. For Mac, you should be fine. For Windows, you need to have git bash installed or WSL.

Creating SSH keys

The first thing we need to do is to create a SSH key for each account.
Go to your terminal and run each command.

ssh-keygen -t rsa -b 4096 -f ~/.ssh/id_rsa_fulltime -C "fulltime@mail.com"
ssh-keygen -t rsa -b 4096 -f ~/.ssh/id_rsa_portfolio -C "portfolio@mail.com"
ssh-keygen -t rsa -b 4096 -f ~/.ssh/id_rsa_personal -C "personal@mail.com"

The first command is going to create a SSH key for full time account that are connected with the specified Github email: fulltime@mail.com and save it into the specified path: ~/.ssh/id_rsa_fulltime.
The second and third commands do the same thing respectively for portfolio account and personal account.

Adding SSH Keys Into SSH Authentication Agent

ssh-add ~/.ssh/id_rsa_fulltime
ssh-add ~/.ssh/id_rsa_portfolio
ssh-add ~/.ssh/id_rsa_personal

Creating SSH Config File

Now you need to go to ~/.ssh and create a file named config inside it. Open it with vim or other text editor and add the following configurations.

Host *
AddKeysToAgent yes
IdentitiesOnly yes

Host github.com-fulltime
HostName github.com
IdentityFile ~/.ssh/id_rsa_fulltime

Host github.com-portfolio
HostName github.com
IdentityFile ~/.ssh/id_rsa_portfolio

Host github.com-personal
HostName github.com
IdentityFile ~/.ssh/id_rsa_personal

Host * means the config will be applied to every host, not only github.com-prefix.

AddKeysToAgent tells SSH to remember your passphrase, so you don't need to enter it every time, only on every new login session.

IdentitiesOnly is the gotcha I mean earlier. Let's say you are currently authenticated with fulltime account, and you want to switch to personal account. If you did not specify it, SSH will continue to use the first authenticated SSH key even though we have already specified different SSH key for each account, in this case it will continue to use fulltime SSH key for each account.

The postfix (eg -personal) after github.com is used to identify different SSH keys, you can name it whatever you like but it should be unique, usually I named it the context in which the GitHub account is used for.

Final Setup

If you already have an existing cloned repo from GitHub, you need to go the repo directory and edit .git/config file to append identifier postfix to the github.com hostname.

For example, you already clone a repo named personal-project from your personal account, to connect the correct SSH key to the repo you have to append this postfix -personal to the github.com hostname.

[remote "origin"]
url = git@github.com-personal:personal-account/personal-project.git

If you want to clone a new repo. Simply append the identifier postfix to the github.com hostname of the clone url.
git clone git@github.com-personal:personal-account/personal-project.git

After that, you will need to set up Github username and email for the repo by running these commands:

git config user.name "personal-account"
git config user.email "personal@mail.com"

Additional config if SSH still keep asking for passphrase (optional)

I encounter this problem recently and was so confused at first, luckily I found a blog post which explains how to fix this issue using keychain library. keychain library is used to
store SSH passphrase for one login session, so you only need to enter passphrase one time per login session.

First install the library if you haven't installed it:

sudo apt-get install keychain

Add this to the start of your shell config, .zshrc if you are using zsh or .bashrc if you are using bash.

if [[ `uname` == Linux ]] then
  /usr/bin/keychain $HOME/.ssh/id_rsa_personal
  /usr/bin/keychain $HOME/.ssh/id_rsa_portfolio
  /usr/bin/keychain $HOME/.ssh/id_rsa_fulltime
source $HOME/.keychain/$HOST-sh
fi

/usr/bin/keychain command will prompt for ssh passphrase for every login session and save it to $HOME/.keychain/$HOST-sh, in this case because we have 3 keys, it will be prompted 3 times.

Bonus: complete SSH keys automation!

Currently we still need to append identifier postfix to the github.com hostname every time we clone a new repo so SSH know which key to be used.

This can be very repetitive and prone to typo. Is it possible to only specify the postfix only once? Yes, it is possible with the help .gitconfig. With this solution you also don't need to set email and username anymore.

For example, you have a personal SSH key and a directory named personal which contains repositories, you want it to use personal SSH key automatically. Here's the step to make it automatic:

Create a .gitconfig in the root of personal directory.
Specify email, username and identifier postfix in below format.

[user]
  email = test@gmail.com
  name = test

[url "git@github.com-personal"]
  insteadOf = git@github.com

Create a global .gitconfig in your home directory to tell SSH which local .gitconfig to use for personal directory by specifying the path.

[includeif "gitdir:~/Projects/personal/"]
  path = ~/Projects/personal/.gitconfig

Now try to clone a repository, SSH will automatically choose the correct SSH key.

Congratulations, you have successfully setup multiple SSH keys.

See you in the next article!