godbhelper

package module
v1.1.3 Latest Latest
Warning

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

 Go to latest Published: Feb 28, 2020 License: MIT Imports: 15 Imported by: 0

README

GoDBHelper

A simple and lightweight orm library for golang

Features

  • Database versioning/migrating
  • Executing prepared/named/normal statements easily with formatting strings (queries)
  • Easily switching between Databases (see Driver)
  • All sqlx functions
Driver

Usage

Use one of the following imports matching the driver you want to use.
Sqlite: github.com/mattn/go-sqlite3
Sqlite encrypt: github.com/CovenantSQL/go-sqlite3-encrypt
MySQL: github.com/go-sql-driver/mysql
PostgreSQL: github.com/lib/pq

Example

Connections
package main

import (
	"fmt"
	dbhelper "github.com/JojiiOfficial/GoDBHelper/"

	//_ "github.com/go-sql-driver/mysql"
	//_ "github.com/mattn/go-sqlite3"
	//_ "github.com/lib/pq"
	//_ "github.com/CovenantSQL/go-sqlite3-encrypt"
)

type testUser struct {
	ID       int    `db:"id" orm:"pk,ai"`
	Username string `db:"username"`
	Pass     string `db:"password"`
}

func sqliteExample() {
	db := connectToSqlite()
	if db == nil {
		return
	}
	defer db.DB.Close()

	db.Exec("CREATE TABLE user (id int, username text, password text)")
	db.Exec("INSERT INTO user (id, username, password) VALUES (1,'will', 'iamsafe')")

	var user testUser
	db.QueryRow(&user, "SELECT * FROM user")
	fmt.Println(user.ID, ":", user.Username, user.Pass)
}


//TestStruct an example for MySQL
type TestStruct struct {
	Pkid      uint32    `db:"pk_id" orm:"pk,ai,nn"`
	Name      string    `db:"name" orm:"nn"`
	Age       uint8     `db:"age" orm:"nn" default:"1"`
	Email     string    `db:"email" orm:"nn"`
	CreatedAt time.Time `db:"createdAt" orm:"nn" default:"now()"`
}

//An example using mysql as database
func mysqlExample(){
	db := connectToMysql()
	if db == nil {
		return
	}
	defer db.DB.Close()
	
	//Create a Table from a struct. (CreateOption is optional)
	err = db.CreateTable(TestStruct{}, &godbhelper.CreateOption{
		//Create table if not exists
		IfNotExists: true,
		//Use a different name for the table than 'TestStruct'
		TableName: "TestDB",
	})
	
	s1 := TestStruct{
		Email: "email@test.com",
		Name:  "goDbHelper",
	}

	//Insert s1 into the Database. If you want to automatically set the PKid field, you have to pass the address of s1!
	resultSet, err = db.Insert(&s1, &godbhelper.InsertOption{
		//Ignore 'age' to let the DB insert the default value (otherwise it would be 0)
		IgnoreFields: []string{"age"},
		//Automatically fill the PKid field in s1. Only works if the 'orm'-Tag contains 'pk' and 'ai' and the reference to s1 is passed
		SetPK:        true,
	})

	//Load the new entry into s2. Note that you have to set parseTime=True to read 'createdAt' in a time.Time struct
	var s2 TestStruct
	err = db.QueryRow(&s2, "SELECT * FROM TestDB WHERE pk_id=?", s1.Pkid)
	if err != nil {
		fmt.Println(err)
	} else {
		fmt.Println(s2)
	}
}

//Connect to Mysql and return a DBhelper pointer
func connectToMysql() *dbhelper.DBhelper {
	user := "dbUser"
	pass := "pleaseMakeItSafe"
	host := "localhost"
	port := "3306"
	database := "test"
	db, err := dbhelper.NewDBHelper(dbhelper.Mysql).Open(user, pass, host, port, database, "parseTime=True")
	if err != nil {
		fmt.Fatal(err.Error())
		return nil
	}
	return db
}

