SQLite - rk-boot (original) (raw)

Connect to Sqlite with rk-boot and rk-db/sqlite plugin.

Overview#

We will use rk-boot & rk-db/sqlite to connect to Sqlite cluster.rk-db/sqlite uses gorm as driver by default.

In order to demonstrate full example，we will use rk-gin to start a back end service with APIs as bellow.

GET /v1/user, List users
GET /v1/user/:id, Get user
PUT /v1/user, Create user
POST /v1/user/:id, Update user
DELETE /v1/user/:id, Delete user

Install#

rk-boot: Base package.
rk-gin: To start gin-gonic/gin microservice.
rk-db/sqlite: Plugin to connect to Sqlite with gorm.

go get github.com/rookie-ninja/rk-boot/v2 go get github.com/rookie-ninja/rk-gin/v2 go get github.com/rookie-ninja/rk-db/sqlite

Quick start#

1. Create boot.yaml#

`--- gin:

name: user-service port: 8080 enabled: true sqlite:
name: user-db # Required enabled: true # Required domain: "*" # Optional database:
- name: user # Required

inMemory: true # Optional, default: false

dbDir: "" # Optional, default: "", directory where db file created or imported, can be absolute or relative path

dryRun: true # Optional, default: false

params: [] # Optional, default: ["cache=shared"]

logger:

entry: ""

level: info

encoding: json

outputPaths: [ "stdout", "log/db.log" ]

slowThresholdMs: 5000

ignoreRecordNotFoundError: false

2. Create main.go#

import ( "context" "github.com/gin-gonic/gin" "github.com/rookie-ninja/rk-boot/v2" "github.com/rookie-ninja/rk-db/sqlite" "github.com/rookie-ninja/rk-gin/v2/boot" "gorm.io/gorm" "net/http" "strconv" "time" )

var userDb *gorm.DB

func main() { boot := rkboot.NewBoot()

boot.Bootstrap(context.TODO())

// Auto migrate database and init global userDb variable
sqliteEntry := rksqlite.GetSqliteEntry("user-db")
userDb = sqliteEntry.GetDB("user")
if !userDb.DryRun {
    userDb.AutoMigrate(&User{})
}

// Register APIs
ginEntry := rkgin.GetGinEntry("user-service")
ginEntry.Router.GET("/v1/user", ListUsers)
ginEntry.Router.GET("/v1/user/:id", GetUser)
ginEntry.Router.PUT("/v1/user", CreateUser)
ginEntry.Router.POST("/v1/user/:id", UpdateUser)
ginEntry.Router.DELETE("/v1/user/:id", DeleteUser)

boot.WaitForShutdownSig(context.TODO())

}

// ************************************* // *************** Model *************** // *************************************

type Base struct { CreatedAt time.Time yaml:"-" json:"-" UpdatedAt time.Time yaml:"-" json:"-" DeletedAt gorm.DeletedAt yaml:"-" json:"-" gorm:"index" }

type User struct { Base Id int yaml:"id" json:"id" gorm:"primaryKey" Name string yaml:"name" json:"name" }

func ListUsers(ctx *gin.Context) { userList := make([]*User, 0) res := userDb.Find(&userList)

if res.Error != nil {
    ctx.JSON(http.StatusInternalServerError, res.Error)
    return
}
ctx.JSON(http.StatusOK, userList)

}

func GetUser(ctx *gin.Context) { uid := ctx.Param("id") user := &User{} res := userDb.Where("id = ?", uid).Find(user)

if res.Error != nil {
    ctx.JSON(http.StatusInternalServerError, res.Error)
    return
}
ctx.JSON(http.StatusOK, user)

}

func CreateUser(ctx *gin.Context) { user := &User{ Name: ctx.Query("name"), }

res := userDb.Create(user)

if res.Error != nil {
    ctx.JSON(http.StatusInternalServerError, res.Error)
    return
}
ctx.JSON(http.StatusOK, user)

}

func UpdateUser(ctx *gin.Context) { uid := ctx.Param("id") user := &User{ Name: ctx.Query("name"), }

res := userDb.Where("id = ?", uid).Updates(user)

if res.Error != nil {
    ctx.JSON(http.StatusInternalServerError, res.Error)
    return
}

if res.RowsAffected < 1 {
    ctx.JSON(http.StatusNotFound, "user not found")
    return
}

// get user
userDb.Where("id = ?", uid).Find(user)

ctx.JSON(http.StatusOK, user)

}

func DeleteUser(ctx *gin.Context) { uid, _ := strconv.Atoi(ctx.Param("id")) res := userDb.Delete(&User{ Id: uid, })

if res.Error != nil {
    ctx.JSON(http.StatusInternalServerError, res.Error)
    return
}

if res.RowsAffected < 1 {
    ctx.JSON(http.StatusNotFound, "user not found")
    return
}

ctx.String(http.StatusOK, "success")

} ``

3.Directory hierarchy#

$ tree . ├── boot.yaml ├── go.mod ├── go.sum └── main.go

4.Start main.go#

`$ go run main.go

