A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management

Related tags

Database tools ploto
Overview

ploto

A go Library for scan database/sql rows to struct、slice、other types. And it support multiple databases connection management

It's not an ORM. works with database/sql

功能

  • Scan rows, 支持struct,slice,map,其他基本类型
  • 多数据库配置连接管理

说明

仅对database/sql的DB.Query,DB.QueryContext进行封装,其他使用保持不变,Query结果Scan支持*Slice、*Struct、*Map、*int等基本类型.

数据库配置

配置支持多数据库连接,格式如下:

mysql

{"mysql": {
		"clients": {
			"test":{
				"host": "127.0.0.1",
				"port": 3307,
				"user": "test",
				"password": "[email protected]#sddfsdf",
				"database": "test"
			}
		},
		"default": {
			"port": 3306,
			"dialect": "mysql",
			"pool": {
				"maxIdleConns": 2,
				"maxLeftTime": 60000, 
				"maxOpenConns": 5
			},
			"dialectOptions": {
				"parseTime":true,
				"multiStatements": true,
				"writeTimeout": "3000ms",
				"readTimeout": "3000ms",
				"timeout":"3000ms",
				"parseTime": true,
				"loc":"Local",

			}	
		}
	}}

更多dialectOptions参数见: https://github.com/go-sql-driver/mysql#parameters

mssql

{"mssql": {
		"clients": {
	 
			"test":{
				"host": "127.0.0.1",
				"user": "sa",
				"password": "test123",
				"database": "test",
				"pool": {
					"maxIdleConns": 20,
					"maxLeftTime": 60000,
					"maxOpenConns": 50
				},
				"dialectOptions": {
					"dial timeout": "10"

				}
			}
		},
		"default": {
			"port": 1433,
			"dialect": "sqlserver", //or mssql
			"pool": {
				"maxIdleConns": 2,
				"maxLeftTime": 60000,
				"maxOpenConns": 5
			},
			"dialectOptions": {
				"dial timeout": "3"
			}
		}
	}}

更多dialectOptions 参数见:https://github.com/denisenkom/go-mssqldb#connection-parameters-and-dsn

Using

配合多数据库管理一起使用

package main

import (
    "encoding/json"
    "fmt"
    "github.com/feiin/ploto"
     _ "github.com/go-sql-driver/mysql"
)

func getConfig() (config Configs) {
    testConfig := `{"mysql": {
        "clients": {
            "test":{
                "host": "127.0.0.1",
                "port": 3306,
                "user": "root",
                "password": "root",
                "database": "test"
            }
        },
        "default": {
            "port": 3306,
            "dialect": "mysql",
            "pool": {
                "maxIdleConns": 2,
                "maxLeftTime": 60000, 
                "maxOpenConns": 5
            },
            "dialectOptions": {
                "parseTime":true,
                "multiStatements": true,
                "writeTimeout": "3000ms",
                "readTimeout": "3000ms",
                "timeout":"3000ms",
				"parseTime": true,
				"loc":"Local",
            }   
        }
    }}`

    var conf Configs

    json.Unmarshal([]byte(testConfig), &conf)

    // fmt.Printf("conf %+v", conf)
    return conf

}

type User struct {
    Id          int64  `db:"id"`
    Name        string `db:"name"`
    CreatedTime string `db:"created_time"`
    UpdatedTime string `db:"updated_time"`
}

type Configs struct {
    Mysql ploto.DialectConfig `json:"mysql"`
   // Mssql ploto.DialectConfig `json:"mssql"`
}

func main() {

    configs := getConfig()
    db, err := ploto.Open(configs.Mysql, nil)
    if err != nil {
        panic(err)
    }
    defer db.Close()

    
    var users []User
    err = db.Use("test").Query("select * from users where id<100").Scan(&users)
    if err != nil {
        panic(err)
    }
    fmt.Printf("users %+v", users)

	//Exec....
	result, err := db.Use("test").Exec("update users set name=? where  id=?","xxx",1)
    if err != nil {
		//...
        panic(err)
    }
	
	
	//Exec....
	result, err := db.Use("test").Exec("insert uesrs(name,created_time) values(?,now())","xxx")
    if err != nil {
		//...
        panic(err)
    }

}

只用Scan功能

支持对rows结果转化到struct,slice,int等

struct定义字段tag为db

type User struct {
    Id          int64  `db:"id"`
    Name        string `db:"name"`
    CreatedTime string `db:"created_time"`
    UpdatedTime string `db:"updated_time"`
}
package main

import (
	"database/sql"
	"fmt"
	"github.com/feiin/ploto"
	_ "github.com/go-sql-driver/mysql"
)

