SQLite
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#
// Copyright (c) 2021 rookie-ninja
//
// Use of this source code is governed by an Apache-style
// license that can be found in the LICENSE file.
package main
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
# 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