Date:

Share:

Accessing PostgreSQL databases in Go

Related Articles

This post discusses some options for accessing PostgreSQL databases from Go. I will only cover low-level packages that provide access to the basic database; This post is not about ORM, previously reviewed on this blog. The full source code that accompanies this post is On GitHub.

We are going to use a simple data model that can serve as a basis for an online course system (like Coursera):

There is a lot to many connection between courses and users (a user can take any number of courses, and each course has a number of registered users), and a one to many connection between courses and projects (the course has a large number of users projects, but a project belongs to a single course).

The SQL to create these tables is:

create table if not exists courses (
    id bigserial primary key,
    created_at timestamp(0) with time zone not null default now(),
    title text not null,
    hashtags text[]
);

create table if not exists projects (
    id bigserial primary key,
    name text not null,
    content text not null,
    course_id bigint not null references courses (id) on delete cascade
);

create table if not exists users (
    id bigserial primary key,
    name text not null
);

create table if not exists course_user (
    course_id bigint not null references courses (id) on delete cascade,
    user_id bigint not null references users (id) on delete cascade,
    constraint course_user_key primary key (course_id, user_id)
);

Note that the hashtags The column is of the PostgreSQL array type: Text hashtags[]; This is on purpose, to demonstrate how custom types of PostgreSQL are formatted in the various Go approaches presented here.

Database / sql with pq driver

Probably the most common way to access PostgreSQL databases on Go is to use the standard directory Database / sql, together with Because
As the database manager. The full code for this approach, applied to our sample database is available Here; I will present below some relevant passages:

import (
  "database/sql"
  "fmt"
  "log"
  "os"

  _ "github.com/lib/pq"
)

// Check is a helper that terminates the program with err.Error() logged in
// case err is not nil.
func Check(err error) 
  if err != nil 
    log.Fatal(err)
  


func main() 
  db, err := sql.Open("postgres", os.Getenv("MOOCDSN"))
  Check(err)
  defer db.Close()

  // ... use db here

There is the usual empty import of the drivers package, which registers itself with it Database / sql; Then, e "Postgreers" A name can be used as a name used to convey to him sql.Open. The path to the database is passed in env var; For example, it could be something like:

MOOCDSN=postgres://testuser:testpassword@localhost/testmooc

If the database is created with the name testmooc, With the user
Uses the test Having access to it.

Following this initialization, we will be able to issue queries to the database using db. Before we look at sample queries, here is the data model translated into Go types:

type course struct 
  Id        int64
  CreatedAt time.Time
  Title     string
  Hashtags  []string


type user struct 
  Id   int64
  Name string


type project struct 
  Id      int64
  Name    string
  Content string

Note that unlike ORMs, relationships between tables are not captured here. A Course There is no collection of projects; This is something we need to manually configure when DB queries. Another thing to pay attention to is this
Hashtags There is the type []wire Which will be mapped to PostgreSQL’s
Text[].

The following is a sample function that wraps an SQL query:

func dbAllCoursesForUser(db *sql.DB, userId int64) ([]course, error) 
  rows, err := db.Query(`
    select courses.id, courses.created_at, courses.title, courses.hashtags
    from courses
    inner join course_user on courses.id = course_user.course_id
    where course_user.user_id = $1`, userId)
  if err != nil 
    return nil, err
  
  defer rows.Close()
  var courses []course
  for rows.Next() 
    var c course
    err = rows.Scan(&c.Id, &c.CreatedAt, &c.Title, pq.Array(&c.Hashtags))
    if err != nil 
      return nil, err
    
    courses = append(courses, c)
  
  if err := rows.Err(); err != nil 
    return nil, err
  
  return courses, nil

Given a user ID, this function obtains all the courses to which the user is enrolled, by joinExecution of the course table with User_Course Link table.
Database / sql Requires reading the query result in a scan loop, and manually placing the results in structures; Is unaware of any mapping between Go structures and SQL tables. PostgreSQL arrays are called by browsing with a
pq.Array Type.

