database

package
v1.0.0-pre Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Mar 21, 2026 License: MIT Imports: 14 Imported by: 0

README

last updated: 2026-01-16 am

Database Design Research

Some database design and related research will be recorded here.

Query Analyze

This section attempts a simple analysis of SELECT queries in SQL for relational databases, as this is the most common and frequently used DML operation. Ultimately, we simply want to extract the final dataset from a large dataset using the operations provided by SELECT, representing a data processing procedure. In reality, perfectly using SELECT to obtain the result isn't particularly complicated. In principle, it requires deconstructing the actual problem in reverse. Let's try to understand this by starting with a practical problem:

eg_1: Try to retrieve the name of the student with the highest score in each subject from stu (id, name) and sc(id, subject, score).

Here, our final result needs to be "name", calculated from the "highest score in each subject". Here's an example implementation:

SELECT name FROM stu LEFT JOIN sc sc1 ON stu.id = sc1.id WHERE sc1.score = (SELECT max(sc2. score) FROM sc sc2 WHERE sc2.subject = sc1.subject)

One of the most interesting aspects of SELECT is its execution order. The from table_expr where ... group by ... having ... select ... order by ... limit ... clause is intuitively important, considering both the scope of influence of table aliases and logical operations. Aggregate functions cannot appear in the where clause, but they can be introduced after grouping.

RBAC Simple

Implement a simple RBAC system called NBAC, containing only basic operations while possessing a certain degree of scalability. We abstract the actual entities as: user, namespace, permission, and resource. Each entity has a unique identifier, which is for the purpose of allowing for easy expansion of operations, similar to the permission design in Unix.

NBAC implements a concept similar to a file system in an operating system. In fact, it involves similar operations, but only the minimal fields are implemented.

NBAC is a simplified version of the RBAC system, mainly used for permission management in specific system environments. It mainly uses the concept of namespace to restrict the operations of different users on resources. This requires a set of rules to restrict to a certain extent. For users, we can always create in a general and natural way. Users have natural permissions to create some resources, which is obviously only related to the resources themselves. If you need to build your own resources in the real world, it is obviously a lack of construction materials, not any necessary constraint permissions. This self-created resource, whether it is a namespace or a resource, has no restrictions on the creator. The creator can set outsider access permissions on the namespace and resource, which includes the default permissions of the resource itself, which can also be directly called the maximum accessible permissions. At the same time, the necessary access permissions can be directly specified for each user who may need to access the resource, which greatly facilitates refinement and is used to control the granularity of permissions. A resource is an abstract association collection, and it does not represent any concrete resource. Its extern_id is enough to associate it with any possible actual resource, but this only depends on the actual resource creation, which is not discussed in the permission system. Permissions should be a predefined limited set. Obviously using uint64 can basically cover any permissions(of course it must be well designed). The association between permissions is loosely coupled, which represents any possible independent capabilities. These detailed permissions must be reasonable. Assume that non-2^n permissions are allowed to be defined in permissions, This means that code will become a value that is not a power of 2 under uint64. But no matter what, we just regard this as a collection of some special permissions, similar to role combinations of some permissions, which means that namespace or resources can continue to use these. But one problem is that code is more likely to be defined as an index in the db, which can directly reduce the number of permissions in the table, thereby further restricting permissions.

Comment System

ref

Redis Design

Documentation

Overview

Package database Provides a unified API interface for relational and non-relational databases.

For relational databases, MySQL is integrated by default, and PostgreSQL, SQLite, Oracle, etc. will be integrated later. For non-relational databases, Redis and MongoDB are integrated by default, and will be integrated according to actual use later.

A simple integration example 

type (
	MysqlDB     *sqlx.DB
	MysqlOption func(MysqlDB)
)

func InitMysql(dsn string) MysqlDB {
  	if dsn == "" {
  		dsn = _default_dsn
  	}
  	ctx, cancle := context.WithTimeout(context.Background(), _conn_timeout)
  	defer cancle()
  	db, err := sqlx.ConnectContext(ctx, _driver_mysql, dsn)
  	if err != nil {
		panic("init mysql database instance fail.")
  	}
  	return db
}

func SetupMysql(mdb MysqlDB, opts ...MysqlOption) {
    for _, fn := range opts {
    	fn(mdb)
    }
}

