pgsqlbuilder

README

pgsql-builder

This package generates Postgre SQL queries based on a struct instance. The concept is to define a struct, create a corresponding table to store its instances, and generate queries for managing the rows in that table, such as creating, updating, deleting, and selecting records.

⚠️ The project might be good for small prototyping and showcase of reflection package. It is not recommended to use it in production. Please use a more mature library instead.

The following queries can be generated:

• CREATE TABLE
• DROP TABLE
• INSERT
• UPDATE ... WHERE id = ...
• INSERT ... ON CONFLICT UPDATE ... (upsert)
• SELECT ... WHERE id = ...
• DELETE ... WHERE id = ...
• SELECT ... WHERE ...
• SELECT COUNT(*) WHERE ...
• DELETE ... WHERE ...
• DELETE ... WHERE ... RETURNING id
• UPDATE ... WHERE ...

How to use

TL;DR

Check the code in main_test.go file that contains tests for all use cases.

Defining a struct

Create a struct to define an object to be stored in a database table. In the example below, let's create a Product.

As of now, a field called ID is required. Also, the corresponding table column name that is generated for it is always prefixed with struct name. Hence, for ID field in Product that would be product_id.

There is another field called Flags that could be added, and it treated the same (meaning product_flags is generated, instead of flags).

type Product struct {
  ID int64
  Flags int64
  Name string
  Description string `sql:"type:varchar(2000)"` // "type" is used to force a specific string type for a column
  Code string `sql:"uniq"` // "uniq" tells the module to make this column uniq
  ProductionYear int
  CreatedByUserID int64
  LastModifiedByUserID int64
}

Field tags

In the above definition, a special tag sql is used to add specific configuration for the column when creating a table.

Tag key	Description
uniq	When passed, the column will get a UNIQUE constraint
type	Overwrites default VARCHAR(255) column type for string field. Possible values are: TEXT, BPCHAR(X), CHAR(X), VARCHAR(X), CHARACTER VARYING(X), CHARACTER(X) where X is the size. See PostgreSQL character types for more information.

A different than sql tag can be used by passing TagName in StructSQLOptions{} when calling NewStructSQL function (see below.)

Create a controller for the struct

To generate an SQL query based on a struct, a StructSQL object is used. One per struct.

import (
  sqlbuilder "miko.gs/pgsql-builder"
)

(...)

b := sqlbuilder.New(Product{}, sqlbuilder.Options{})

Options

There are certain options that can be provided to the New function which change the functionality of the object. See the table below.

Key	Type	Description
TableNamePrefix	string	Prefix for the table name, eg. myprefix_
StructName	string	Table name is created out of the struct name, eg. for MyProduct that would be my_product. It is possible to overwrite the struct name, and further table name.
TagName	string	Uses a different tag than sql. It is very useful when another module uses this module.

Get SQL queries

Use any of the following functions to get a desired SQL query.

Function
DropTable()
CreateTable()
Insert()
UpdateByID()
InsertOnConflictUpdate()
SelectByID()
DeleteByID()
Select(order []string, limit int, offset int, filters *Filters)
SelectCount(filters *Filters)
Delete(filters *Filters)
DeleteReturningID(filters *Filters)
Update(values map[string]interface{}, filters *Filters)

Get SQL queries with conditions

It is possible to generate queries such as SELECT, DELETE or UPDATE with conditions based on fields. In the following examples below, all the conditions (called "filters" in the code) are optional - there is no need to pass them.

SELECT

// SELECT * FROM products WHERE (created_by_user_id=$1 AND name=$2) OR (product_year > $3
// AND product_year > $4 AND last_modified_by_user_id IN ($5,$6,$7,$8))
// ORDER BY production_year ASC, name ASC
// LIMIT 100 OFFSET 10
sql := b.Select(
  []string{"ProductionYear", "asc", "Name", "asc"},
  100, 10, 
  &b.Filters{
    "CreatedByUserID": {Op: OpEqual, Val: 4},
    "Name":            {Op: OpEqual, Val: "Magic Sock"},
    Raw: {
      Op: OpOR, 
      Val: []interface{}{
        ".ProductYear > ? AND .ProductYear < ? AND .LastModifiedByUserID(?)",
        // The below values are not important, but the overall number of args must match question marks.
        0,
        0,
        []int{0,0,0,0}, // this list must contain the same number of items as values
      },
    },
  })

SELECT COUNT(*)

