Skip to content

civet148/sqlca

Folders and files

NameName
Last commit message
Last commit date

Latest commit

551083c · Aug 8, 2024
Aug 8, 2024
May 12, 2023
May 12, 2023
Jan 30, 2024
Jun 27, 2023
May 12, 2023
May 12, 2023
Jun 17, 2024
Aug 8, 2024
Aug 8, 2024
Aug 8, 2024
Aug 8, 2024
Jun 27, 2023
Nov 4, 2023
Aug 8, 2024
Aug 8, 2024
Jan 30, 2024
Nov 4, 2023
Mar 11, 2024
Jan 22, 2024
Aug 8, 2024

Repository files navigation

SQLCA

The simplest ORM for gopher which supports mysql/sqlite/mssql-server/postgresql

Quick start

database models generation

  • create database
/*
MySQL - 8.0.23 : Database - sqlca-db
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`sqlca-db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

USE `sqlca-db`;

/*Table structure for table `user` */

CREATE TABLE `user` (
                        `id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto inc id',
                        `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'user name',
                        `phone` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'phone number',
                        `sex` tinyint unsigned NOT NULL DEFAULT '0' COMMENT 'user sex',
                        `email` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'email',
                        `disable` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'disabled(0=false 1=true)',
                        `balance` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT 'balance of decimal',
                        `sex_name` varchar(16) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT 'sex name',
                        `data_size` bigint NOT NULL DEFAULT '0' COMMENT 'data size',
                        `extra_data` json DEFAULT NULL COMMENT 'extra data (json)',
                        `created_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'created time',
                        `updated_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'updated time',
                        PRIMARY KEY (`id`) USING BTREE,
                        KEY `phone` (`phone`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

  • install db2go
$ go install github.com/civet148/db2go@latest
  • script to generate
#!/bin/sh

# output directory
OUT_DIR=..
# golang package name of models
PACK_NAME=models
# model file suffix
SUFFIX_NAME=do
# set columns to readonly, they will be ignored when insert or update
READ_ONLY=created_time,updated_time
# more tags separated by colon
TAGS=bson
# database source name
DSN_URL='mysql://root:[email protected]:3306/sqlca-db?charset=utf8'
# customized types for table columns
SPEC_TYPES='user.is_admin=bool, user.extra_data=UserExtra'
# tiny int columns as boolean when exporting for all tables
TINYINT_TO_BOOL=deleted,disabled,banned,is_admin
# the models import path for --dao flag
IMPORT_MODELS=/path/to/models

db2go --url "${DSN_URL}" --out "${OUT_DIR}" --enable-decimal --spec-type "${SPEC_TYPES}" \
      --suffix "${SUFFIX_NAME}" --package "${PACK_NAME}" --readonly "${READ_ONLY}" --tag "${TAGS}" --tinyint-as-bool "${TINYINT_TO_BOOL}" \
      --dao dao --import-models "${IMPORT_MODELS}"

gofmt -w "${OUT_DIR}"/"${PACK_NAME}"
  • model sample

    /path/to/models/user_do.go

// Code generated by db2go. DO NOT EDIT.
// https://github.com/civet148/sqlca

package models

import "github.com/civet148/sqlca/v2"

const TableNameUser = "user" 

const (
	USER_COLUMN_ID           = "id"
	USER_COLUMN_NAME         = "name"
	USER_COLUMN_PHONE        = "phone"
	USER_COLUMN_SEX          = "sex"
	USER_COLUMN_EMAIL        = "email"
	USER_COLUMN_DISABLE      = "disable"
	USER_COLUMN_BALANCE      = "balance"
	USER_COLUMN_SEX_NAME     = "sex_name"
	USER_COLUMN_DATA_SIZE    = "data_size"
	USER_COLUMN_EXTRA_DATA   = "extra_data"
	USER_COLUMN_CREATED_TIME = "created_time"
	USER_COLUMN_UPDATED_TIME = "updated_time"
)

type UserDO struct {
	Id          uint64        `json:"id" db:"id" bson:"_id"`                                               //auto inc id
	Name        string        `json:"name" db:"name" bson:"name"`                                          //user name
	Phone       string        `json:"phone" db:"phone" bson:"phone"`                                       //phone number
	Sex         uint8         `json:"sex" db:"sex" bson:"sex"`                                             //user sex
	Email       string        `json:"email" db:"email" bson:"email"`                                       //email
	Disable     int8          `json:"disable" db:"disable" bson:"disable"`                                 //disabled(0=false 1=true)
	Balance     sqlca.Decimal `json:"balance" db:"balance" bson:"balance"`                                 //balance of decimal
	SexName     string        `json:"sex_name" db:"sex_name" bson:"sex_name"`                              //sex name
	DataSize    int64         `json:"data_size" db:"data_size" bson:"data_size"`                           //data size
	ExtraData   UserExtra     `json:"extra_data" db:"extra_data" sqlca:"isnull" bson:"extra_data"`         //extra data (json)
	CreatedTime string        `json:"created_time" db:"created_time" sqlca:"readonly" bson:"created_time"` //created time
	UpdatedTime string        `json:"updated_time" db:"updated_time" sqlca:"readonly" bson:"updated_time"` //updated time
}

type UserExtra struct {
	HomeAddress string `json:"home_address"`
}

database connection

package main

import (
    "github.com/civet148/log"
    "github.com/civet148/sqlca-bench/models"
    "github.com/civet148/sqlca/v2"
)

const (
    MysqlDSN = "mysql://root:[email protected]:3306/sqlca-db?charset=utf8mb4"
)

var db *sqlca.Engine

func main() {
    var err error
    db, err = sqlca.NewEngine(MysqlDSN)
    if err != nil {
        log.Panic(err.Error())
    }
    //db.Debug(true) //open debug mode
}

query by id

func QueryById(db *sqlca.Engine) (err error) {
    var rows int64
    var user *models.UserDO
    
    //SELECT * FROM user WHERE id=1
    var userId = 1
    rows, err = db.Model(&user).
                    Table(models.TableNameUser).
                    Id(userId).
                    Query()
    if err != nil {
        return log.Errorf(err.Error())
    }
    if rows == 0 || user.Id == 0 {
        return log.Errorf("query user by id %v not found", userId)
    }
    log.Infof("[SELECT * FROM user WHERE id=1] query result: [%+v]", user)
    return nil
}

query by page

func QueryByPage(db *sqlca.Engine) (err error) {
	var rows, total int64
	var users []*models.UserDO

	//SELECT id, name, sex_name, balance, created_time, updated_time FROM user LIMIT 0, 100
	rows, total, err = db.Model(&users).
		Table(models.TableNameUser).
		Select(models.USER_COLUMN_ID, models.USER_COLUMN_NAME, models.USER_COLUMN_SEX_NAME, models.USER_COLUMN_CREATED_TIME, models.USER_COLUMN_UPDATED_TIME, models.USER_COLUMN_BALANCE).
		Page(0, 100).
		QueryEx()
	if err != nil {
		return log.Errorf(err.Error())
	}
	log.Infof("[SELECT id, name, sex_name, balance, created_time, updated_time FROM user LIMIT 0, 100] query result: rows [%v] total [%v]", rows, total)
	return nil
}

query by conditions

func QueryByConditions(db *sqlca.Engine, id int64, name, createdTime string) (err error) {
    var rows int64
    var users []*models.UserDO
    
    //SELECT id, name, sex_name, balance, created_time, updated_time FROM user WHERE id = ? AND name = ? AND created_time >= ?
    e := db.Model(&users).
            Table(models.TableNameUser).
            Select(models.USER_COLUMN_ID, models.USER_COLUMN_NAME, models.USER_COLUMN_SEX_NAME, models.USER_COLUMN_CREATED_TIME, models.USER_COLUMN_UPDATED_TIME, models.USER_COLUMN_BALANCE)
    if id != 0 {
        e.Eq(models.USER_COLUMN_ID, id)
    }
    if name != "" {
        e.Eq(models.USER_COLUMN_NAME, name)
    }
    if createdTime != "" {
        e.Gte(models.USER_COLUMN_CREATED_TIME, createdTime)
    }
    if rows, err = e.Query(); err != nil {
        return log.Errorf(err.Error())
    }
    log.Infof("[SELECT id, name, sex_name, balance, created_time, updated_time FROM user  WHERE id = ? AND name = ? AND created_time >= ?] query result: rows [%v] users [%+v]", rows, users)
    return nil
}

insert

func Insert(db *sqlca.Engine) (err error) {
	//INSERT INTO user(name, phone, sex_name, balance) VALUES('john', '+0014155787342', 'male', '12423.32356')
	var user = &models.UserDO{
		Name:    "john",
		Phone:   "+0014155787342",
		Balance: sqlca.NewDecimal("12423.32356"),
		SexName: "male",
	}
	var id int64
	id, err = db.Model(&user).Table(models.TableNameUser).Insert()
	if err != nil {
		return log.Errorf(err.Error())
	}
	log.Infof("[INSERT INTO user(name, phone, sex_name, balance) VALUES('john', '+0014155787342', 'male', '12423.32356')] result: last insert id [%v]", id)
	return nil
}

insert batch

func InsertBatch(db *sqlca.Engine) (err error) {
	//INSERT INTO user(name, phone, sex_name, balance) VALUES('john', '+0014155787342', 'male', '12423.32356'),('rose', '+0014155787343', 'female', '423.006')
	var users = []*models.UserDO{
		{
			Name:    "john",
			Phone:   "+0014155787342",
			Balance: sqlca.NewDecimal("12423.32356"),
			SexName: "male",
		},
		{
			Name:    "rose",
			Phone:   "+0014155787343",
			Balance: sqlca.NewDecimal("423.006"),
			SexName: "female",
		},
	}
	_, err = db.Model(&users).Table(models.TableNameUser).Insert()
	if err != nil {
		return log.Errorf(err.Error())
	}
	log.Infof("[INSERT INTO user(name, phone, sex_name, balance) VALUES('john', '+0014155787342', 'male', '12423.32356'),('rose', '+0014155787343', 'female', '423.006')] successful")
	return nil
}

About

a golang database orm tool based on sqlx

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Languages