Generating Go Entities from PostgreSQL Tables and Views

Overview

Back in early 2014, I was getting ready to revamp the cmscomputing.com website from my aging hand-written ASP.NET CMS to a Go-based solution. I was still poised to write my own basic mini-wiki / blog engine, but was not sure how to automate database access in a simple and effective manner.

Since I had a clear preference in favor of PostgreSQL as my db, I looked for pure Go solutions for drivers. By chance, I noticed that Jack Christensen had posted a golang-nuts message board announcement for the second version of his pgx library, and I was instantly impressed. The library used no reflection, and was measurably faster than the "de facto" pg driver. Even a brute-force bombardment with INSERTs and SELECTs showed it clocking anywhere from 25 to 40 percent faster than reflection based (and ultimately, pg-based) alternatives.

I used pgx as the communicating driver with Postgres, and on top of it I wrote a generator tool, under the generic, and disturbingly cacophonous name of pgtogogen. If I remember correctly, the first dump of code on Github occurred somewhere at the beginning of 2015, and I have been gradually adding bits of functionality and fixes ever since, based on time and needs.

Use Cases

I would not call the pgtogogen-generated libraries an ORM. They are simply database read-write automations: bits of code packaged together that spared me a whole buch of copy and paste. I also wanted a very simple, and opinionated way of ingesting http form values into structures mapped to database tables that I could easily perform database inserts and updates on. A very basic transactional support was added to support more robust publishing flows.

Please don't use this blindly in production anywhere. This website functions on top of it, but it doesn't mean the code is pristine or bug free. In fact some parts are simply lingering there, half-dead. The parts that work definitely suit my purposes, so if they suit yours, feel free to dissect and adapt any of them against your objectives.

Installing PgToGoGen

To install the generator, run:

go get github.com/silviucm/pgtogogen

If all goes well, you should have the pgtogogen tool binary inside the $GOPATH/bin folder.

Generating the Model

What I had in mind was a tool that would be given the connection details for a PostgreSQL database, try to rummage as much as possible through its metainformation, and would end up generating Go structures and functionality that roughly corresponds to (and allows working with) its tables, views, and, minimally, its functions.

By default, the tool will generate a package named model, hence it expects an empty model subdirectory in the project parent directory. The tool should be run from the project directory (e.g. $GOPATH/src/myproj).

Suppose your database is named hamlet, hosted on localhost and you have a db owner user named yorik with password pensiveskull, you would run the tool like this:

$GOPATH/src/myproj/pgtogogen -h=localhost -n=hamlet -u=yorik -pass=pensiveskull

At this point, the hitherto empty $GOPATH/src/myproj/models directory should be full of Go files, roughly mapped to your tables and view with a few core functionality files thrown in there as well.

Using the model Package

To make my life easier, I will simply go over the manner in which the generated files are used as part of cmscomputing.com backend. First, we need to initialize the db connection pointer. We do this inside main:

var poolMaxConnections int = 100
_, err := models.InitDatabaseMinimal("localhost", 5432, "yorik", "pensiveskull",
    "hamlet", poolMaxConnections)

if err != nil {
	log.Fatal("InitDatabase FAIL:", err)
}

To operate on the Postgres entities, I have divided the various types into singleton containers. The tables are grouped inside models.Tables, the views are grouped inside models.Views, and the functions inside models.Functions.

Working with Tables

Tables are generated as structures using the PascalCase notation. For example, if you have a cms_article table in the database, expect a CmsArticle struct to be available for you to use. It would look something like this:

/* The article main details table  */
type CmsArticle struct {
	Id int64 // database field name: id, IsPK: true , IsCompositePK: false, IsFK: false
	
	/* The title of the article */
	Title string // database field name: title, IsPK: false , IsCompositePK: false, IsFK: false
    
    // ... (the rest of the fields here)
    
    // nullable fields are accompanied by a bool flag
	DateModified time.Time // database field name: date_modified, IsPK: false , IsCompositePK: false, IsFK: false
	DateModified_IsNotNull bool // if true, it means the corresponding field does not currently carry a null value    
    
	// Set this to true if you want Inserts to ignore the PK fields	
	PgToGo_IgnorePKValuesWhenInsertingAndUseSequence bool 

	// Set this to true if you want New or Create operations to automatically
	// set all time.Time (datetime) fields to time.Now()
	PgToGo_SetDateTimeFieldsToNowForNewRecords bool 

	// Set this to true if you want New or Create operations to automatically
	// set all Guid fields to a new guid
	PgToGo_SetGuidFieldsToNewGuidsNewRecords bool    
}

In general, I tried to capture all the table and field comments from Postgres and generate them on top of its respective Go counterpart. The comments at the right side of the fields are simply useful meta information.

Regarding nullable fields, the approach I decided to take was a rather opinionated one: I opted towards having an "is not null" flag. Thus, a value of true would indicate a non-null value, and vice-versa. The responsibility (sadly, but hey, that's life) rests with the programmer to check the value of that flag before deciding upon using the main field.

In addition to the database fields, each table struct type has a few PgToGo_ prefixed fields. Those are control fields, and allow tweaking of the insert / update behaviour. More about that a bit later.

Selecting

Retrieving multiple table records can be done using a few predefined functions. In general, if any criteria bits or ordering is specified, it is simply SQL.

Retrieve all records for the table from the database:

articles, err := models.Tables.CmsArticle.SelectAll()
if err != nil {
	log.Error("AdminController.GetAdminHomePage() error in CmsArticle.SelectAll: ", err)		
	return
}
if articles != nil {
	// do something with a slice of CmsArticle
}

Retrieve all records for the table matching the given criteria (do not include the "WHERE" keyword):

articles, err := models.Tables.CmsArticle.Select("title LIKE '%$1%'", "Postgres")
if err != nil {
	log.Error("AdminController.GetAdminHomePage() error in CmsArticle.Select: ", err)
	return
}
if articles != nil {
	// do something with a slice of CmsArticle
}

Retrieve all records for the table and order them accrodingly (do not include the "ORDER BY" keywords):

articles, err := models.Tables.CmsArticle.SelectAllOrderBy("date_created DESC")
if err != nil {
	log.Error("AdminController.GetAdminHomePage() error in CmsArticle.SelectAllOrderBy: ", err)
	return
}
if articles != nil {
	// do something with a slice of CmsArticle
}

Retrieve all 5 paginated records from page 2:

articles, err = models.Tables.CmsArticle.SelectPage(5,2,"status_id = $1",1)
if err != nil {
	log.Error("AdminController.GetAdminHomePage() error in CmsArticle.SelectPage: ", err)
	return
}
if articles != nil {
	// do something with a slice of CmsArticle
}