database

README

GoCryptoTrader package Database

This database package is part of the GoCryptoTrader codebase.

This is still in active development

You can track ideas, planned features and what's in progress on this Trello board: https://trello.com/b/ZAhMhpOy/gocryptotrader.

Join our slack to discuss all things related to GoCryptoTrader! GoCryptoTrader Slack

Current Features for database package

• Establishes & Maintains database connection across program life cycle
• Migration handed by Goose
• Model generation handled by SQLBoiler

How to use

Prerequisites

SQLBoiler

go install github.com/thrasher-corp/sqlboiler

Postgres Driver

go install github.com/thrasher-corp/sqlboiler/drivers/sqlboiler-psql

SQLite Driver

go install github.com/thrasher-corp/sqlboiler-sqlite3

Configuration

The database configuration struct is currently:

type Config struct {
	Enabled                   bool   `json:"enabled"`
	Verbose                   bool   `json:"verbose"`
	Driver                    string `json:"driver"`
	drivers.ConnectionDetails `json:"connectionDetails"`
}

And Connection Details:

type ConnectionDetails struct {
	Host     string `json:"host"`
	Port     uint16 `json:"port"`
	Username string `json:"username"`
	Password string `json:"password"`
	Database string `json:"database"`
	SSLMode  string `json:"sslmode"`
}

With an example configuration being:

 "database": {
  "enabled": true,
  "verbose": true,
  "driver": "postgres",
  "connectionDetails": {
   "host": "localhost",
   "port": 5432,
   "username": "gct-dev",
   "password": "gct-dev",
   "database": "gct-dev",
   "sslmode": "disable"
  }
 },

Create and Run migrations

Migrations are created using a modified version of Goose

A helper tool sits in the ./cmd/dbmigrate folder that includes the following features:

• Check current database version with the "status" command

dbmigrate -command status

• Create a new migration

dbmigrate -command "create" -args "model"

This will create a folder in the ./database/migration folder that contains postgres.sql and sqlite.sql files

• Run dbmigrate command with -command up

dbmigrate -command "up"

dbmigrate provides a -migrationdir flag override to tell it what path to look in for migrations

Note: its highly recommended to backup any data before running migrations against a production database especially if you are running SQLite due to alter table limitations

Adding a new model

Model's are generated using SQLBoiler A helper tool has been made located in gen_sqlboiler_config that will parse your GoCryptoTrader config and output a SQLBoiler config

gen_sqlboiler_config

By default this will look in your gocryptotrader data folder and default config, these can be overwritten along with the location of the sqlboiler generated config

-config "configname.json"
-datadir "~/.gocryptotrader/"
-outdir "~/.gocryptotrader/"

Generate a new model that gets placed in ./database/models/ folder

Linux:

sqlboiler -o database/models/postgres -p postgres --no-auto-timestamps --wipe psql 

Windows:

sqlboiler -o database\\models\\postgres -p postgres --no-auto-timestamps --wipe psql

Helpers have been provided in the Makefile for linux users

make gen_db_models

And in the contrib/sqlboiler.cmd for windows users

Adding a Repository

• Create Repository directory in github.com/aaabigfish/gocryptotrader/database/repository/

DBSeed helper

A helper tool cmd/dbseed has been created for assisting with data migration

Contribution

Please feel free to submit any pull requests or suggest any desired features to be added.

When submitting a PR, please abide by our coding guidelines:

• Code must adhere to the official Go formatting guidelines (i.e. uses gofmt).
• Code must be documented adhering to the official Go commentary guidelines.
• Code must adhere to our coding style.
• Pull requests need to be based on and opened against the master branch.

Donations

If this framework helped you in any way, or you would like to support the developers working on it, please donate Bitcoin to:

bc1qk0jareu4jytc0cfrhr5wgshsq8282awpavfahc

Documentation

Index

• Constants
• Variables
• type Config
• type IDatabase
• type ISQL
• type Instance
  • func (i *Instance) CloseConnection() error
  • func (i *Instance) GetConfig() *Config
  • func (i *Instance) GetSQL() (*sql.DB, error)
  • func (i *Instance) IsConnected() bool
  • func (i *Instance) Ping() error
  • func (i *Instance) SetConfig(cfg *Config) error
  • func (i *Instance) SetConnected(v bool)
  • func (i *Instance) SetPostgresConnection(con *sql.DB) error
  • func (i *Instance) SetSQLiteConnection(con *sql.DB) error
