Talking with PostgreSQL through TCP

Why?

I wondered how hard it was to get data from a database without using a client.

The PostgreSQL protocol was surprisingly easy to understand and the documentation was well-written, which made the process pretty straightforward.

For simple applications, I think it makes sense to handle the communication yourself - if you like the challenge.

But for anything customer-facing or system critical it's best to avoid the edge cases and use a client.

The setup

For this, I've created a database called "solar_system" with a table called "planet".

The "planet" table contains the following:

Authenticate

The first step in the communication is to authenticate with the PostgreSQL server.

For simplicity, we're using the "postgres" user, which doesn't require a password.

Sending to PostgreSQL

Received from PostgreSQL

We're now authenticated.

But in a more real-life cenario, you will probably receive one of the following messages.

• AuthenticationCleartextPassword
• AuthenticationMD5Password
• AuthenticationGSS
• AuthenticationSSPI
• AuthenticationSASL

Each of these authentication methods requires that additional messages be sent back and forth.

For instance if "AuthenticationCleartextPassword" is returned, you will have to respond with "PasswordMessage" containing the password, before receiving "AuthenticationOk"

Simple Query

Now we're ready to start sending queries to PostgreSQL.

PostgreSQL has a concept called simple query - which is a very easy way to query the database.

However, it's not possible to use SQL parameters, and all values are returned as strings.

The query message only contains a Message type, message size, and a SQL query.

Sending to PostgreSQL

Received from PostgreSQL

We received 5 messages.

RowDescription contains information about each column we're receiving from the result.

RowData (x2) represents a row with data for each column returned.

CommandCompletion gives a summary of number of rows affected. In this case "SELECT 2".

ReadyForQuery tells us that postgres (surprisingly) is ready for a new query.

Extended Query

Extended queries open up more options - for instance, the possibilities to use SQL parameters, reuse prepared queries, and get numbers back as in binary instead of text.

But to use extended queries we have to send 4 messages Parse, Bind, Describe, and Execute

The Parse message primarily contains your SQL query.
You can also optionally specify a name for the statement, to reuse the parsed query multiple times and you can optionally specify what parameter types are going to be used.

The Bind message primarily contains your SQL parameters.
You can also specify the optional prepared statement and whether data should be returned as Text or Binary.

The Describe message tells PostgreSQL if we want column information back.
The message is optional. You can specify the name of the portal or statement, or leave it blank.

The Execute message primarily indicates that we're done.
You can also specify a maximum number of rows to return.

Sending to PostgreSQL

Received from PostgreSQL

We received 8 messages.

ParseComplete tells us that our Parse message was successful.

BindComplete tells us that our Bind message was successful.

ParameterDescription tells which type our parameter has been interpreted as.

RowDescription contains information about each column we're receiving from the result. Now columns "radius" and "moons" aren't variable length anymore, but instead 4 bytes.

RowData (x2) represents a row with data for each column returned.

CommandCompletion gives a summary of number of rows affected. In this case "SELECT 2".

ReadyForQuery tells us that postgres (surprisingly) is ready for a new query.

That's basically it - at least for a simple example of how the protocol works. I can recommend looking up Protocol Message Formats in the documentation if you want to dig deeper.

Thanks!

Thank you for sticking around till the end. I hope it has been informative and perhaps you've learned a thing or two. :)

If you have any questions, thoughts, or comments, feel free to reach me at hello@eibx.com