Exploring automatic table fields discovery in Go

pqprobe_main.go

// pqprobe probes Postgresql database for given table name and tries to obtain the table's fields.
package main

import (
	"fmt"
	"log"
	"strings"

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

type (
	// TableRelation contains table's oid.
	TableRelation struct {
		OID        int64  `db:"oid"`
		SchemaName string `db:"nspname"`
		TableName  string `db:"relname"`
	}

	// TableFieldsInfo contains table's fields info.
	TableFieldsInfo struct {
		FieldNumber     int64  `db:"attnum"`
		TypeID          uint32 `db:"atttypid"`
		FieldName       string `db:"attname"`
		Type            string `db:"format_type"`
		Nullable        bool   `db:"attnotnull"`
		IsPrimary       bool   `db:"indisprimary"`
		IsUnique        bool   `db:"indisunique"`
		IsValid         bool   `db:"indisvalid"`
		IndexDefinition string `db:"pg_get_indexdef"`
	}
)

func typeString(typ oid.Oid) string {
	switch typ {
	case oid.T_char, oid.T_varchar, oid.T_text:
		return "string"
	case oid.T_bytea:
		return "[]byte"
	case oid.T_timestamptz:
		fallthrough
	case oid.T_timestamp, oid.T_date:
		fallthrough
	case oid.T_time:
		fallthrough
	case oid.T_timetz:
		return "time.Time"
	case oid.T_bool:
		return "bool"
	case oid.T_int8, oid.T_int4, oid.T_int2:
		return "int64"
	case oid.T_float4, oid.T_float8:
		return "float64"
	}

	return "[]byte"
}

func varnameRule(in string) (out string) {
	out = strings.ToLower(in)
	out = strings.Replace(out, "id", "ID", -1)
	out = strings.Replace(out, "uri", "URI", -1)
	out = strings.Replace(out, "url", "URL", -1)
	out = strings.Replace(out, "sql", "SQL", -1)
	return
}

func camelify(in string) (out string) {
	phrase := strings.Split(in, "_")
	for i, v := range phrase {
		phrase[i] = strings.Join([]string{strings.ToUpper(string(v[0])), string(v[1:])}, "")
	}
	return strings.Join(phrase, "")
}

// Name returns field info in camelcase.
// Useful for struct field variable name.
func (t TableFieldsInfo) Name() string {
	return camelify(varnameRule(t.FieldName))
}

// TypeString returns type with given ID as a string.
// Useful for struct variable type.
// See https://godoc.org/github.com/lib/pq#hdr-Data_Types for conventions.
func (t TableFieldsInfo) TypeString() string {
	return typeString(oid.Oid(t.TypeID))
}

func main() {
	connectionString := "user=*** password=*** dbname=***"
	tableName := "***"

	db, err := sqlx.Open("postgres", connectionString)
	if err != nil {
		log.Fatalf("Unable to connect to database %v: %v", connectionString, err)
	}

	var rel TableRelation
	err = db.QueryRowx(`
		SELECT 
		  c.oid,
		  n.nspname,
		  c.relname
		FROM pg_catalog.pg_class c
		     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
		WHERE c.relname ~ $1
		  AND pg_catalog.pg_table_is_visible(c.oid)
		ORDER BY 2, 3;`, fmt.Sprintf("^(%v)$", tableName)).StructScan(&rel)
	if err != nil {
		log.Printf("Unable to find relation of %v: %v\n", tableName, err)
		return
	}

	tableInfoRows, err := db.Queryx(`
		SELECT
		  a.attnum,
		  a.atttypid,
		  a.attname,
		  pg_catalog.format_type(a.atttypid, a.atttypmod),
		  a.attnotnull,
		  COALESCE(i.indisprimary, false) indisprimary,
		  COALESCE(i.indisunique, false) indisunique,
		  COALESCE(i.indisvalid, false) indisvalid,
		  COALESCE(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true), '') pg_get_indexdef
		FROM pg_catalog.pg_attribute a
		  LEFT JOIN pg_catalog.pg_index i ON (i.indrelid = a.attrelid AND a.attnum = ANY (i.indkey))
		WHERE a.attrelid = $1 AND a.attnum > 0 AND NOT a.attisdropped
		ORDER BY a.attnum;`, rel.OID)
	if err != nil {
		log.Printf("Unable to retrieve fields info: %v\n", err)
		return
	}
	defer tableInfoRows.Close()

	tableInfo := []TableFieldsInfo{}
	for tableInfoRows.Next() {
		ti := TableFieldsInfo{}
		if err := tableInfoRows.StructScan(&ti); err != nil {
			log.Printf("Error during scan: %v\n", err)
			return
		}
		tableInfo = append(tableInfo, ti)
	}

	for _, v := range tableInfo {
		tag := ""
		if v.IsPrimary {
			tag = fmt.Sprintf(" [PrimaryKey] %v", v.IndexDefinition)
		}
		log.Printf("%v %v `db:\"%v\"`%v\n", v.Name(), v.TypeString(), v.FieldName, tag)
	}

	return
}

Sample output:

2017/10/20 20:04:27 ID int64 `db:"id"` [PrimaryKey] CREATE UNIQUE INDEX musics_pkey ON musics USING btree (id)
2017/10/20 20:04:27 Artist string `db:"artist"`
2017/10/20 20:04:27 Title string `db:"title"`
2017/10/20 20:04:27 Album string `db:"album"`
2017/10/20 20:04:27 ReleaseDate time.Time `db:"release_date"`
2017/10/20 20:04:27 LastPlayed time.Time `db:"last_played"`
2017/10/20 20:04:27 Rating float64 `db:"rating"`
2017/10/20 20:04:27 Description string `db:"description"`