sqldb

package module
v1.1.1 Latest Latest
Warning

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

 Go to latest Published: May 21, 2022 License: MIT Imports: 15 Imported by: 0

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.

Details:

  • Supports MySQL, MariaDB, and SQLite. 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.

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 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 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".

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.
	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"
)
View Source 
const (
	DBTypeMySQL   = dbType("mysql")
	DBTypeMariaDB = dbType("mariadb")
	DBTypeSQLite  = dbType("sqlite")
)
View Source 
const (
	SQLiteJournalModeRollback = journalMode("DELETE")
	SQLiteJournalModeWAL      = journalMode("WAL")
)

Variables

View Source 
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 

func Connection() *sqlx.DB

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 

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 package level config with some defaults set. This wraps around NewSQLiteConfig and saves the config to the package.

func DefaultMySQLConfig 

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

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 

func DeploySchema(dontInsert bool) (err error)

DeploySchema deploys the database for the default package level config.

func GetSQLiteVersion 

func GetSQLiteVersion() (version string, err error)

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 

func MapperFunc(m func(string) string)

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 

func TFMySQLToSQLiteReformatDatetime(in string) (out string)

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 

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 returns values in UTC timezone.

func TFMySQLToSQLiteReformatID added in v1.1.0 

func TFMySQLToSQLiteReformatID(in string) (out string)

TFMySQLToSQLiteReformatID reformats the ID column to a SQLite format.

func TFMySQLToSQLiteRemovePrimaryKeyDefinition added in v1.1.0 

func TFMySQLToSQLiteRemovePrimaryKeyDefinition(in string) (out string)

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 

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 UFAlreadyExists 

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

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 

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 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 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",
}

func (Columns) ForInsert 

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

ForInsert builds the column string for an INSERT query.

func (Columns) ForSelect 

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

ForSelect builds the column string for a SELECT query.

func (Columns) ForUpdate 

func (c 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

	//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 

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

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 

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

NewMariaDBConfig returns a config for connecting to a MySQL database.

func NewMySQLConfig 

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

NewMySQLConfig returns a config for connecting to a MySQL database.

func NewSQLiteConfig 

func NewSQLiteConfig(pathToFile string) (c *Config)

NewSQLiteConfig returns a config for connecting to a SQLite database.

func (*Config) Close 

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

Close closes the connection to the database.

func (*Config) Connect 

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

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 

func (c *Config) Connected() bool

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

func (*Config) Connection 

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

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

func (*Config) DeploySchema 

func (c *Config) DeploySchema(dontInsert bool) (err error)

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 

func (c *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 (c.Type == sqldb.DBTypeSQLite).

func (*Config) IsMySQL 

func (c *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 (c.Type == sqldb.DBTypeSQLite).

func (*Config) IsMySQLOrMariaDB 

func (c *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 (c *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 (c.Type == sqldb.DBTypeSQLite).

func (*Config) UpdateSchema 

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

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 

type TranslateFunc func(string) string

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 

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 Where 

type Where string

Where is the WHERE statement in a query. This separate type is useful for times when you are only 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 appending of strings cannot append Where and string b/c they are different types even though they aren't

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.