// SELECT COUNT(*) AS cnt FROM products WHERE (created_by_user_id=$1 AND name=$2) OR (product_year > $3
// AND product_year > $4 AND last_modified_by_user_id IN ($5,$6,$7,$8))
sql := b.SelectCount(
  &b.Filters{
    "CreatedByUserID": {Op: OpEqual, Val: 4},
    "Name":            {Op: OpEqual, Val: "Magic Sock"},
    Raw: {
      Op: OpOR,
      Val: []interface{}{
        ".ProductYear > ? AND .ProductYear < ? AND .LastModifiedByUserID(?)",
        // The below values are not important, but the overall number of args must match question marks.
        0,
        0,
        []int{0,0,0,0}, // this list must contain the same number of items as values
      },
    },
  })

DELETE

// DELETE FROM products WHERE (created_by_user_id=$1 AND name=$2) OR (product_year > $3
// AND product_year > $4 AND last_modified_by_user_id IN ($5,$6,$7,$8))
sql := b.Delete(
  &b.Filters{
    "CreatedByUserID": {Op: OpEqual, Val: 4},
    "Name": {Op: OpEqual, Val: "Magic Sock"},
    Raw: {
      Op: OpOR,
      Val: []interface{}{
        ".ProductYear > ? AND .ProductYear < ? AND .LastModifiedByUserID(?)",
        // The below values are not important, but the overall number of args must match question marks.
        0,
        0,
        []int
      }{0,0,0,0}, // this list must contain the same number of items as values
    },
  })

UPDATE

// UPDATE products SET production_year=$1, last_modified_by_user_id=$2
// WHERE name LIKE $3;
sql := b.Update(
  map[string]interface{
    "ProductionYear": 1984,
    "LastModifiedByUserID": 13
  },
  &b.Filters{
    Raw: {
      Op: OpAND,
      Val: []interface{} {
        ".Name LIKE ?",
		0,
      },
    },
  })

Documentation

Index

• Constants
• func IsStructField(u interface{}, field string) bool
• func MapInterfaces(mapObj map[string]interface{}) []interface{}
• func PrettifyCreateTable(sql string) string
• func SetStructFieldValueFromString(obj interface{}, name string, value string) (bool, interface{})
• func StructFieldValueFromString(obj interface{}, name string, value string) (bool, interface{})
• type Builder
  • func New(obj interface{}, options Options) *Builder
  • func (b *Builder) CreateTable() string
  • func (b *Builder) DatabaseColumnToFieldName(n string) string
  • func (b *Builder) Delete(filters *filters.Filters) (string, error)
  • func (b *Builder) DeleteByID() string
  • func (b *Builder) DeleteReturningID(filters *filters.Filters) (string, error)
  • func (b *Builder) DropTable() string
  • func (b *Builder) HasAliasedColumnNames() bool
  • func (b *Builder) HasModificationFields() bool
  • func (b *Builder) Insert() string
  • func (b *Builder) InsertOnConflictUpdate() string
  • func (b *Builder) PasswordFields() []string
  • func (b *Builder) Select(order []string, limit int, offset int, filters *filters.Filters) (string, error)
  • func (b *Builder) SelectByID() string
  • func (b *Builder) SelectCount(filters *filters.Filters) (string, error)
  • func (b *Builder) UniqueFields() []string
  • func (b *Builder) Update(values map[string]interface{}, filters *filters.Filters) (string, error)
  • func (b *Builder) UpdateByID() string
• type BuilderError
  • func (e *BuilderError) Error() string
• type Options

Constants

const (
	DefaultTagName = "sql"
)

Functions

func IsStructField

func IsStructField(u interface{}, field string) bool

IsStructField checks if a field exists in a struct.

func MapInterfaces

func MapInterfaces(mapObj map[string]interface{}) []interface{}

MapInterfaces returns a slice of interfaces from a map.

func PrettifyCreateTable

func PrettifyCreateTable(sql string) string

PrettifyCreateTable prettifies SQL query to make it more human-readable.

func SetStructFieldValueFromString

func SetStructFieldValueFromString(obj interface{}, name string, value string) (bool, interface{})

SetStructFieldValueFromString sets a field from value that is string (converts it if necessary).

func StructFieldValueFromString

func StructFieldValueFromString(obj interface{}, name string, value string) (bool, interface{})

StructFieldValueFromString takes a field value as string and converts it (if possible) to a value type of that field.

Types