func connectToSqlite() *dbhelper.DBhelper {
	db, err := dbhelper.NewDBHelper(dbhelper.Sqlite).Open("test.db")
	if err != nil {
		fmt.Fatal(err.Error())
		return nil
	}
	return db
}

func connectToSqliteEncrypt() *dbhelper.DBhelper {
	//Opens the encrypted test.db using 'passKEY' to decrypt it 
	db, err := dbhelper.NewDBHelper(dbhelper.SqliteEncrypted).Open("test.db", "passKEY")
	if err != nil {
		fmt.Fatal(err.Error())
		return nil
	}
	return db
}
Migrating

The following codesnippet demonstrates, how you can integrate database migration to your applications

//db is an instance of dbhelper.DBhelper

//load sql queries from a .sql file
//Queries loaded from this function (LoadQueries) are always version 0. The last argument ('0') specifies the order of the chains.
db.LoadQueries("chain1", "./test.sql", 0)

//Add sql queries manually
//The order specifies the execution order of the queries. So in this case, chain1 would be loaded before chain2
db.AddQueryChain(dbhelper.QueryChain{
	Order: 1,
	Name: "chain2",
	Queries: []dbhelper.SQLQuery{
		dbhelper.SQLQuery{
			VersionAdded: 0,
			QueryString:  "CREATE TABLE user (id int, username text, password text)",
		},
		dbhelper.SQLQuery{
			VersionAdded: 0,
			QueryString:  "INSERT INTO user (id, username, password) VALUES (?,?,?)",
			Params:       []string{"0", "admin", "lol123"},
		},
		//added in a later version (version 0.1)
		dbhelper.SQLQuery{
			VersionAdded: 0.1,
			QueryString:  "CREATE TABLE test1 (id int)",
		},
		//added in a later version (version 0.21)
		dbhelper.SQLQuery{
			VersionAdded: 0.21,
			QueryString:  "INSERT INTO test1 (id) VALUES (?),(?)",
			Params:       []string{"29", "1"},
		},
	},
})

//runs the update
err := db.RunUpdate()
if err != nil {
	fmt.Println("Err updating", err.Error())
}

Documentation

Index

Constants

View Source 
const (
	//Sqlite  sqlite db
	Sqlite dbsys = iota
	//SqliteEncrypted Sqlite encrypted
	SqliteEncrypted
	//Mysql mysql db
	Mysql
	//Postgres postgres db
	Postgres
)
View Source 
const (
	//MysqlURIFormat formats mysql uri
	MysqlURIFormat = "%s:%s@tcp(%s:%d)/%s%s"
	//PostgresURIFormat formats mysql uri
	PostgresURIFormat = "user='%s' password='%s' host='%s' port=%d dbname='%s' %s"
)
View Source 
const (
	//OrmTag orm-tag
	OrmTag = "orm"
	//DBTag db-tag
	DBTag = "db"
	//DefaultTag default
	DefaultTag = "default"
)

Tags

View Source 
const (
	TagIgnore = "-"

	TagPrimaryKey          = "pk"
	TagAutoincrement       = "ai"
	TagInsertAutoincrement = "iai"
	TagNotNull             = "nn"
)

Tag values

View Source 
const (
	//TableDBVersion tableName for db version store
	TableDBVersion = "DBVersion"
)

Variables

