Scum-Gazeta
Published in

Scum-Gazeta

Writing your own PostgreSQL driver

The most common task is getting data from a database — we all know how to do this very well, but let’s see how it works.
You can see all this in a well-documented library, but I will simplify this process for you — focusing only on essential things.

What do we have to work with the database?

  • various drivers implementing the standard library interface

Standard library

The main goals of the standard library are described in the doc.txt file

  • make type casting / conversions consistent between all drivers
  • take on the complexity of implementing some things

The sense is clear, the general work for all databases is placed in a separate package. And then we just use the implementation (driver) necessary for a particular database.

Since today we will consider working with a database using PostgreSQL as an example, some of you probably use a fairly popular ORM — https://github.com/go-pg/pg, but if you look at the usage example, you will notice that in the imports there is no standard library.
It’s simple — the guys went their own way and did not implement the standard interface, and problems started from here — at least I could not mock work with the repository for tests and had to manage with integration tests. But now the project seems to be already outdated and they ask to use https://bun.uptrace.dev/ in which this dependency reappears! Standardization is our common success! :)

By the way, you can read about mocking a database connection here:

Driver Implementation

I will skip some non-critical points for easier reading — let’s see the driver.

We start working with the database by connecting to it

sql.Open(“driver_name”, connStr)

As I already said, there are no specific driver implementations in the standard library — third-party ones are used.

It usually looks like this in code:

_ "github.com/lib/pq"

How can you implement this simple approach in Go like plugins at runtime — everything we love so much, global variables, init functions, etc.

Map with drivers declared globally

drivers   = make(map[string]driver.Driver)

And then they are initialized by the driver using a method from the standard library

func init() {  sql.Register("driver_name", &Driver{})}

Next, we are already trying to use the driver specified when connecting, and if it is not registered, we get a familiar error for everyone.

sql: unknown driver `driver_name` (forgotten import?)

And if everything is fine, then when working with the database, we will already use a specific implementation of the driver. Today we are just with you and will analyze the principle of its work.

The first thing that is needed to implement communication between two objects is to develop a protocol. Our program with PostgreSQL needs to communicate in the same language — everything is like in ordinary life. If we want to communicate, we must understand each other.

Protocol

We go to the PostgreSQL documentation and collect the postulates we need.

Looking ahead, I was already familiar with this protocol, because I had already worked with logical replication in my open source project:
https://github.com/ihippik/wal-listener

And logical replication differs from the usual just in that it is the sequential execution of the SQL instructions we need instead of the usual copying of files.

Let’s put the highlights together

  • the first step is to verify your identity
  • next comes the standard interaction — question (SQL) — answer (data)

Let’s see how the protocol itself looks like

  • the next 4 bytes are the length of the message
  • next comes the message itself — its content depends on the type of message

Message type

There are several types of messages in the protocol, which you can find here:
https://www.postgresql.org/docs/current/protocol-message-formats.html
In the same section, you can see the format of each type of message (a specific sequence of bytes)

And here the ability to create a new driver is a few knowledge:

  • be able to parse server responses depending on the message type
  • be able to compose your messages and send them to the server
  • know the sequence of actions

This is probably all we need to be successful.

Sequencing

Of course, we also take the sequence of actions from the documentation
https://www.postgresql.org/docs/current/protocol-flow.html

  1. we authenticate using one of the available methods (in our case, we use the SASL framework, the SCRAM mechanism: https://en.wikipedia.org/wiki/Simple_Authentication_and_Security_Layer)
  2. making a request
  3. to parse the answer

Everything else is done for us by the standard library. For example, efficient connection pooling or type casting (PostgreSQL / Golang) using the familiar scanner/valuer interface and much more.

Let’s get down to practice

Our goal is to make this simple piece of code work:

for this simple table:

Driver structure

Driver

The main entity that implements the driver interface has an Open method that returns a connection to the database.

Connector

An entity that implements the interface of the same name from the standard library.
As we can see from the name, it is well able to connect to the database.

Connection

The main workhorse of our driver. This structure contains:

  • reader to read data from our connection
  • configuration in which we store connection parameters for convenience

Buffer

An entity that helps us prepare a message before sending it over our connection.

Here we have standard recording methods that help us build a message in protocol format:

  • write string
  • write bytes
  • calculate and write message size

Anyone who has not worked with bytes before should know that for messages containing data longer than one byte, the sequence of bytes is very important.

Order from oldest to youngest (our version) is called big-endian.

All other information in the novel Gulliver’s Travels :)

Rows

A structure that helps parse the response with data from the database
It also stores the connection to the database and the description of the received data, the type, and the name of each field.

Startup message

Let’s see how I form it in code:

This is the only place where we do not need the first byte with the message type — the size comes right away.
Empty 4 bytes are written to the size, which will be overwritten at the end of the message.

Authentification

Here I implemented a minimum, ideally, several authentication mechanisms should be supported.
Also, I did not get into the process of this mechanism itself — I took a ready-made library for this using the example of one of the drivers.

https://mellium.im/sasl/

It remains our responsibility only to prepare messages of the required type and read the server’s response, and the result of this work should be approved by the server (AuthenticationOk), which will allow us to make SQL-query already. In case of failure, we can get an ErrorResponse.

Making a request

First of all, we will substitute all the arguments in the request.

Here we write the first byte of the message type (also 4 bytes message size), which means the beginning of the request
then the query itself in text format, we calculate the size of the message and send it.

Read the answer

First of all, we must read the message with the description of the fields (RowDescription), and the data which we will further receive in the Next and Scan method.

The description consists of the column name and its type.

We have a Rows structure that we all know has as a Next method.

The Next method simply reads the next piece from the reader connection of data row by row until it encounters EOF and returns false.

Because we already know the types of each column (OID) that we got from the description and size in bytes of each column we can easily bring each type of PostgreSQL to a similar one in Golang and pass this array to the standard library.

Here I’m only interested in two types because I have only two columns in the table.

Wireshark

Let’s see what our communication looks like from the outside. We will look at the traffic using a sniffer.

Startup message

Authentication

SCRAM mechanisms consist of several iterations — I left only the first one. Who cares how it works can read here:

https://www.rfc-editor.org/rfc/rfc5802

Request

Response

The result of the work

Of course, I had ready-made drivers as an example:

Using these examples and documentation, I got my own driver with a minimal set of features.

https://github.com/ihippik/tiny-driver

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store