• type Logger
  • func (l Logger) Write(p []byte) (n int, err error)

Constants

const (
	// DBSQLite const string for sqlite across code base
	DBSQLite = "sqlite"
	// DBSQLite3 const string for sqlite3 across code base
	DBSQLite3 = "sqlite3"
	// DBPostgreSQL const string for PostgreSQL across code base
	DBPostgreSQL = "postgres"
	// DBInvalidDriver const string for invalid driver
	DBInvalidDriver = "invalid driver"
)

Variables

var (
	// DB Global Database Connection
	DB = &Instance{}
	// MigrationDir which folder to look in for current migrations
	MigrationDir = filepath.Join("..", "..", "database", "migrations")
	// ErrNoDatabaseProvided error to display when no database is provided
	ErrNoDatabaseProvided = errors.New("no database provided")
	// ErrDatabaseSupportDisabled error to display when no database is provided
	ErrDatabaseSupportDisabled = errors.New("database support is disabled")
	// SupportedDrivers slice of supported database driver types
	SupportedDrivers = []string{DBSQLite, DBSQLite3, DBPostgreSQL}
	// ErrFailedToConnect for when a database fails to connect
	ErrFailedToConnect = errors.New("database failed to connect")
	// ErrDatabaseNotConnected for when a database is not connected
	ErrDatabaseNotConnected = errors.New("database is not connected")
	// DefaultSQLiteDatabase is the default sqlite3 database name to use
	DefaultSQLiteDatabase = "gocryptotrader.db"
	// ErrNilInstance for when a database is nil
	ErrNilInstance = errors.New("database instance is nil")
	// ErrNilConfig for when a config is nil
	ErrNilConfig = errors.New("received nil config")
)

Types

type Config

type Config struct {
	Enabled                   bool   `json:"enabled"`
	Verbose                   bool   `json:"verbose"`
	Driver                    string `json:"driver"`
	drivers.ConnectionDetails `json:"connectionDetails"`
}

Config holds all database configurable options including enable/disabled & DSN settings

type IDatabase

type IDatabase interface {
	IsConnected() bool
	GetSQL() (*sql.DB, error)
	GetConfig() *Config
}

IDatabase allows for the passing of a database struct without giving the receiver access to all functionality

type ISQL

type ISQL interface {
	BeginTx(context.Context, *sql.TxOptions) (*sql.Tx, error)
	Exec(string, ...interface{}) (sql.Result, error)
	Query(string, ...interface{}) (*sql.Rows, error)
	QueryRow(string, ...interface{}) *sql.Row
	ExecContext(context.Context, string, ...interface{}) (sql.Result, error)
	QueryContext(context.Context, string, ...interface{}) (*sql.Rows, error)
	QueryRowContext(context.Context, string, ...interface{}) *sql.Row
}

ISQL allows for the passing of a SQL connection without giving the receiver access to all functionality

type Instance

type Instance struct {
	SQL      *sql.DB
	DataPath string
	// contains filtered or unexported fields
}

Instance holds all information for a database instance

func (*Instance) CloseConnection

func (i *Instance) CloseConnection() error

CloseConnection safely disconnects the global database instance

func (*Instance) GetConfig

func (i *Instance) GetConfig() *Config

GetConfig safely returns a copy of the config

func (*Instance) GetSQL

func (i *Instance) GetSQL() (*sql.DB, error)

GetSQL returns the sql connection

func (*Instance) IsConnected

func (i *Instance) IsConnected() bool

IsConnected safely checks the SQL connection status

func (*Instance) Ping

func (i *Instance) Ping() error

Ping pings the database

func (*Instance) SetConfig

func (i *Instance) SetConfig(cfg *Config) error

SetConfig safely sets the global database instance's config with some basic locks and checks

func (*Instance) SetConnected

func (i *Instance) SetConnected(v bool)

SetConnected safely sets the global database instance's connected status

func (*Instance) SetPostgresConnection

func (i *Instance) SetPostgresConnection(con *sql.DB) error

SetPostgresConnection safely sets the global database instance's connection to use Postgres

func (*Instance) SetSQLiteConnection

func (i *Instance) SetSQLiteConnection(con *sql.DB) error

SetSQLiteConnection safely sets the global database instance's connection to use SQLite

type Logger

type Logger struct{}

Logger implements io.Writer interface to redirect SQLBoiler debug output to GCT logger

func (Logger) Write

func (l Logger) Write(p []byte) (n int, err error)

Write takes input and sends to GCT logger