rkbootsqlserver

package module
v0.0.4 Latest Latest
Warning

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

 Go to latest Published: Jan 30, 2022 License: Apache-2.0 Imports: 3 Imported by: 0

README

rk-boot/database/sqlserver

Table of Contents generated with DocToc

Quick Start

In the bellow example, we will run SQL Server locally and implement API of Create/List/Get/Update/Delete for User model with Gin.

  • 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
Installation

In this example, we will start a web service with gin. As a result, bellow dependencies should be installed.

go get github.com/rookie-ninja/rk-boot/gin
go get github.com/rookie-ninja/rk-boot/database/sqlserver
Start SQL Server with docker

We are going to use [sa:MyStrongPass$] as credential of SQL Server.

$ docker run --name sql-server -e "ACCEPT_EULA=Y" -e "SA_PASSWORD=MyStrongPass$" -p 1433:1433 -d mcr.microsoft.com/mssql/server
1.Create boot.yaml
  • Create web server with Gin framework at port 8080
  • Create SQL Server entry which connects SQL Server at localhost:1433
---
gin:
  - name: user-service
    port: 8080
    enabled: true
sqlServer:
  - name: user                        # Required
    enabled: true                     # Required
    locale: "*::*::*::*"              # Required
    addr: "localhost:1433"            # Optional, default: localhost:1433
    user: sa                          # Optional, default: sa
    pass: MyStrongPass$               # Optional, default: pass
    database:
      - name: user-meta               # Required
        autoCreate: true              # Optional, default: false
#        dryRun: true                 # Optional, default: false
#        params: []                   # Optional, default: []
#    logger:
#      level: info                    # Optional, default: warn
#      encoding: json                 # Optional, default: console
#      outputPaths: ["sqlserver/log"] # Optional, default: []
2.Create main.go

In the main() function, we implement two things.

  • Add User{} as auto migrate option which will create table in DB automatically if missing.
  • Register APIs into Gin router.
// 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"
	"github.com/rookie-ninja/rk-boot/database/sqlserver"
	"github.com/rookie-ninja/rk-boot/gin"
	"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
	userDb = rkbootsqlserver.GetGormDb("user", "user-meta")

	if !userDb.DryRun {
		userDb.AutoMigrate(&User{})
	}

	// Register APIs
	ginEntry := rkbootgin.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 server
$ go run main.go

2022-01-09T04:41:17.010+0800    INFO    Bootstrap sqlServer entry       {"entryName": "user", "sqlServerUser": "sa", "sqlServerAddr": "localhost:1433"}
2022-01-09T04:41:17.010+0800    INFO    creating database user-meta if not exists
2022-01-09T04:41:17.492+0800    INFO    /Users/dongxuny/go/pkg/mod/github.com/rookie-ninja/rk-db/sqlserver@v0.0.1/boot.go:404 SLOW SQL >= 200ms
[366.743ms] [rows:0] 
IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = 'user-meta')
BEGIN
  CREATE DATABASE [user-meta];
END;

2022-01-09T04:41:17.492+0800    INFO    creating successs or database user-meta exists
2022-01-09T04:41:17.492+0800    INFO    connecting to database user-meta
2022-01-09T04:41:17.510+0800    INFO    connecting to database user-meta success
2022-01-09T04:41:17.510+0800    INFO    boot/gin_entry.go:913   Bootstrap ginEntry      {"eventId": "88026b54-8782-4ab2-99db-0698739db073", "entryName": "user-service"}
------------------------------------------------------------------------
endTime=2022-01-09T04:41:17.510765+08:00
startTime=2022-01-09T04:41:17.510717+08:00
elapsedNano=48442
timezone=CST
ids={"eventId":"88026b54-8782-4ab2-99db-0698739db073"}
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
4.Validation
4.1 Create user

Create a user with name of rk-dev.

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

Update user name to rk-dev-updated.

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

List users.

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

Get user with id=1.

$ curl -X GET localhost:8080/v1/user/1
{"id":1,"name":"rk-dev-updated"}
4.1 Delete user
$ curl -X DELETE localhost:8080/v1/user/1
success

YAML Options

User can start multiple gorm instances at the same time. Please make sure use different names.

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.locale 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.encoding Optional Log encoding type, json & console are available options string console
sqlServer.logger.outputPaths Optional Output paths of logger []string [stdout]
sqlServer.logger.level Optional Logger level, options: silent, error, warn, info string warn
Usage of locale
RK use <realm>::<region>::<az>::<domain> to distinguish different environment.
Variable of <locale> could be composed as form of <realm>::<region>::<az>::<domain>
- realm: It could be a company, department and so on, like RK-Corp.
         Environment variable: REALM
         Eg: RK-Corp
         Wildcard: supported

- region: Please see AWS web site: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html
          Environment variable: REGION
          Eg: us-east
          Wildcard: supported

- az: Availability zone, please see AWS web site for details: https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html
      Environment variable: AZ
      Eg: us-east-1
      Wildcard: supported

- domain: Stands for different environment, like dev, test, prod and so on, users can define it by themselves.
          Environment variable: DOMAIN
          Eg: prod
          Wildcard: supported

How it works?
First, we will split locale with "::" and extract realm, region, az and domain.
Second, get environment variable named as REALM, REGION, AZ and DOMAIN.
Finally, compare every element in locale variable and environment variable.
If variables in locale represented as wildcard(*), we will ignore comparison step.

Example:
# let's assuming we are going to define DB address which is different based on environment.
# Then, user can distinguish DB address based on locale.
# We recommend to include locale with wildcard.
---
DB:
  - name: redis-default
    locale: "*::*::*::*"
    addr: "192.0.0.1:6379"
  - name: redis-in-test
    locale: "*::*::*::test"
    addr: "192.0.0.1:6379"
  - name: redis-in-prod
    locale: "*::*::*::prod"
    addr: "176.0.0.1:6379"

Development Status: Stable

Released under the Apache 2.0 License

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func GetGormDb 

func GetGormDb(entryName, dataBaseName string) *gorm.DB

GetGormDb return gorm.DB instance with entryName and database name.

rksqlserver will init gorm.DB by reading boot.yaml file and validate the connectivity for us.

The entryName and dataBaseName was specified in boot.yaml file as bellow.

entryName: user dataBaseName: user-meta

sqlServer:

  • name: user # Required enabled: true # Required locale: "*::*::*::*" # Required addr: "localhost:1433" # Optional, default: localhost:1433 user: sa # Optional, default: sa pass: pass # Optional, default: pass database:
  • name: user-meta # Required autoCreate: true # Optional, default: false

func GetSqlserverEntry 

func GetSqlserverEntry(entryName string) *rksqlserver.SqlServerEntry

GetSqlserverEntry return rksqlserver.SqlServerEntry with name.

The entryName was specified in boot.yaml file as bellow.

user is the name of entry as function parameter.

sqlite:

  • name: user # Required enabled: true # Required locale: "*::*::*::*" # Required

Types

This section is empty.

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.