structsqlpostgres

README

struct-sql-postgres

This package generates PostgreSQL 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 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 ...
• DELETE ... WHERE ...
• 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 an 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 `2sql:"db_type:varchar(2000)"` // "db_type" is used to force a specific string type for a column
  Code string `2sql:"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 2sql 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
db_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 2sql 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 (
  stsql "github.com/go-phings/struct-sql-postgres"
)

(...)

s := stsql.NewStructSQL(Product{}, stsql.StructSQLOptions{})

StructSQLOptions

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

Key	Type	Description
DatabaseTablePrefix	string	Prefix for the table name, eg. myprefix_
ForceName	string	Table name is created out of the struct name, eg. for MyProduct that would be my_products. It is possible to overwrite the struct name, and further table name. Hence, when ForceName is AnotherStruct then table name that will be used becomes another_structs.
TagName	string	Uses a different tag than 2sql. It is very useful when another module uses this module.
Base	*StructSQL	In some cases, we'd like to use already existing instance of this object as a base, instead of parsing the struct again. For example, tags are already defined in another struct and should be re-used. This is often used by other modules.
Joined	map[string]*StructSQL	When struct is used to describe a SELECT query with INNER JOIN to another structs (tables), this map can be used to overwrite StructSQL objects for children structs. If not passed, then children structs that are meant to be used with INNER JOIN will be created using NewStructSQL. See one of below sections on joined select queries for more details.
UseRootNameWhenJoinedPresent	bool	When struct is used to describe a SELECT query with INNER JOIN to another structs (tables) and the parent struct has a name like Product_WithDetails then it's so-called root name is Product, and that will be used as a base for the table name (so it'll be products).

Get SQL queries

Use any of the following GetQuery* commands to get a desired SQL query. See examples below.

drop := s.GetQueryDropTable() // retursn 'DROP TABLE IF EXISTS products'

create := s.GetQueryCreateTable() // returns 'CREATE TABLE products (...)'

updateById := s.GetQueryUpdateById() // returns 'UPDATE products SET product_flags = $1, name = $2 ... WHERE product_id = $8

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 condition (called "filters" in the code) are optional - there is no need to pass them.

The _raw (and _rawConjuction) is a special filter that allows passing a raw query.

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
sqlSelect := s.GetQuerySelect(
  []string{"ProductionYear", "asc", "Name", "asc"},
  100, 10, 
  map[string]interface{
    "CreatedByUserID": 4,
    "Name": "Magic Sock",
    "_raw": []interface{}{
      ".ProductYear > ? AND .ProductYear < ? AND .LastModifiedByUserID(?)",
      // Below values are not important but the overall number of args match question marks
      0,
      0,
      []int{0,0,0,0}, // this list must contain same number of items as values
    },
    "_rawConjuction": stsql.RawConjuctionOR,
  }, nil, nil)

SELECT COUNT(*)

// Use GetQuerySelectCount without th first 3 arguments to get SELECT COUNT(*)

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))
sqlDelete := s.GetQuerySelect(
  map[string]interface{
    "CreatedByUserID": 4,
    "Name": "Magic Sock",
    "_raw": []interface{}{
      ".ProductYear > ? AND .ProductYear < ? AND .LastModifiedByUserID(?)",
      // Below values are not important but the overall number of args match question marks
      0,
      0,
      []int{0,0,0,0}, // this list must contain same number of items as values
    },
    "_rawConjuction": stsql.RawConjuctionOR,
  }, nil, nil)

UPDATE

// UPDATE products SET production_year=$1, last_modified_by_user_id=$2
// WHERE name LIKE $3;
sqlUpdate := s.GetQueryUpdate(
  map[string]interface{
    "ProductionYear": 1984,
    "LastModifiedByUserID": 13
  },
  map[string]interface{}{
    "_raw": ".Name LIKE ?",
    0, // One question mark, hence one additional value
  }, nil, nil)

Get SELECT query with INNER JOIN

With struct-sql-postgres it is possible to build a query that would select data from multiple tables joined with INNER JOIN.

Creating a struct with joined struct

Suppose we need a query to select products with information on users that recently created and modified them.

type Users struct {
  ID int64
  FirstName string
  LastName string
}

type Product_WithDetails struct {
  ID int64
  Flags int64
  Name string
  Description string `2sql:"db_type:varchar(2000)"` // "db_type" is used to force a specific string type for a column
  Code string `2sql:"uniq"` // "uniq" tells the module to make this column uniq
  ProductionYear int
  CreatedByUserID int64
  LastModifiedByUserID int64
  CreatedByUser *User `2sql:"join"`
  CreatedByUser_FirstName string
  CreatedByUser_LastName string
  LastModifiedByUser *User `2sql:"join"`
  LastModifiedByUser_FirstName string
  LastModifiedByUser_LastName string
}

Getting SELECT query for joined structs

An example query such as:

SELECT t1.product_id,t1.product_flags,t1.name,t1.description,t1.code,t1.production_year,
t1.created_by_user_id,t1.last_modified_by_user_id,t2.first_name,t2.last_name,t3.first_name,t3.last_name
FROM products t1 INNER JOIN users t2 ON t1.created_by_user_id=t2.user_id
INNER JOIN users t3 ON t1.last_modified_by_user_id=t3.user_id
WHERE (t1.production_year=$1) AND (t2.first_name=$2 AND t3.first_name=$2)
ORDER BY t2.first_name ASC,t1.name DESC LIMIT 100 OFFSET 10

can be generated with the following code:

got = h.GetQuerySelect([]string{"CreatedByUser_FirstName", "asc", "Name", "desc"}, 100, 10, map[string]interface{}{
  "ProductionYear": 1984,
  "_raw": []interface{}{
    ".CreatedByUser_FirstName=? AND .LastModifiedByUser_FirstName=?",
    // We do not really care about the values, the query contains $x only symbols
    // However, we need to pass either value or an array so that an array can be extracted into multiple $x's
    0,
    0,
  },
  "_rawConjuction": RawConjuctionAND,
}, nil, nil)

Documentation

Index

• Constants
• func GetStructFieldNames(u interface{}) []string
• func GetStructName(u interface{}) string
• func GetStructNamesFromConstructors(objFuncs ...func() interface{}) []string
• func IsFieldKindSupported(k reflect.Kind) bool
• func IsStructField(u interface{}, field string) bool
• type ErrStructSQL
  • func (e ErrStructSQL) Error() string
  • func (e ErrStructSQL) Unwrap() error
• type StructSQL
  • func NewStructSQL(obj interface{}, options StructSQLOptions) *StructSQL
  • func (h *StructSQL) Err() error
  • func (h *StructSQL) GetFieldNameFromDBCol(n string) string
  • func (h *StructSQL) GetFlags() int
  • func (h StructSQL) GetQueryCreateTable() string
  • func (h *StructSQL) GetQueryDelete(filters map[string]interface{}, filterFieldsToInclude map[string]bool) string
  • func (h *StructSQL) GetQueryDeleteById() string
  • func (h *StructSQL) GetQueryDeleteReturningID(filters map[string]interface{}, filterFieldsToInclude map[string]bool) string
  • func (h StructSQL) GetQueryDropTable() string
  • func (h *StructSQL) GetQueryInsert() string
  • func (h *StructSQL) GetQueryInsertOnConflictUpdate() string
  • func (h *StructSQL) GetQuerySelect(order []string, limit int, offset int, filters map[string]interface{}, ...) string
  • func (h *StructSQL) GetQuerySelectById() string
  • func (h *StructSQL) GetQuerySelectCount(filters map[string]interface{}, filterFieldsToInclude map[string]bool) string
  • func (h *StructSQL) GetQueryUpdate(values map[string]interface{}, filters map[string]interface{}, ...) string
  • func (h *StructSQL) GetQueryUpdateById() string
  • func (h *StructSQL) HasModificationFields() bool
• type StructSQLOptions

Constants

const RawConjuctionAND = 2

const RawConjuctionOR = 1

const VERSION = "0.7.0"

const ValueBit = 9

const ValueEqual = 1

const ValueGreater = 5

const ValueGreaterOrEqual = 7

const ValueLike = 3

const ValueLower = 6

const ValueLowerOrEqual = 8

const ValueMatch = 4

const ValueNotEqual = 2

Functions

func GetStructFieldNames

func GetStructFieldNames(u interface{}) []string

GetStructFieldNames returns list of names of fields of a struct instance, which are supported in generating SQL query.

func GetStructName

func GetStructName(u interface{}) string

GetStructName returns struct name of a struct instance

func GetStructNamesFromConstructors

func GetStructNamesFromConstructors(objFuncs ...func() interface{}) []string

GetStructNamesFromConstructors returns a list of struct names from a list of constructors (functions that return struct instances)

func IsFieldKindSupported

func IsFieldKindSupported(k reflect.Kind) bool

IsFieldKindSupported checks if a field kind is supported by this module

func IsStructField

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

IsStructField checks if a specific string is a name of a field

Types

type ErrStructSQL

type ErrStructSQL struct {
	Op  string
	Tag string
	Err error
}

ErrStructSQL wraps original error with operation/step where the error occurred and optionally with a tag when parsing "crud" failed

func (ErrStructSQL) Error

func (e ErrStructSQL) Error() string

func (ErrStructSQL) Unwrap

func (e ErrStructSQL) Unwrap() error

type StructSQL

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

StructSQL reflects the object to generate and cache PostgreSQL queries (CREATE TABLE, INSERT, UPDATE etc.). Database table and column names are lowercase with underscore and they are generated from field names. StructSQL is created within Controller and there is no need to instantiate it

func NewStructSQL

func NewStructSQL(obj interface{}, options StructSQLOptions) *StructSQL

NewStructSQL takes object and database table name prefix as arguments and returns StructSQL instance.

func (*StructSQL) Err

func (h *StructSQL) Err() error

Err returns error that occurred when reflecting struct

func (*StructSQL) GetFieldNameFromDBCol

func (h *StructSQL) GetFieldNameFromDBCol(n string) string

GetFieldNameFromDBCol returns field name from a table column.

func (*StructSQL) GetFlags

func (h *StructSQL) GetFlags() int

GetFlags returns StructSQL flags.

func (StructSQL) GetQueryCreateTable

func (h StructSQL) GetQueryCreateTable() string

GetQueryCreateTable return a CREATE TABLE query. Columns in the query are ordered the same way as they are defined in the struct, eg. SELECT field1_column, field2_column, ... etc.

func (*StructSQL) GetQueryDelete

func (h *StructSQL) GetQueryDelete(filters map[string]interface{}, filterFieldsToInclude map[string]bool) string

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

func (*StructSQL) GetQueryDeleteById

func (h *StructSQL) GetQueryDeleteById() string

GetQueryDeleteById returns a DELETE query with WHERE condition on ID field.

func (*StructSQL) GetQueryDeleteReturningID

func (h *StructSQL) GetQueryDeleteReturningID(filters map[string]interface{}, filterFieldsToInclude map[string]bool) string

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

func (StructSQL) GetQueryDropTable

func (h StructSQL) GetQueryDropTable() string

GetQueryDropTable returns a DROP TABLE query.

func (*StructSQL) GetQueryInsert

func (h *StructSQL) GetQueryInsert() string

GetQueryInsert returns an INSERT query. Columns in the INSERT query are ordered the same way as they are defined in the struct, eg. SELECT field1_column, field2_column, ... etc.

func (*StructSQL) GetQueryInsertOnConflictUpdate

func (h *StructSQL) GetQueryInsertOnConflictUpdate() string

GetQueryInsertOnConflictUpdate returns an "upsert" query, which will INSERT data when it does not exist or UPDATE it otherwise. Columns in the query are ordered the same way as they are defined in the struct, eg. SELECT field1_column, field2_column, ... etc.

func (*StructSQL) GetQuerySelect

func (h *StructSQL) GetQuerySelect(order []string, limit int, offset int, filters map[string]interface{}, orderFieldsToInclude map[string]bool, filterFieldsToInclude map[string]bool) string

GetQuerySelect returns a SELECT query with WHERE condition built from 'filters' (field-value pairs). Struct fields in 'filters' argument are sorted alphabetically. Hence, when used with 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, eg. SELECT field1_column, field2_column, ... etc.

func (*StructSQL) GetQuerySelectById

func (h *StructSQL) GetQuerySelectById() string

GetQuerySelectById returns a SELECT query with WHERE condition on ID field. Columns in the SELECT query are ordered the same way as they are defined in the struct, eg. SELECT field1_column, field2_column, ... etc.

func (*StructSQL) GetQuerySelectCount

func (h *StructSQL) GetQuerySelectCount(filters map[string]interface{}, filterFieldsToInclude map[string]bool) string

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

func (*StructSQL) GetQueryUpdate

func (h *StructSQL) GetQueryUpdate(values map[string]interface{}, filters map[string]interface{}, valueFieldsToInclude map[string]bool, filterFieldsToInclude map[string]bool) string

GetQueryUpdate 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 'filters' arguments, are sorted alphabetically. Hence, when used with database connection, their values (or pointers to it) must be sorted as well.

func (*StructSQL) GetQueryUpdateById

func (h *StructSQL) GetQueryUpdateById() string

GetQueryUpdateById returns an UPDATE query with WHERE condition on ID field. Columns in the UPDATE query are ordered the same way as they are defined in the struct, eg. SELECT field1_column, field2_column, ... etc.

func (*StructSQL) HasModificationFields

func (h *StructSQL) HasModificationFields() bool

HasModificationFields returns true if struct has all of the following int64 fields: CreatedAt, CreatedBy, LastModifiedAt, LastModifiedBy

type StructSQLOptions

type StructSQLOptions struct {
	DatabaseTablePrefix string
	ForceName           string
	TagName             string
	Joined              map[string]*StructSQL
	// In some cases, we might want to copy over tags from already existing StructSQL instance. Such is called Base in here.
	Base *StructSQL
	// When struct has a name like 'xx_yy' and it has joined structs, use 'xx' as a name for table and column names
	UseRootNameWhenJoinedPresent bool
}