Let's Build a Postgres CLI Client in Ruby!

And in only 200 lines of code!

Along the way, we’ll touch on

• how to parse command-line options
• signal handling
• packing and unpacking bytes
• TCP sockets (and IO in a more general sense), and of course
• the PostgreSQL protocol

If that sounds like fun to you then let’s jump in!

To follow along, you’ll need

• a POSIX environment
• a recent version of Ruby installed - 3.4 and above but you could probably get away with using an earlier version with some minor tweaks
• Postgres installed locally. Any version should probably be alright as long as it’s not super old like 9.x or below.

I’ll be using the psql client (which is the inspiration for our little Ruby client) but if you have another way you usually run PostgreSQL queries (say pgAdmin) that might work too. Although I can’t speak to specifically using it myself.

Let’s give our client a name. I think we can go with something like irbpsql for interactive Ruby psql. It doesn’t exactly roll off the tongue so my apologies for that. But it does give us a handle that we can refer to it with.

Prep Postgres

If you’ve used Postgres, then you know that it has authentication mechanisms that can be leveraged. Unfortunately, supporting auth would probably blow up our LoC count and admittedly probably not an exciting prospect for me. So we’ll just bypass all that by telling Postgres to trust us.

If you already have a user that Postgres will allow without requiring a password, then you can skip this part. Just make sure your user has access to at least one database.

If not, I’d suggest creating a user/role for this to avoid messing with your already existing users. You can delete the user (if you so wish) once you’ve played around with the client to your heart’s content.

It probably goes without saying but don’t follow along on a mission-critical Postgres installation. It is assumed that you are on a local machine that you only use for development/testing purposes.

Create the PostgreSQL Role

You’ll need to create a PostgreSQL ROLE WITH LOGIN. The docs show us how to do this well and with some nice examples.

From now on we’ll refer to the created role is irbpsql_user but feel free to use a different name.

Make Postgres “trust” the user

Since we don’t want Postgres to require a password from our user, we need to tell it to trust the user if it’s connecting locally.

To do that, we need to edit the pg_hba.conf file. The location of this file might be dependent on your particular Postgres installation.

If you are unsure of where exactly it is, fire up psql (or your PostgreSQL client of choice) and ask Postgres to show you.

$ SHOW hba_file;
              hba_file               
-------------------------------------
 /etc/postgresql/16/main/pg_hba.conf
(1 row)

On my machine, it’s located under /etc.

We are going to edit that file, but you might want to back up your current config first. For me, it would be something like

$ cd /etc/postgresql/16/main
$ cp -v pg_hba.conf pg_hba.conf.2026-02-07-bak
'pg_hba.conf' -> 'pg_hba.conf.2026-02-07-bak'

You can substitute the directory with what SHOW hba_file output for you. I like to add dates to my backed up config files in case I might need to restore the configuration to an even earlier version. Totally up to you as to how you want to do it, as long as we are able to go back to how things were in case something gets messed up, or you don’t want to keep the changes around once you’re done with our Ruby client.

Now use your favourite text editor to edit the file. You might need to make sure you’re doing this with a user that has write access to that file.

When you first open the file, you’ll probably see a preamble in the form of a lot of comments directing us on the file usage. Feel free to read it all but the first section of the file of most relevance to us is the line with the contents:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

You might have to scroll down a bit to find it. The file allows us to specify the types of connections we want to let through along with the user(s) and database(s) allowed for a connection.

We want to allow irbpsql_user to access all the database by connecting via localhost. We also don’t want them to have to enter a password.

Find the line with the contents

# IPv4 local connections:

and add the following line immediately below it and save the changes.

host    all             irbpsql_user             127.0.0.1/32            trust # Trust toy Ruby client user

What we’ve done is specified the connection type as host. We’ve also allowed access to all the databases. The 127.0.0.1/32 just means we’re connecting locally.

If you’re unfamiliar with the /32 bit don’t worry. It’s not terribly relevant for what we’re doing now. But if you’re curious, it’s a notation that marks the IP address as one that uses CIDR (Classless Inter-Domain Routing). This essentially means that the IP address is divided into 2 sections, a network part and a host part. The number after the trailing /, 32 in our case, is the number of the network bits. This means that the remaining bits make up the host part.

If you’ve ever come across netmasks (for example 255.255.255.0) while configuring the internet settings on your machine, then the / notation is just a more succint way of specifying the same information.

You can play around with CIDR calculator to get a better sense of how it works if you’re still curious.

The last value on the line we added was trust. This tells the Postgres server to let us in without a password.

We now need to tell Postgres to reload the file so that the changes are effected. Lucky for us, the same file tells us how to do just that.

# This file is read on server startup and when the server receives a
# SIGHUP signal.  If you edit the file on a running system, you have to
# SIGHUP the server for the changes to take effect, run "pg_ctl reload",
# or execute "SELECT pg_reload_conf()".

Go ahead and pg_ctl reload from your terminal or

SELECT pg_reload_conf()

from your PostgreSQL client if the first option is proving troublesome.

Test the user

Before we go further, we want to make sure that our user can log into and is trusted by the Postres server.

Log into the server using irbpsql_user. Remember to use 127.0.0.1 to specify the server’s IP address.

Since I use psql and my Postgres server is listening on the default port (5432), for me logging in would look like the following:

