Skip to content
kanmuPublic

About

dgw generates Golang struct, and simple Table/Row Data Gateway functions from PostgreSQL table metadata

Topics

Resources

Stars

190 stars

Watchers

23 watching

Forks

Repository files navigation

dgw

test GitHub license Go Report Card

Description

dgw generates Golang struct, and simple Table/Row Data Gateway functions from PostgreSQL table metadata. Heavily inspired by xo.

Why created

Personally, I prefer Table/Row Data Gateway over ORM/Query Builder approach when using Go with RDBMS. However, it is very time consuming, tedious, and error-prone to write a lot of columns, query place holders, and struct fields that all have to be exactly in order even for a simple select/insert statement. dgw generate Go struct, and simple functions to get/create row from PostgreSQL table definitions to avoid manually writing simple but tedious SQL.

  • dgw can properly detect autogenerated column (e.g. serial, bigserial), and composit primary key to create appropriate SQL.
  • dgw has ability to easily customize PostgreSQL column type <-> Go type mapping using toml config file.

Installation

brew install kanmu/tools/dgw

How to use

usage: dgw [<flags>] <conn>

Flags:
      --help                 Show context-sensitive help (also try --help-long and --help-man).
  -s, --schema="public"      PostgreSQL schema name
  -p, --package="main"       package name
  -t, --typemap=TYPEMAP      column type and go type map file path
  -k, --autogenkey=AUTOGENKEY ...
                             auto generate key list
  -x, --exclude=EXCLUDE ...  table names to exclude
  -X, --exclude-column=EXCLUDE-COLUMN ...
                             column names to exclude in "table.column" format
      --template=TEMPLATE    custom template path
  -o, --output=OUTPUT        output file path
      --no-interface         output without Queryer interface
      --deprecated=DEPRECATED ...
                             deprecated table names
      --queryer=QUERYER      Queryer type name
      --version              Show application version.

Args:
  <conn>  PostgreSQL connection string in URL format
dgw postgres://dbuser@localhost/dbname?sslmode=disable

Excluding columns

--exclude-column (-X) drops a column from the generated struct field, and from the generated INSERT/SELECT statements. It must be given in table.column format, so a bare column name is rejected. Pass the flag multiple times to exclude more than one column.

dgw postgres://dbuser@localhost/dbname?sslmode=disable -X t1.nullable_str -X t1.xml_data

dgw stops with an error, instead of generating broken code, when the exclusion is:

  • a primary key column (GetXByPk would no longer match the table)
  • a table or column which does not exist in the schema (usually a typo)
  • every column of a table (use --exclude to skip the whole table)

Example

CREATE TABLE t1 (
  id bigserial primary key
  , i integer not null unique
  , str text not null
  , num_float numeric not null
  , nullable_str text
  , t_with_tz timestamp without time zone not null
  , t_without_tz timestamp with time zone not null
  , nullable_tz timestamp with time zone
  , json_data json not null
  , xml_data xml not null
);

CREATE TABLE t2 (
  id bigserial not null
  , i integer not null
  , str text not null
  , t_with_tz timestamp without time zone not null
  , t_without_tz timestamp with time zone not null
  , PRIMARY KEY(id, i)
);

CREATE TABLE t3 (
  id integer not null
  , i integer not null
  , PRIMARY KEY(id, i)
);

Generate Go code by the following command.

$ dgw postgres://dgw_test@localhost/dgw_test?sslmode=disable --typemap=./typemap.toml --schema=public --package=dgwexample --output=example.go
// T1 represents public.t1
type T1 struct {
	ID          int64          // id
	I           int            // i
	Str         string         // str
	NumFloat    float64        // num_float
	NullableStr sql.NullString // nullable_str
	TWithTz     time.Time      // t_with_tz
	TWithoutTz  time.Time      // t_without_tz
	NullableTz  *time.Time     // nullable_tz
	JSONData    []byte         // json_data
	XMLData     []byte         // xml_data
}

