This is a simple object mapper for Google Go.
It is based on the idea of Dapper.
Is fairly limited, e.g. query only. But if you can use it (or like it to implement your own simple object mapper), here it is.
This is still a work in progress. Use at your own risk.
Install via go get github.com/olivere/dapper
.
Dapper has two concepts: Generating SQL statements (limited to MySQL) and running SQL statements and returning results in Go structs.
Maybe you're like me and want your program to generate a SQL statement based on some conditions. While you can build SQL manually, that's rather cumbersome.
I'd rather do something like this:
sql := dapper.Q("users").Alias("u").
Join("tweets").Alias("t").On("u.id", "t.user_id").
Project("u.name", "t.message").
Order().Asc("u.name").
Order().Desc("t.created").
Take(10).
Sql()
=> SELECT u.name,t.message
FROM users u
JOIN tweets t ON u.id=t.user_id
ORDER BY u.name ASC,t.created DESC
LIMIT 10
You can use the SQL generation as input for querying, or you create the SQL manually. Either way, querying works as follows.
First, specify the "entities" and apply tags to let Dapper find a mapping between the struct field and the database column:
type User struct {
Id int64 `dapper:"id,primarykey,autoincrement,table=users"`
Name string `dapper:"name"`
Country string `dapper:"country"`
Karma *float64 `dapper:"karma"`
Suspended bool
Ignored string `dapper:"-"`
}
Notice the dapper
field tags:
- If you do not tag a field with
dapper
, the column name in the generated SQL is the same as the field name. In the User struct above, theSuspended
field is generated as column nameSuspended
. - By default, all fields in the struct will be used to generate SQL. If
you do want a field to be ignored in SQL generation, mark it with
dapper:"-"
(seeIgnored
above). - The first element of the
dapper
tag specifies the DB column name (seedapper:"name"
inName
field). - You may specify the table name in one
dapper
tag, seeId
field in the example above. - Use the
primarykey
tag element to mark a column as primary key. - Use the
autoincrement
tag element to mark a column as auto-increment.
Of course, you need to connect to a database and get yourself a *sql.DB
:
db, err := sql.Open(...)
Then create a Dapper session:
session := dapper.New(db)
Now you can throw some SQL at Dapper and let it fill your result set:
// Build SQL statement (or use a manually crafted SQL string)
sql := dapper.Q("users").Alias("u").
Join("tweets").Alias("t").On("u.id", "t.user_id").
Project("u.name", "t.message").
Order().Asc("u.name").
Order().Desc("t.created").
Take(10).
Sql()
// Run the query and return all users in a slice
var users []User
err := session.Find(sql, nil).All(&users)
if err != nil {
// ...
}
// Iterate
for _, user := range users {
fmt.Println(user.Name)
}
But there's a second way of executing SQL queries. You can use with a struct that serves as a binding to the query. Here's how:
// A binding with a UserId field in it
type UserByIdQuery struct {
UserId int64
}
// Another binding with some more fields
type UserbyComplexQuery struct {
MinKarma float64
MaxKarma float64
Country string
}
To get the first result of a query:
// Reserve a User variable for the result
var user User
// Fill the binding for the query: UserId=1
queryParam := UserByIdQuery{UserId: 1}
// Now run the query:
// Notice the ":UserId" will be retrieved from the binding, so the
// SQL statement is: "select * from users where id=1"
err := session.Find("select * from users where id=:UserId", queryParam).Single(&user)
if err != nil {
// ...
}
To perform a query returning not a single entity but a slice:
// Another binding
queryParam := UsersByComplexQuery{MinKarma: 17.0, Country: "DE"}
// Retrieve the results
var results []User
err := session.Find("select * from users "+
"where karma > :MinKarma and country = :Country "+
"order by name limit 30", queryParam).All(&results)
if err != nil {
// ...
}
// Iterate
for _, user := range users {
fmt.Println(user.Name)
}
You can also retrieve the first column of the first row by using the Scalar function:
// Create a var for the result
var name string
// Stores the user name
err := session.Find("select name from users where id=1", nil).Scalar(&name)
As counting is a very common operating, there is a shortcut:
// Returns the number of users
count, err := session.Count("select count(*) from users", nil)
For insert, update, and delete to work, you need to mark the struct with a primary key and a table name. Here's the example from above:
type User struct {
Id int64 `dapper:"id,primarykey,autoincrement,table=users"`
...
}
Then insert some data:
// Create a session
session := dapper.New(db)
// Create a user
u := &User{
Name: "Steve",
Country: "DE",
}
// Now insert
err := session.Insert(u)
if err != nil { ... }
// BTW u.Id is now set
Now let's update the user:
u.Name = "Peter"
err := session.Update(u)
if err != nil { ... }
... and delete:
err := session.Delete(u)
if err != nil { ... }
If you want to insert, update, or delete in the context of a database
transaction, use InsertTx(tx, ...)
, UpdateTx(tx, ...)
, and
DeleteTx(tx, ...)
.
To run tests, you need a MySQL database called dapper_test
and a user
travis
(empty password) with access.
To run tests on PostgreSQL, you need to create a database dapper_test
and
allow access to the user travis
.
Then run go test
.
- Dapper is not targeting a specific Go MySQL driver. However, at the
current time, we are limited to Michał Derkacz's
mymysql driver. The reason
for this is that it provides a mapping between the various
date/time-types in MySQL and
time.Time
in Golang. However, theDATE
type in MySQL is mapped tomysql.Date
, and Dapper handles that. - We are looking forward to support Julien Schmidt
Go-SQL-Driver/MySQL as well.
Julien has not yet decided how to handle
time.Time
(see issue #9).
- Sam Saffron for Dapper.
MIT LICENSE. See LICENSE or the LICENSE file in the repository.