func main() {
	db, err := sql.Open("mysql", "user:[email protected]/database")
	if err != nil {
		panic(err.Error()) // Just for example purpose. You should use proper error handling instead of panic
	}
	defer db.Close()

	//scan rows to slices
	var users []User
	rows, err = db.Query("select * from users where id<100")
	if err != nil {
		panic(err)
	}
	defer rows.Close()

	for rows.Next() {
		var user User
		err := ploto.Scan(rows, &user)
		users = append(users, user)
	}


	//ScanResult等同上代码
	var users []User
	rows, err = db.Query("select * from users where id<100")
	if err != nil {
		panic(err)
	}

	//No need to Close
	err := ploto.ScanResult(rows, &users)

	//.....
	// select count(1) as cnt from users

	if rows.Next() {
		var a int64
		ploto.Scan(rows,&a)
	}
	//.....

	// select * from users where id=1

	if rows.Next() {
		var user User 
		ploto.Scan(rows,&user)
	}
	//.....
}
Releases(v0.3.5)
Owner
solar
bio
solar
SQLite extension for accessing other SQL databases

dblite SQLite extension for accessing other SQL databases, in SQLite. Similar to how Postgres Foreign Data Wrappers enable access to other databases i

MergeStat 9 May 20, 2022
OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.

OctoSQL OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases, streaming sources and file formats using

Jacob Martin 3.1k Jun 27, 2022
A tool for finding corrupted data rows in TiDB

tidb-bad-rows A tool for finding corrupted data rows in TiDB. It scans the target table and using a divide and conquer paradigm to locate all corrupte

Wenxuan 1 Nov 17, 2021
PolarDB Cluster Manager is the cluster management component of PolarDB for PostgreSQL, responsible for topology management, high availability, configuration management, and plugin extensions.

What is PolarDB Cluster Manager PolarDB Cluster Manager is the cluster management component of PolarDB for PostgreSQL, responsible for topology manage

null 8 Dec 15, 2021
Prep finds all SQL statements in a Go package and instruments db connection with prepared statements

Prep Prep finds all SQL statements in a Go package and instruments db connection with prepared statements. It allows you to benefit from the prepared

Max Chechel 31 Jan 23, 2022
Use SQL to instantly query instances, networks, databases, and more from Scaleway. Open source CLI. No DB required.

Scaleway Plugin for Steampipe Use SQL to query infrastructure servers, networks, databases and more from your Scaleway project. Get started → Document

Turbot 5 Dec 16, 2021
Manage SQL databases, users and grant using kubernetes manifests

SqlOperator Operate sql databases, users and grants. This is a WIP project and should not at all be used in production at this time. Feel free to vali

Stenic 1 Nov 28, 2021
Use SQL to query databases, logs and more from PlanetScale

Use SQL to instantly query PlanetScale databases, branches and more. Open source CLI. No DB required.

Turbot 1 Feb 13, 2022
Go package for sharding databases ( Supports every ORM or raw SQL )

Octillery Octillery is a Go package for sharding databases. It can use with every OR Mapping library ( xorm , gorp , gorm , dbr ...) implementing data

BlasTrain Co., Ltd. 165 Jun 13, 2022
Universal command-line interface for SQL databases

usql A universal command-line interface for PostgreSQL, MySQL, Oracle Database, SQLite3, Microsoft SQL Server, and many other databases including NoSQ

XO 7.3k Jun 28, 2022
SQL API is designed to be able to run queries on databases without any configuration by simple HTTP call.

SQL API SQL API is designed to be able to run queries on databases without any configuration by simple HTTP call. The request contains the DB credenti

Çiçeksepeti Tech 24 Jun 21, 2022
This is the code example how to use SQL to query data from any relational databases in Go programming language.

Go with SQL example This is the code example how to use SQL to query data from any relational databases in Go programming language. To start, please m

Muhammad Uzair Mohd Faizul 1 Mar 12, 2022
A simple auditor of SQL databases.

DBAuditor SQL数据库审计系统,目前支持SQL注入攻击审计 环境配置 sudo apt install golang 运行方式 将待审计语句填入test.txt中,然后运行主程序: 直接运行: go run main.go 编译运行: go build main.go ./main 主要目

Yunjie Xiao 4 May 13, 2022
A go package to add support for data at rest encryption if you are using the database/sql.

go-lockset A go package to add support for data at rest encryption if you are using the database/sql to access your database. Installation In your Gol

Bartlomiej Mika 0 Jan 30, 2022
🏋️ dbbench is a simple database benchmarking tool which supports several databases and own scripts

dbbench Table of Contents Description Example Installation Supported Databases Usage Custom Scripts Troubeshooting Development Acknowledgements Descri

Simon Jürgensmeyer 68 May 24, 2022
A database connection wrapper to cache prepared statements by transforming queries to use with array arguments.

sqlpp sqlpp is a sql(MySQL and PostgreSQL) database connection wrapper to cache prepared statements by transforming queries ("...in (?)...", []) to us

Nazım Parlar 9 Feb 9, 2022
A database connection tool for sensitive data

go-sql 用于快速统计数据库行数、敏感字段匹配、数据库连接情况。 usage ./go-sql_darwin_amd64 -h ./go-sql_darwin_amd64 -f db.yaml -k name,user ./go-sql_darwin_amd64 -f db.yaml --min

null 5 Apr 4, 2022
A Go SQL query builder and struct mapper.

godb - a Go query builder and struct mapper godb is a simple Go query builder and struct mapper, not a full-featured ORM. godb does not manage relatio

Samuel GAY 715 Jun 15, 2022
sqlx is a library which provides a set of extensions on go's standard database/sql library

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

Jason Moiron 12.2k Jun 26, 2022