Exploring automatic table fields discovery in Go

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"`
	}
)

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 name.
func (t TableFieldsInfo) Name() string {
	return camelify(varnameRule(t.FieldName))
}

// TypeString returns type with given ID as a string.
// Useful for struct tags.
// 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=*** host=***"
	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
		FROM pg_catalog.pg_attribute a
		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 {
		log.Printf("%v %v db:\"%v\"\n", v.Name(), v.TypeString(), v.FieldName)
	}

	return
}

Sample output:

ID int64 db:"id"
Artist string db:"artist"
Title string db:"title"
Album string db:"album"
ReleaseDate time.Time db:"release_date"
LastPlayed time.Time db:"last_played"
Rating float64 db:"rating"
Description string db:"description"