sqldb

package module
v2.5.1 Latest Latest
Warning

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

 Go to latest Published: Mar 28, 2023 License: MIT Imports: 16 Imported by: 17

README

Introduction:

Package sqldb provides tooling for establishing and managing a database connection, deploying and updating schemas, and running queries. This wraps the sql package to reduce some boilerplate while providing tools for schema and query management.

Install:

Assuming you are using go modules the latest version can be installed with:

go get github.com/c9845/sqldb/v2

Then use in your code with:

import "github.com/c9845/sqldb/v2

And use via:

sqldb.New...()
sqldb.Connect()
//...

Details:

  • Supports MySQL, MariaDB, and SQLite (with and without CGO). Additional database support should be relatively easy to add.
  • Define a database connection configuration and manage the connection pool for running queries.
  • Deploy and update database schema.
  • Works either by storing the connection details within the package in a global manner or you can store the details separately elsewhere in your app. Storing the details separately allows for multiple different databases to be connected to at the same time.

Getting Started:

You first need to define a configuration using New...Config or Default...Config based on if you want to store the configuration yourself or store it within this package. You can also define the configuration yourself using sqldb.Config{}.

Once you have a configuration defined, you can connect to your database using Connect() which will validate your configuration and then try connecting. If you want to deploy or update your database schema, call DeploySchema() or UpdateSchema() before connecting.

Now with an established connection, run queries using your config in a manner such as myconfig.Connection().Exec().

Deploying and Updating Schema:

This works by providing a list of queries to run in your config. When the appropriate function is called, the queries will be run in order.

Queries used to deploy a database can optionally be translated from one database format to another (i.e.: MySQL to SQLite). This is useful since different database types structure their CREATE TABLE queries slightly differently but it would be a lot of extra work to maintain a separate set of queries for each database type. This works by doing a string replacement, and some more modifications, to queries so that you can write the queries in one database's format and still deploy to multiple database types.

Queries used to update the database are checked for safely ignorable errors. This is useful for instances where you rerun the UpdateSchema function (think, on each app start to ensure the schema is up to date) and want to ignore errors such as when a column already exists or was already removed.

SQLite Libraries:

You can use github.com/mattn/go-sqlite3 or modernc.org/sqlite. The mattn library requires CGO which makes cross compiling a bit more difficult, however, it does use the SQLite C source code. The modernc library is a C to Go translation of the SQLite source, which while it doesn't required CGO, it isn't the original SQLite source code.

Simply put: if you are worried about cross compiling, using modernc is the way to go.

Use either library by providing the associated build tag (mattn is the default if no tag is provided):

go build -tags mattn ...
go build -tags modernc ...
go run -tags mattn ...
go run -tags modernc ...

Documentation

Overview

Package sqldb provides some tooling to make connecting to, deploying, updating, and using a SQL database easier. This provides some wrapping around the sql package. This package was written to reduce the amount of boilerplate code to connect, deploy, or update a database.

NOTE! Microsoft SQL Server support is very untested!

Global or Local DB Connection

You can use this package in two methods: as a singleton with the database configuration and connection stored in a package-level globally-accessible variable, or store the configuration and connection somewhere else in your application. Storing the data yourself allows for connecting to multiple databases at once.

Deploying a Database

Deploying of a schema is done via queries stored in your configuration's DeployQueries and DeployFuncs fields. These queries and funcs are run when DeploySchema() is called. DeploySchema() will create the database, if needed, before deploying tables, creating indexes, inserting initial data, etc. Each DeployQuery is run before the DeployFuncs.

DeployQueries are typically used for creating tables or indexes. DeployFuncs are typically used for more complex operations such as inserting initial data only if no data already exists or handling a complicated schema change based on existing values. When writing DeployQueries or DeployFuncs you should ensure the result of each is indempotent; rerunning DeployQueries or DeployFuncs over and over should be successful without returning errors about "already exists" or creating or inserting duplicate records.

