Sep 30, 2022
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?
- standard library “database/sql”
- various drivers implementing the standard library interface
Standard library
The main goals of the standard library are described in the doc.txt file
- provide a generic database API
- 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
- client and server can communicate with each other via TCP/IP
- 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
- first byte — message type
- 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:
- know the required message types and their format
- 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
- we form the start message (pass the username and database)
- 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)
- making a request
- 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:
- TCP/IP connection to the DB
- 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 int32
- 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.
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.
