SqlServer
Connect to SqlServer with rk-boot and rk-db/sqlserver plugin.
Overview#
We will use rk-boot & rk-db/sqlserver to connect to SqlServer cluster. rk-db/sqlserver 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/sqlserver: Plugin to connect to SqlServer 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/sqlserver
Quick start#
1. Create boot.yaml#
---
gin:
- name: user-service
port: 8080
enabled: true
sqlServer:
- name: user-db # Required
enabled: true # Required
domain: "*" # Optional
addr: "localhost:1433" # Optional, default: localhost:1433
user: sa # Optional, default: sa
pass: pass # Optional, default: pass
database:
- name: user # Required
autoCreate: true # Optional, default: false
# dryRun: true # Optional, default: false
# params: [] # Optional, default: []
# 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/sqlserver"
"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
sqlServerEntry := rksqlserver.GetSqlServerEntry("user-db")
userDb = sqlServerEntry.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.Start SqlServer locally#
$ docker run -it --rm --name rk-sqlserver -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=yourStrong(!)Password" -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest
4.Directory hierarchy#
$ tree
.
├── boot.yaml
├── go.mod
├── go.sum
└── main.go
5.Start main.go#
$ go run main.go
2022-01-07T00:54:02.448+0800 INFO boot/gin_entry.go:913 Bootstrap ginEntry {"eventId": "c67c3f2b-9d51-4908-86c9-5b7df25ce719", "entryName": "user-service"}
------------------------------------------------------------------------
endTime=2022-01-07T00:54:02.448265+08:00
startTime=2022-01-07T00:54:02.448131+08:00
elapsedNano=134519
timezone=CST
ids={"eventId":"c67c3f2b-9d51-4908-86c9-5b7df25ce719"}
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-07T00:54:02.448+0800 INFO Bootstrap sqlServer entry {"entryName": "user-db", "sqlServerUser": "sa", "sqlServerAddr": "localhost:1433"}
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 options#
| name | Required | description | type | default value |
|---|---|---|---|---|
| sqlServer.name | Required | The name of entry | string | SqlServer |
| sqlServer.enabled | Required | Enable entry or not | bool | false |
| sqlServer.domain | Required | See locale description bellow | string | "*" |
| sqlServer.description | Optional | Description of echo entry. | string | "" |
| sqlServer.user | Optional | SQL Server username | string | sa |
| sqlServer.pass | Optional | SQL Server password | string | pass |
| sqlServer.addr | Optional | SQL Server remote address | string | localhost:1433 |
| sqlServer.database.name | Required | Name of database | string | "" |
| sqlServer.database.autoCreate | Optional | Create DB if missing | bool | false |
| sqlServer.database.dryRun | Optional | Run gorm.DB with dry run mode | bool | false |
| sqlServer.database.params | Optional | Connection params | []string | [] |
| sqlServer.logger.entry | Optional | Reference of zap logger entry name | string | "" |
| sqlServer.logger.level | Optional | Logging level, [info, warn, error, silent] | string | warn |
| sqlServer.logger.encoding | Optional | log encoding, [console, json] | string | console |
| sqlServer.logger.outputPaths | Optional | log output paths | []string | ["stdout"] |
| sqlServer.logger.slowThresholdMs | Optional | Slow SQL threshold | int | 5000 |
| sqlServer.logger.ignoreRecordNotFoundError | Optional | As name described | bool | false |
sqlServer:
- name: user-db # Required
enabled: true # Required
domain: "*" # Optional
addr: "localhost:1433" # Optional, default: localhost:1433
user: sa # Optional, default: sa
pass: pass # Optional, default: pass
database:
- name: user # Required
autoCreate: true # Optional, default: false
# dryRun: true # Optional, default: false
# params: [] # Optional, default: []
# logger:
# entry: ""
# level: info
# encoding: json
# outputPaths: [ "stdout", "log/db.log" ]
# slowThresholdMs: 5000
# ignoreRecordNotFoundError: false