Why I built this

Most of the time, we connect to PostgreSQL like this:

sql.Open("postgres", connString)

Or we use pgx, database/sql, gorm, or another library. But what actually happens under the hood? I was very interested in that

Postgres listens on a socket. A client connects to that socket. Then both sides send bytes to each other using the PostgreSQL protocol.

Let’s see it step by step, how can we connect? how can we communicate with PostgreSQL?

The whole flow

Before looking at code, this is the shape of the connection:

Go program                         PostgreSQL server
----------                         -----------------
TCP connect        ------------->  listens on :5432
StartupMessage     ------------->  checks user/database
Authentication     <------------>  SCRAM-SHA-256 flow
ReadyForQuery      <-------------  server is ready
Query              ------------->  SELECT ...
Rows               <-------------  result messages
ReadyForQuery      <-------------  ready again

The important thing I learned:

You cannot just connect and immediately send SQL.

Step 1: open a TCP connection

PostgreSQL usually listens on:

localhost:5432

So the first step is not PostgreSQL-specific at all. It is just TCP:

conn, err := net.DialTimeout(
    "tcp",
    fmt.Sprintf("%s:%d", host, port),
    5*time.Second,
)

Go program  <---- TCP byte stream ---->  PostgreSQL server