README
¶
GoSQL
GoSQL is an SQL ORM for the Go programming language. It is intended to add type safety and ease of use to SQL.
Installation
go get github.com/tkdeng/gosql
# Any SQL Driver
# SQLite
go get github.com/mattn/go-sqlite3
# MySQL
go get github.com/go-sql-driver/mysql
Usage
import (
"github.com/tkdeng/gosql"
// SQLite
_ "github.com/mattn/go-sqlite3"
// MySQL
_ "github.com/go-sql-driver/mysql"
)
func main(){
// Local File
db, err := gosql.Open("sqlite3", "/path/to/db.sqlite")
// Memory/RAM
db, err := gosql.Open("sqlite3", "")
// Server
db, err := gosql.Open("mysql", gosql.Server{
username: "user",
password: "p@ssw0rd!",
host: "localhost",
port: 1433,
protocol: "tcp", // or udp
database: "db",
})
// close database
defer db.Close()
}
Adding data to a table
// Create or Select a Table
// note: this method will automatically create a table if it doesn't exist
table := db.Table("users",
INT("id").Primary().Unique().Default(0), // note: `.AutoInc()` is not supported by sqlite
TEXT("username"),
TEXT("password"),
)
// INSERT new row
err := table.Set(map[string]any{
"username": "user",
"password": "p@ssw0rd!",
})
// INSERT or UPDATE row
err := table.Set(map[string]any{
"username": "user",
"password": "NewPassword!",
}, "username") // optional: specify unique keys
// UPDATE row
err := table.Where("id").Equal(0).Set(map[string]any{
"username": "user",
"password": "NewerPassword!",
})
// In the above example, if the database finds that
// "username" = "user" already exists, it will update the
// "password" of the existing user, instead of creating
// a new user. If not found, a new user will be created.
Getting data from a table
// SELECT data from database
err := table.Get([]string{"id", "username", "password"}, func(scan func(dest ...any) error) bool {
var id int
var username string
var password string
scan(&id, &username, &password) // runs rows.Scan from core sql module
// return true to continue rows.Next()
// return false to break the loop and close the query
return true
})
// check if table has a row WHERE key = value
if table.Has(map[string]any{"username": "admin"}) {
// admin user exists
}
adding WHERE query
err := table.Where("username") // WHERE username
.Equal("user") // = 'user'
.AND("password") // AND password
.EQUAL("p@ssw0rd!") // = 'p@ssw0rd!'
.Get(nil, func(scan func(dest ...any) error) bool { // SELECT * FROM table
// do stuff
})
query := table.Where("username", false).Equal("admin") // WHERE NOT username = 'admin'
query := table.Where("username").NotEqual("admin") // WHERE username <> 'admin'
// note: `<>` is equivalent to `!=` in sql
query := table.Where("id").GreaterThan(0) // WHERE id > 0
query := table.OrderBy("id") // ORDER BY id
query := table.OrderBy("id", true) // ORDER BY id DESC
// note: the Where method returns a new instance of the query
table.Where("id").Equal(0)
err := table.Delete() // will not register the above where query
err == gosql.Error_UnsafeQuery
// For safety, the Delete method will actually return an error if
// the WHERE query is empty by default.
// This prevents you from accidently deleting the entire table.
// (more info about safety checks below)
Removing data from a table
// delete row from database
err := table.Where("password").Equal("p@ssw0rd!").Delete()
// note: the above example will delete any user with the password "p@ssw0rd!"
// If you plan on removing insucure passwords, I would recommend locking the account
// by updating it with a random password, and let the user change it via email or the
// 'forgot password' button. Your users will likely be mad if you randomly delete their account.
// running Delete, without a Where query will return an error
err := table.Delete()
err == gosql.Error_UnsafeQuery
// to override this (set @force = true)
err := table.Delete(true)
err == nil
// note: the above method will only delete all rows, and keeps the empty table
// to Drop the table
table.Drop(true) // note: you must pass 'true' to confirm dropping the table
Query safety checks
// to run raw sql queries
db.Query() || db.Exec() || db.Prepare()
// these are a 1 to 1 of the core sql params, but with an additional safety check on the query
// note: this module uses the above methods when running sql queries (eccept for the Drop method)
// this method gets run by default, but you can also call it manually
if db.SafeQuery("SELECT * FROM users") {
// this passed safety checks
}
if db.SafeQuery("SELECT * FROM users WHERE username = 'admin' OR 1=1") {
// this will fail the safety check by default
// common sql injection includes escaping an input and adding `OR 1=1`,
// if any WHERE query checks if something is equal to itself,
// it will fail the default safety check.
}
if db.SafeQuery("SELECT * FROM users WHERE username = 'admin'; DROP *") {
// this will fail the safety check by default
// common sql injection loves to abuse the `;` character.
// this feature is not frequently needed, and can simply
// be replaced by running another query function.
}
if db.SafeQuery("DROP *") {
// this will fail the safety check by default
// this is not only to protect against sql injection,
// but to also prevent developers from accidently deleting
// an entire database.
// note: the `table.Drop(true)` method will override safety checks.
}
// adding your own safety checks
// this method will add another callback to a list
// that will be called whenever a query safety check is ran.
gosql.AddSafetyCheck(func(query string) bool {
if safe {
return true // return true to continue the safety check list
} else {
return false // return false to report an unsafe query string
}
})
// for a simple regex match, this method will check if the
// query matched a regular expression (RE2).
// if the query matches this regex, it will repoort the query as unsafe
gosql.AddSafetyCheckRE(`(?i)(WHERE|AND|OR)\s+NOT`) // this example prevents any `WHERE NOT` queries
// pass @where: true, to only check after the WHERE query
gosql.AddSafetyCheckRE(`(?i)NOT`, true)
// overriding safety checks (Not Recommended)
db = db.Unsafe("I Know What Im Doing!") // returns a new database instance that allows unsafe queries
// note: the raw database object from the core sql module will also bypass query safety checks
var rawDB *sql.DB = db.SQL
Documentation
¶
Index ¶
- Variables
- func AddSafetyCheck(cb func(query string) bool)
- func AddSafetyCheckRE(re string, where ...bool)
- func SafeQuery(query string) bool
- type DB
- func (db *DB) Close()
- func (db *DB) Exec(query string, args ...any) (sql.Result, error)
- func (db *DB) Prepare(query string) (*sql.Stmt, error)
- func (db *DB) Query(query string, args ...any) (*sql.Rows, error)
- func (db *DB) Table(name string, rows ...*DataType) *Query
- func (db DB) Unsafe(confirm string) *DB
- type DataType
- func BIGINT(key string) *DataType
- func BINARY(key string, size ...uint8) *DataType
- func BIT(key string, size ...uint8) *DataType
- func BLOB(key string, size ...uint16) *DataType
- func BOOL(key string) *DataType
- func CHAR(key string, size ...uint8) *DataType
- func DATE(key string) *DataType
- func DATETIME(key string, fsp ...string) *DataType
- func DECIMAL(key string, sizeD ...uint8) *DataType
- func DOUBLE(key string, sizeD ...uint8) *DataType
- func ENUM(key string, val ...string) *DataType
- func FLOAT(key string, p ...uint8) *DataType
- func INT(key string) *DataType
- func LONGBLOB(key string) *DataType
- func LONGTEXT(key string) *DataType
- func MEDIUMBLOB(key string) *DataType
- func MEDIUMINT(key string) *DataType
- func MEDIUMTEXT(key string) *DataType
- func SET(key string, val ...string) *DataType
- func SMALLINT(key string) *DataType
- func TEXT(key string, size ...uint16) *DataType
- func TIME(key string, fsp ...string) *DataType
- func TIMESTAMP(key string, fsp ...string) *DataType
- func TINYBLOB(key string) *DataType
- func TINYINT(key string) *DataType
- func TINYTEXT(key string) *DataType
- func TYPE(key string, dataType string) *DataType
- func VARBINARY(key string, size ...uint16) *DataType
- func VARCHAR(key string, size ...uint16) *DataType
- func YEAR(key string) *DataType
- type Query
- func (query Query) And(key string, truthy ...bool) *whereQuery
- func (query *Query) Delete(force ...bool) error
- func (query *Query) Drop(force bool) error
- func (query *Query) Get(keys []string, cb func(scan func(dest ...any) error) bool) error
- func (query *Query) Has(values map[string]any) bool
- func (query Query) Or(key string, truthy ...bool) *whereQuery
- func (query Query) OrderBy(key string, desc ...bool) *Query
- func (query *Query) Set(values map[string]any, unique ...string) error
- func (query Query) Where(key string, truthy ...bool) *whereQuery
- type Server
Constants ¶
This section is empty.
Variables ¶
var Error_UnsafeQuery = errors.New("unsafe query")
Functions ¶
func AddSafetyCheck ¶
AddSafetyCheck adds another safety check to the SafeQuery method
return false, if you think the query looks unsafe. return true, to continue down the safety check list.
func AddSafetyCheckRE ¶
AddSafetyCheckRE adds another safety check to the SafeQuery method
The `RE` stands for RegExp, so you can simply pass a regex string, which will check for a match, instead of a full callback method.
If the query matches this regex, the query will be seen as unsafe.
@where: if true, will only check after the WHERE keyword
Types ¶
type DB ¶
func (*DB) Exec ¶
Exec executes a query without returning any rows. The args are for any placeholder parameters in the query.
Exec uses context.Background internally; to specify the context, use [DB.ExecContext].
func (*DB) Prepare ¶
Prepare creates a prepared statement for later queries or executions. Multiple queries or executions may be run concurrently from the returned statement. The caller must call the statement's [*Stmt.Close] method when the statement is no longer needed.
Prepare uses context.Background internally; to specify the context, use [DB.PrepareContext].
func (*DB) Query ¶
Query executes a query that returns rows, typically a SELECT. The args are for any placeholder parameters in the query.
Query uses context.Background internally; to specify the context, use [DB.QueryContext].
func (*DB) Table ¶
Table selects a database table
if any rows are specified, this method will create a table if it does not exist
func (DB) Unsafe ¶
Unsafe will disable sql safety checks
By default, the final output will be checked for potentially dangorous queries, like `DROP *` which could happen by accident.
Note: this safety check does Not guarantee safety, and should Not be relied on.
To use this method, you must pass the confirm argument as "I Know What Im Doing!", to confirm that you have read the documentation, and know what you are doing.
type DataType ¶
type DataType struct {
// contains filtered or unexported fields
}
func BIGINT ¶
BIGINT is a Numeric DataType
size: -9223372036854775808 to 9223372036854775807 | 0 to 18446744073709551615
func DATE ¶
DATE is a DateTime DataType
A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'.
func DATETIME ¶
DATETIME is a DateTime DataType
A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. Adding DEFAULT and ON UPDATE in the column definition to get automatic initialization and updating to the current date and time.
func DECIMAL ¶
DECIMAL is a Numeric DataType
An exact fixed-point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter. The maximum number for size is 65. The maximum number for d is 30. The default value for size is 10. The default value for d is 0.
func DOUBLE ¶
DOUBLE is a Numeric DataType
A normal-size floating point number. The total number of digits is specified in size. The number of digits after the decimal point is specified in the d parameter.
func ENUM ¶
ENUM is a String DataType
A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. The values are sorted in the order you enter them.
func FLOAT ¶
FLOAT is a Numeric DataType
A floating point number. MySQL uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type. If p is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to 53, the data type becomes DOUBLE().
func SET ¶
SET is a String DataType
A string object that can have 0 or more values, chosen from a list of possible values. You can list up to 64 values in a SET list.
func TIME ¶
TIME is a DateTime DataType
A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'.
func TIMESTAMP ¶
TIMESTAMP is a DateTime DataType
A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC. Automatic initialization and updating to the current date and time can be specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP in the column definition.
func TYPE ¶
TYPE is a Custom DataType
You can use this if an SQL DataType is not supported by this module. A list of SQL DataTypes can be found here: https://www.w3schools.com/sql/sql_datatypes.asp
func YEAR ¶
YEAR is a DateTime DataType
A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000.
func (DataType) Append ¶
Append allows you to add custom type constraints to a DataType
You can use this if an SQL DataType constraint is not supported by this module.
func (DataType) AutoInc ¶
AutoInc makes a type AUTO_INCREMENT
Note: AUTO_INCREMENT may Not be supported by sqlite
type Query ¶
type Query struct {
// contains filtered or unexported fields
}
func (Query) And ¶
And will select where ... AND key
set @truthy to false, to select where ... AND NOT key
func (*Query) Delete ¶
Delete will remove a row from the database table
! Warning: setting @force to true, will allow the database to delete all rows from a table, if its missing a `where` query
func (*Query) Drop ¶
Drop will drop an entire table from the database, deleting everything
! Warning: This method will Delete the entire table, and will ignore any `where` queries
func (*Query) Get ¶
Get will SELECT keys FROM table, and run a loop over the selected rows
@cb: will be called for every row
- return true, to continue the loop
- return false, to close the query and break the loop
func (*Query) Has ¶
Has will check if key value pairs are found in the database (using SELECT WHERE)
If a row is found, this method will return true. If nothing is found, or an error occurs, this method will return false.
func (Query) Or ¶
Or will select where ... OR key
set @truthy to false, to select where ... OR NOT key
func (*Query) Set ¶
Set will INSERT or UPDATE values FROM table
If a where query exists, this method will only use UPDATE.
If the unique arg exists, this method will check if the database contains all matching key = value pairs. If it finds and, it will use UPDATE, and if nothing is found, it will use INSERT.
If no unique args or where query exists, this method will default to INSERT.
Source Files