Open platform to collect and prioritize feedback
package dbx
import (
"context"
"database/sql"
"os"
"reflect"
"time"
"strings"
"github.com/getfider/fider/app"
"github.com/getfider/fider/app/models/dto"
"github.com/getfider/fider/app/pkg/env"
"github.com/getfider/fider/app/pkg/errors"
"github.com/getfider/fider/app/pkg/log"
"github.com/lib/pq"
)
var conn *sql.DB
var rowMapper *RowMapper
func init() {
var err error
conn, err = sql.Open("postgres", env.Config.Database.URL)
if err != nil {
panic(wrap(err, "failed to open connection to the database"))
}
conn.SetMaxIdleConns(env.Config.Database.MaxIdleConns)
conn.SetMaxOpenConns(env.Config.Database.MaxOpenConns)
rowMapper = NewRowMapper()
}
func Connection() *sql.DB {
return conn
}
// Ping checks if current database connection is healthy
func Ping() error {
_, err := conn.Exec("SELECT 1")
return err
}
// BeginTx returns a new SQL transaction
func BeginTx(ctx context.Context) (*Trx, error) {
tx, err := conn.BeginTx(ctx, nil)
if err != nil {
return nil, wrap(err, "failed to start new transaction")
}
return &Trx{tx: tx, ctx: ctx}, nil
}
func load(path string) {
content, err := os.ReadFile(env.Path(path))
if err != nil {
panic(wrap(err, "failed to read file %s", path))
}
_, err = conn.Exec(string(content))
if err != nil {
panic(wrap(err, "failed to execute %s", path))
}
}
// Seed clean and insert new seed data for testing
func Seed() {
if env.IsTest() {
load("/app/pkg/dbx/setup.sql")
}
}
// Trx represents a Database transaction
type Trx struct {
tx *sql.Tx
ctx context.Context
}
var formatter = strings.NewReplacer("\t", "", "\n", " ")
// Execute given SQL command
func (trx *Trx) Execute(command string, args ...any) (int64, error) {
if log.IsEnabled(log.DEBUG) {
command = formatter.Replace(command)
start := time.Now()
defer func() {
log.Debugf(trx.ctx, "@{Command:yellow} @{Args:blue} executed in @{ElapsedMs:magenta}ms", dto.Props{
"Command": command,
"Args": args,
"ElapsedMs": time.Since(start).Nanoseconds() / int64(time.Millisecond),
})
}()
}
result, err := trx.tx.ExecContext(trx.ctx, command, args...)
if err != nil {
return 0, wrap(err, "failed to execute trx.Execute")
}
rows, _ := result.RowsAffected()
return rows, nil
}
// Scalar returns first row and first column
func (trx *Trx) Scalar(data any, command string, args ...any) error {
if log.IsEnabled(log.DEBUG) {
command = formatter.Replace(command)
start := time.Now()
defer func() {
log.Debugf(trx.ctx, "@{Command:yellow} @{Args:blue} executed in @{ElapsedMs:magenta}ms", dto.Props{
"Command": command,
"Args": args,
"ElapsedMs": time.Since(start).Nanoseconds() / int64(time.Millisecond),
})
}()
}
row := trx.tx.QueryRowContext(trx.ctx, command, args...)
err := row.Scan(data)
if err != nil {
if err == sql.ErrNoRows {
return app.ErrNotFound
}
return wrap(err, "failed to execute trx.Scalar")
}
return nil
}
// Get first row and bind to given data
func (trx *Trx) Get(data any, command string, args ...any) error {
if log.IsEnabled(log.DEBUG) {
command = formatter.Replace(command)
start := time.Now()
defer func() {
log.Debugf(trx.ctx, "@{Command:yellow} @{Args:blue} executed in @{ElapsedMs:magenta}ms", dto.Props{
"Command": command,
"Args": args,
"ElapsedMs": time.Since(start).Nanoseconds() / int64(time.Millisecond),
})
}()
}
rows, err := trx.tx.QueryContext(trx.ctx, command, args...)
if err != nil {
return wrap(err, "failed to execute trx.Get")
}
defer func() { _ = rows.Close() }()
if rows.Next() {
columns, _ := rows.Columns()
err := rowMapper.Map(data, columns, rows.Scan)
if err != nil {
return wrap(err, "failed to map result to model")
}
return nil
}
return app.ErrNotFound
}
// Exists returns true if at least one record is found
func (trx *Trx) Exists(command string, args ...any) (bool, error) {
if log.IsEnabled(log.DEBUG) {
command = formatter.Replace(command)
start := time.Now()
defer func() {
log.Debugf(trx.ctx, "@{Command:yellow} @{Args:blue} executed in @{ElapsedMs:magenta}ms", dto.Props{
"Command": command,
"Args": args,
"ElapsedMs": time.Since(start).Nanoseconds() / int64(time.Millisecond),
})
}()
}
rows, err := trx.tx.QueryContext(trx.ctx, command, args...)
if err != nil {
return false, wrap(err, "failed to execute trx.Exists")
}
defer func() { _ = rows.Close() }()
return rows.Next(), nil
}
// Count returns number of rows
func (trx *Trx) Count(command string, args ...any) (int, error) {
if log.IsEnabled(log.DEBUG) {
command = formatter.Replace(command)
start := time.Now()
defer func() {
log.Debugf(trx.ctx, "@{Command:yellow} @{Args:blue} executed in @{ElapsedMs:magenta}ms", dto.Props{
"Command": command,
"Args": args,
"ElapsedMs": time.Since(start).Nanoseconds() / int64(time.Millisecond),
})
}()
}
rows, err := trx.tx.QueryContext(trx.ctx, command, args...)
if err != nil {
return 0, wrap(err, "failed to execute trx.Count")
}
defer func() { _ = rows.Close() }()
count := 0
for rows.Next() {
count++
}
return count, nil
}
// Select all matched rows bind to given data
func (trx *Trx) Select(data any, command string, args ...any) error {
if log.IsEnabled(log.DEBUG) {
command = formatter.Replace(command)
start := time.Now()
defer func() {
log.Debugf(trx.ctx, "@{Command:yellow} @{Args:blue} executed in @{ElapsedMs:magenta}ms", dto.Props{
"Command": command,
"Args": args,
"ElapsedMs": time.Since(start).Nanoseconds() / int64(time.Millisecond),
})
}()
}
rows, err := trx.tx.QueryContext(trx.ctx, command, args...)
if err != nil {
return wrap(err, "failed to execute trx.Select")
}
defer func() { _ = rows.Close() }()
sliceType := reflect.TypeOf(data).Elem()
items := reflect.New(sliceType).Elem()
itemType := sliceType.Elem().Elem()
var columns []string
for rows.Next() {
if columns == nil {
columns, _ = rows.Columns()
}
item := reflect.New(itemType)
if err = rowMapper.Map(item.Interface(), columns, rows.Scan); err != nil {
return wrap(err, "failed to map result to model")
}
items = reflect.Append(items, item)
}
if items.Len() > 0 {
reflect.Indirect(reflect.ValueOf(data)).Set(items)
}
return nil
}
// Query all matched rows and return raw sql.Rows
func (trx *Trx) Query(command string, args ...any) (*sql.Rows, error) {
if log.IsEnabled(log.DEBUG) {
command = formatter.Replace(command)
start := time.Now()
defer func() {
log.Debugf(trx.ctx, "@{Command:yellow} @{Args:blue} executed in @{ElapsedMs:magenta}ms", dto.Props{
"Command": command,
"Args": args,
"ElapsedMs": time.Since(start).Nanoseconds() / int64(time.Millisecond),
})
}()
}
rows, err := trx.tx.QueryContext(trx.ctx, command, args...)
if err != nil {
return nil, wrap(err, "failed to execute trx.Select")
}
return rows, nil
}
// Commit current transaction
func (trx *Trx) Commit() error {
err := trx.tx.Commit()
if err != nil && err != sql.ErrTxDone {
return wrap(err, "failed to commit transaction")
}
return nil
}
// MustCommit current transaction
func (trx *Trx) MustCommit() {
err := trx.Commit()
if err != nil {
panic(err)
}
}
// Rollback current transaction
func (trx *Trx) Rollback() error {
if trx.tx == nil {
return nil
}
err := trx.tx.Rollback()
if err != nil && err != sql.ErrTxDone {
return wrap(err, "failed to rollback transaction")
}
return nil
}
// MustRollback current transaction
func (trx *Trx) MustRollback() {
err := trx.Rollback()
if err != nil {
panic(err)
}
}
func wrap(err error, format string, a ...any) error {
if pqErr, ok := err.(*pq.Error); ok {
if pqErr.Code == "57014" { //query canceled
return errors.Wrap(context.Canceled, format, a...)
}
}
return errors.Wrap(err, format, a...)
}