View Source 
var (
	//ErrDBNotSupported error if database is not supported
	ErrDBNotSupported = errors.New("Database not supported")

	//ErrPostgresURIMissingArg error if Open() mysqlDB and missing an arg
	ErrPostgresURIMissingArg = errors.New("Postgres missing argument. Use Open(username, password, address, port, database)")

	//ErrMysqlURIMissingArg error if Open() mysqlDB and missing an arg
	ErrMysqlURIMissingArg = errors.New("MYSQL missing argument. Use Open(username, password, address, port, database)")

	//ErrPortInvalid if given port is invalid
	ErrPortInvalid = errors.New("Port invalid. Port must be <= 65535 and > 0")

	//ErrSqliteEncryptMissingArg error if Open() SqliteEncrypt and missing argument
	ErrSqliteEncryptMissingArg = errors.New("SqliteEncrypt missing argument. Use Open(file, key)")

	//ErrSqliteMissingArg error if Open() Sqlite and missing argument
	ErrSqliteMissingArg = errors.New("Sqlite missing argument. Use Open(file)")

	//ErrVersionStoreTooManyVersions if VersionStore contains more than one version
	ErrVersionStoreTooManyVersions = errors.New("Too many versions stored in VersionStore")

	//ErrCantStoreVersionInDB err if running update and StoreVersionInDB=false
	ErrCantStoreVersionInDB = errors.New("Can't store Version in Database. Set StoreVersionInDB=true")

	//ErrInvalidDatabase an invalid dbsys was used
	ErrInvalidDatabase = errors.New("Invalid database")

	//ErrNoStruct if the given data is no struct
	ErrNoStruct = errors.New("Data must be a struct")

	//ErrNoRowsInResultSet error if no rows in resultSet
	ErrNoRowsInResultSet = "sql: no rows in result set"

	//ErrCantAddress if input is no pointer
	ErrCantAddress = errors.New("Can't address value")
)
View Source 
var NoHook = func(err error, s1, s2 string) {}

NoHook run a query without a hook

Functions

func BuildDSN added in v1.1.2 

func BuildDSN(dbkind dbsys, username, password, host, database string, port uint16, dsnString ...string) (string, error)

BuildDSN creates connection string for mysql

Types

type CreateOption added in v1.1.2 

type CreateOption struct {
	TableName   string
	IfNotExists bool
}

CreateOption options for inserting structs into DB

type DBhelper 

type DBhelper struct {

	//Versions for upgrading
	//CurrentVersion the version currently running
	CurrentVersion float32
	//AvailableVersion the version which is newly added
	AvailableVersion float32

	//DBhelper data
	DB          *sqlx.DB
	Options     DBhelperOptions
	IsOpen      bool
	QueryChains []QueryChain `json:"chains"`

	ErrHookFunc    ErrHookFunc
	ErrHookOptions *ErrHookOptions

	NextErrHookFunc   ErrHookFunc
	NextErrHookOption *ErrHookOptions
	// contains filtered or unexported fields
}

DBhelper the dbhelper object

func NewDBHelper 

func NewDBHelper(dbKind dbsys, bv ...bool) *DBhelper

NewDBHelper the DBhelper constructor NewDBHelper(database, debug, stopUpdateOnError, storeVersionInDB, useColors)

func (*DBhelper) AddQueryChain 

func (dbhelper *DBhelper) AddQueryChain(chain QueryChain) *DBhelper

AddQueryChain adds a queryChain

func (*DBhelper) CreateTable 

func (dbhelper *DBhelper) CreateTable(data interface{}, options ...*CreateOption) error

CreateTable creates a table for struct Leave name empty to use the name of the struct

func (*DBhelper) Exec 

func (dbhelper *DBhelper) Exec(query string, args ...interface{}) (sql.Result, error)

Exec executes command in DB

func (*DBhelper) Execf 

func (dbhelper *DBhelper) Execf(queryFormat string, formatParams []string, args ...interface{}) (sql.Result, error)

Execf executes a formatted query in DB

func (*DBhelper) Insert 

func (dbhelper *DBhelper) Insert(data interface{}, options ...*InsertOption) (*sql.Result, error)

Insert creates a table for struct Leave name empty to use the name of the struct

func (*DBhelper) LoadQueries 

func (dbhelper *DBhelper) LoadQueries(name, file string, chainOrder int) error

LoadQueries loads queries from a .sql file and executes the statements (row for row). The SQLQuery Version of the statements are 0. This is intended to initialize the database-schema

func (*DBhelper) Open 

func (dbhelper *DBhelper) Open(params ...string) (*DBhelper, error)

Open db Sqlite - Open(filename) SqliteEncrypted - Open(filename, key) Mysql - Open(username, password, address, port, database) Postgres - Open(username, password, address, port, database)

