GORM

Overview

sgorm is a higher-level database component built upon GORM. While retaining all GORM features, it adds the following capabilities:

• Full tracing support
• Enhanced custom condition querying functionality

Database Configuration

Configure database settings in the YAML configuration files located in the configs directory:

MySQL Configuration

# database setting
database:
  driver: "mysql"
  mysql:
    # dsn format,  <user>:<pass>@(127.0.0.1:3306)/<db>?[k=v& ......]
    dsn: "root:123456@(127.0.0.1:3306)/account?parseTime=true&loc=Local&charset=utf8mb4"
    enableLog: true             # Whether to enable logging
    maxIdleConns: 3             # Sets the maximum number of connections in the idle connection pool
    maxOpenConns: 100           # Sets the maximum number of open database connections
    connMaxLifetime: 30         # Sets the maximum amount of time a connection may be reused, unit (minutes)
    #slavesDsn:                 # Sets slave mysql dsn
    #  - "your dsn 1"
    #  - "your dsn 2"
    #mastersDsn:                # Sets masters mysql dsn, array type, optional field. If there's only one master, no need to set the mastersDsn field, the default dsn field is the mysql master.
    #  - "your master dsn"

PostgreSQL Configuration

# database setting
database:
  driver: "postgresql"
  postgres:
    # dsn format,  <username>:<password>@<hostname>:<port>/<db>?[k=v& ......]
    dsn: "root:123456@192.168.3.37:5432/account?sslmode=disable"
    enableLog: true             # Whether to enable logging
    maxIdleConns: 3             # Sets the maximum number of connections in the idle connection pool
    maxOpenConns: 100           # Sets the maximum number of open database connections
    connMaxLifetime: 30         # Sets the maximum amount of time a connection may be reused, unit (minutes)

SQLite Configuration

# database setting
database:
  driver: "sqlite"
  sqlite:
    dbFile: "test/sql/sqlite/sponge.db"   # If in a Windows environment, path separators are \\
    enableLog: true             # Whether to enable logging
    maxIdleConns: 3             # Sets the maximum number of connections in the idle connection pool
    maxOpenConns: 100           # Sets the maximum number of open database connections
    connMaxLifetime: 30         # Sets the maximum amount of time a connection may be reused, unit (minutes)

Example of Initialization

MySQL Initialization

    import "github.com/go-dev-frame/sponge/pkg/sgorm/mysql"

    var dsn = "root:123456@(127.0.0.1:3306)/test?charset=utf8mb4&parseTime=True&loc=Local"

    // case 1: connect to the database using the default settings
    gdb, err := mysql.Init(dsn)

    // case 2: customised settings to connect to the database
    db, err := mysql.Init(
        dsn,
        mysql.WithLogging(logger.Get()),  // print log
        mysql.WithLogRequestIDKey("request_id"),  // print request_id
        mysql.WithMaxIdleConns(5),
        mysql.WithMaxOpenConns(50),
        mysql.WithConnMaxLifetime(time.Minute*3),
        // mysql.WithSlowThreshold(time.Millisecond*100),  // only print logs that take longer than 100 milliseconds to execute
        // mysql.WithEnableTrace(),  // enable tracing
        // mysql.WithRWSeparation(SlavesDsn, MastersDsn...)  // read-write separation
        // mysql.WithGormPlugin(yourPlugin)  // custom gorm plugin
    )
    
    if err != nil {
        panic("mysql.Init error: " + err.Error())
    }

PostgreSQL Initialization

    import (
        "github.com/go-dev-frame/sponge/pkg/sgorm/postgresql"
        "github.com/go-dev-frame/sponge/pkg/utils"
    )

    func InitPostgresql() {
        opts := []postgresql.Option{
            postgresql.WithMaxIdleConns(10),
            postgresql.WithMaxOpenConns(100),
            postgresql.WithConnMaxLifetime(time.Duration(10) * time.Minute),
            postgresql.WithLogging(logger.Get()),
            postgresql.WithLogRequestIDKey("request_id"),
        }

        dsn := "root:123456@127.0.0.1:5432/test"  // or dsn := "host=127.0.0.1 user=root password=123456 dbname=account port=5432 sslmode=disable TimeZone=Asia/Shanghai"
        dsn = utils.AdaptivePostgresqlDsn(dsn)
        db, err := postgresql.Init(dsn, opts...)
        if err != nil {
            panic("postgresql.Init error: " + err.Error())
        }
    }

SQLite Initialization

    import "github.com/go-dev-frame/sponge/pkg/sgorm/sqlite"

    func InitSqlite() {
        opts := []sqlite.Option{
            sqlite.WithMaxIdleConns(10),
            sqlite.WithMaxOpenConns(100),
            sqlite.WithConnMaxLifetime(time.Duration(10) * time.Minute),
            sqlite.WithLogging(logger.Get()),
            sqlite.WithLogRequestIDKey("request_id"),
        }

        dbFile: = "test.db"
        db, err := sqlite.Init(dbFile, opts...)
        if err != nil {
            panic("sqlite.Init error: " + err.Error())
        }
    }

GORM CRUD Guide

• https://gorm.io/docs/index.html

Transaction Example

    func createUser() error {
        // note that you should use tx as the database handle when you are in a transaction
        tx := db.Begin()
        defer func() {
            if err := recover(); err != nil { // rollback after a panic during transaction execution
                tx.Rollback()
                fmt.Printf("transaction failed, err = %v\n", err)
            }
        }()

        var err error
        if err = tx.Error; err != nil {
            return err
        }

        if err = tx.Where("id = ?", 1).First(table).Error; err != nil {
            tx.Rollback()
            return err
        }

        panic("mock panic")

        if err = tx.Create(&userExample{Name: "Mr Li", Age: table.Age + 2, Gender: "male"}).Error; err != nil {
            tx.Rollback()
            return err
        }

        return tx.Commit().Error
    }

Model Embedding Example

package model

import "github.com/go-dev-frame/sponge/pkg/sgorm"

// User object fields mapping table
type User struct {
    sgorm.Model `gorm:"embedded"`

    Name   string `gorm:"type:varchar(40);unique_index;not null" json:"name"`
    Age    int    `gorm:"not null" json:"age"`
    Gender string `gorm:"type:varchar(10);not null" json:"gender"`
}

// TableName get table name
func (table *User) TableName() string {
    return sgorm.GetTableName(table)
}