The most regrettable aspect is that it's impossible to bind all public APIs to each individual database type. This is a limitation of Go, and it's also why we need to pass the database instance. Since some public APIs don't always require generic types, we have to simplify the design here, but we've still achieved a highly templated code structure.

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func DeleteWithName 

func DeleteWithName(ctx context.Context, db *sqlx.DB, sqlStr string, obj any) error

DeleteWithName return error occurred during the execution of the delete SQL with named parameters. The database instance needs to be explicitly specified.

func DeleteWithPlace 

func DeleteWithPlace(ctx context.Context, db *sqlx.DB, sqlStr string, args ...any) error

DeleteWithPlace return error occurred during the execution of the delete SQL with placeholdler parameters. The database instance needs to be explicitly specified.

func GetRedisDB 

func GetRedisDB() *redis.Client

func InitRedisDB 

func InitRedisDB(ro *redis.Options) *redis.Client

func InsertWithName 

func InsertWithName(ctx context.Context, db *sqlx.DB, sqlStr string, obj any) error

InsertWithName return error occurred during the execution of the insert SQL with named parameters. The primary key that returns a successful insert may be modified later. The database instance needs to be explicitly specified.

func InsertWithPlace 

func InsertWithPlace(ctx context.Context, db *sqlx.DB, sqlStr string, args ...any) error

InsertWithPlace return error occurred during the execution of the insert SQL with placeholder parameters. The primary key that returns a successful insert may be modified later. The database instance needs to be explicitly specified.

func QListWithName 

func QListWithName[R any](ctx context.Context, db *sqlx.DB, sqlStr string, obj any) ([]R, error)

QListWithName return the list of specify generic type and error occurred during the execution of the select SQL with named parameters. R should have the largest set of all fields that need to be retrieved and not be a pointer type. The database instance needs to be explicitly specified.

func QListWithPlace 

func QListWithPlace[R any](ctx context.Context, db *sqlx.DB, sqlStr string, args ...any) ([]R, error)

QListWithPlace return the list of specify generic type and error occurred during the execution of the select SQL with placeholdler parameters. R should have the largest set of all fields that need to be retrieved and not be a pointer type. The database instance needs to be explicitly specified.

func QueryWithName 

func QueryWithName[R any](ctx context.Context, db *sqlx.DB, sqlStr string, obj any) (R, error)

QueryWithName return the specify generic type and error occurred during the execution of the select SQL with named parameters. R should have the largest set of all fields that need to be retrieved and not be a pointer type. The database instance needs to be explicitly specified.

func QueryWithPlace 

func QueryWithPlace[R any](ctx context.Context, db *sqlx.DB, sqlStr string, args ...any) (R, error)

QueryWithPlace return the specify generic type and error occurred during the execution of the select SQL with placeholdler parameters. R should have the largest set of all fields that need to be retrieved and not be a pointer type. The database instance needs to be explicitly specified.

func SetupMysql 

func SetupMysql(db MysqlDB, opts ...MysqlOption)

func SetupSqlite 

func SetupSqlite(db SqliteDB, opts ...SqliteOption)

func ToStmt 

func ToStmt(db *sqlx.DB, sqlStr string) (*sqlx.Stmt, error)

func ToStmtContext 

func ToStmtContext(ctx context.Context, db *sqlx.DB, sqlStr string) (*sqlx.Stmt, error)

func ToTx 

func ToTx(db *sqlx.DB) (*sqlx.Tx, error)

func ToTxContext 

func ToTxContext(ctx context.Context, db *sqlx.DB, opts *sql.TxOptions) (*sqlx.Tx, error)

func UpdateWithName 

func UpdateWithName(ctx context.Context, db *sqlx.DB, sqlStr string, obj any) error

UpdateWithName return error occurred during the execution of the udpate SQL with named parameters. The database instance needs to be explicitly specified.

func UpdateWithPlace 

func UpdateWithPlace(ctx context.Context, db *sqlx.DB, sqlStr string, args ...any) error

UpdateWithPlace return error occurred during the execution of the update SQL with placeholder parameters. The database instance needs to be explicitly specified.

Types

type MongoDB 

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

MongoDB is just a layer of mongo encapsulation.

func InitMongo 

func InitMongo(dsn string) MongoDB

InitMongo return new instance of mongodb.

func (*MongoDB) Close 

func (db *MongoDB) Close()

In most scenarios, the client needs to be closed in defer, which may be related to the location where initialization is called.

type MysqlDB 