// Create inserts the T1 to the database.
func (r *T1) Create(db Queryer) error {
	err := db.QueryRow(
		`INSERT INTO t1 (i, str, num_float, nullable_str, t_with_tz, t_without_tz, nullable_tz, json_data, xml_data) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9) RETURNING id`,
		&r.I, &r.Str, &r.NumFloat, &r.NullableStr, &r.TWithTz, &r.TWithoutTz, &r.NullableTz, &r.JSONData, &r.XMLData).Scan(&r.ID)
	if err != nil {
		return errors.WithStack(err)
	}
	return nil
}

// GetT1ByPk select the T1 from the database.
func GetT1ByPk(db Queryer, pk0 int64) (*T1, error) {
	var r T1
	err := db.QueryRow(
		`SELECT id, i, str, num_float, nullable_str, t_with_tz, t_without_tz, nullable_tz, json_data, xml_data FROM t1 WHERE id = $1`,
		pk0).Scan(&r.ID, &r.I, &r.Str, &r.NumFloat, &r.NullableStr, &r.TWithTz, &r.TWithoutTz, &r.NullableTz, &r.JSONData, &r.XMLData)
	if err != nil {
		return nil, errors.WithStack(err)
	}
	return &r, nil
}

// T2 represents public.t2
type T2 struct {
	ID         int64     // id
	I          int       // i
	Str        string    // str
	TWithTz    time.Time // t_with_tz
	TWithoutTz time.Time // t_without_tz
}

// Create inserts the T2 to the database.
func (r *T2) Create(db Queryer) error {
	err := db.QueryRow(
		`INSERT INTO t2 (str, t_with_tz, t_without_tz) VALUES ($1, $2, $3) RETURNING id, i`,
		&r.Str, &r.TWithTz, &r.TWithoutTz).Scan(&r.ID, &r.I)
	if err != nil {
		return errors.WithStack(err)
	}
	return nil
}

// GetT2ByPk select the T2 from the database.
func GetT2ByPk(db Queryer, pk0 int64, pk1 int) (*T2, error) {
	var r T2
	err := db.QueryRow(
		`SELECT id, i, str, t_with_tz, t_without_tz FROM t2 WHERE id = $1 AND i = $2`,
		pk0, pk1).Scan(&r.ID, &r.I, &r.Str, &r.TWithTz, &r.TWithoutTz)
	if err != nil {
		return nil, errors.WithStack(err)
	}
	return &r, nil
}

// T3 represents public.t3
type T3 struct {
	ID int // id
	I  int // i
}

// Create inserts the T3 to the database.
func (r *T3) Create(db Queryer) error {
	_, err := db.Exec(
		`INSERT INTO t3 (id, i) VALUES ($1, $2)`,
		&r.ID, &r.I)
	if err != nil {
		return errors.WithStack(err)
	}
	return nil
}

// GetT3ByPk select the T3 from the database.
func GetT3ByPk(db Queryer, pk0 int, pk1 int) (*T3, error) {
	var r T3
	err := db.QueryRow(
		`SELECT id, i FROM t3 WHERE id = $1 AND i = $2`,
		pk0, pk1).Scan(&r.ID, &r.I)
	if err != nil {
		return nil, errors.WithStack(err)
	}
	return &r, nil
}

Test

Using Docker (Recommended)

# Start PostgreSQL 16 with pre-configured test database and user
$ docker compose up -d

# Wait for PostgreSQL to be ready
$ docker compose exec postgres pg_isready -U dgw_test

# Run tests
$ go test -v

# Stop PostgreSQL when done
$ docker compose down

Manual Setup

$ psql -d template1
> CREATE USER dgw_test;
> CREATE DATABASE  dgw_test OWNER dgw_test;
> \q
$ go test -v

About

dgw generates Golang struct, and simple Table/Row Data Gateway functions from PostgreSQL table metadata

Topics

Resources

Stars

190 stars

Watchers

23 watching

Forks

Releases

Used by

Contributors

Languages