Here’s a slightly more mixed query, which joinThree tables for achieving all the projects the user needs to complete (there can be several projects in each course, and a user can register for several courses):

func dbAllProjectsForUser(db *sql.DB, userId int64) ([]project, error) 
  rows, err := db.Query(`
    select projects.id, projects.name, projects.content
    from courses
    inner join course_user on courses.id = course_user.course_id
    inner join projects on courses.id = projects.course_id
    where course_user.user_id = $1`, userId)
  if err != nil 
    return nil, err
  
  defer rows.Close()
  var projects []project
  for rows.Next() 
    var p project
    err = rows.Scan(&p.Id, &p.Name, &p.Content)
    if err != nil 
      return nil, err
    
    projects = append(projects, p)
  
  if err := rows.Err(); err != nil 
    return nil, err
  
  return projects, nil

While SQL is more complicated, the rest of the code is almost identical to the previous function.

pgx

While Because Has been around for a long time and has served the Go community well, it will not be actively maintained recently. In fact, if you read to the end of his README, you will find it in the status section:

This package is actually in maintenance mode and is not actively developed. Small fixes and features are rarely tested and merged. We recommend using pgx that is actively maintained.

So what is that pgx? It Driver and Toolkit for PostgreSQL:

pgx strives to be low-level, fast and efficient, while also enabling PostgreSQL-specific features that are standard Database / sql The package does not allow.

The pgx driver component can be used alongside the device
Database / sql package.

God pgx The package has two separate operating modes:

  1. It can be used as a standard driver for Database / sql.
  2. It can be used as a direct interface to PostgreSQL, which does not depend on the standard API of Database / sql, And thus can use PostgreSQL-specific attributes and code paths.

To use option (1), we can reuse 99% of the previous sample (e)
Database / sql The interface is really very standard!). All we have to do is replace the import drivers with:

_ "github.com/jackc/pgx/v4/stdlib"

Then change the sql.Open Call to activate the pgx Driver:

db, err := sql.Open("postgres", os.Getenv("MOOCDSN"))

We do not need to update the rest of the code.

What about the direct interface? To do this, we will need to slightly change our code, as the types are slightly different. The full code for this is available
Here; The following are the notable changes:

ctx := context.Background()
conn, err := pgx.Connect(ctx, os.Getenv("MOOCDSN"))
Check(err)
defer conn.Close(ctx)

Instead of using sql.Open, We read pgx.Connect Instead. When it’s time to query in DB, our function to capture all the courses a user has enrolled in will be:

func dbAllCoursesForUser(ctx context.Context, conn *pgx.Conn, userId int64) ([]course, error) 
  rows, err := conn.Query(ctx, `
    select courses.id, courses.created_at, courses.title, courses.hashtags
    from courses
    inner join course_user on courses.id = course_user.course_id
    where course_user.user_id = $1`, userId)
  if err != nil 
    return nil, err
  
  defer rows.Close()
  var courses []course
  for rows.Next() 
    var c course
    err = rows.Scan(&c.Id, &c.CreatedAt, &c.Title, &c.Hashtags)
    if err != nil 
      return nil, err
    
    courses = append(courses, c)
  
  if err := rows.Err(); err != nil 
    return nil, err
  
  return courses, nil

Note that the Go struct types that represent table values ​​remain exactly the same. Reading query results with pgx Very similar to Database / sql, But there is no need to wrap more types of arrays pq.Array, Since pgx
Supports original reading of PostgreSQL arrays into Go slices.

So what do we get from the use pgx instead of Database / sql? According to List of features in his README, Pretty much, including native support for custom PostgreSQL types, JSON, an advanced pool of connections, and a host of performance-oriented features. especially,
pgx Uses PostgreSQL binary protocol directly for faster processing and release of types. as per The pgx indices, There are noticeable differences in performance in some cases.

sqlx

We’ve seen some examples of non-trivial SQL queries scanned into Go objects so far; They all involve the same pattern:

  • The query is submitted
  • The result is repeated line by line
  • Each row is manually broken down into structure fields

