db

Documentation

Overview

Package db defines database related APIs.

Index

• Constants
• Variables
• func NewPool(cfg *Config) (pool *sqlx.DB, cleanup func(), err error)
• type Config
• type DO
• type KV
• type MockRepo
  • func NewMockRepo[DO any](ctrl *gomock.Controller) *MockRepo[DO]
  • func (m *MockRepo[DO]) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sqlx.Tx, error)
  • func (m *MockRepo[DO]) Delete(ctx context.Context, d *DO) error
  • func (m *MockRepo[DO]) EXPECT() *MockRepoMockRecorder[DO]
  • func (m *MockRepo[DO]) Insert(ctx context.Context, d *DO) error
  • func (m *MockRepo[DO]) QueryByID(ctx context.Context, id int64) (*DO, error)
  • func (m *MockRepo[DO]) Update(ctx context.Context, d *DO) error
• type MockRepoMockRecorder
  • func (mr *MockRepoMockRecorder[DO]) BeginTx(ctx, opts any) *gomock.Call
  • func (mr *MockRepoMockRecorder[DO]) Delete(ctx, d any) *gomock.Call
  • func (mr *MockRepoMockRecorder[DO]) Insert(ctx, d any) *gomock.Call
  • func (mr *MockRepoMockRecorder[DO]) QueryByID(ctx, id any) *gomock.Call
  • func (mr *MockRepoMockRecorder[DO]) Update(ctx, d any) *gomock.Call
• type Repo
• type StmtBuilder
  • func NewStmtBuilder(tbl string, dri string) StmtBuilder

Constants

const Placeholder = "?"

Placeholder is the placeholder of an argument that can be used in StmtBuilder.

Variables

var DOCols = []string{
	"id",
	"create_time",
	"update_time",
	"ext",
}

DOCols contains the column names of DO.

Functions

func NewPool

func NewPool(cfg *Config) (pool *sqlx.DB, cleanup func(), err error)

NewPool initializes a new database connection pool.

Types

type Config

type Config struct {
	Driver string `json:"driver,omitempty" yaml:"driver" toml:"driver" xml:"driver"`
	DSN    string `json:"dsn,omitempty" yaml:"dsn" toml:"dsn" xml:"dsn"`
}

Config is the config model for initializing a new database pool. The driver and DSN (Data Source Name) can be found in your SQL driver documentation, for example github.com/go-sql-driver/mysql.

type DO

type DO struct {
	// ID is the primary key.
	ID int64 `db:"id"`

	// CreateTime is the create time of a record.
	CreateTime time.Time `db:"create_time"`

	// UpdateTime is the update time of a record.
	UpdateTime time.Time `db:"update_time"`

	// Ext is the extension field.
	// If the table structure is hard to modify after running for a period of time and you want to add a new column, you
	// can use ext as a temporary alternative.
	//
	// NOTE: The ext field should be of type json in the database.
	Ext string `db:"ext"`
}

DO defines a common data object. You should embed this struct in your own data object.

type KV

type KV struct {
	Key string
	Val string
}

KV is the key-value pair that can be used in StmtBuilder.

type MockRepo

type MockRepo[DO any] struct {
	// contains filtered or unexported fields
}

MockRepo is a mock of Repo interface.

func NewMockRepo

func NewMockRepo[DO any](ctrl *gomock.Controller) *MockRepo[DO]

NewMockRepo creates a new mock instance.

func (*MockRepo[DO]) BeginTx

func (m *MockRepo[DO]) BeginTx(ctx context.Context, opts *sql.TxOptions) (*sqlx.Tx, error)

BeginTx mocks base method.

func (*MockRepo[DO]) Delete

func (m *MockRepo[DO]) Delete(ctx context.Context, d *DO) error

Delete mocks base method.

func (*MockRepo[DO]) EXPECT

func (m *MockRepo[DO]) EXPECT() *MockRepoMockRecorder[DO]

EXPECT returns an object that allows the caller to indicate expected use.

func (*MockRepo[DO]) Insert

func (m *MockRepo[DO]) Insert(ctx context.Context, d *DO) error

Insert mocks base method.

func (*MockRepo[DO]) QueryByID

func (m *MockRepo[DO]) QueryByID(ctx context.Context, id int64) (*DO, error)

QueryByID mocks base method.

func (*MockRepo[DO]) Update

func (m *MockRepo[DO]) Update(ctx context.Context, d *DO) error

Update mocks base method.

type MockRepoMockRecorder

type MockRepoMockRecorder[DO any] struct {
	// contains filtered or unexported fields
}

MockRepoMockRecorder is the mock recorder for MockRepo.

func (*MockRepoMockRecorder[DO]) BeginTx