An advanced tool of deployment is TranslateDeployCreateTableFuncs. Funcs listed here are used to translate a CREATE TABLE query from one database format to another (i.e.: MySQL to SQLite). This allows you to write your CREATE TABLE queries in one database format but allow running your application with a database deployed in multiple formats; it just makes writing CREATE TABLE queries a bit easier. This translation is necessary since different databases have different CREATE TABLE query formats or column types ( for example, SQLite doesn't really have VARCHAR). Each DeployQuery is run through each TranslateDeployCreateTableFunc with translation of the query performed as needed.

Updating a Schema

Updating a database schema happens in a similar manner to deploying, a list of queries in UpdateQueries and UpdateFuncs is run against the database. All queries are run encapsulated in a single transaction so that either the updated is successful, or none of updates are applied. This is done to eliminate the possibility of a partial update.

Each UpdateQuery is run through a list of error analyzer functions, UpdateIgnoreErrorFuncs, to determine if an error can be ignored. This is typically used to ignore errors for when you are adding a column that already exists, removing a column that is already removed, etc. These funcs just help ignore errors that aren't really errors.

SQLite Library

This package support two SQLite libraries, mattn/go-sqlite3 and modernc/sqlite. The mattn library encapsulates the SQLite C source code and requires CGO for compilation which can be troublesome for cross-compiling. The modernc library is an automatic translation of the C source code to golang, however, it isn't the "source" SQLite C code and therefore doesn't have the same level of trustworthiness or extent of testing, however, it can be cross-compiled much more easily.

As of now, mattn is the default if no build tags are provided. This is simply due to the longer history of this library being available and the fact that this uses the SQLite C source code.

Use either library with build tags: 

go build -tags mattn ...
go build -tags modernc ...
go run -tags mattn ...
go run -tags modernc ...

The mattn/go-sqlite3 library sets some default PRAGMA values, as noted in the source code at https://github.com/mattn/go-sqlite3/blob/ae2a61f847e10e6dd771ecd4e1c55e0421cdc7f9/sqlite3.go#L1086. Some of these are just safe defaults, for example, busy_timeout. In order to treat the mattn/go-sqlite3 and modernc/sqlite more similarly, some of these mattn/go-sqlite3 default PRAGMAs are set when using the modernc/sqlite library. This is simply done to make using either SQLite library more comparibly interchangable.

Notes

This package uses "sqlx" instead of the go standard library "sql" package because "sqlx" provides some additional tooling which makes using the database a bit easier (i.e.: Get(), Select(), and StructScan() that can thus be in queries).

You should design your queries (DeployQueries, DeployFuncs, UpdateQueries, UpdateFuncs) so that deploying or updating the schema is safe to rerun multiple times. You do not want issues to occur if a user interacting with yourapp somehow tries to deploy the database over and over or update it after it has already been updated. For example, use "IF NOT EXISTS" with creating tables or indexes.

Index

Constants

View Source 
const (

	//InMemoryFilePathRacy is the "path" to provide for the SQLite file when you want
	//to use an in-memory database instead of a filesystem file database. This is racy
	//because each "Connect" call to :memory: will open a brand new database.
	//
	//This is good for running tests since then each test runs with a separate
	//in-memory db.
	InMemoryFilePathRacy = ":memory:"

	//InMemoryFilePathRaceSafe is the "path" to provide for the SQLite file when you
	//want to use an in-memory database between multiple "Connect" calls. This is race
	//safe since multiple calls of "Connect" will connect to the same in-memory db,
	//although connecting more than once to the same db would be very odd.
	InMemoryFilePathRaceSafe = "file::memory:?cache=shared"
)

defaults

View Source 
const (
	DBTypeMySQL   = dbType("mysql")
	DBTypeMariaDB = dbType("mariadb")
	DBTypeSQLite  = dbType("sqlite")
	DBTypeMSSQL   = dbType("mssql")
)
View Source 
const (
	LogLevelNone  logLevel = iota //no logging.
	LogLevelError                 //general errors, most typical use/
	LogLevelInfo                  //some info on db connections, deployment, updates.
	LogLevelDebug                 //primarily development related.
)

Variables

View Source 
var (
	//ErrConnected is returned when a trying to establish a connection to an already
	//connected-to database.
	ErrConnected = errors.New("sqldb: connection already established")

	//ErrSQLitePathNotProvided is returned when user doesn't provided a path to the
	//SQLite database file, or the path provided is all whitespace.
	ErrSQLitePathNotProvided = errors.New("sqldb: SQLite path not provided")

	//ErrHostNotProvided is returned when user doesn't provide the host IP or FQDN
	//of a MySQL or MariaDB server.
	ErrHostNotProvided = errors.New("sqldb: database server host not provided")

	//ErrInvalidPort is returned when user doesn't provide, or provided an invalid
	//port, of a MySQL or MariaDB server.
	ErrInvalidPort = errors.New("sqldb: database server port invalid")

	//ErrNameNotProvided is returned when user doesn't provide a name of a database.
	ErrNameNotProvided = errors.New("sqldb: database name not provided")

	//ErrUserNotProvided is returned when user doesn't provide a user to connect to
	//the database server with.
	ErrUserNotProvided = errors.New("sqldb: database user not provided")

	//ErrPasswordNotProvided is returned when user doesn't provide the password to
	//connect to the database with. Blank passwords are not supported for security.
	ErrPasswordNotProvided = errors.New("sqldb: password for database user not provided")

	//ErrNoColumnsGiven is returned when user is trying to build a column list for a
	//query but no columns were provided.
	ErrNoColumnsGiven = errors.New("sqldb: no columns provided")

	//ErrExtraCommaInColumnString is returned when building a column string for a
	//query but an extra comma exists which would cause the query to not run correctly.
	//Extra commas are usually due to an empty column name being provided or a comma
	//being added to the column name by mistake.
	ErrExtraCommaInColumnString = errors.New("sqldb: extra comma in column name")

	//ErrInvalidLoggingLevel is returned when an invalid logging level is provided.
	ErrInvalidLoggingLevel = errors.New("sqldb: invalid logging level")
)

errors

Functions

func AddConnectionOption added in v2.4.0 

func AddConnectionOption(key, value string)

AddConnectionOption adds a key-value pair to the ConnectionOptions field for the package level config.

func Close 

func Close() (err error)

Close closes the connection using the default package level config.

func Connect 

func Connect() (err error)

Connect handles the connection to the database using the default package level config.

func Connected 

func Connected() bool

Connected returns if the config represents an established connection to the database.

func Connection 

func Connection() *sqlx.DB

Connection returns the database connection for the package level config.

func DBType 

func DBType(s string) dbType

DBType returns a dbType. This is used when parsing a user-provided database type (such as from a configuration file) to convert to a db type defined in this package.

func DefaultConfig 

func DefaultConfig(t dbType) (err error)

DefaultConfig initializes the globally accessible package level config with some defaults set. Typically you would use Default...Config() instead.

func DefaultMSSQLConfig added in v2.3.1 

func DefaultMSSQLConfig(host string, port uint, name, user, password string)

DefaultMSSQLConfig initializes the globally accessible package level config with some defaults set.

func DefaultMapperFunc 

func DefaultMapperFunc(s string) string

DefaultMapperFunc is the default MapperFunc set on configs. It returns the column names unmodified.

func DefaultMariaDBConfig 

func DefaultMariaDBConfig(host string, port uint, name, user, password string)

DefaultMariaDBConfig initializes the globally accessible package level config with some defaults set.

func DefaultMySQLConfig 

func DefaultMySQLConfig(host string, port uint, name, user, password string)

DefaultMySQLConfig initializes the globally accessible package level config with some defaults set.

func DefaultSQLiteConfig 

func DefaultSQLiteConfig(pathToFile string)

DefaultSQLiteConfig initializes the globally accessible package level config with some defaults set.

func DeploySchema 

func DeploySchema() (err error)

DeploySchema runs DeploySchemaWithOps with some defaults set for the default package level config.

func DeploySchemaWithOps 

func DeploySchemaWithOps(ops DeploySchemaOptions) (err error)

DeploySchemaWithOps deploys the database for the default package level config.

func GetDefaultSQLitePragmas 

func GetDefaultSQLitePragmas() []string

GetDefaultSQLitePragmas returns the default PRAGMAs this package defines for use with either SQLite library. This can be helpful for debugging. We don't just export the sqliteDefaultPragmas slice so that it cannot be modified.

func GetSQLiteLibrary 

func GetSQLiteLibrary() string

GetSQLiteLibrary returns the SQLite library that was used to build the binary. The library is set at build/run with -tags {mattn || modernc}.

func GetSQLiteVersion 

func GetSQLiteVersion() (version string, err error)

GetSQLiteVersion returns the version of SQLite that is embedded into the app. This works by creating a temporary in-memory SQLite database to run a query against. We don't use the config or an already established connection because we may want to get the SQLiter version before a database is connected to!

func IsMSSQL added in v2.3.1 

func IsMSSQL() bool

IsMSSQL returns true if the database is a Microsoft SQL Server database. This is easier than checking for equality against the Type field in the config.

func IsMariaDB 

func IsMariaDB() bool

IsMariaDB returns true if the database is a MariaDB database. This is easier than checking for equality against the Type field in the config.

func IsMySQL 

func IsMySQL() bool

IsMySQL returns true if the database is a MySQL database. This is easier than checking for equality against the Type field in the config.

func IsMySQLOrMariaDB 

func IsMySQLOrMariaDB() bool

IsMySQLOrMariaDB returns if the database is a MySQL or MariaDB for the package level config.

func IsSQLite 

func IsSQLite() bool

IsSQLite returns true if the database is a SQLite database. This is easier than checking for equality against the Type field in the config.

func MapperFunc 

func MapperFunc(m func(string) string)

MapperFunc sets the mapper func for the package level config.

func Save 

func Save(cfg Config)

Save saves a configuration to the package level config. Use this in conjunction with New...Config(), or just sqldb.Config{}, when you want to heavily customize the config. This is not a method on Config so that any modifications done to the original config after Save() is called aren't propagated to the package level config without calling Save() again.

func TFMySQLToSQLiteBLOB added in v2.4.0 

func TFMySQLToSQLiteBLOB(in string) (out string)

TFMySQLToSQLiteBLOB translates TINYBLOB, MEDIUMBLOB, and LONGBLOB to BLOB.

func TFMySQLToSQLiteReformatDatetime 

func TFMySQLToSQLiteReformatDatetime(in string) (out string)

TFMySQLToSQLiteReformatDatetime replaces DATETIME columns with TEXT columns. SQLite doesn't have a DATETIME column type so values stored in these columns can be converted oddly. Just use TEXT column type for SQLite for ease of use.

The mattn/go-sqlite3 library converts DATETIME columns in yyyy-mm-dd hh:mm:ss format to yyyy-mm-ddThh:mm:ssZ upon returning values (via a SELECT query) which is unexpected since that is is not what is stored in the database when using the sqlite3 command

func TFMySQLToSQLiteReformatDefaultTimestamp 

func TFMySQLToSQLiteReformatDefaultTimestamp(in string) (out string)

TFMySQLToSQLiteReformatDefaultTimestamp handles converting UTC_TIMESTAMP values to CURRENT_TIMESTAMP values. On MySQL and MariaDB, both UTC_TIMESTAMP and CURRENT_TIMESTAMP values exist, with CURRENT_TIMESTAMP returning a datetime in the server's local timezone. However, SQLite doesn't have UTC_TIMESTAMP and CURRENT_TIMESTAMP is different, it returns values in UTC timezone.

func TFMySQLToSQLiteReformatID 

func TFMySQLToSQLiteReformatID(in string) (out string)

TFMySQLToSQLiteReformatID reformats the ID column from a MySQL format to a SQLite format.

func TFMySQLToSQLiteRemovePrimaryKeyDefinition 

func TFMySQLToSQLiteRemovePrimaryKeyDefinition(in string) (out string)

TFMySQLToSQLiteRemovePrimaryKeyDefinition removes the primary key definition from a MySQL query for use in SQLite. SQLite doesn't use this PRIMARY KEY(ID), the PRIMARY KEY note is assigned as part of the column definition. We also have to remove the comma preceeding this line too since a trailing comma creates a bad query!

func UFAddDuplicateColumn 

func UFAddDuplicateColumn(c Config, query string, err error) bool

UFAddDuplicateColumn checks if an error was generated because a column already exists. This typically happens because you are rerunning UpdateSchema() and the column has already been added. This error can be safely ignored since a duplicate column won't be create.

func UFDropUnknownColumn 

func UFDropUnknownColumn(c Config, query string, err error) bool

UFDropUnknownColumn checks if an error from was generated because a column does not exist. This typically happens because you are rerunning UpdateSchema() and the column has already been dropped. This error can be safely ignored in most cases.

func UFIndexAlreadyExists added in v2.2.0 

func UFIndexAlreadyExists(c Config, query string, err error) bool

UFIndexAlreadyExists handles errors when an index already exists. If you use "IF NOT EXISTS" in your query to add a column or index this function will not be used since IF NOT EXISTS doesn't return an error if the item already exists.

func UFModifySQLiteColumn 

func UFModifySQLiteColumn(c Config, query string, err error) bool

UFModifySQLiteColumn checks if an error occured because you are trying to modify a column for a SQLite database. SQLite does not allow modifying columns. In this case, we just ignore the error. This is ok since SQLite allows you to store any type of value in any column.

To get around this error, you should create a new table with the new schema, copy the old data to the new table, delete the old table, and rename the new table to the old table.

func UpdateSchema 

func UpdateSchema() (err error)

UpdateSchema runs UpdateSchemaWithOps with some defaults set for the default package level config. This was implemented to to support legacy compatibility while expanding the feature set with update options.

func UpdateSchemaWithOps 

func UpdateSchemaWithOps(ops UpdateSchemaOptions) (err error)

UpdateSchemaWithOps updates the database for the default package level config.

func UseDefaultTranslateFuncs added in v2.4.0 

func UseDefaultTranslateFuncs()

UseDefaultTranslateFuncs populates TranslateCreateTableFuncs and TranslateUpdateFuncs with the default translation funcs for the package level config.

Types

type Bindvars 

type Bindvars []interface{}

Bindvars holds the parameters you want to use in a query. This helps in organizing a query you are building. You can use the values stored in this slice when running a query by providing Bindvars... (ex.: c.Get(&var, q, b...) or stmt.Exec(b...). This is typically used when building complex queries and in conjunction with the Columns type.

type Columns 

type Columns []string

Columns is used to hold columns for a query. This helps in organizing a query you are building and is useful for generating the correct placeholders when needed using the ForSelect(), ForUpdate(), ForInsert() funcs.

Ex: 

cols := Columns{
	"Fname",
	"Birthday",
 "CompanyID",
}

colString, valString, _ := cols.ForInsert //colString will be "Fname,Birthday,CompanyID" //valString will be "?,?,?" Use like: "INSERT INTO users (" + colString + ") VALUES (" + valString + ")"

func (Columns) ForInsert 

func (cols Columns) ForInsert() (colString, valString string, err error)

ForInsert builds the column string for an INSERT query and also returns the placholder VALUES() string you should use.

func (Columns) ForSelect 

func (cols Columns) ForSelect() (colString string, err error)

ForSelect builds the column string for a SELECT query.

func (Columns) ForUpdate 

func (cols Columns) ForUpdate() (colString string, err error)

ForUpdate builds the column string for an UPDATE query.

type Config 

type Config struct {
	//Type represents the type of database to use.
	Type dbType

	//Connection information for a non-SQLite database.
	Host     string
	Port     uint
	Name     string
	User     string
	Password string

	//ConnectionOptions is a list of key-value pairs of options used when building
	//the connection string used to connect to a database. Each driver/database type
	//will handle these differently. Use AddConnectionOption() instead of having to
	//do Config.ConnectionOptions = map[string]string{"key", "value"}.
	ConnectionOptions map[string]string

	//SQLitePath is the path where the SQLite database file is located.
	SQLitePath string

	//SQLitePragmas is a list of PRAGMA queries to run when connecting to a SQLite
	//database. Typically this is used to set the journal mode or busy timeout. PRAGMAs
	//provided here are in SQLite format with an equals sign (ex.: PRAGMA busy_timeout=5000).
	//
	//Both the mattn/go-sqlite3 and modernc/sqlite packages allow setting of PRAGMAs
	//in the database filename path. See the below links. PRAGMA statements here will
	//be appended (after translating to the correct package's format) to the SQLitePath
	//so that the PRAGMAs are set properly for the database upon initially connecting.
	//
	//Setting PRAGMAs via a query cannot be trusted; the sql package provides a
	//connection pool and PRAGMAs are set per connection and you cannot be guaranteed
	//to get the same connection to resuse again.
	//
	//https://github.com/mattn/go-sqlite3#connection-string)
	//https://pkg.go.dev/modernc.org/sqlite#Driver.Open
	SQLitePragmas []string

	//MapperFunc is used to override the mapping of database column names to struct
	//field names or struct tags. Mapping of column names is used during queries where
	//sqlx's StructScan(), Get(), or Select() is used.
	//
	//By default, column names are not modified in any manner. This is in contrast to
	//the default for sqlx where column names are returned as all lower case which
	//requires your structs to use struct tags for each field. By not modifying column
	//names you will not need to use struct tags since column names can exactly match
	//exportable struct field names.
	//
	//http://jmoiron.github.io/sqlx/#:~:text=You%20can%20use%20the%20db%20struct%20tag%20to%20specify%20which%20column%20name%20maps%20to%20each%20struct%20field%2C%20or%20set%20a%20new%20default%20mapping%20with%20db.MapperFunc().%20The%20default%20behavior%20is%20to%20use%20strings.Lower%20on%20the%20field%20name%20to%20match%20against%20the%20column%20names.
	MapperFunc func(string) string

	//DeployQueries is a list of queries used to deploy the database schema. These
	//queries typically create tables or indexes or insert initial data into the
	//database. The queries listed here will be executed in order when DeploySchema()
	//is called. Make sure the order of the queries listed here makes sense for your
	//foreign key relationships! Each query should be safe to rerun multiple times!
	DeployQueries []string

	//DeployFuncs is a list of functions used to deploy the database. Use this for more
	//complicated deployment queries than the queries provided in DeployQueries. These
	//funcs get executed after all DeployQueries and should be used much more sparsely
	//compared to DeployQueries. Each func should be safe to rerun multiple times!
	DeployFuncs []DeployFunc

	//UpdateQueries is a list of queries used to update the database schema. These
	//queries typically add new columns, alter a column's type, or drop a column.
	//The queries listed here will be executed in order when UpdateSchema() is called.
	//Each query should be safe to rerun multiple times!
	UpdateQueries []string

	//UpdateFuncs is a list of functions used to deploy the database. Use this for more
	//complicated updates to the database schema or values stored within the database.
	//These funcs get executed after all UpdateQueries and should be used much more
	//sparsely compared to UpdateQueries. Each func should be safe to rerun multiple
	//times!
	UpdateFuncs []UpdateFunc

	//UpdateIgnoreErrorFuncs is a list of functions run when an UpdateQuery results in
	//an error and determins if the error can be ignored. This is used to ignore errors
	//for queries that aren't actual errors (ex.: adding a column that already exists).
	//Each func in this list should be very narrowly focused, checking both the query
	//and error, so that real errors aren't ignored by mistake.
	//
	//Some default funcs are predefined. See funcs in this package starting with UF...
	UpdateIgnoreErrorFuncs []UpdateIgnoreErrorFunc

	//TranslateCreateTableFuncs is a list of functions run against each DeployQuery
	//or UpdateQuery that contains a CREATE TABLE clause that modifies the query to
	//translate it from one database format to another. This tooling is used so that
	//you can write your CREATE TABLE queries in one database format (ex.: MySQL) but
	//deploy your database in multiple formats (ex.: MySQL & SQLite).
	//
	//A list of default funcs are predefined. See funcs in this package starting with
	//TF...
	TranslateCreateTableFuncs []func(string) string

	//TranslateUpdateFuncs is a list of functions run against each UpdateQuery that
	//modifies the query to translate it from one database format to another. See
	//TranslateCreateTableFuncs for more info.
	TranslateUpdateFuncs []func(string) string

	//LoggingLevel enables logging at ERROR, INFO, or DEBUG levels.
	LoggingLevel logLevel
	// contains filtered or unexported fields
}

Config is the details used for establishing and using a database connection.

func GetDefaultConfig 

func GetDefaultConfig() *Config

GetDefaultConfig returns the package level saved config.

func NewConfig 

func NewConfig(t dbType) (cfg *Config, err error)

NewConfig returns a base configuration that will need to be modified for use to connect to and interact with a database. Typically you would use New...Config() instead.

func NewMSSQLConfig added in v2.3.1 

func NewMSSQLConfig(host string, port uint, name, user, password string) (cfg *Config)

NewMSSQLConfig returns a config for connecting to a Microsoft SQL Server database.

func NewMariaDBConfig 

func NewMariaDBConfig(host string, port uint, name, user, password string) (cfg *Config)

NewMariaDBConfig returns a config for connecting to a MariaDB database.

func NewMySQLConfig 

func NewMySQLConfig(host string, port uint, name, user, password string) (cfg *Config)

NewMySQLConfig returns a config for connecting to a MySQL database.

func NewSQLiteConfig 

func NewSQLiteConfig(pathToFile string) (cfg *Config)

NewSQLiteConfig returns a config for connecting to a SQLite database.

func (*Config) AddConnectionOption added in v2.4.0 

func (cfg *Config) AddConnectionOption(key, value string)

AddConnectionOption adds a key-value pair to a config's ConnnectionOptions field. Using this func is just easier then calling map[string]string{"key", "value"}. This does not check if the key already exist, it will simply add a duplicate key-value pair.

func (*Config) Close 

func (cfg *Config) Close() (err error)

Close closes the connection to the database.

func (*Config) Connect 

func (cfg *Config) Connect() (err error)

Connect connects to the database. This establishes the database connection, and saves the connection pool for use in running queries. For SQLite this also runs any PRAGMA commands.

func (*Config) Connected 

func (cfg *Config) Connected() bool

Connected returns if the config represents an established connection to the database.

func (*Config) Connection 

func (cfg *Config) Connection() *sqlx.DB

Connection returns the database connection stored in a config for use in running queries.

func (*Config) DeploySchema 

func (cfg *Config) DeploySchema() (err error)

DeploySchema runs DeploySchemaWithOps with some defaults set.

func (*Config) DeploySchemaWithOps 

func (cfg *Config) DeploySchemaWithOps(ops DeploySchemaOptions) (err error)

DeploySchemaWithOps deploys the database schema by running the list of DeployQueries and DeployFuncs defined in config. This will create the database if needed. This is typically used to deploy an empty, or near empty, database. A database connection must not already be established; this func will establish the connection.

Although each DeployQuery and DeployFunc should be indempotent (ex.: using CREATE TABLE IF NOT EXISTS), you should still not call this func each time your app starts or otherwise. Typically you would check if the database already exists or use a flag, such as --deploy-db, to run this func.

func (*Config) IsMSSQL added in v2.3.1 

func (cfg *Config) IsMSSQL() bool

IsMSSQL returns true if the database is a Microsoft SQL Server database. This is easier than checking for equality against the Type field in the config.

func (*Config) IsMariaDB 

func (cfg *Config) IsMariaDB() bool

IsMariaDB returns true if the database is a MariaDB database. This is easier than checking for equality against the Type field in the config.

func (*Config) IsMySQL 

func (cfg *Config) IsMySQL() bool

IsMySQL returns true if the database is a MySQL database. This is easier than checking for equality against the Type field in the config.

func (*Config) IsMySQLOrMariaDB 

func (cfg *Config) IsMySQLOrMariaDB() bool

IsMySQLOrMariaDB returns if the database is a MySQL or MariaDB. This is useful since MariaDB is a fork of MySQL and most things are compatible; this way you don't need to check IsMySQL() and IsMariaDB().

func (*Config) IsSQLite 

func (cfg *Config) IsSQLite() bool

IsSQLite returns true if the database is a SQLite database. This is easier than checking for equality against the Type field in the config.

func (*Config) SQLitePragmasAsString added in v2.3.1 

func (cfg *Config) SQLitePragmasAsString() (filenamePragmaString string)

SQLitePragmasAsString builds the string of pragmas that should be appended to the filename when connecting to a SQLite database. This is needed to set pragmas reliably since pragmas must be set upon initially connecting to the database. The difficulty in setting pragmas is that each SQLite library (mattn vs modernc) has a slighly different format for setting pragmas. This takes the list of pragmas in SQLite query format (PRAGMA busy_timeout = 5000) and translates them to the correct format for the SQLite library in use.

func (*Config) UpdateSchema 

func (cfg *Config) UpdateSchema() (err error)

UpdateSchema runs UpdateSchemaWithOps with some defaults set. This was implemented to support legacy compatibility while expanding the feature set with update options.

func (*Config) UpdateSchemaWithOps 

func (cfg *Config) UpdateSchemaWithOps(ops UpdateSchemaOptions) (err error)

UpdateSchemaWithOps updates a database by running the list of UpdateQueries and UpdateFuncs defined in config. This is typically used to add new colums, alter columns, add indexes, or updates values stored in the database.

Although each UpdateQuery and DeployFunc should be indempotent, you should still not call this func each time your app starts or otherwise. Typically you would check if the database has already been updated or use a flag, such as --update-db, to run this func.

When each UpdateQuery is run, if an error occurs the error is passed into each defined UpdateIgnoreErrorFuncs to determine if and how the error needs to be handled. Sometimes an error during a schema update isn't actually an error we need to handle, such as adding a column that already exists. Most times these types of errors occur because the UpdateSchema func is being rerun. The list of funcs you add to UpdateIgnoreErrorFuncs will check the returned error message and query and determine if the error can be ignored.

func (*Config) UseDefaultTranslateFuncs added in v2.4.0 

func (cfg *Config) UseDefaultTranslateFuncs()

UseDefaultTranslateFuncs populates TranslateCreateTableFuncs and TranslateUpdateFuncs with the default translation funcs.

type DeployFunc 

type DeployFunc func(*sqlx.DB) error

DeployFunc is the format of a function used to deploy the database schema. The type is defined for easier use when defining the list of DeployFuncs versus having to type "cfg.DeployFuncs = []func(*sqlx.DB) error {...}".

type DeploySchemaOptions 

type DeploySchemaOptions struct {
	CloseConnection bool
}

DeploySchemaOptions provides options when deploying a schema.

CloseConnection determines if the database connection should be closed after this func successfully completes. This was added to support SQLite in-memory databases since each connection to an im-memory db uses a new database, so if we deploy with a connection we need to reuse it to run queries.

type UpdateFunc 

type UpdateFunc func(*sqlx.Tx) error

UpdateFunc is the format of a function used to update the database schema. The type is defined for easier use when defining the list of UpdateFuncs versus having to type "cfg.UpdateFuncs = []func(*sqlx.Tx) error {...}".

type UpdateIgnoreErrorFunc 

type UpdateIgnoreErrorFunc func(Config, string, error) bool

UpdateIgnoreErrorFunc is function for handling errors returned when trying to update the schema of your database using UpdateSchema(). The query being run, as well as the error from running the query, are passed in so that the function can determine if this error can be ignored for this query. Each function of this type, and used for this purpose should be very narrowly focused so as not to ignore errors by mistake (false positives).

type UpdateSchemaOptions 

type UpdateSchemaOptions struct {
	CloseConnection bool
}

UpdateSchemaOptions provides options when updating a schema.

CloseConnection determines if the database connection should be closed after ths func successfully completes. This was added to support SQLite in-memory databases since each connection to an im-memory db uses a new database, so if we deploy with a connection we need to reuse it to run queries.

type Where 

type Where string

Where is the WHERE statement in a query. This separate type is useful for times when you are passing a WHERE clause into a func and you want a bit more control over what is provided.

func (Where) String 

func (w Where) String() string

String converts the Where type into a string type for easier use.

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.