A SQL toolkit for Go - in the style of Python's SQLAlchemy Core:
- Build complete database schemas
- Create reusable and cross-dialect SQL statements
- Allow struct instances and slices to be directly populated by the database
- Support for MySQL, PostGres, and SQLite3
package main
import (
"log"
"github.com/aodin/sol"
_ "github.com/aodin/sol/sqlite3"
"github.com/aodin/sol/types"
)
// Database schemas are created using sol's Table function
var Users = sol.Table("users",
sol.Column("id", types.Integer().NotNull()),
sol.Column("name", types.Varchar().Limit(32).NotNull()),
sol.Column("password", types.Varchar().Limit(128).NotNull()),
sol.PrimaryKey("id"),
)
// Structs can be used to send and receive values
type User struct {
ID int64
Name string
Password string
}
func main() {
// Create a connection pool for an in-memory sqlite3 instance
conn, err := sol.Open("sqlite3", ":memory:")
if err != nil {
log.Fatal(err)
}
defer conn.Close()
// Test the connection
if err := conn.Ping(); err != nil {
log.Fatalf("Failed to open a database connection: %v", err)
}
// Create the users table
conn.Query(Users.Create())
// Insert a user by struct
admin := User{ID: 1, Name: "admin", Password: "secret"}
conn.Query(Users.Insert().Values(admin))
// Select a user - query methods must be given a pointer receiver!
var user User
conn.Query(Users.Select(), &user)
log.Println(user)
}go get -u github.com/aodin/sol
Import Sol and at least one database dialect:
import (
"github.com/aodin/sol"
_ "github.com/aodin/sol/mysql"
_ "github.com/aodin/sol/postgres"
_ "github.com/aodin/sol/sqlite3"
)Calling Open will return a *DB that implements Sol's Conn interface and embeds Go's *sql.DB. All queries use the Query method, which will return an error if the query fails:
conn, err := sol.Open("sqlite3", ":memory:")
if err != nil {
log.Panic(err)
}
defer conn.Close()
if err = conn.Query(Users.Create()); err != nil {
log.Panic(err)
}
var user User
if err = conn.Query(Users.Select(), &user); err != nil {
log.Panic(err)
}If you'd prefer to have queries panic on error, you can create a panicky version of the connection:
panicky := conn.PanicOnError() // or Must()
panicky.Query(Users.Create())Transactions are started with Begin and include the standard methods Rollback and Commit. There is also a Close method which will rollback the transaction unless IsSuccessful is called:
tx, _ := conn.PanicOnError().Begin()
defer tx.Close()
tx.Query(Users.Insert().Values(User{Name: "Zero"}))
tx.IsSuccessful()SQL can be handwritten using the Text function, which requires parameters to be written in a dialect neutral format and passed via Values:
sol.Text(`SELECT * FROM users WHERE id = :id OR name = :name`).Values(
sol.Values{"name": "admin", "id": 1},
)Or struct types:
user := struct {
ID int64
Name string
}{
ID: 1,
Name: "admin",
}
sol.Text(`SELECT * FROM users WHERE id = :id OR name = :name`).Values(user)The parameters will be re-written for the current dialect:
SELECT * FROM users WHERE id = ? OR name = ?Sol also includes a variety of statements that can be constructed directly from declared schemas.
Once a schema has been specified with Table, such as:
var Users = sol.Table("users",
sol.Column("id", types.Integer().NotNull()),
sol.Column("name", types.Varchar().Limit(32).NotNull()),
sol.Column("password", types.Varchar().Limit(128).NotNull()),
sol.PrimaryKey("id"),
)A CREATE TABLE statement can be created with:
Users.Create()As with most statements, it will output dialect neutral SQL from its String() method. Dialect specific output is created with the String() method on the current connection.
CREATE TABLE users (
id INTEGER NOT NULL,
name VARCHAR(32) NOT NULL,
password VARCHAR(128) NOT NULL,
PRIMARY KEY (id)
);Using the Users schema, a DROP TABLE statement can be created with:
Users.Drop()DROP TABLE "users"Insert statements can be created without specifying values. For instance, the method Insert() on a schema such as Users can be created with:
Users.Insert()When using the Sqlite3 dialect it will generate the following SQL:
INSERT INTO users (id, name, password) VALUES (?, ?, ?)Values can be inserted to the database using custom struct types or the generic sol.Values type. If given a struct, Sol will attempt to match SQL column names to struct field names both exactly and via a camel to snake case conversion. More complex names or aliases should specify db struct tags:
type User struct {
DoesNotMatchColumn int64 `db:"id"`
Name string
Password string
}Example camel to snake case conversions:
| Camel Case | Snake Case |
|---|---|
| TextBlock | text_block |
| UserID | user_id |
| UUID | uuid |
Rows in a table can be updated using either of:
Users.Update()
sol.Update(Users)Both will produce the following SQL with the sqlite3 dialect:
UPDATE users SET id = ?, name = ?, password = ?Conditionals can be specified using Where:
conn.Query(Users.Update().Values(
sol.Values{"password": "supersecret"},
).Where(Users.C("name").Equals("admin")))Users.Delete().Where(Users.C("name").Equals("admin"))DELETE FROM users WHERE users.name = ?Results can be queried in a number of ways. Each of the following statements will produce the same SQL output:
Users.Select()
sol.Select(Users)
sol.Select(Users.C("id"), Users.C("name"), Users.C("password"))SELECT users.id, users.name, users.password FROM usersMultiple results can be returned directly into slice of structs:
var users []User
conn.Query(Users.Select(), &users)Single column selections can select directly into a slice of the appropriate type:
var ids []int64
conn.Query(sol.Select(Users.C("id")), &ids)Tables can be constructed with foreign keys, unique constraints, and composite primary keys. See the sol_test.go file for more examples:
var Contacts = sol.Table("contacts",
sol.Column("id", types.Integer()),
sol.ForeignKey("user_id", Users),
sol.Column("key", types.Varchar()),
sol.Column("value", types.Varchar()),
sol.PrimaryKey("id"),
sol.Unique("user_id", "key"),
)CREATE TABLE contacts (
id INTEGER,
user_id INTEGER NOT NULL REFERENCES users(id),
key VARCHAR,
value VARCHAR,
PRIMARY KEY (id),
UNIQUE (user_id, key)
);Some dialects require a configuration to be set via an environmental variable for testing, such as SOL_TEST_POSTGRES for the postgres dialect. Example variables and, if possible, Docker containers have been provided in the subpackages where these variables are required.
Statements and clauses can be tested by creating a new dialect-specific tester; for example using the postgres package:
expect := NewTester(t, postgres.Dialect())The instance's SQL method will test expected output and parameterization:
expect.SQL(Users.Delete(), `DELETE FROM users`)
expect.SQL(
Users.Insert().Values(Values{"id": 1, "name": "user"}),
`INSERT INTO users (id, name) VALUES ($1, $2)`,
1, "user",
)And the Error method will test that an error occurred:
expect.Error(sql.Select(Users.C("does-not-exist")))Happy Hacking!
aodin, 2015-2017