func (mr *MockRepoMockRecorder[DO]) BeginTx(ctx, opts any) *gomock.Call

BeginTx indicates an expected call of BeginTx.

func (*MockRepoMockRecorder[DO]) Delete

func (mr *MockRepoMockRecorder[DO]) Delete(ctx, d any) *gomock.Call

Delete indicates an expected call of Delete.

func (*MockRepoMockRecorder[DO]) Insert

func (mr *MockRepoMockRecorder[DO]) Insert(ctx, d any) *gomock.Call

Insert indicates an expected call of Insert.

func (*MockRepoMockRecorder[DO]) QueryByID

func (mr *MockRepoMockRecorder[DO]) QueryByID(ctx, id any) *gomock.Call

QueryByID indicates an expected call of QueryByID.

func (*MockRepoMockRecorder[DO]) Update

func (mr *MockRepoMockRecorder[DO]) Update(ctx, d any) *gomock.Call

Update indicates an expected call of Update.

type Repo

type Repo[DO any] interface {
	// Insert inserts a record and updates the ID field of the given data object based on returned ID.
	Insert(ctx context.Context, d *DO) error

	// QueryByID queries record by ID.
	// If no record is found, return [sql.ErrNoRows], otherwise it will return an error that may occur during execution.
	QueryByID(ctx context.Context, id int64) (*DO, error)

	// Update updates a record.
	Update(ctx context.Context, d *DO) error

	// Delete deletes a record.
	Delete(ctx context.Context, d *DO) error

	// BeginTx begins a transaction.
	BeginTx(ctx context.Context, opts *sql.TxOptions) (*sqlx.Tx, error)
}

Repo defines a interface for common database operations, where DO is the struct of data object.

type StmtBuilder

type StmtBuilder interface {
	// GetTbl returns the table name used in this builder.
	GetTbl() string

	// GetDri returns the driver name used in this builder.
	GetDri() string

	// BuildMappedInsertStmt builds mapped insert statement.
	// If the given cols is empty, an empty string will be returned.
	BuildMappedInsertStmt(cols []KV) string

	// BuildMappedQueryStmt builds mapped query statement.
	// If the given selectedCols is empty, ["*"] will be used.
	BuildMappedQueryStmt(selectedCols []string, conds []KV) string

	// BuildMappedUpdateStmt builds mapped update statement.
	// If the given cols is empty, an empty string will be returned.
	BuildMappedUpdateStmt(cols, conds []KV) string

	// BuildMappedDeleteStmt builds mapped delete statement.
	BuildMappedDeleteStmt(conds []KV) string

	// BuildNamedInsertStmt builds named insert statement.
	// If the given cols is empty, an empty string will be returned.
	BuildNamedInsertStmt(cols []string) string

	// BuildNamedQueryStmt builds named query statement.
	// If the given selectedCols is empty, ["*"] will be used.
	BuildNamedQueryStmt(selectedCols, conds []string) string

	// BuildNamedUpdateStmt builds named update statement.
	// If the given cols is empty, an empty string will be returned.
	BuildNamedUpdateStmt(cols, conds []string) string

	// BuildNamedDeleteStmt builds named delete statement.
	BuildNamedDeleteStmt(conds []string) string
}

StmtBuilder builds SQL statements.

SQL injection and placeholders

This builder will use string replacement to build SQL statements, so please make sure the values used here, for example the table name, column names and values, are safe and won't lead to SQL injection.

If the input argument is at risk of SQL injection, you pass a placeholder and bind values to them instead.

Although different databases use different placeholders (e.g. MySQL and SQLite use ?, and PostgreSQL uses $N, where N is 1-based positional argument index), you should always use ? or Placeholder in StmtBuilder.

StmtBuilder will rebind them according to the driver name specified during initialization.

Mapped and named statements

There are two ways to build SQL statements, namely mapped and named.

Mapped building takes KV to build a SQL statement. It maps a value to a key, for example "WHERE `name` = ?".

Mapped building will directly use the value of KV in the SQL statement, that means you can also use numbers, strings and functions as the value. For example:

• { Key: "age", Val: "20" }
• { Key: "name", Val: "'my name'" }
• { Key: "create_at", Val: "NOW()" }

Named building however, only needs the column names. It binds columns based on the "db" struct tag. For example "WHERE `name` = :name" will bind the value of a struct that has struct field `db:"name"`.

As a rule of thumb, use mapped building when you are targeting at a set of specific columns, and use named building when you are targeting at a set of specific columns or all columns.

func NewStmtBuilder

func NewStmtBuilder(tbl string, dri string) StmtBuilder

NewStmtBuilder initializes a new StmtBuilder, where tbl is the table name, and dri is the driver name. Nil will be returned if one of the given arguments is invalid.