type MysqlDB *sqlx.DB

mysql database from sqlx

func InitMysql 

func InitMysql(dsn string) MysqlDB

InitMysql return a new mysql database instance with specific data source name. It does not check the validity of the dsn and will panic if dsn is invalid.

type MysqlMeta 

type MysqlMeta struct {
	Id        uint64    `db:"id"`         // primary key
	ExternId  uuid.UUID `db:"extern_id"`  // extern id as foreign key
	CreatedAt time.Time `db:"created_at"` // created time
	UpdatedAt time.Time `db:"updated_at"` // updated time
	Deleted   bool      `db:"deleted"`    // deleted or not
}

MysqlMeta is mysql attribute-independent metadata structure. It is generally used as an embedded structure.

type MysqlOption 

type MysqlOption func(MysqlDB)

mysql database instance functional configuration

type NullBool 

type NullBool struct{ sql.NullBool }

func NewNullBool 

func NewNullBool(b *bool) NullBool

func (NullBool) BoolValue 

func (nb NullBool) BoolValue() *bool

type NullFloat64 

type NullFloat64 struct{ sql.NullFloat64 }

func (NullFloat64) Float64Value 

func (nf NullFloat64) Float64Value() *float64

func (NullFloat64) NewNullFloat64 

func (nf NullFloat64) NewNullFloat64(f *float64) NullFloat64

type NullInt16 

type NullInt16 struct{ sql.NullInt16 }

func NewNullInt16 

func NewNullInt16(i *int16) NullInt16

func (NullInt16) Int16Value 

func (ni NullInt16) Int16Value() *int16

type NullInt32 

type NullInt32 struct{ sql.NullInt32 }

func NewNullInt32 

func NewNullInt32(i *int32) NullInt32

func (NullInt32) Int32Value 

func (ni NullInt32) Int32Value() *int32

type NullInt64 

type NullInt64 struct{ sql.NullInt64 }

func NewNullInt64 

func NewNullInt64(i *int64) NullInt64

func (NullInt64) Int64Value 

func (ni NullInt64) Int64Value() *int64

type NullString 

type NullString struct{ sql.NullString }

func NewNullString 

func NewNullString(s *string) NullString

func (NullString) StringValue 

func (ns NullString) StringValue() *string

type NullTime 

type NullTime struct{ sql.NullTime }

func NewNullTime 

func NewNullTime(t *time.Time) NullTime

func (NullTime) TimeValue 

func (nt NullTime) TimeValue() *time.Time

type Page 

type Page[T any] struct {
	CurrentPage int `json:"current_page"` // current page to calculate offset
	PageSize    int `json:"page_size"`    // page size
	Total       int `json:"total"`        // total records obtained
	Items       []T `json:"items"`        // list of records
}

Page is used for paginated queries.

func QPageWithName 

func QPageWithName[R any](ctx context.Context, db *sqlx.DB, sqlStr string, obj any) (Page[R], error)

QPageWithName return the page of specify generic type and error occurred during the execution of the select SQL with named parameters. R should have the largest set of all fields that need to be retrieved and not be a pointer type. The database instance needs to be explicitly specified.

func QPageWithPlace 

func QPageWithPlace[R any](ctx context.Context, db *sqlx.DB, sqlStr string, args ...any) (Page[R], error)

QPageWithPlace return the page of specify generic type and error occurred during the execution of the select SQL with placeholdler parameters. R should have the largest set of all fields that need to be retrieved and not be a pointer type. The database instance needs to be explicitly specified.

func (*Page[T]) Offset 

func (p *Page[T]) Offset() int

Offset return the starting offset accroding to current page and page size.

type SqliteDB 

type SqliteDB *sqlx.DB

Simple encapsulation for sqlite database from sqlx.

func InitSqlite 

func InitSqlite(dsn string) SqliteDB

type SqliteMeta 

type SqliteMeta struct {
	Id        uint64    `db:"id"`         // primary key
	ExternId  uuid.UUID `db:"extern_id"`  // extern id as foreign key
	CreatedAt time.Time `db:"created_at"` // created time
	UpdatedAt time.Time `db:"updated_at"` // updated time
	Deleted   bool      `db:"deleted"`    // deleted or not
}

SqliteMeta is sqlite attribute-independent metadata structure. It is generally used as an embedded structure.

type SqliteOption 

type SqliteOption func(SqliteDB)

Source Files

View all Source files

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.