$ psql -h 127.0.0.1 -U irbpsql_user -d postgres
psql (16.4 (Ubuntu 16.4-1.pgdg22.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=> 

I chose to connect to the database named postgres on login since this database should be in every Postgres installation.

We are now ready to get started on our Ruby client!

Create the script file

Let’s create the script that will contain all the code we’re going to be writing. We can name it irbpsql.

Add a Ruby shebang directive to it.

#!/usr/bin/env ruby

Let’s make it executable so that we don’t have to explicitly invoke Ruby when we want to run the script.

$ chmod -v +x irbpsql
mode of 'irbpsql' changed from 0644 (rw-r--r--) to 0755 (rwxr-xr-x)

Feel free to run it. It won’t do anything but it might help catch something if you’ve missed anything.

Parse command-line options

In order to connect to our Postgres server, we are going to need a

• username
• hostname
• port

We’ll be using a user that Postgres trusts so we don’t need a password. We also don’t need a database name since Postgres will default to connecting us to a database that has the name as the username.

Since we’re drawing inspiration from psql, let’s take a look at that. psql can be run without any options. It will default to the OS user’s username, localhost for the hostname and 5432 for the port.

Let’s emulate this for our client, that is, allow the user to specify the following parameters via command-line options:

• username
• hostname
• port
• dbname

while having the same sensible defaults as psql.

Ruby already comes with a way to parse options. Enter OptionParser.

OptionParser gives us a nice DSL to work with so we don’t have to manually fiddle about with the command-line arguments ourselves.

Let’s go back to our script and use OptionParser to allow it to accept optional parameters from the user.

#!/usr/bin/env ruby

require 'optparse'

cli_options = {}

OptionParser
.new do
  it.on('-d', '--dbname=dbname')
  it.on('-h', '--hostname=hostname')
  it.on('-p', '--port=port')
  it.on('-U', '--username=username')
end
.parse!(into: cli_options)

Let’s run the script again but now with --help option supplied.

$ ./irbpsql --help
Usage: irbpsql [options]
    -h, --hostname=hostname
    -d, --dbname=dbname
    -U, --username=username
    -p, --port=port

How cool is that? We didn’t have to code in the help/usage stuff. OptionParse automatically handles that for us!

As you can see, OptionParser allows us to specify both short and long options. You can omit the = from the long options.

  it.on('-U', '--username USERNAME')

should still work.

Another cool thing OptionParser does is allow us to give it a hash for it to use store the parsed options. That’s exactly what we did with initialising cli_options with an empty hash and telling OptionParser to parse!(into: cli_options)

Feel free to test out the different options. Experiment with both long and short options. You can print out the contents of cli_options to see what values get saved when you supply different options.

pp cli_options

Mix and match as you wish. For example

$ ./irbpsql --hostname=127.0.0.1 -U alice --dbname postgres -p 1234

Open connection to Postgres

Now we are ready to Connect to Postgres. According to the PostgreSQL docs, the protocol clients can use to communicate with servers is supported via TCP/IP and Unix domain sockets.

We’ll be using TCP for our client.

If you’re unfamiliar with sockets, you can think of them as an IP address/port pair. This is what processes use to communicate with each other over the network. TCP socket is such a pair but with the messages being exchanged over TCP. As opposed to, say, UDP.

A server picks an IP address and a port number to listen for incoming messages on. A client also needs to open a socket on its side in order to communicate with a server.

There’s some ceremony involved in opening a socket on the client side. You might be too well aware of this if you’ve programmed with sockets at a lower level. Luckily for us, Ruby comes with a TCPSocket class that takes care of a lot of that stuff for us.

Let’s append the following snippet of code to our script:

hostname = cli_options[:hostname] || '127.0.0.1'
port     = cli_options[:port] || 5432
username = cli_options[:username] || Etc.getpwuid.name
dbname   = cli_options[:dbname]

conn = TCPSocket.new(hostname, port)

We now have 2 new requires. We’ve also added descriptions to our command line options. Running our client with --help now prints out additional information indicating the default values.

$ ./irbpsql --help
Usage: irbpsql [options]
    -h, --hostname=hostname          Defaults to 127.0.0.1
    -d, --dbname=dbname              Defaults to using username as the database name as well
    -U, --username=username          Defaults to OS-level username
    -p, --port=port                  Defaults to 5432

Yet another thing that OptionParser let’s us do with relative ease!

By default -h should also bring up the usage text but we’ve coopted it for the hostname option in order to be more consistent with psql.

We also add some local variables to store the options passed in or default their corresponding default values.

I opted to use 127.0.0.1 to avoid any IPv6 weirdness. Using localhost might result in the name resolving to the IPv6 version of the loopback link (::1/128). Our edits to pg_hba.conf only trusts the connection from our user if over the IPv4 version of the address (127.0.0.1). It should downgrade to IPv4 if IPv6 doesn’t work but I’m not sure if this behaviour carries over across different Ruby versions and Postgres installations so it seems safer to just go with IPv4 from the start.

Since we can override the hostname via the command line options, feel free to test out different versions of the hostname and see what happens. If you are on a system with strace, you can even use it to peek into the system calls being made by the client to connect to the server. Ruby makes use of the connect(2) system call behind the scenes so that’s one particular syscall we can trace.

$ strace -e connect ./irbpsql -h localhost 2>&1 | grep connect
connect(7, {sa_family=AF_INET6, sin6_port=htons(5432), sin6_flowinfo=htonl(0), inet_pton(AF_INET6, "::1", &sin6_addr), sin6_scope_id=0}, 28) = -1 EINPROGRESS (Operation now in progress)
connect(7, {sa_family=AF_INET, sin_port=htons(5432), sin_addr=inet_addr("127.0.0.1")}, 16) = -1 EINPROGRESS (Operation now in progress)

As you can see above, using localhost on my system results in 2 connect(2) syscalls being made. One with sa_family=AF_INET6 and then a second one with sa_family=AF_INET. It seems that the script first tries to connect via IPv6 (that’s what AF_INET6 means) and then falls back to IPv4 (AF_INET).

The first call used the address ::1 which is an IPv6 address. The fallback, on the other hand, was to 127.0.0.1 the more familiar IPv4 address.

When I strace but with the IPv4 address directly,

$ strace -e connect ./irbpsql 2>&1 | grep connect
connect(5, {sa_family=AF_INET, sin_port=htons(5432), sin_addr=inet_addr("127.0.0.1")}, 16) = -1 EINPROGRESS (Operation now in progress)

there’s only a solitary call to connect(2) and that’s with the IPv4 address.

The other defaults also follow in psql’s footsteps. 5432 is the default port and the default dbname is the same as the username.

We use Etc.getpwuid.name to get username of the user logged in and default to that as the username to supply to Postgres unless a different username is specified.

There is a more direct method we can call, Etc.getlogin. The Ruby docs, however, warn that

it is often rather easy to fool ::getlogin.

Avoid ::getlogin for security-related purposes.

I don’t think we’ll be using our little client for anything production related so this probably does not matter too much. But it might be a good idea to get into the habit of avoiding Etc.getlogin if we don’t have to use it.

Build the first message

The PostgreSQL protocol overview page tells us that there are 2 phases, “startup” and “normal operation”.

In the startup phase, the frontend opens a connection to the server and authenticates itself to the satisfaction of the server. (This might involve a single message, or multiple messages depending on the authentication method being used.)

If all goes well, the server then sends status information to the frontend, and finally enters normal operation. Except for the initial startup-request message, this part of the protocol is driven by the server.

The “frontend” in this case is our Ruby client.

But now we need to find out more about these “messages” that our client needs to send. The messaging overview section tells us a bit more abou this.

All communication is through a stream of messages. The first byte of a message identifies the message type, and the next four bytes specify the length of the rest of the message (this length count includes itself, but not the message-type byte). The remaining contents of the message are determined by the message type.

Alright. So we have some idea of the general message format. We just need more information about the startup message specifically.

The protocol docs have us covered there too with the StartupMessage (F) section of the message formats documentation page. The F designates the message as one sent by the frontend.

According to the docs, startup message is comprised of 4 sections

• length of the message (Int32) (including the length of the message itself)
• the protocol version number (Int32)
• parameter name (String)
• parameter value (String)

in that order.

It also lists the parameters expected but only the user parameter is required. The rest are optional.

We now have enough information to craft our startup message and send it over to Postgres.

First, let’s define some constants near the beginning or our script so that we don’t have a lot of magic numbers later on.

#!/usr/bin/env ruby

require 'etc'
require 'optparse'
require 'socket'

FOUR_BYTES = 4
PROT_VER_MAJOR = 3
PROT_VER_MINOR = 0

cli_options = {}

Then add the following new code at the tail end to construct of startup message.

prot_ver = (PROT_VER_MAJOR << 16) + PROT_VER_MINOR
startup_msg_params = "user\0#{username}\0"
startup_msg_params += "database\0#{dbname}\0" if dbname
startup_msg_params += "\0"
startup_msg = [FOUR_BYTES + FOUR_BYTES + startup_msg_params.bytesize,
               prot_ver]
              .pack('N*')
              .concat(startup_msg_params)

As we saw from the Postgres protocol docs, we have to specify which protocol version we are using. At the time of writing, the latest protocol version (3.2) is used only by PostgreSQL 18. The next most recent version (3.0) is used by all PostgreSQL versions since 7.4 so that’s what we’ll use. 3.1 is not used at all due to a quirk of history. More on all of that here.

The StartupMessage format docs tells us that

The most significant 16 bits are the major version number

The least significant 16 bits are the minor version number

3 is our major version number. But the whole version needs to take up 4 bytes. So we need 3 to occupy the 2 leading bytes and 0 our minor version number to occupy the rest.

An attempt to illustrate this might look like the following:

*--------*--------*--------*--------*
|        |       3|        |       0|
*--------*--------*--------*--------*

We need to shift 3 by 2 bytes (16 bits) to left. Hence the use of the bitwise left shift operator <<. We then add the minor version number to the shifted major version number so that it can occupy the lower 2 bytes. In our case, the minor version number is 0 so this last step is a bit redundant since it just adds 0 to 0. But if we were using version 3.2 of the protocol, then this step would be more useful. Feel free to try setting PROT_MINOR_VER = 2 if you have PostgreSQL 18 (or newer) installed.

The next thing our code snippet does is construct a string to hold our startup message params. Recall that only user is mandatory. database is optional because if it’s not specified, PostgreSQL will try to find a database that is named the same as the username and attempt to connect us to it. But I think it’s fun to see how we can specify multiple params so we’ll handle the default case ourselves.

Whenever we’ve encountered params specified as names and values, we’ve needed some way to know where the name ends and the value begins. It also helps to have a way to separate one param from another. For example, URL query strings use = to specify the value of a parameter and & to separate multiple params. If we had to pass user and database as a part of a query string, it might look like user=alice&database=foo.

PostgreSQL is no different. Only instead of = and & we use the null character \0. And we add an additional \0 at the end to signal the end of our string. So if Postgres sees only one \0 it knows to keep going, if it encounters \0\0 it knows that it has read all the params sent.

Next, our code snippet calculates the size of the startup message params and the protocol version. Remember we have to tell Postgres how big our message is but the number also includes the field that tells it how big it is. This can be a little confusing to wrap your head around at first so let’s try to visualise it.

|--size of message (self included)--|------- protocol version ----------|---string params---|
*--------*--------*--------*--------*--------*--------*--------*--------*--------   --------*
|        |        |        |   N    |        |       3|        |       0|        ...        |
*--------*--------*--------*--------*--------*--------*--------*--------*--------   --------*

Here N is the total length of our message (in bytes). But remember that the message size data type is Int32 so this means its size is fixed. 4 bytes to be specific. The same goes for the protocol version number. That means only the size of the string params is dynamic. Put in another way, message_size = 4 bytes + 4 bytes + sizeof(string_params). We have this in our Ruby code as FOUR_BYTES + FOUR_BYTES + startup_msg_params.bytesize.

Why use String#bytesize instead of String#length? Because beyond ASCII, there are multi-byte characters. Take the increasingly ubiquitous emojis for example. We can use them to demonstrate this.

$ ruby -e 'puts "😊".length; puts "😊".bytesize'
1
4

We see that even though our smiley face string is one character long, it’s weighs in at whopping 4 bytes! Certainly, we know our string doesn’t contain multi-byte characters since we built it ourselves. But I think it’s a good habit to get into using bytesize to count the size of all strings we send over to the server. Later on this will include user-input strings that may or may not contain multi-byte characters. At that point we won’t have to remember to switch to bytesize since that will be our default. It also helps keep things more consistent across our code.

We put this number in an array together with the protocol version and then call pack('N*') on the array. This tells Ruby to convert our 32-bit integers from native endian to network order.

If this is the first time you’re hearing about endianness, it’s basically how the bytes in a numeric value are ordered. I’m on an x86-64 machine. That means my processor is little endian. For a 4-byte integer, the most significant byte is on the “little” end. I like to think of it like writing a decimal number with the ones on the leftmost side. 1,024 would be written as 4,201 if the decimal system were “little endian”!

PostgreSQL on the other hand wants the bytes of its integers to be in “network order”. Binary representations for integers use network byte order (most significant byte first)

Network order is “big” endian which means its the opposite of little endian. If you’re on a recently released Mac, then you are using an ARM processor which, according to Wikipedia, is “bi-endian” but defaults to little endian.

So chances are your processor is either little endian or is running in little-endian mode.

We, therefore, need to convert our Int32 integer fields to big endian before sending them over to PostgreSQL. That’s where Array#pack comes in. There are different directives for packing different kinds of data but we are only concerned with 32-bit integers in network order at the moment. The docs tell us that ‘N’ is the directive to use in that case. Since we have more than one integer we’d like packed this way, we have to tell that to Ruby. That’s what the * is for. Although in our case NN would work just as well as N*.

Finally, our code snippet concatenates the packed integers with the startup message string params and our first message is built.

Now to send it!

Send the first message

We now have an open connection to Postgres and a startup message ready to send. We can do this by adding the following 2 lines of code to our script.

conn.write startup_msg
p conn.readpartial(1_024)

Go ahead and give the client another spin.

$ ./irbpsql -U postgres
"R\x00\x00\x00\b\x00\x00\x00\x00S\x00\x00\x00\x17in_hot_standby\x00off\x00S\x00\x00\x00\x19integer_datetimes\x00on\x00S\x00\x00\x00\x1CTimeZone\x00Africa/Nairobi\x00S\x00\x00\x00\eIntervalStyle\x00postgres\x00S\x00\x00\x00\x14is_superuser\x00on\x00S\x00\x00\x00\x16application_name\x00\x00S\x00\x00\x00&default_transaction_read_only\x00off\x00S\x00\x00\x00\x1Ascram_iterations\x004096\x00S\x00\x00\x00\x17DateStyle\x00ISO, MDY\x00S\x00\x00\x00#standard_conforming_strings\x00on\x00S\x00\x00\x00#session_authorization\x00postgres\x00S\x00\x00\x00\x19client_encoding\x00UTF8\x00S\x00\x00\x004server_version\x0016.4 (Ubuntu 16.4-1.pgdg22.04+1)\x00S\x00\x00\x00\x19server_encoding\x00UTF8\x00K\x00\x00\x00\f\x00\x00/V\xAB$\x90\x8DZ\x00\x00\x00\x05I"

Remember to substitue postgres with the user that you allowed.

And we’ve done it. We’ve sent a message to Postgres and got a response back!

We use the write method on our socket object to send messages to the other side. It’s possible to use send as well as in conn.send startup_msg, 0. But I find that a bit confusing since we can send messages to any object in Ruby as alternative way to call methods. Not to mention that required flag param. Although that can be avoided by using sendmsg: conn.sendmsg startup_msg Still, I prefer write as it’s much simpler and is easy to remember since it exists for IO objects in general and not just sockets.

After writing to the socket, we the read from it to get the response back. We use readpartial and instead of plain old read as you might expect if you’ve worked with other IO objects before. This is because read waits for the other side to finish sending all the content before returning. It will “block” until end-of-file is reached. You can give a number of bytes to read but it’ll still block until the server sends all those bytes. This puts us in a weird situation where read is most ideal if we know exactly the number of bytes we’ll be receiving. Or if we are okay with not doing anything but waiting for all the data. Or if, perhaps, we are okay with reading only some of the already sent data but not all of it.

readpartial, on the other hand, will read up to the specified number of bytes. In our case, it will read at most 1_024 bytes before returning. If it finds only 512 bytes, it will return those and it will not block.

Later on we will prefer read to readpartial when we know exactly what to expect from Postgres. For now readpartial is a quick way to get at the response without getting bogged down by the details.

We use p to print out the contents of the response. This is preferable to something like puts because the response contains non-printable characters. As you might have seen, p will just print the non-printable bytes in hexadecimal format which makes it much easier to see all of the contents.

Try playing around with using read instead of readpartial if you want to get a better handle on their differences. Try calling read with no params and then with different numbers of bytes to see how it behaves.

Also, you can using puts in place of p and compare the different outputs.

Set up the main loop

Our client app is now able to communicate with Postgres. But the problem is it just exits. It doesn’t give the user a chance to input queries and see results. What we need to do is make our client interactive.

For interactivity, our client should

1. wait for a query from the user
2. accept a query from the user
3. send that query off to the server
4. get the results from the server
5. display the results to the user
6. go back to 1

And this is how a lot of interactive apps work. Video games, desktop app UIs and even web servers are usually implemented by having an event loop. They sit in a loop waiting for an event, like user input, and then process that event before going back to waiting for the next event.

Let’s go about giving our client a main loop.

First we have to make sure the server would be in a position to accept and run queries.

According to the Postgres docs description of the message flow, the server uses the startup message from the client

…and the contents of its configuration files (such as pg_hba.conf) to determine whether the connection is provisionally acceptable, and what additional authentication is required (if any).

Since we’ve already edited our pg_hba.conf to allow our user without additional authentication, we can jump straight to the post-authentication phase.

But we need to be able to understand responses from the server in order to go further. The protcol messaging overview section of the docs says

The first byte of a message identifies the message type, and the next four bytes specify the length of the rest of the message (this length count includes itself, but not the message-type byte). The remaining contents of the message are determined by the message type. For historical reasons, the very first message sent by the client (the startup message) has no initial message-type byte.

All messages, except for the startup message we send, will have the first byte identifying what type of message it is. This goes for messages we get back from the server as well.

If we look at the contents retrieved by our readpartial we see that it starts with an R. Let’s head over to the message format docs and see what type of message that is.

Immediately we see AuthenticationOk (B) has a Byte1('R') field. This lines up with what we are expecting if we have configured Postgres to not ask our user for a password. However, the next several entries also have a Byte1('R') leading byte. So R by itself is only seems to narrow down the message type to an auth-related one. We still need more information in order to discern what type of auth message it is.

The next field, as we saw earlier, should be 4 bytes indicating the length of the message. For all the authentication messages, this seems to be followed by another 4 bytes that gives us more information. This is what we’re looking for. We can see that for AuthenticationOk it is Int32(0), meaning it’s a 32-bit integer whose value is 0. But AuthenticationKerberosV5, for example, it is Int32(2) and for AuthenticationCleartextPassword, it is Int32(3). This means that it would be a 32-bit integer with the values 2 and 3 respectively for those auth messages.

This should be enough information for us to get started on our main loop.

First, we add a new constant for messages whose type is authentication request.

# ...
FOUR_BYTES = 4
MSG_TYPE_AUTH_REQUEST = 'R'
PROT_VER_MAJOR = 3
# ...

Then, let’s replace the printing at the bottom of our script

p conn.readpartial(1_024)

with

def handle_auth_message(conn)
  message_length, request_type = conn
                                 .read(2 * FOUR_BYTES)
                                 .unpack('N*')

  conn.read(message_length - 2 * FOUR_BYTES) # Consume the rest of the message

  return if request_type.zero? # AuthenticationOk
  raise "Unsupported authentication request type '#{request_type}'"
end

loop do
  message_type = conn.getc

  case message_type
  when MSG_TYPE_AUTH_REQUEST
    handle_auth_message(conn)
  else
    abort "Unsupported message '#{message_type}' received from server"
  end
end

We’ve added code to handle authentication-related requests from Postgres. The handle_auth_message method reads 8 bytes and unpacks them into two 32-bit integers. The first is the length of the message while the second is what type of authentication message it is.

It then consumes the rest of the message. Remember the message length includes the length field as well so we have to account for that. That’s why we deduct 8 bytes (including the request type field) to get the number of bytes left to read. It then calls read with the number of bytes needed to get the rest of the message.

We use read instead of readpartial like we did earlier because we know exactly how much to expect (assuming everything went well). We also don’t do anything with the rest of the message but we want our method to consume anything auth-related such that the next bit of code to read data from the connection need only concern itself with what’s relevant to it.

We use 2 * FOUR_BYTES instead of just 8 to reflect that we intend to read 2 fields. But you don’t have to do it this way.

Next, we added our main loop. It doesn’t do much for now. It reads the next byte using getc. Since we know each message we get from the server will always start with a byte and that the byte is a character representing the message type, we can use IO#getc to read the byte as a character.

After reading the byte, we check what type of message we need to process next and invoke the logic to handle it. We only handle authentication request messages for now, but we’ll gradually build up to handling other types of messages.

If we don’t (yet) support that type of message we exit with an error and we print out the message type byte.

Go ahead and run the client now if you haven’t already.

$ ./irbpsql -U irbpsql_user
Unsupported message 'S' received from server

Looks like there was still another message from the backend after the authentication message. Let’s add some code to handle it.

Finish handling post-startup messages

We need to figure out what type of message is represented by S. Doing a search for ('S') on the data formats page of the protocol documentation yields 3 results. But only ParameterStatus matches an uppercase “S” from the backend.

ParameterStatus is described as having an Int32 field for the message length (as is expected by now), followed by 2 strings - the name of the parameter and its value.

Seems straightforward enough. Similar to what we did for the authentication request message type, let’s add a constant MSG_TYPE_PARAMETER_STATUS for the parameter status message near the top of our script.

# ...
MSG_TYPE_AUTH_REQUEST = 'R'
MSG_TYPE_PARAMETER_STATUS = 'S'
PROT_VER_MAJOR = 3
# ...

Next, let’s define a method just before our main loop that will handle the message.

def handle_param_status_message(conn)
  message_length = conn.read(FOUR_BYTES)
                        .unpack1('N')
  param_name, param_value = conn.read(message_length - FOUR_BYTES)
                                .split("\0")
  puts "#{param_name}: #{param_value}"
end

Note here we are using String#unpack1 . instead of String#unpack because unpack returns an array even for solitary values. Rather than manually extract the value from the array, unpack1 does that automatically for us.

We read the parameter name and value at once and then split on \0 to get them separately while taking care to account for the 4 bytes of the already-read message length field. Remember Postgres likes to use the null byte to separate multiple strings. The last thing the method does is print out the params so we can see what the params actually are.

Finally, we update our loop to call the new method we’ve added.

loop do
  message_type = conn.getc

  case message_type
  when MSG_TYPE_AUTH_REQUEST
    handle_auth_message(conn)
  when MSG_TYPE_PARAMETER_STATUS
    handle_param_status_message(conn)
  else
    abort "Unsupported message '#{message_type}' received from server"
  end
end

Let’s run it and see what happens.

$ ./irbpsql -U irbpsql_user -d postgres
in_hot_standby: off
integer_datetimes: on
TimeZone: Africa/Nairobi
IntervalStyle: postgres
is_superuser: off
application_name: 
default_transaction_read_only: off
scram_iterations: 4096
DateStyle: ISO, MDY
standard_conforming_strings: on
session_authorization: irbpsql_user
client_encoding: UTF8
server_version: 16.4 (Ubuntu 16.4-1.pgdg22.04+1)
server_encoding: UTF8
Unsupported message 'K' received from server

Turns out there were quite a few parameters to report on. But since we handling them in a loop, that’s no problem for our code.

But now we have another unhandled message. This one’s type is signified by the byte K. Searching the data formats doc page for “Byte1(‘K’)” leads us to BackendKeyData as the type of this message.

From the description, it looks like the fields peculiar to this message type are an Int32 that holds the process ID of the backend and a Byten field which is the secret key of the backend with n being a variable number of bytes.

We won’t be using either of these fields so we can just consume them to get them out of the way.

You know the drill by now. Add a new constant for the new message type. This time it’s MSG_TYPE_BACKEND_KEY_DATA.

# ...
MSG_TYPE_AUTH_REQUEST = 'R'
MSG_TYPE_BACKEND_KEY_DATA = 'K'
MSG_TYPE_PARAMETER_STATUS = 'S'
# ...

Then just before our loop, a new method to handle that message type.

def handle_backend_key_data_message(conn)
  message_length = conn.read(FOUR_BYTES)
                       .unpack1('N')
  conn.read(message_length - FOUR_BYTES) # Just consume the message
end

Now we just update our loop to use the method.

  # ...
  case message_type
  when MSG_TYPE_AUTH_REQUEST
    handle_auth_message(conn)
  when MSG_TYPE_BACKEND_KEY_DATA
    handle_backend_key_data_message(conn)
  when MSG_TYPE_PARAMETER_STATUS
    handle_param_status_message(conn)
  else
    abort "Unsupported message '#{message_type}' received from server"
  end
  # ...

Running our script again, we should get the parameter status output just as before, but now the last ouput should be a different unhandled message.

As a very light exercise to see if you are getting the hang of reading messages from Postgres, feel free to try updating the backend key data handler method to display the process ID.

Our client is now telling us that we have an

Unsupported message 'Z' received from server

From the docs, Byte1('K') is for the ReadyForQuery message.

Looks like the server is ready to receive queries. We just need to handle this last message before we can start working on sending it SQL queries.

There are 2 fields of concern. An Int32(5) and a Byte1. The 32-bit integer is the length of the message and has a very specific value of 5. This means that we know that message is going to be exactly 5-bytes long. The Byte1 field is described as

Current backend transaction status indicator. Possible values are ‘I’ if idle (not in a transaction block); ‘T’ if in a transaction block; or ‘E’ if in a failed transaction block (queries will be rejected until block is ended).

Since we’re keeping our client as simple as possible, we need not concern ourselves with the details. We can just assume that the server is ready to receive queries and jump straight into a state where the user can input queries.

Let’s handle the final post-auth-ok message.

As with the other messages, we add a constant MSG_TYPE_READY_READY_FOR_QUERY

# ...
MSG_TYPE_PARAMETER_STATUS = 'S'
MSG_TYPE_READY_READY_FOR_QUERY = 'Z'
PROT_VER_MAJOR = 3
# ...

Then a handler method right before our loop:

def handle_ready_for_query_message(conn)
  # We don't do anything with the data so read it but don't retain it
  conn.read FOUR_BYTES + ONE_BYTE
end

Remember, 4 bytes for the message length and 1 byte for the status indicator.

And finally, we update our main loop.

  # ...
  case message_type
  when MSG_TYPE_AUTH_REQUEST
    handle_auth_message(conn)
  when MSG_TYPE_BACKEND_KEY_DATA
    handle_backend_key_data_message(conn)
  when MSG_TYPE_PARAMETER_STATUS
    handle_param_status_message(conn)
  when MSG_TYPE_READY_READY_FOR_QUERY
    handle_ready_for_query_message(conn)
  else
    abort "Unsupported message '#{message_type}' received from server"
  end
  # ...

Try running the script again. This time, there should be no unhandled messages. Perfect!

But now the script appears to be stuck doing nothing. Kill it with Ctrl-C and let’s set about rectifying that.

Accept queries from the user

How a lot CLI applications work, is that they have a prompt that is displayed to the user to let them know that the app is ready for input.

A user types their input and has to hit enter in order for the app to process that input. The app displays some output to let the user know how the processing went. It then goes back to displaying the prompt and waiting for the user input.

Some apps do offer support for multiline inputs. Given how long SQL queries can get, we want to the same for our client.

Thinking slightly ahead to our implementation of the interactivity, how would we be able to tell when a user is hasn’t finished inputting the query if they are allowed to spread it across multiple lines?

We would need to use something other than the end of a line to know when a query ends. Luckily there is something we can use for this that is already part of SQL - ;. SQL allows multiple queries to be separated by ; but we can use this as a way to terminate a query.

To sum it up, we should update our client such that it

1. prompts the user for input
2. accepts a line from the user
3. if the line does not end with a ; goes back to (1)
4. otherwise, we have a query so it sends that query to the server
5. processes the response from the server
6. displays results of the query
7. goes back to (1)

This sounds good but we don’t yet know how to format queries before sending them to the server. As always the docs have our back here. The message flow page mentions 2 types of queries - simple and extended.

Continuing with our theme of keeping things simple, let’s only go with simple queries.

The formats page tells us that a simple query consists of

Byte1(‘Q’)

Identifies the message as a simple query.

Int32

Length of message contents in bytes, including self.

String

The query string itself.

Given that, I would say simple query lives up to its name!

Now we need to update our logic to support the basic interaction flow we came up with earlier. The last message we handled from the server was ReadyForQuery. Since we are working on sending queries to the server, the handle_ready_for_query_message method seems the appropriate place to add our new logic. Especially since it is currently not doing anything of note.

Let’s update that method so that it looks more like this

def handle_ready_for_query_message(conn)
  # We don't do anything with the data so read it but don't retain it
  conn.read(FOUR_BYTES + ONE_BYTE)

  qry_str = ''

  loop do
    prompt_symbol = qry_str.empty? ? '>' : '*'
    print "irbpsql#{prompt_symbol} "
    line = readline
    qry_str += line

    if qry_str.rstrip.end_with?(';')
      qry_str.rstrip!
      qry_str += "\0"
      qry_msg_len = FOUR_BYTES + qry_str.bytesize
      qry_msg = "Q#{[qry_msg_len].pack('N')}#{qry_str}"

      conn.write(qry_msg) and return
    end
  end
end

The first new thing our updated handler does is set up a string to hold the query input by the user.

It then jumps into a loop and prompts the user for a query. In case you are more familiar with puts but haven’t yet encountered print in Ruby, we use print here because we want what the user types to be on the same line as our prompt. Just as we would expect of most other CLI tools.

Our prompt is

$ irbpsql>

when the client is expecting an entirely new query.

It changes to

$ irbpsql*

if the user is in the midst of writing a multiline query. This signals to the user that they can keep going with the query. It can also serve as a reminder that they forgot to terminate their query so that they can go ahead and input ; in case they’d been too quick to hit the Enter key.

If the loop detects a line ending in ; (we strip the input of any trailing whitespace in the process), we add a null byte to the end of the string (remember that’s how Postgres knows when a string ends). We also add Q to the beginning of the string. Just as messages from the server had a byte to indicate the type of message, Postgres expects messages from the client to be similarly formatted. The only exception is the startup message we send after we connect to the server.

Q is followed by the message length, which we calculate by adding 4 bytes (the size of the message length field) to the length of the query string.

We send the fully formed simple query to the server after which we exit not only the loop but the handler method as well. This will put us back at the beginning of the main loop which starts by waiting from a message from the server and calling the appropriate handler.

Let’s run our our client again and input a very simple query.

irbpsql> SELECT 1;
Unsupported message 'T' received from server

Looks like it works. Although we have a message from the server whose type our code does not yet know how to handle.

Let’s try making the query multiline to see if our client handles it correctly.

irbpsql> SELECT 
irbpsql* 1, 2, 3;
Unsupported message 'T' received from server

Looks like that works too. Nice!

Now we set about processing the response from the server.

Process query results

We are back to creating new handlers for as yet unhandled messages from the server. We can see that the new message to handle is of type T. Looking up the docs, we can see that Byte1('T') is sent to us when the trailing message is a RowDescription.

There’s quite a lot going on with this message type. Other than the usual message length field, we have an Int16 which is the count of the number of fields in a row And for each field, we have

• String - row field name
• Int32 - object ID of the table if the row field is the column of a specific table (0 otherwise)
• Int16 - attribute number of the column if the field is a table-specific column (0 otherwise)
• Int32 - object ID of the row field’s data type
• Int16 - data type size (negative if it’s a variable-width type)
• Int32 - type modifier
• Int16 - 0 for text and 1 for binary

But upon closer inspection of these fields, we see that most of these fields are unlikely to be of use to a regular user. The most relevant one is the name of each column since it would be useful to show that to the user.

I think it’s reasonable for the user to expect the results of the query to be printed like a table. So let’s add a handler that turns this message into a table header.

Since we’ve encountered a new message type, let’s add a constant for it near the top of our script. Slot in MSG_TYPE_ROW_DESCRIPTION amongst the existing message type constants. we’ll also need to read 16-bit fields so adding a TWO_BYTES constant will help with readability of our code.

# ...
ONE_BYTE = 1
TWO_BYTES = 2
FOUR_BYTES = 4
MSG_TYPE_AUTH_REQUEST = 'R'
MSG_TYPE_BACKEND_KEY_DATA = 'K'
MSG_TYPE_PARAMETER_STATUS = 'S'
MSG_TYPE_READY_READY_FOR_QUERY = 'Z'
MSG_TYPE_ROW_DESCRIPTION = 'T'
PROT_VER_MAJOR = 3
# ...

Then add a new handler method right before our main loop.

def handle_row_description(conn)
  conn.read(FOUR_BYTES) # consume length field

  column_count = conn.read(TWO_BYTES)
                      .unpack1('s>')

  column_count.times do |col_index|
    column_name = conn.gets("\0")

    if col_index.zero?
      puts '-' * 40
      print '| '
    end

    print "#{column_name} | "
    print "\n" if col_index == (column_count - 1)

    conn.read(FOUR_BYTES) # consume table object ID
    conn.read(TWO_BYTES)  # consume table attribute number
    conn.read(FOUR_BYTES) # consume data type object ID
    conn.read(TWO_BYTES)  # consume data type width
    conn.read(FOUR_BYTES) # consume data type modifier
    conn.read(TWO_BYTES)  # consume format code
  end

  puts '-' * 40
end

We also remember to update our main loop to invoke the handler when needed.

  # ...
  when MSG_TYPE_READY_READY_FOR_QUERY
    handle_ready_for_query_message(conn)
  when MSG_TYPE_ROW_DESCRIPTION
    handle_row_description(conn)
  # ...

Now let’s take a step back and look at what the new code is doing.

First, we see that the handler consumes the message length field. We don’t use the length field so we don’t hold onto its value.

The next thing the handler does is get the number of columns our result has. We use the s> directive for unpacking since this the directive used for network order 16-bit signed integers.

Now that we know how many more fields to expect, we use the previously obtained count to iterate over them. This is why we didn’t need to store the value of the message length field we encountered earlier.

Since for us, only the name of the column is important, that’s the only field we do anything with for each iteration. Because Postgres will terminate strings with the null byte, we can use IO#gets to read the string and tell it to stop reading when it encounters a \0.

When displaying headers for a table, it usually helps to have them visually stand out from the other rows. For our client, we just print a line of 40 hyphens above and below the column names. This is a rather unsophisticated way to do it since it isn’t dynamic. The lines will either overshoot or not be long enough depending on the number of columns and width of the column names. But it keeps things simple and let’s us focus on the fun stuff.

We also separate use | to separate the column names.

The rest of the loop consists of us consuming the other fields that we don’t use. It’s possible (and likely more efficient) to just consume them in one method call but doing it separately helps us understand better what’s going on. Especially with the more field-specific comments.

Let’s give our new handler a spin.

irbpsql> SELECT 1;
----------------------------------------
| ?column? | 
----------------------------------------
Unsupported message 'D' received from server

The row header is printed out as intended. Great!

But we also see a new unhandled message. It’s likely related to the rest of the rows but let’s check the docs to see if that’s really the case.

The docs point to DataRow as the type of message we need to handle. Once we handle this, we’ll at least have a complete query results table printed.

Compared to RowDescription, this has fewer fields for us to worry about. We still have a number of columns as indicated by an Int32 field. But it’s followed by at most 2 fields. The first is an Int16 for the length of the column value though we have to watch out since it is negative for NULL values. If the column length was a positive number, then it’s followed by the number of bytes indicated.

Alright, let’s add more code. We start with a new constant MSG_TYPE_DATA_ROW.

MSG_TYPE_BACKEND_KEY_DATA = 'K'
MSG_TYPE_DATA_ROW = 'D'
MSG_TYPE_PARAMETER_STATUS = 'S'

Then a new handler method right before our main loop.

def handle_data_row(conn)
  conn.read(FOUR_BYTES) # consume length field
  
  column_count = conn.read(TWO_BYTES)
                      .unpack1('s>')

  column_count.times do |col_index|
    value_length = conn.read(FOUR_BYTES)
                       .unpack1('l>')

    value = if value_length.negative?
              'NULL'
            elsif value_length == 0
              ' '
            else
              conn.read(value_length)
            end

    print '| ' if col_index.zero?
    print "#{value} | "
    print "\n" if col_index == (column_count - 1)
  end
end

The handler logic should be familiar. It looks similar to our RowDescription handler with the added benefit of not having extraneous fields that we don’t use.

One thing that’s slightly different is the way we are unpacking the length of the value field. We are using the l> directive as opposed to N as we have been doing so far. This is because the value can be a negative number. So we use the l> since it unpacks the 4 bytes as a signed integer making correctly read negative integer values.

We also choose to distinguish between a NULL and a zero-width value by checking whether the length of the value is 0 or a negative number.

After that, we remember to update our main loop.

  # ...
  when MSG_TYPE_BACKEND_KEY_DATA
    handle_backend_key_data_message(conn)
  when MSG_TYPE_DATA_ROW
    handle_data_row(conn)
  # ...

Let’s fire up our client and re-run our query. With any luck, we should see the results.

irbpsql> SELECT 1;
----------------------------------------
| ?column? | 
----------------------------------------
| 1 | 
Unsupported message 'C' received from server

Success! We are now able to see the results of our query. But we see that we have yet another unhandled message.

It might seem bothersome to still have to handle another message even though we are already handling the rows returned. But that also means this particular unhandled message is unlikely to require elaborate processing.

Let’s have a look at what it is. The docs point to it being a CommandComplete message.

And indeed we can see that only a string trails the message length field. The string is made up of 2 (or in the case of INSERT 3) subfields. The first is the command/query that was just executed, e.g. SELECT for a SELECT query or INSERT for an INSERT query. The last field is the number of rows.

The row count is useful since we can display it to the users. Let’s add a handler for our message and let the user know how many rows there are in total while we are at it.

We can probably do this in our sleep by now.

• new constant for the new message type

  MSG_TYPE_BACKEND_KEY_DATA = 'K'
  MSG_TYPE_COMMAND_COMPLETE = 'C'
  MSG_TYPE_DATA_ROW = 'D'
  

• new handler method for the new message type

  def handle_command_complete_message(conn)
  message_length = conn.read(FOUR_BYTES)
                       .unpack1('N')
  
  command_tag = conn.read(message_length - FOUR_BYTES)
                     .split
  row_count = command_tag.last.to_i
  row_count_output = "(#{row_count} row"
  row_count_output += 's' unless row_count == 1
  row_count_output += ')'
  
  puts "\n#{row_count_output}"
  end
  

• update the main loop to call the new handler

  # ...
  when MSG_TYPE_BACKEND_KEY_DATA
    handle_backend_key_data_message(conn)
  when MSG_TYPE_COMMAND_COMPLETE
    handle_command_complete_message(conn)
  # ...
  

Our handler logic is very simple, as expected. String#split will split on null characters too so we use that to our advantage and get the subfields as an array. We are only interested in the number of rows which will always be the last element in that array.

We then proceed to show the user the row count making sure to correctly pluralise the word row.

Let’s run our client again. We can test out more complex queries.

irbpsql> SELECT d.datname AS "Name",
irbpsql* pg_catalog.pg_get_userbyid(d.datdba) AS "Owner"
irbpsql* FROM pg_catalog.pg_database d
irbpsql* WHERE pg_catalog.pg_get_userbyid(d.datdba) = 'postgres';
----------------------------------------
| Name | Owner | 
----------------------------------------
| postgres | postgres | 
| template1 | postgres | 
| template0 | postgres | 
| example_app_test | postgres | 
| sample_app_test | postgres | 
| example_app_development | postgres | 
| api_only_rails_app_test | postgres | 

(7 rows)

The row count is nicely displayed. And there’s no new unhandled messages. We’ve done it!

Try testing out different kinds of queries. If you are accessing a database you whose data you can modify without any worry, you can even try out queries like INSERT, UPDATE, etc.

Exiting gracefully

So far, in order to quit, we have been Ctrl-Cing. It does the job but it is a bit ugly since it throws a stack trace in our face.

irbpsql> ^C./irbpsql:82:in 'ARGF.class#readline': Interrupt
        from ./irbpsql:82:in 'Kernel#readline'
        from ./irbpsql:82:in 'block in Object#handle_ready_for_query_message'
        from ./irbpsql:79:in 'Kernel#loop'
        from ./irbpsql:79:in 'Object#handle_ready_for_query_message'
        from ./irbpsql:177:in 'block in <main>'
        from ./irbpsql:162:in 'Kernel#loop'
        from ./irbpsql:162:in '<main>'

Let’s add a clean way to exit.

We could add a command, e.g. quit, but I’m partial to using Ctrl-D for this purpose. You can also use that key-combination to exit out of applications like irb and psql in addition to using quit or exit.

If we send Ctrl-D to our client at the moment, it appears to do something similar to Ctrl-C at first glance. That is, it exits while printing a backtrace.

irbpsql> ./irbpsql:82:in 'ARGF.class#readline': end of file reached (EOFError)
        from ./irbpsql:82:in 'Kernel#readline'
        from ./irbpsql:82:in 'block in Object#handle_ready_for_query_message'
        from ./irbpsql:79:in 'Kernel#loop'
        from ./irbpsql:79:in 'Object#handle_ready_for_query_message'
        from ./irbpsql:177:in 'block in <main>'
        from ./irbpsql:162:in 'Kernel#loop'
        from ./irbpsql:162:in '<main>'

But look closer and realise a key difference. The former exits due to an Interrupt whereas the latter reports the exit is because of an EOFError.

It appears that Ctrl-D sends an end-of-file to our standard input. But since our code isn’t setup to handle that, it raises an exception. We can just exit cleanly by handling that exception.

But it would be better to not have that exception occur in the first place. Is there a way to get line input without raising an exception on EOF? Turns out there’s IO#gets allows us to do exactly that. We used IO#gets earlier when reading null terminated strings from the server. But if you don’t pass an argument to it, it’ll just read a line. And it returns nil if it encounters an end-of-file.

So go ahead and update the loop inside the handle_ready_for_query_message method to replace line = readline with the following snippet of EOF-aware code.

    line = gets
    unless line
      # Handle EOF (Ctrl-D)
      puts "\nexiting..."
      exit
    end

Now everytime the user inputs Ctrl-D, we declare that we stopping and then exit.

Go ahead and give that try.

irbpsql> 
exiting...

Nice, isn’t it? No more backtrace when exiting via Ctrl-D!

Resetting query input

One thing that’s annoying about the way our client currently handles user input is that if your query spans multiple lines you can only edit the current line. This means that you can’t edit a previous line if you realise you’ve made a mistake.

While it would be fantastic if our client allowed for multi-line editing, let’s aim for something simpler. Allowing the user to cancel their current mult-line query.

In irb or psql, one way to do this is by hitting Ctrl-C. We can try to do the same of our little client.

But how do we detect Ctrl-C? As you may already know, in POSIX systemes, Ctrl-C at a terminal usually sends a signal to the application(s) running in the terminal. Specifically, an interrupt signal (SIGINT).

If an application is interested in being notified of a signal sent to it, it can “trap” it and then handle it as appropriate.

In Ruby, we can use the appropriately named Signal.trap method. We can pass it the name of the signal we want to handle as well as the handler to be invoked. It returns the previous signal handler. This allows us to preserve the some of the original signal-handling behaviour if we wish to.

For our case, we want to only handle this signal while we are accepting user input. When we enter the server enters the ready-for-query state, we can preserve the initial SIGINT handler while registering our own handler to reset the input. Upon sending the query to the server, we can restore the initial SIGINT handler.

Let’s make our handle_ready_for_query_message method to do this. Update the method so that it now looks like this:

def handle_ready_for_query_message(conn)
  # We don't do anything with the data so read it but don't retain it
  conn.read(FOUR_BYTES + ONE_BYTE)

  qry_str = ''
  prompt = -> do
    prompt_symbol = qry_str.empty? ? '>' : '*'
    print "irbpsql#{prompt_symbol} "
  end

  old_sigint_handler = trap(:SIGINT) do
		qry_str = ''
		print "\n"
		prompt.call
  end

  loop do
    prompt.call

    line = gets
    unless line
      # Handle EOF (Ctrl-D)
      puts "\nexiting..."
      exit
    end

    qry_str += line

    if qry_str.rstrip.end_with?(';')
      qry_str.rstrip!
      qry_str += "\0"
      qry_msg_len = FOUR_BYTES + qry_str.bytesize
      qry_msg = "Q#{[qry_msg_len].pack('N')}#{qry_str}"

      conn.write(qry_msg) and return
    end
  end

  trap(:SIGINT, old_sigint_handler)
end

The first change is moving the prompt logic into a lambda. Since lambdas are closures, our prompt logic will be able to access qry_str from wherever it’s called. Meaning we can use it from within the handler and it will still be able to work as expected.

The next change is traping SIGINT. We save the initial handler as old_sigint_handler and register our own that just clears the query string and prints the prompt.

The loop remains largely unchanged with the only difference being that it now needs to prompt the user by calling the prompt lambda.

The last thing our method does is restoring the initial SIGINT handler.

There are certain things to consider while implementing a signal handler in order to ensure signal safety.

For example, the docs list

any IO write operations when IO#sync is false; including IO#write, IO#write_nonblock, IO#puts.

as being unsafe. And since we use stdout to display information to the user, that makes our handler potentially unsafe.

By default, STDOUT is not synced.

❯ ruby -e 'p STDOUT.sync'
false

We could try to make our handler safer but that would be overkill for our simple toy client.

With the input handling updated, let’s actually try out cancelling a multiline query using Ctr-C.

irbpsql> SELCT 
irbpsql* 1, 2^C
irbpsql> SELECT 1, 2;
----------------------------------------
| ?column? | ?column? | 
----------------------------------------
| 1 | 2 | 

(1 row)

It works. Now you can cancel out of a mult-line query and start afresh without having to quit and restart the client!

Toggle parameter status display

We’ve been fixing some annoyances in order to improve the user experience of our client. A minor annoyance that still remains is the client dumping the parameter status onto the display every time we start it up.

$ ./irbpsql -U irbpsql_user -d postgres
in_hot_standby: off
integer_datetimes: on
TimeZone: Africa/Nairobi
IntervalStyle: postgres
is_superuser: off
application_name: 
default_transaction_read_only: off
scram_iterations: 4096
DateStyle: ISO, MDY
standard_conforming_strings: on
session_authorization: irbpsql_user
client_encoding: UTF8
server_version: 16.4 (Ubuntu 16.4-1.pgdg22.04+1)
server_encoding: UTF8
Unsupported message 'K' received from server
irbpsql> 

It’d be much better if this were turned off by default and then toggled on by the user if needed.

A simple way to do this would be via a command-line switch. Good-old OptionParser has just the thing for this. We can add a no- prefix to our long options to negate them.

A nicely, albeit verbose, name for the parameter status printing switch is --print-param-status. We can specify this as [--no-]print-param-status to get the negated option. Let’s make our OptionParser aware of this by adding it to the block that pass into it when we new it up.

  it.on('--[no-]print-parameter-status', 'Defaults to not printing the server runtime parameter values')

The usage information should now include the new option.

$ ./irbpsql --help
Usage: irbpsql [options]
    -h, --hostname=hostname          Defaults to 127.0.0.1
    -d, --dbname=dbname              Defaults to using username as the database name as well
    -U, --username=username          Defaults to OS-level username
    -p, --port=port                  Defaults to 5432
        --[no-]print-parameter-status

Next, we make our parameter status handler require instructions on whether to print out the parameter status fields. We do this by updating it such that it now looks like this:

def handle_param_status_message(conn, printout_requested)
  message_length = conn.read(FOUR_BYTES)
                        .unpack1('N')

  param_name, param_value = conn.read(message_length - FOUR_BYTES)
                                .split("\0")
  puts "#{param_name}: #{param_value}" if printout_requested
end

Our handler method now requires an additional argument to tell it whether or not to print out the param status. We use this new argument as a condition on the last line of the method.

All that’s left now is to update our main loop to pass in the new command-line option as the second argument to the handler.

  # ...
  when MSG_TYPE_PARAMETER_STATUS
    handle_param_status_message(conn, cli_options[:'print-parameter-status'])
  # ...

And with that relauch the client. It should now take you straight to the prompt.

Try passing in the --print-param-status flag.

$ ./irbpsql -U irbpsql_user -d postgres --print-param-status
in_hot_standby: off
integer_datetimes: on
TimeZone: Africa/Nairobi
IntervalStyle: postgres
is_superuser: off
application_name: 
default_transaction_read_only: off
scram_iterations: 4096
DateStyle: ISO, MDY
standard_conforming_strings: on
session_authorization: irbpsql_user
client_encoding: UTF8
server_version: 16.4 (Ubuntu 16.4-1.pgdg22.04+1)
server_encoding: UTF8
Unsupported message 'K' received from server
irbpsql> 

The param status is back.

You can even try out the --no-print-param-status flag if you wish though it’ll have no effect compared to not passing it all since we don’t print the param status by default.

$ ./irbpsql -U irbpsql_user -d postgres --no-print-param-status
irbpsql> 

Handle error messages from the server

We sorted out some of the annoyances our little client has. But there’s still one major one - what happens when you send a query with an error to the server.

At the moment, you get a query wrong and the client will exit due to an unhandled message.

irbpsql> SELECT "Hello" FROM world;
Unsupported message 'E' received from server

We could have fixed this earlier but I thought it better to do something different for a while before we get sick of message handling.

Now it won’t be so bad having to deal with another message. Especially since this is the last one. Let’s get to it.

The docs tell us that E signifies an ErrorResponse.

We are told that

The message body consists of one or more identified fields, followed by a zero byte as a terminator.

Each field has a byte that is a

code identifying the field type; if zero, this is the message terminator and no string follows.

and a string that is the field value.

The full list of codes is also made available.

There are nearly 20 of them by my count. But we will not be handling all of them. Let’s start with the ones we expect to always be present for each error message.

From reading the docs, it seems like the fields we can always reliable expect for each error message are:

• C: The SQLSTATE error code.
• M: The human-readable error message.
• S: The severity e.g. ERROR, FATAL, WARNING, etc. It may be localised/translated.
• V: Like S but always in English. Never localised/translated.

First, we add the message type constant to our list of constants.

# ...
MSG_TYPE_DATA_ROW = 'D'
MSG_TYPE_ERROR_RESPONSE = 'E'
MSG_TYPE_PARAMETER_STATUS = 'S'
# ...

Then we create a new handler method just before our main loop.

def handle_error_response(conn)
  conn.read(FOUR_BYTES) # consume length field

  error_code = ''
  error_message = ''
  severity = ''

  while (field_type = conn.getc) && field_type != "\0"
    value = conn.gets("\0")

    case field_type
    when 'C'
      error_code = value
    when 'M'
      error_message = value
    when 'S'
      severity = value
    when 'V'
			# Duplicate of 'S' if PostgreSQL messages are output in English
			# No need to do anything with the value
		else
			puts "Unknown error field type: #{field_type}.\n"
    end
  end

  puts "\n#{severity}: #{error_code} - #{error_message}"
end

Our handler contains a loop that keeps consuming each error subfield until we are out of them. If it encounters a subfield it does not yet know about, it will print out an error message and keep moving on. This should help us catch new unhandled error field types without having to implement all of them at once.

Finally, it prints out a formatted version of the error response for the user to see.

We just need to wire up our main loop to the handler.

  # ...
  when MSG_TYPE_DATA_ROW
    handle_data_row(conn)
  when MSG_TYPE_ERROR_RESPONSE
    handle_error_response(conn)
  # ...

Let’s give the error handler a test drive.

irbpsql> SELECT "Hello" FROM world;
Unknown error field type: P.
Unknown error field type: F.
Unknown error field type: L.
Unknown error field type: R.

ERROR: 42P01 - relation "world" does not exist

Not bad. We do get our formatted error message. But that’s in addition to some unknown fields - P, F, L and R.

The docs can clear up what these fields mean. It looks like the fields can be described as :-

• F: the file name of the source-code location where the error was reported.
• L: the line number of the source-code location where the error was reported.
• P: decimal ASCII integer, indicating an error cursor position as an index into the original query string. The first character has index 1, and positions are measured in characters not bytes.
• R: the name of the source-code routine reporting the error

3 of those fields, F, L and R, are about the location in the PostgreSQL source code that raised the error. That is useful to a Postrgres hacker but not the average user of our client.

That leaves us with P. We can use this to point the user to the specific part of their query that Postgres finds problematic. But in order to do that, we need to remember what the user had input.

We aren’t doing that yet so let’s fix that.

Let’s add a variable to hold the last input query.

# ...
cli_options = {}
last_query = ''
# ...

Then update our handle_error_response method to accept the last query input by the user and use it to show the user where the problem is. The updated version should look like the snippet below.

# ...
def handle_error_response(conn, last_query)
  conn.read(FOUR_BYTES) # consume length field

  error_code = ''
  error_message = ''
  query_segment_with_caret = ''
  severity = ''

  while (field_type = conn.getc) && field_type != "\0"
    value = conn.gets("\0")
    
    case field_type
    when 'C'
      error_code = value
    when 'M'
      error_message = value
    when 'P'
      error_position = value.to_i - 1
      query_segment_start = last_query
                            .rindex("\n", error_position)
      # We add 1 to the start index because we don't want to include the newline character in the query segment
      query_segment_start = query_segment_start ? query_segment_start.to_i + 1 : 0
      query_segment_end = last_query.index("\n", error_position)
      query_segment = last_query[query_segment_start..query_segment_end]
      caret_offset = error_position - query_segment_start + 1

      query_segment_with_caret = "#{query_segment}\n#{'^'.rjust(caret_offset)}"
    when 'S'
      severity = value
    when 'V'
      # Similar to `S` with the only difference being that `S` might be localised.
      # Not necessary to do anything with the value.
    when 'F', 'L', 'R'
      # These fields point to the location in the PostgreSQL source code.
      # Ulikely to be useful to the average user so do nothing with the values.
    else
      puts "Unknown error field type: #{field_type}.\n"
    end
  end

  puts "\n#{severity}: #{error_code} - #{error_message}"
  puts query_segment_with_caret unless query_segment_with_caret.empty?
end
# ...

We’ve added a new local variable query_segment_with_caret.

There’s a bit of logic in our updated handler to set it to the specific line of the query that’s problematic. While also augmenting it with a ^ to point to where exactly on the line Postgres reports the issue as emanating from.

Basically, we use String#rindex to find where the offending line starts and String#index to figure out where it ends. Both methods accept an offset parameter to specify where to begin the search. They search in opposite directions essentially allowing us to go from the middle-out in search of our line boundaries. If we don’t find the either boundary, we use the beginning/end of the string as the boundary.

We also take care to avoid off-by-one errors since according the pointer from Postgres applies 1-based indexing while Ruby string methods tend to use 0-based indexing.

Our updated method finally prints out query_segment_with_caret if it’s not empty.

With the biggest part of our update out of the way, we just need to tweak the code in a couple of places to bring it all together.

The last_query needs to be updated everytime the user sends a query to the server. We can do this by tweaking the handle_ready_for_query_message to return the query it’s just dispatched to Postgres.

Change

      conn.write(qry_msg) and return

to

      conn.write(qry_msg)

      return qry_str

And

  trap(:SIGINT, old_sigint_handler)

to

  trap(:SIGINT, old_sigint_handler)

  return ''

Lastly we update our main loop to accomodate the updated handlers

  # ...
  when MSG_TYPE_ERROR_RESPONSE
    handle_error_response(conn, last_query)
  when MSG_TYPE_PARAMETER_STATUS
    handle_param_status_message(conn, cli_options[:'print-parameter-status'])
  when MSG_TYPE_READY_READY_FOR_QUERY
    last_query = handle_ready_for_query_message(conn)
  # ...

That’s it. Let’s rerun our client and send the wrong query agains.

irbpsql> SELECT "Hello" 
irbpsql* FROM world;

ERROR: 42P01 - relation "world" does not exist
FROM world;
     ^

It works. We’ve done it! Our toy PostgreSQL client in pure Ruby is usable! And in 200 lines of Ruby code, as promised.

❯ tokei irbpsql
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 Language            Files        Lines         Code     Comments       Blanks
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 Ruby                    1          256          200            8           48
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
 Total                   1          256          200            8           48
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

It took a while but we finally got there in the end.

What next?

I hope you had fun following along and more importantly learnt some new things. Whether it was doing something in Ruby you’ve never done before or just got a feel for the PostgreSQL client-server protocol.

Where to go next? Try playing around with it some more. Compare with psql. Maybe extend it to work more like psql in certain places. For example, adding a line number to query_segment_with_caret similar to how psql does it.

Or adding a quit command instead of just relying on Ctrl-D to exit.

Maybe you can even add query history so you don’t have to re-type the same queries over and over again.

Perhaps a flag to turn on printing the PostgreSQL source location for errors.

It could even be that you spot opportunities to DRY up some things.

The possibilities are nigh endless.

If you need to take a gander at the complete source, you can find it here.