dotsql

A Golang library for using SQL.

It is not an ORM, it is not a query builder. Dotsql is a library that helps you keep sql files in one place and use it with ease.

Dotsql is heavily inspired by yesql.

Installation

Simple install the package to your $GOPATH with the go tool from shell:

$ go get github.com/gchaincl/dotsql

Make sure Git is installed on your machine and in your system's $PATH

Usage

First of all, you need to define queries into a file:

-- name: create-users-table
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    name VARCHAR(255),
    email VARCHAR(255)
);

-- name: create-user
INSERT INTO users (name, email) VALUES(?, ?)

-- name: find-users-by-email
SELECT id,name,email FROM users WHERE email = ?

-- name: find-one-user-by-email
SELECT id,name,email FROM users WHERE email = ? LIMIT 1

--name: drop-users-table
DROP TABLE users

Notice that every query has a name tag (--name:<some name>), this will be helpful for referring to a specific query

Then you should be able to run something like:

// Get a database handle
db, err := sql.Open("sqlite3", ":memory:")

// Loads queries from file
dot, err := dotsql.LoadFromFile("queries.sql")

// Run queries
res, err := dot.Exec(db, "create-users-table")
res, err := dot.Exec(db, "create-user", "User Name", "[email protected]")
rows, err := dot.Query(db, "find-users-by-email", "[email protected]")
row, err := dot.QueryRow(db, "find-one-user-by-email", "[email protected]")

stmt, err := dot.Prepare(db, "drop-users-table")
result, err := stmt.Exec()

For a complete example please refer to integration_test.go and test_schema.sql

Development

Dotsql is in a very early stage so api may change. Contributions are welcome! Integration tests are tagged with +integration, so if you want to run them you should:

go test -tags=integration

If integration tests takes too long remember to go install github.com/mxk/go-sqlite/sqlite3

Otherwise just run:

go test

Embeding

To avoid distributing sql files with the binary, you will need to embed into it, tools like gotic may help

TODO

• Enable text interpolation inside queries using text/template
• sqlx integration