func (*DBhelper) QueryRow 

func (dbhelper *DBhelper) QueryRow(a interface{}, query string, args ...interface{}) error

QueryRow runs statement and fills a with db data

func (*DBhelper) QueryRowf 

func (dbhelper *DBhelper) QueryRowf(a interface{}, queryf string, queryArgs []string, args ...interface{}) error

QueryRowf like QueryRow but formatted

func (*DBhelper) QueryRows 

func (dbhelper *DBhelper) QueryRows(a interface{}, query string, args ...interface{}) error

QueryRows like QueryRow but for multiple rows

func (*DBhelper) QueryRowsf 

func (dbhelper *DBhelper) QueryRowsf(a interface{}, query string, queryArgs []string, args ...interface{}) error

QueryRowsf like QueryRows but formatted

func (*DBhelper) RunUpdate 

func (dbhelper *DBhelper) RunUpdate(options ...bool) error

RunUpdate updates new sql queries RunUpdate(fullUpdate, dropAllTables bool)

func (*DBhelper) SetErrHook 

func (dbhelper *DBhelper) SetErrHook(hook ErrHookFunc, options ...ErrHookOptions)

SetErrHook sets the error hook function

func (*DBhelper) WithHook 

func (dbhelper *DBhelper) WithHook(hook ErrHookFunc, options ...ErrHookOptions) *DBhelper

WithHook adds next log prefix

func (*DBhelper) WithMessage 

func (dbhelper *DBhelper) WithMessage(s string) *DBhelper

WithMessage adds next log prefix

func (*DBhelper) WithOption added in v1.0.3 

func (dbhelper *DBhelper) WithOption(option ErrHookOptions) *DBhelper

WithOption adds next ErrHookOption

type DBhelperOptions 

type DBhelperOptions struct {
	Debug             bool
	StopUpdateOnError bool
	StoreVersionInDB  bool
	UseColors         bool
}

DBhelperOptions options for DBhelper

type ErrHookFunc 

type ErrHookFunc func(error, string, string)

ErrHookFunc error hook 1. string is the query; 2. string is the hookPrefix if set

type ErrHookOptions 

type ErrHookOptions struct {
	ReturnNilOnErr bool
	Prefix         string
}

ErrHookOptions options for ErrorHooks

type InitSQL 

type InitSQL struct {
	Query   string
	Params  string
	FParams []string
}

InitSQL init sql obj

type InsertOption added in v1.1.2 

type InsertOption struct {
	TableName        string
	IgnoreFields     []string
	SetPK            bool
	FillNotSetFields bool
}

InsertOption options for inserting structs into DB

type QueryChain 

type QueryChain struct {
	Name    string     `json:"name"`
	Order   int        `json:"order"`
	Queries []SQLQuery `json:"queries"`
}

QueryChain a list of SQL queries over time

func LoadQueries 

func LoadQueries(name, file string, chainOrder int) (*QueryChain, error)

LoadQueries loads queries from a .sql file and executes the statements (row for row). The SQLQuery Version of the statements are 0. This is intended to initialize the database-schema

func NewQueryChain 

func NewQueryChain(name string, order int) *QueryChain

NewQueryChain QueryChain constructor

func RestoreQueryChain 

func RestoreQueryChain(file string) (*QueryChain, error)

RestoreQueryChain loads an exported queryChain from file

func (*QueryChain) ExportQueryChain 

func (queryChain *QueryChain) ExportQueryChain(file string, perm os.FileMode) error

ExportQueryChain saves/exports a queryChain to a file

type SQLQuery 

type SQLQuery struct {
	VersionAdded float32  `json:"vs"`
	QueryString  string   `json:"query"`
	Params       []string `json:"params"`
	FqueryString string   `json:"queryf"`
	Fparams      []string `json:"fparams"`
}

SQLQuery a query

func CreateInitVersionSQL 

func CreateInitVersionSQL(arg ...InitSQL) []SQLQuery

CreateInitVersionSQL creates SQLQuery[] for init version

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.