sqldb
sqldb allows access to databases in golang. It is a thin wrapper around github.com/jmoiron/sqlx.
It is designed to allow the reuse of the same code for multiple types of databases (see the insert and all functions below), and for database specific sql where required (see the createTable function below).
// Item is a cache item
type Item struct {
Id int
Bucket string `db:"bucket"`
Key string `db:"key"`
Size int64 `db:"size"`
AccessCount int64 `db:"access_count"`
ExpiresAt time.Time `db:"expires_at"`
CreatedAt time.Time `db:"created_at"`
UpdatedAt time.Time `db:"updated_at"`
}
func main() {
ctx, cancelFunc := context.WithTimeout(context.Background(), 10*time.Second)
defer cancelFunc()
dbList := []struct {
dbType string
connectString string
}{
{"sqlite", "/path/to/sqldatabase.db"},
{"postgresql", postgres.ConnectString("localhost", 5432, "postgres", "", "", false, "UTC")}
}
for _, d := range dbList {
db, err := sqldb.Init(ctx, cancelFunc, d.dbType, d.connectString)
if err != nil {
log.Fatal(err)
}
err = createTable(&db)
if err != nil {
log.Fatal(err)
}
err = insert(&db, "mybucket", "mykey", 100, 1, time.Now())
if err != nil {
log.Fatal(err)
}
items, err = all(db *DB)
if err != nil {
log.Fatal(err)
}
fmt.Println(items)
err = db.Close()
if err != nil {
log.Fatal(err)
}
}
}
func insert(db *sqldb.DB, bucket, key string, size, access_count int64, expires_at time.Time) error {
db.Mutex.Lock()
defer db.Mutex.Unlock()
sqlString := "INSERT INTO cache (bucket, key, size, access_count, expires_at) VALUES (?,?,?,?,?)"
_, err := db.Exec(db.Rebind(sqlString),
i.Bucket,
i.Key,
i.Size,
i.AccessCount,
i.ExpiresAt,
)
return err
}
func all(db *sqldb.DB) ([]Item, error) {
db.Mutex.RLock()
defer db.Mutex.RUnlock()
sqlString := "SELECT * FROM cache"
var items []Item
err := db.Select(&items, db.Rebind(sqlString))
return items, err
}
func createTable(db *sqldb.DB) error {
db.Mutex.Lock()
defer db.Mutex.Unlock()
switch db.Type {
case "sqlite":
_, err := db.Exec(`
CREATE TABLE IF NOT EXISTS cache (
id INTEGER PRIMARY KEY,
bucket TEXT,
key TEXT,
size INT,
access_count INT,
expires_at TIMESTAMP,
created_at TIMESTAMP NULL DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW')),
updated_at TIMESTAMP NULL DEFAULT(STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW'))
)
`)
if err != nil {
return err
}
// Create updated_at trigger for cache table
_, err = db.Exec(`
CREATE TRIGGER [update_cache_updated_at]
AFTER UPDATE
ON cache
BEGIN
UPDATE cache SET updated_at=STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW') WHERE id=NEW.id;
END;
`)
if err != nil && err.Error() != "trigger [update_cache_updated_at] already exists" {
return err
}
case "postgres":
_, err := db.Exec(`
CREATE TABLE IF NOT EXISTS cache (
id BIGSERIAL PRIMARY KEY,
bucket TEXT,
key TEXT,
size BIGINT,
access_count BIGINT,
expires_at TIMESTAMP,
created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
)
`)
if err != nil {
return err
}
// Create or replace update_updated_at_column function
// Note we only need to do this once for all of the tables that we update
_, err = db.Exec(`
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ language 'plpgsql';
`)
if err != nil {
return err
}
// Create updated_at trigger for cache table
_, err = db.Exec(`
CREATE TRIGGER update_cache_updated_at
BEFORE UPDATE
ON cache
FOR EACH ROW
EXECUTE PROCEDURE update_updated_at_column();
`)
if err != nil && err.Error() != "pq: trigger \"update_cacheupdated_at\" for relation \"cache\" already exists" {
return err
}
default:
return fmt.Errorf("Create table error: database type not implemented: %s", db.Type)
}
return nil
}