type Builder

type Builder struct {
	// contains filtered or unexported fields
}

Builder reflects the object to generate and cache PostgreSQL queries (CREATE TABLE, INSERT, UPDATE, etc.). Database table and column names are lowercase with an underscore, and they are generated from field names.

func New

func New(obj interface{}, options Options) *Builder

New takes a struct and returns a Builder instance.

func (*Builder) CreateTable

func (b *Builder) CreateTable() string

CreateTable returns an SQL query for creating the table.

func (*Builder) DatabaseColumnToFieldName

func (b *Builder) DatabaseColumnToFieldName(n string) string

DatabaseColumnToFieldName takes a database column and converts it to a struct field name.

func (*Builder) Delete

func (b *Builder) Delete(filters *filters.Filters) (string, error)

Delete returns a DELETE query with a WHERE condition built from 'filters' (field-value pairs). Struct fields in the 'filters' argument are sorted alphabetically. Hence, when used with a database connection, their values (or pointers to it) must be sorted as well.

func (*Builder) DeleteByID

func (b *Builder) DeleteByID() string

DeleteByID returns an SQL query for deleting an object by its ID.

func (*Builder) DeleteReturningID

func (b *Builder) DeleteReturningID(filters *filters.Filters) (string, error)

DeleteReturningID returns a DELETE query with a WHERE condition built from 'filters' (field-value pairs) with RETURNING id. Struct fields in the 'filters' argument are sorted alphabetically. Hence, when used with a database connection, their values (or pointers to it) must be sorted as well.

func (*Builder) DropTable

func (b *Builder) DropTable() string

DropTable returns an SQL query for dropping the table.

func (*Builder) HasAliasedColumnNames

func (b *Builder) HasAliasedColumnNames() bool

HasAliasedColumnNames returns true if any of the fields have column names in the format of "alias.column_name".

func (*Builder) HasModificationFields

func (b *Builder) HasModificationFields() bool

HasModificationFields returns true if all the following int64 fields are present: CreatedAt, CreatedBy, ModifiedAt, ModifiedBy.

func (*Builder) Insert

func (b *Builder) Insert() string

Insert returns an SQL query for inserting a new object to the table.

func (*Builder) InsertOnConflictUpdate

func (b *Builder) InsertOnConflictUpdate() string

InsertOnConflictUpdate returns an SQL query for inserting when a conflict is detected.

func (*Builder) PasswordFields

func (b *Builder) PasswordFields() []string

PasswordFields returns a list with field names that are passwords.

func (*Builder) Select

func (b *Builder) Select(order []string, limit int, offset int, filters *filters.Filters) (string, error)

Select returns a SELECT query with a WHERE condition built from 'filters' (field-value pairs). Struct fields in the 'filters' argument are sorted alphabetically. Hence, when used with a database connection, their values (or pointers to it) must be sorted as well. Columns in the SELECT query are ordered the same way as they are defined in the struct: SELECT field1_column, field2_column, ... etc.

func (*Builder) SelectByID

func (b *Builder) SelectByID() string

SelectByID returns an SQL query for selecting an object by its ID.

func (*Builder) SelectCount

func (b *Builder) SelectCount(filters *filters.Filters) (string, error)

SelectCount returns a SELECT COUNT(*) query to count rows with a WHERE condition built from 'filters' (field-value pairs). Struct fields in the 'filters' argument are sorted alphabetically. Hence, when used with a database connection, their values (or pointers to it) must be sorted as well.

func (*Builder) UniqueFields

func (b *Builder) UniqueFields() []string

UniqueFields returns a list with field names that are unique.

func (*Builder) Update

func (b *Builder) Update(values map[string]interface{}, filters *filters.Filters) (string, error)

Update returns an UPDATE query where specified struct fields (columns) are updated and rows match specific WHERE condition built from 'filters' (field-value pairs). Struct fields in 'values' and the 'filters' arguments are sorted alphabetically. Hence, when used with a database connection, their values (or pointers to it) must be sorted as well.

func (*Builder) UpdateByID

func (b *Builder) UpdateByID() string

UpdateByID returns an SQL query for updating an object by their ID.

type BuilderError

type BuilderError struct {
	Op  string
	Tag string
	Err error
}

func (*BuilderError) Error

func (e *BuilderError) Error() string

type Options

type Options struct {
	TableNamePrefix string
	StructName      string
	TagName         string
}

Options are passed to Builder to change default values.