One of the biggest complaints about Database / sql In Go is the text of this process; Especially the second and third step above. Why we can not just say:

var courses []course
db.FillInQueryResults(&courses, ....)

After all, many packages in the Go device library already work this way; for example Coding / json, Etc. The reason is the variety of types of SQL supports. While JSON has relatively few supported types, SQL has many types; Moreover, SQL types are different by database. Therefore, it was quite complicated for Project Go to offer such advanced scanning capabilities in the standard library, and we should rely on third-party packages instead.

Fortunately, a plethora of third-party packages exist for just this purpose. One of the most prominent is sqlx. Let’s look again at the sample query code of our database, this time using sqlx. The full code for this is available Here.

The database definition code is very similar to vanilla Database / sql version:

import (
  "fmt"
  "log"
  "os"

  "github.com/jmoiron/sqlx"
  _ "github.com/lib/pq"
)

func Check(err error) 
  if err != nil 
    log.Fatal(err)
  


func main() 
  db, err := sqlx.Open("postgres", os.Getenv("MOOCDSN"))
  Check(err)
  defer db.Close()

  // ... use db here

sqlx.Open Wraps sql.Open And uses the same database administrator registration mechanism. The kind he returns is sqlx.DB, Which lengthens sql.DB With some convenient methods. Here’s our function to ask all the courses a user has signed up for, this time using sqlx:

func dbAllCoursesForUser(db *sqlx.DB, userId int64) ([]course, error) 
  var courses []course
  err := db.Select(&courses, `
    select courses.id, courses.created_at, courses.title, courses.hashtags
    from courses
    inner join course_user on courses.id = course_user.course_id
    where course_user.user_id = $1`, userId)
  if err != nil 
    return nil, err
  
  return courses, nil

This is exactly what we wanted! The code scans the result into a slice of
Course Objects directly, without the need for a line-by-line loop. sqlx
Achieves this achievement through reflection – it examines the basic type of structure in a slice and maps DB columns to struct fields automatically. But sometimes it needs help; For example ours Course The structure should be changed as follows:

type course struct 
  Id        int64
  CreatedAt time.Time `db:"created_at"`
  Title     string
  Hashtags  pq.StringArray

since sqlx Will not map the database Created in Column to Created in
Field automatically, we must provide instruction to do so explicitly in the field tag.

sqlx Requires a base Database / sql Driver for actual DB interactions. In the example above, we used Because, But stdlib’s driver pgx Can be used as well. unfortunately, sqlx Does not support the original pgx driver. However, another package was called wretched Supports both original drivers and stdlib’s pgx. I wrote another version of this sample, using
wretched; I will not see this code here, as it is very similar to sqlx
For example, but you can find it On GitHub.

the mother sqlx worth it?

Looking at ours dbAllCoursesForUser Function, the user version sqlx
Saves about 14 lines of code compared to scanning vanilla with Database / sql. I’m listed and say that ORMs will probably not be worthwhile in Go, but what about
sqlx? Is saving 14 LOC per DB query function worth the hassle of additional dependency, with the potential quirks, bugs and leaking abstractions?

It’s hard to answer this question all over the world, so I’ll just say “it depends”.

On the one hand, 14 LOC per DB query is really not much. Suppose you have 50 possible SQL queries in your application, this saves 700 LOC of trivial code and repeats itself. It is much? In most cases, almost certainly not. Ultimately, it all boils down to the central thesis of the benefits of additional dependence as a function of effort.

On the other hand, unlike ORMs, packages like sqlx and wretched
Provide fairly focused utilities with not much magic involved. After all, the standard directory already has similar tools built into Unmarshaling JSON, so this is a proven method that can work for data even in relational databases. Because the benefits of these packages are focused, it is not terribly difficult to rip them off a code base and replace them, in case things do not go as expected, so they also pose a much smaller risk than getting into ORMs.

Finally, packages like sqlx and wretched Provide a midpoint between raw SQL access and full ORM; This means mid-way pros as well as cons.


Source

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Popular Articles