2022-01-06T20:53:24.023+0800 INFO boot/gin_entry.go:913 Bootstrap ginEntry {"eventId": "505acbfa-7f0b-425b-be53-c9b80036a3f7", "entryName": "user-service"}

endTime=2022-01-06T20:53:24.023552+08:00 startTime=2022-01-06T20:53:24.023405+08:00 elapsedNano=147732 timezone=CST ids={"eventId":"505acbfa-7f0b-425b-be53-c9b80036a3f7"} app={"appName":"rk","appVersion":"","entryName":"user-service","entryType":"GinEntry"} env={"arch":"amd64","az":"","domain":"","hostname":"lark.local","localIP":"10.8.0.2","os":"darwin","realm":"","region":""} payloads={"ginPort":8080} error={} counters={} pairs={} timing={} remoteAddr=localhost operation=Bootstrap resCode=OK eventStatus=Ended EOE 2022-01-06T20:53:24.023+0800 INFO Bootstrap SQLite entry {"entryName": "user-db"} `

6.Validate#

6.1 Create user#

$ curl -X PUT "localhost:8080/v1/user?name=rk-dev" {"id":2,"name":"rk-dev"}

6.2 Update user#

$ curl -X POST "localhost:8080/v1/user/2?name=rk-dev-updated" {"id":2,"name":"rk-dev-updated"}

6.3 List users#

$ curl -X GET localhost:8080/v1/user [{"id":2,"name":"rk-dev-updated"}]

6.4 Get user#

$ curl -X GET localhost:8080/v1/user/2 {"id":2,"name":"rk-dev-updated"}

6.5 Delete user#

$ curl -X DELETE localhost:8080/v1/user/2 success

Full YAML Option#

name	Required	description	type	default value
sqlite.name	Required	The name of entry	string	SQLite
sqlite.enabled	Required	Enable entry or not	bool	false
sqlite.domain	Required	See locale description bellow	string	"*"
sqlite.description	Optional	Description of echo entry.	string	""
sqlite.database.name	Required	Name of database	string	""
sqlite.database.inMemory	Optional	SQLite in memory	bool	false
sqlite.database.dbDir	Optional	Specify *.db file directory	string	"", current working directory if empty
sqlite.database.dryRun	Optional	Run gorm.DB with dry run mode	bool	false
sqlite.database.params	Optional	Connection params	[]string	["cache=shared"]
sqlite.logger.entry	Optional	Reference of zap logger entry name	string	""
sqlite.logger.level	Optional	Logging level, [info, warn, error, silent]	string	warn
sqlite.logger.encoding	Optional	log encoding, [console, json]	string	console
sqlite.logger.outputPaths	Optional	log output paths	[]string	["stdout"]
sqlite.logger.slowThresholdMs	Optional	Slow SQL threshold	int	5000
sqlite.logger.ignoreRecordNotFoundError	Optional	As name described	bool	false

`sqlite:

name: user-db # Required enabled: true # Required locale: "::::::" # Required database:
- name: user # Required

inMemory: true # Optional, default: false

dbDir: "" # Optional, default: "", directory where db file created or imported, can be absolute or relative path