This project is under development, If you want, Let's Go!
Database
- Introduction
- Retrieving Results
- Aggregates
- Selects
- Where Clauses
- Ordering, Grouping, Limit, & Offset
- Inserts
- Updates
- Deletes
Introduction
Database SQL builder, written in Go, provides a convenient to creating and running database queries. Here is an api server example.
Configuration
The database configuration is located at database.yml. In this file
you may define all of your database connections, as well as specify
which connection should be used by default. Examples for most of the
supported database systems are provided in this file.
default: mysql
sqlite:
driver: sqlite3
database: /absolute/path/to/gogogo.sqlite
prefix:
mysql:
driver: mysql
host: localhost
port: 3306
database: gogogo
username: root
password:
unix_socket:
charset: utf8mb4
collation: utf8mb4_unicode_ci
prefix:
pgsql:
driver: postgres
host: 127.0.0.1
port: 5432
database: gogogo
username: qclaogui
password:
charset: utf8
prefix:
sslmode: disableUsing Multiple Database Connections
When using multiple connections, you may access each connection via the
connection method on the DM. The name passed to the connection
method should correspond to one of the connections listed in your
database.yml configuration file:
package main
import "github.com/qclaogui/database/builder"
DB, DM := builder.Run("/absolute/path/to/database.yml")
users :=DB.Table("users").Get()
// or
users = DM.Connection("pgsql").Table("users").Get()Retrieving Results
Retrieving All Rows From A Table
You may use the Table method on the DB(Connector interface) to begin a query
DB, _ := builder.Run("/absolute/path/to/database.yml")
users := DB.Table("users").Get()Retrieving A Single Row / Column From A Table
users := DB.Table("users").Limit(1).Get()Value method will return the value of the column directly:
users := DB.Table("users").Where("name", "John").Value("email")Aggregates
The query builder also provides a variety of aggregate methods such as count, max, min, avg, and sum.
You may call any of these methods after constructing your query:
users := DB.Table("users").Count()
users := DB.Table("users").Max("id")
price := DB.Table("orders").Where("finalized", "1").Avg("price")Selects
Specifying A Select Clause
Using the Select method, you can specify a custom Select clause for
the query:
users :=DB.Table("users").Select("name", "email as user_email").Get()The Distinct method allows you to force the query to return distinct
results:
users :=DB.Table("users").Distinct().Get()Where Clauses
Simple Where Clauses
In general, the Where method requires three arguments. The first argument is the name of the column. The second argument is an operator, which can be any of the database's supported operators. Finally, the third argument is the value to evaluate against the column.
users :=DB.Table("users").Where("votes", "=", "100").Get()
// or
users :=DB.Table("users").Where("votes", "100").Get()you may use a variety of other operators when writing a where clause:
users :=DB.Table("users").Where("votes",">=", "100").Get()
users :=DB.Table("users").Where("votes","<>", "100").Get()
users :=DB.Table("users").Where("votes","like", "T%").Get()Or Statements
You may chain where constraints together as well as add or clauses to the query. The orWhere method accepts the same arguments as the where method:
users :=DB.Table("users").Where("votes",">", "100").OrWhere('name', 'John').Get()whereBetween
The whereBetween method verifies:
users :=DB.Table("users").WhereBetween("created_at", "2017-01-08", "2018-03-06").Get()whereBetween / WhereNotBetween
The whereBetween method verifies that a column's value is between two values:
users :=DB.Table("users").WhereBetween("votes", "1", "100").Get()
users :=DB.Table("users").WhereNotBetween("votes", "1", "100").Get()WhereIn / whereNotIn
The WhereIn method verifies that a given column's value is contained within the given array:
users :=DB.Table("users").WhereIn("id", "1", "2","3").Get()
// or
users :=DB.Table("users").WhereIn("id", []string{"1","2","3"}...).Get()
users :=DB.Table("users").WhereNotIn("id", "1", "2","3").Get()
users :=DB.Table("users").WhereNotIn("id", []string{"1","2","3"}...).Get()WhereDate / WhereMonth / WhereDay / WhereYear / WhereTime
The WhereDate method may be used to compare a column's value against a date:
users :=DB.Table("users").WhereDate("created_at", "2018-05-20").Get()The WhereMonth method may be used to compare a column's value against a
specific month of a year:
users :=DB.Table("users").WhereMonth("created_at", "12").Get()The WhereDay method may be used to compare a column's value against a
specific day of a month:
users :=DB.Table("users").WhereDay("created_at", "12").Get()The WhereYear method may be used to compare a column's value against a
specific year:
users :=DB.Table("users").WhereYear("created_at", "2018").Get()The WhereTime method may be used to compare a column's value against a
specific time:
users :=DB.Table("users").WhereTime("created_at","=", "12:30:15").Get()Parameter Grouping
Sometimes, you might want to use parentheses to create a more advanced where statement,
Only in this case ,The Where method requires four arguments. like:
users :=DB.Table("users").Where("age", ">=", "22", "(").Where("gender", "Male").Where("house", ">=", "1", ")").
OrWhere("age", ">=", "20", "(").Where("gender", "=", "Female", ")").
Get()The example above will produce the following SQL(mysql):
select * from users where (age >= ? and gender = ? and house >= ?) or (age >= ? and gender = ?)
Ordering, Grouping, Limit, & Offset
OrderBy
The OrderBy method allows you to sort the result of the query by a given column. The first argument to the OrderBy method should be the column you wish to sort by, while the second argument controls the direction of the sort and may be either asc or desc:
users :=DB.Table("users").OrderBy("name", "desc").Get()GroupBy / Having
The GroupBy and Having methods may be used to group the query results. The Having method's signature is similar to that of the Where method:
users :=DB.Table("users").GroupBy("account_id").Having("account_id", ">", "10").Get()Skip / Take
To limit the number of results returned from the query, or to skip a given number of results in the query, you may use the Skip and Take methods:
users :=DB.Table("users").Skip(10).Take(5).Get()Alternatively, you may use the limit and offset methods:
users :=DB.Table("users").Skip(10).Limit(5).Get()Inserts
The query builder also provides an Insert method for inserting records
into the database table.:
var usersData = []map[string]string{map[string]string{
"name": "gopher1",
"email": "[email protected]",
}, map[string]string{
"name": "gopher2",
"email": "[email protected]",
}}
users :=DB.Table("users").Insert(usersData)Updates
Of course, the query builder can also update existing records using the
Update method
var updateData = map[string]string{
"name": "gopher2",
"email": "[email protected]",
}
users :=DB.Table("users").Update(updateData)Deletes
The query builder may also be used to delete records from the table via
the Delete method. You may constrain delete statements by adding
Where clauses before calling the Delete method:
users :=DB.Table("users").Delete()
users :=DB.Table("users").Where('votes', '>', "100").Delete()