README
¶
go-utils Database
The go-utils Database package provides handy methods to read and write data from and to an database. The database uses PostgreSQL as management system.
Installation
To use the log package you must import the package.
import "github.com/eliona-smart-building-assistant/go-utils/db"
You have to define an environment variable named CONNECTION_STRING which defines the database that should be connected to. It is also possible to define INIT_CONNECTION_STRING which is used to create database objects like schemas or tables. If INIT_CONNECTION_STRING is not defined the CONNECTION_STRING is used as default.
export CONNECTION_STRING=postgres://user:secret@localhost
export INIT_CONNECTION_STRING=postgres://user:secret@localhost
Usage
After installation, you can access the database.
import "github.com/eliona-smart-building-assistant/go-utils/db"
SQL statements have to apply the guidelines of PostgreSQL. Placeholders are defined as $1, $2 and so on.
Reading data from database
For example, you can read temperature objects based on an SQL statement. You have to open a new connection and read the result through a channel.
type Temperature struct {
Value int
Unit string
}
Note, that the fields of Temperature correspond to the selected values in the SQL statement. Here Value belongs to
the constant numeric value 23 and Unit to constant string Celsius.
connection := db.NewConnection()
defer connection.Close(context.Background())
temperatures := make(chan Temperature)
go db.Query(connection, "select 23, 'Celsius'", temperatures)
for temperature := range temperatures {
log.Debug("Temperature", "Temperature is: %d %s", temperature.Value, temperature.Unit)
}
To select single values only, you can use simple typed channels instead of structures.
connection := db.NewConnection()
defer connection.Close(context.Background())
values := make(chan int)
go db.Query(connection, "select 23", values)
for value := range values {
log.Debug("Temperature", "Value is: %d", value)
}
Modifying data in database
For example, you can write a temperature in a database table named temperatures.
connection := db.NewConnection()
defer connection.Close(context.Background())
_ = db.Exec(connection, "insert into temperatures (value, unit) values ($1, $2)",
23, "Celsius")
In the same way with db.Exec() you can update, delete and modifying data as you want.
Listen on database channels
You can listen for notifications on database channels. For example, a notification is triggered
for changes on a database table with temperatures, so the database channel is named temperatures.
The payload of the notification have to be a json string, e.g. {"Value": 30, "Unit": "Celsius"}.
In sum the notification is triggered as pg_notify('temperatures', '{"Value": 30, "Unit": "Celsius"}').
You can use a go channel to read this into a corresponding structure. For example notification we can use
the Temperature structure above. The notification payload will be mapped to this structure.
connection := db.NewConnection()
temperatures := make(chan Temperature)
go db.Listen(connection, "temperatures", temperatures)
for temperature := range temperatures {
log.Debug("Temperature", "Temperature is: %d %s", temperature.Value, temperature.Unit)
}
Documentation
¶
Index ¶
- func Begin(connection Connection) (pgx.Tx, error)
- func CloseDatabase()
- func ClosePool()
- func ConnectionConfig() *pgx.ConnConfig
- func ConnectionConfigWithApplicationName(applicationName string) *pgx.ConnConfig
- func ConnectionString() string
- func Database(applicationName string) *sql.DB
- func DatabaseName() string
- func EmptyFloatIsNull(float *float64) pgtype.Float8
- func EmptyIntIsNull(int *int32) pgtype.Int4
- func EmptyJsonIsNull[T any](any *T) pgtype.JSON
- func EmptyLongIntIsNull(int *int64) pgtype.Int8
- func EmptySmallIntIsNull(int *int16) pgtype.Int2
- func EmptyStringIsNull[T any](string *T) pgtype.Text
- func Exec(connection Connection, sql string, args ...interface{}) error
- func ExecFile(connection Connection, path string) error
- func FloatIsNull(float *float64, null float64) pgtype.Float8
- func GetConnectionConfig(conn *Connection) *pgx.ConnConfig
- func Hostname() string
- func InitConnectionConfig() *pgx.ConnConfig
- func InitConnectionConfigWithApplicationName(applicationName string) *pgx.ConnConfig
- func InitConnectionString() string
- func InitDatabaseName() string
- func InitHostname() string
- func InitPassword() string
- func InitPoolConfig() *pgxpool.Config
- func InitPort() int
- func InitUsername() string
- func IntIsNull(int *int32, null int32) pgtype.Int4
- func Listen[T any](conn *pgx.Conn, channel string, payloads chan T, errors chan error)
- func ListenRawWithContext(ctx context.Context, conn *pgx.Conn, channel string, payloads chan string, ...)
- func ListenWithContext[T any](ctx context.Context, conn *pgx.Conn, channel string, payloads chan T, ...)
- func LongIntIsNull(int *int64, null int64) pgtype.Int8
- func NewConnection() *pgx.Conn
- func NewConnectionWithContext(ctx context.Context) *pgx.Conn
- func NewConnectionWithContextAndApplicationName(ctx context.Context, applicationName string) *pgx.Conn
- func NewDatabase(applicationName string) *sql.DB
- func NewInitConnection() *pgx.Conn
- func NewInitConnectionWithContext(ctx context.Context) *pgx.Conn
- func NewInitConnectionWithContextAndApplicationName(ctx context.Context, applicationName string) *pgx.Conn
- func NewInitDatabase(applicationName string) *sql.DB
- func NewInitPool() *pgxpool.Pool
- func NewPool() *pgxpool.Pool
- func Password() string
- func Pool() *pgxpool.Pool
- func PoolConfig() *pgxpool.Config
- func Port() int
- func Query[T any](connection Connection, sql string, results chan T, args ...interface{}) error
- func QuerySingleRow[T any](connection Connection, sql string, args ...interface{}) (T, error)
- func SmallIntIsNull(int *int16, null int16) pgtype.Int2
- func StringIsNull[T any](s *T, null string) pgtype.Text
- func Username() string
- type Connection
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
func CloseDatabase ¶ added in v1.0.5
func CloseDatabase()
CloseDatabase closes the default database hold by this package.
func ConnectionConfig ¶
func ConnectionConfig() *pgx.ConnConfig
ConnectionConfig returns the connection config defined by CONNECTION_STRING environment variable.
func ConnectionConfigWithApplicationName ¶ added in v1.0.49
func ConnectionConfigWithApplicationName(applicationName string) *pgx.ConnConfig
func ConnectionString ¶
func ConnectionString() string
ConnectionString returns the connection string defined in the environment variable CONNECTION_STRING.
func Database ¶ added in v1.0.5
Database returns the configured database connection from CONNECTION_STRING. If once opened this method returns always the same database.
func DatabaseName ¶ added in v1.0.2
func DatabaseName() string
DatabaseName returns the defined database name configured in CONNECTION_STRING
func EmptyFloatIsNull ¶
func EmptyIntIsNull ¶
func EmptyJsonIsNull ¶
func EmptyLongIntIsNull ¶ added in v1.0.1
func EmptySmallIntIsNull ¶
func EmptyStringIsNull ¶
func Exec ¶
func Exec(connection Connection, sql string, args ...interface{}) error
Exec inserts a row using the given sql with arguments
func ExecFile ¶
func ExecFile(connection Connection, path string) error
func GetConnectionConfig ¶ added in v1.0.59
func GetConnectionConfig(conn *Connection) *pgx.ConnConfig
func Hostname ¶ added in v1.0.2
func Hostname() string
Hostname returns the defined hostname configured in CONNECTION_STRING
func InitConnectionConfig ¶ added in v1.0.57
func InitConnectionConfig() *pgx.ConnConfig
InitConnectionConfig returns the connection config defined by INIT_CONNECTION_STRING environment variable.
func InitConnectionConfigWithApplicationName ¶ added in v1.0.57
func InitConnectionConfigWithApplicationName(applicationName string) *pgx.ConnConfig
func InitConnectionString ¶ added in v1.0.57
func InitConnectionString() string
InitConnectionString returns the connection string for init defined in the environment variable INIT_CONNECTION_STRING. Default is value CONNECTION_STRING.
func InitDatabaseName ¶ added in v1.0.63
func InitDatabaseName() string
InitDatabaseName returns the defined database name configured in INIT_CONNECTION_STRING
func InitHostname ¶ added in v1.0.63
func InitHostname() string
func InitPassword ¶ added in v1.0.63
func InitPassword() string
InitPassword returns the defined password configured in INIT_CONNECTION_STRING
func InitPoolConfig ¶ added in v1.0.57
func InitPort ¶ added in v1.0.63
func InitPort() int
InitPort returns the defined port configured in INIT_CONNECTION_STRING
func InitUsername ¶ added in v1.0.63
func InitUsername() string
InitUsername returns the defined username configured in INIT_CONNECTION_STRING
func Listen ¶
Listen waits for notifications on database channel and writes the payload to the go channel. The type of the go channel have to correspond to the payload JSON structure
func ListenRawWithContext ¶ added in v1.0.38
func ListenWithContext ¶ added in v1.0.4
func ListenWithContext[T any](ctx context.Context, conn *pgx.Conn, channel string, payloads chan T, errors chan error)
ListenWithContext waits for notifications on database channel and writes the payload to the go channel. The type of the go channel have to correspond to the payload JSON structure
func NewConnection ¶
func NewConnection() *pgx.Conn
NewConnection returns a new connection defined by CONNECTION_STRING environment variable.
func NewConnectionWithContext ¶ added in v1.0.4
NewConnectionWithContext returns a new connection defined by CONNECTION_STRING environment variable.
func NewConnectionWithContextAndApplicationName ¶ added in v1.0.49
func NewDatabase ¶ added in v1.0.5
NewDatabase returns always a new database connection from CONNECTION_STRING.
func NewInitConnection ¶ added in v1.0.57
func NewInitConnection() *pgx.Conn
NewInitConnection returns a new connection defined by INIT_CONNECTION_STRING environment variable.
func NewInitConnectionWithContext ¶ added in v1.0.57
NewInitConnectionWithContext returns a new connection defined by INIT_CONNECTION_STRING environment variable.
func NewInitConnectionWithContextAndApplicationName ¶ added in v1.0.57
func NewInitDatabase ¶ added in v1.0.63
NewInitDatabase returns always a new database connection from CONNECTION_STRING.
func NewInitPool ¶ added in v1.0.57
func Password ¶ added in v1.0.2
func Password() string
Password returns the defined password configured in CONNECTION_STRING
func Pool ¶
Pool returns the default pool hold by this package. The pool is created if this function is called first time. Afterward this function returns always the same pool. Don't forget to defer the pool with ClosePool function.
func PoolConfig ¶
func Port ¶ added in v1.0.2
func Port() int
Port returns the defined port configured in CONNECTION_STRING
func Query ¶
func Query[T any](connection Connection, sql string, results chan T, args ...interface{}) error
Query gets values read from database into a channel. The value type of channel must match the fields defined in the query. The type can be a single value (e.g. string) if the query returns only a single field. Otherwise, the type have to be a struct with the identical number of elements and corresponding types like the query statement
func QuerySingleRow ¶
func QuerySingleRow[T any](connection Connection, sql string, args ...interface{}) (T, error)
QuerySingleRow returns the value if only a single row is queried
Types ¶
type Connection ¶
type Connection interface {
Exec(context.Context, string, ...interface{}) (pgconn.CommandTag, error)
Query(ctx context.Context, sql string, args ...interface{}) (pgx.Rows, error)
Begin(ctx context.Context) (pgx.Tx, error)
}
The Connection interface allows mocking database connection for testing
Source Files