Documentation ¶
Overview ¶
Package sqldb handles a establishing and managing a database connection, deploying and updating a database schema, translating queries from one database format to another, and provides various other tooling for interacting with SQL databases.
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 can use this package in two manners: store the database configuration globally in the package level variable, or store the configuration elsewhere in your app. Storing the configuration yourself allows for connecting to multiple databases at one time.
Deploying a Database ¶
Deploying of a database schema is done via queries stored as strings and then provided in the DeployQueries field of your config. When you call the DeploySchema() function, a connection to the database (server or file) is established and then each query is run. You should take care to provide the list of queries in DeployQueries in an order where foreign key tables are created first so you don't get unnecessary errors. DeployQueries can also be used to insert initial data into a database. After each DeployQuery is run, DeployFuncs are run. These functions are used to handle more complex operations to deploy your database then simple queries allow. Use TranslateCreateTableFuncs to automatically translate queries from one database format to another (i.e.: MySQL to SQLite) so that you do not need to maintain and list queries for each database type separately.
Updating a Schema ¶
Updating a database schema happens in a similar manner to deploying, a list of queries is run against the database. These queries run encapsulated in a transaction so that either the entire database is updated, or none of queries are applied. This is done to eliminate the possibility of a partially updated database schema. Each update query 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. Take note that SQLite does not allow for columns to be modified!
Extremely important: You should design your queries that deploy or update the schema to be safe to rerun multiple times. You don't want issues to occur if a user interacting with your app somehow tries to deploy the database over and over or update it after it has already been updated. For example, use "IF NOT EXISTS".
SQLite Library ¶
This package support two SQLite libraries, mattn/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 extent of testing.
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 ...
Index ¶
- Constants
- Variables
- func Close() (err error)
- func Connect() (err error)
- func Connected() bool
- func Connection() *sqlx.DB
- func DefaultConfig(t dbType) (err error)
- func DefaultMapperFunc(s string) string
- func DefaultMariaDBConfig(host string, port uint, name, user, password string)
- func DefaultMySQLConfig(host string, port uint, name, user, password string)
- func DefaultSQLiteConfig(pathToFile string)
- func DeploySchema(dontInsert bool) (err error)
- func GetSQLiteLibrary() string
- func GetSQLiteVersion() (version string, err error)
- func IsMariaDB() bool
- func IsMySQL() bool
- func IsSQLite() bool
- func JournalMode(s string) journalMode
- func MapperFunc(m func(string) string)
- func SQLitePragmaJournalMode(j journalMode)
- func Save(c Config)
- func SetDeployFuncs(fs []func() error)
- func SetDeployQueries(qs []string)
- func SetUpdateIgnoreErrorFuncs(fs []UpdateIgnoreErrorFunc)
- func SetUpdateQueries(qs []string)
- func TFMySQLToSQLiteReformatDatetime(in string) (out string)
- func TFMySQLToSQLiteReformatDefaultTimestamp(in string) (out string)
- func TFMySQLToSQLiteReformatID(in string) (out string)
- func TFMySQLToSQLiteRemovePrimaryKeyDefinition(in string) (out string)
- func TranslateCreateTableFuncs(fs []TranslateFunc)
- func UFAddDuplicateColumn(c Config, query string, err error) bool
- func UFAlreadyExists(c Config, query string, err error) bool
- func UFDropUnknownColumn(c Config, query string, err error) bool
- func UFModifySQLiteColumn(c Config, query string, err error) bool
- func UpdateSchema() (err error)
- type Bindvars
- type Columns
- type Config
- func GetDefaultConfig() (c *Config)
- func NewConfig(t dbType) (c *Config, err error)
- func NewMariaDBConfig(host string, port uint, name, user, password string) (c *Config)
- func NewMySQLConfig(host string, port uint, name, user, password string) (c *Config)
- func NewSQLiteConfig(pathToFile string) (c *Config)
- func (c *Config) Close() (err error)
- func (c *Config) Connect() (err error)
- func (c *Config) Connected() bool
- func (c *Config) Connection() *sqlx.DB
- func (c *Config) DeploySchema(dontInsert bool) (err error)
- func (c *Config) IsMariaDB() bool
- func (c *Config) IsMySQL() bool
- func (c *Config) IsMySQLOrMariaDB() bool
- func (c *Config) IsSQLite() bool
- func (c *Config) UpdateSchema() (err error)
- type TranslateFunc
- type UpdateIgnoreErrorFunc
- type Where
Constants ¶
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. 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" )
const ( DBTypeMySQL = dbType("mysql") DBTypeMariaDB = dbType("mariadb") DBTypeSQLite = dbType("sqlite") )
const ( SQLiteJournalModeRollback = journalMode("DELETE") SQLiteJournalModeWAL = journalMode("WAL") )
Variables ¶
var ( //ErrConnected is returned when a database connection is already established and a user is //trying to connect or trying to modify a config that is already in use. 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) where a MySQL //or MariaDB server is running. ErrHostNotProvided = errors.New("sqldb: Database server host not provided") //ErrInvalidPort is returned when user doesn't provide, or provided an invalid port, for where //the MySQL or MariaDB server is running. ErrInvalidPort = errors.New("sqldb: Database server port invalid") //ErrNameNotProvided is returned when user doesn't provide a name for 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. We do not support blank passwords 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 //not columns were provided. ErrNoColumnsGiven = errors.New("sqldb: No columns provided") //ErrDoubleCommaInColumnString is returned when building a column string for a query but //a double comma exists which would cause the query to not run correctly. Double commas //are usually due to an empty column name being provided or a comma being added to the //column name by mistake. ErrDoubleCommaInColumnString = errors.New("sqldb: Extra comma in column name") )
errors
Functions ¶
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 ¶
Connection returns the database connection for the package level config.
func DefaultConfig ¶
func DefaultConfig(t dbType) (err error)
DefaultConfig initializes the package level config. This wraps around NewConfig(). Typically you would use Default...Config() instead.
func DefaultMapperFunc ¶
DefaultMapperFunc is the default MapperFunc set on configs. It returns the column names unmodified.
func DefaultMariaDBConfig ¶
DefaultMariaDBConfig initializes the package level config with some defaults set. This wraps around NewSQLiteConfig and saves the config to the package.
func DefaultMySQLConfig ¶
DefaultMySQLConfig initializes the package level config with some defaults set. This wraps around NewSQLiteConfig and saves the config to the package.
func DefaultSQLiteConfig ¶
func DefaultSQLiteConfig(pathToFile string)
DefaultSQLiteConfig initializes the package level config with some defaults set. This wraps around NewSQLiteConfig and saves the config to the package.
func DeploySchema ¶
DeploySchema deploys the database for the default package level config.
func GetSQLiteLibrary ¶ added in v1.2.0
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}. This returns the import path of the library in use.
func GetSQLiteVersion ¶
GetSQLiteVersion returns the version of SQLite that is embedded into the app. This is used for diagnostics. This works by creating a temporary in-memory SQLite database to run query against.
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 (c.Type == sqldb.DBTypeSQLite).
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 (c.Type == sqldb.DBTypeSQLite).
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 (c.Type == sqldb.DBTypeSQLite).
func JournalMode ¶
func JournalMode(s string) journalMode
JournalMode returns a journalMode. This is provided so that other journal modes besides the const defined Rollback/DELETE and WAL can be used (ex.: TRUNCATE). Providing this tooling allows for a more "I meant that" appearance in code when using a non-const defined journal mode. This is also why a "Valid()" func is not defined for journalMode since other modes can be provided.
func MapperFunc ¶
MapperFunc sets the mapper func for the package level config.
func SQLitePragmaJournalMode ¶
func SQLitePragmaJournalMode(j journalMode)
SQLitePragmaJournalMode set the journal mode for the package level config. Use this before calling Connect() to change the journal mode.
func Save ¶
func Save(c Config)
Save saves a configuration to the package level config. Use this in conjunction with New...Config when you want to heavily customize the config. This does not use a method so that any modifications done to the original config aren't propagated to the package level config without calling Save() again.
func SetDeployFuncs ¶
func SetDeployFuncs(fs []func() error)
SetDeployFuncs sets the list of funcs to deploy the database schema for the package level config.
func SetDeployQueries ¶
func SetDeployQueries(qs []string)
SetDeployQueries sets the list of queries to deploy the database schema for the package level config. Beware of the order! Queries must be listed in order where any foreign key tables were created prior.
func SetUpdateIgnoreErrorFuncs ¶
func SetUpdateIgnoreErrorFuncs(fs []UpdateIgnoreErrorFunc)
SetUpdateIgnoreErrorFuncs sets the list of funcs to handle update schema errors for the package level config.
func SetUpdateQueries ¶
func SetUpdateQueries(qs []string)
SetUpdateQueries sets the list of funcs to update the database schema for the package level config.
func TFMySQLToSQLiteReformatDatetime ¶ added in v1.1.0
TFMySQLToSQLiteReformatDatetime replaces DATETIME columns with TEXT columns. SQLite doesn't have a DATETIME column so values stored in these columns can be converted oddly. Use TEXT column type for SQLite b/c SQLite golang driver converts DATETIME columns in yyyy-mm-dd hh:mm:ss format to yyyy-mm-ddThh:mm:ssZ upon returning value which isn'texpected or what we would usually want; instead user can reformat value returned from database as needed using time package.
func TFMySQLToSQLiteReformatDefaultTimestamp ¶ added in v1.1.0
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 returns values in UTC timezone.
func TFMySQLToSQLiteReformatID ¶ added in v1.1.0
TFMySQLToSQLiteReformatID reformats the ID column to a SQLite format.
func TFMySQLToSQLiteRemovePrimaryKeyDefinition ¶ added in v1.1.0
TFMySQLToSQLiteRemovePrimaryKeyDefinition removes the primary key definition for a SQLite query since SQLite doesn't use this. We also have to remove the comma preceeding this line too.
func TranslateCreateTableFuncs ¶
func TranslateCreateTableFuncs(fs []TranslateFunc)
TranslateCreateTableFuncs sets the translation funcs for creating a table for the package level config.
func UFAddDuplicateColumn ¶
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 UFAlreadyExists ¶
UFAlreadyExists handles errors when an index already exists. This may also work for other thngs that already exist (columns). 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 UFDropUnknownColumn ¶
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 UFModifySQLiteColumn ¶
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 updates the database for the default 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{ "users.Fname", "users.Birthday", }
type Config ¶
type Config struct { //Type represents the type of database to use. Type dbType //Host is the IP or FQDN of the database server. This is not required for SQLite //databases. Host string //Port is the port the database listens on. This is not required for SQLite databases. Port uint //Name is the name of the database to connect to. This is not required for SQLite databases. Name string //User is the user who has access to the database. This is not required for SQLite databases. User string //Password is the matching user's password. This is not required for SQLite databases. Password string //SQLitePath is the path where the SQLite database file is located. This is only required for //SQLite databases. SQLitePath string //SQLitePragmaJournalMode sets the SQLite database journalling mode. This is used to switch //between the default rollback journal ("DELETE") and the write ahead log ("WAL"). WAL is //useful for when you have long-running reads on the database that are blocking access for //writes. SQLitePragmaJournalMode journalMode //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 StructScan(), Get(), //or Select() is used. By default, column names are not modified in any manner, unlike the //default for "sqlx" where column names are returned as all lower case. The default of not //modifying column names is more useful in our option since you will not need to use struct //tags as much since column names can exactly match exportable struct field names (typically //struct fields used for storing column data are exported). //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. The list of queries //provided must be in the correct order to create any tables referred to by a foreign key //first! 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. This list //gets executed after the DeployQueries list. You will need to establish and close the //db connection in each funtion. This is typically used when you want to reuse other funcs //you have defined to handle inserting initial users or creating of default values. This //should rarely be used! DeployFuncs []func() error //TranslateCreateTableFuncs is a list of functions run against each DeployQueries that //contains a "CREATE TABLE" clause that modifies the query to translate it from one //database format to another. This functionality is provided so that you can write your //CREATE TABLE queries in one database's format (ex.: MySQL) but deploy your database //in multiple formats (ex.: MySQL & SQLite). //Some default funcs are predefined, names as TF... TranslateCreateTableFuncs []TranslateFunc //UpdateQueries is a list of queries used to update the database schema. These queries //typically add new columns, alter a column's type, or alter values stored in a column. //The queries listed here will be executed in order when UpdateSchema() is called. The //queries should be safe to be rerun multiple times (i.e.: if UpdateSchema() is called //automatically each time your app starts). UpdateQueries []string //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, named as UF... UpdateIgnoreErrorFuncs []UpdateIgnoreErrorFunc //Debug turns on diagnostic logging. Debug bool // contains filtered or unexported fields }
Config is the details used for establishing and using a database connection.
func GetDefaultConfig ¶
func GetDefaultConfig() (c *Config)
GetDefaultConfig returns the package level saved config.
func NewConfig ¶
NewConfig returns a base configuration that will need to be modified for use with a db. Typically you would use New...Config() instead.
func NewMariaDBConfig ¶
NewMariaDBConfig returns a config for connecting to a MySQL database.
func NewMySQLConfig ¶
NewMySQLConfig returns a config for connecting to a MySQL database.
func NewSQLiteConfig ¶
NewSQLiteConfig returns a config for connecting to a SQLite database.
func (*Config) Connect ¶
Connect connects to the database. This sets the database driver in the config, establishes the database connection, and saves the connection pool for use in making queries. For SQLite this also runs any PRAGMA commands.
func (*Config) Connected ¶
Connected returns if the config represents an established connection to the database.
func (*Config) Connection ¶
Connection returns the database connection stored in a config for use in running queries
func (*Config) DeploySchema ¶
DeploySchema deploys the database schema by running the list of DeployQueries defined on a database config. This will create the database if needed. Typically this is used to deploy an empty, or near empty, database.
Typically this func would be called when your app is passed a flag, such as --deploy-db. This is so that your database is only deployed when needed, not as part of the regular startup of your app.
You should call os.Exit() after this func completes, in most cases, so that you are not tempted to call DeploySchema every time your app starts. Even upon successful deployment, calling os.Exit() is useful so that if you are using a flag to call this func, the end-user doesn't mistakenly put the flag in their script that starts this app.
The dontInsert parameter is used prevent any DeployQueries with "INSERT INTO" statements from running. This is used to deploy a completely empty database.
func (*Config) IsMariaDB ¶
IsMariaDB returns true if the database is a MariaDb database. This is easier than checking for equality against the Type field in the config (c.Type == sqldb.DBTypeSQLite).
func (*Config) IsMySQL ¶
IsMySQL returns true if the database is a MySQL database. This is easier than checking for equality against the Type field in the config (c.Type == sqldb.DBTypeSQLite).
func (*Config) IsMySQLOrMariaDB ¶
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 ¶
IsSQLite returns true if the database is a SQLite database. This is easier than checking for equality against the Type field in the config (c.Type == sqldb.DBTypeSQLite).
func (*Config) UpdateSchema ¶
UpdateSchema updates an already database by running the list of UpdateQueries defined on a database config. Typically this is used to add new columns, alter columns, add new indexes, or change values stored in the database.
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 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.
type TranslateFunc ¶
TranslateFunc is function used to translate a query from one database format to another. This is used when you write your queries for one database (ex.: MySQL) but you allow your app to be deployed in multiple database formats (ex.: MySQL & SQLite). These funcs perform the necessary conversions on a query so that you do not need to write your queries in each database format.
type UpdateIgnoreErrorFunc ¶
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).