sqlagent

README

SQL Agent

SQL Agent is an HTTP service for executing ad-hoc queries on remote databases. The motivation for this service is to be part of a data monitoring process or system in which the query results will be evaluated against previous snapshots of the results.

The supported databases are:

• PostgreSQL
• MySQL, MariaDB
• Oracle
• Microsoft SQL Server
• SQLite
• Snowflake
• Presto

In addition to the service, this repo also defines a sqlagent package for using in other Go programs.

Install

At the moment, it is recommended to run the service using Docker because there are no pre-built binaries yet.

docker run -d -p 5000:5000 dbhi/sql-agent

Usage

To execute a query, simply send a POST request with a payload containing the driver name of the database, connection information to with, and the SQL statement with optional parameters. The service will connect to the database, execute the query and return the results as a JSON-encoded array of maps (see details below).

Request

POST

Headers:

• 'Accept' - required
  • application/json
  • application/x-ldjson
  • text/csv

{
    "driver": "postgres",
    "connection": {
        "host": "localhost",
        "user": "postgres"
    },
    "sql": "SELECT name FROM users WHERE zipcode = :zipcode",
    "params": {
        "zipcode": 18019
    }
}

Response

[
    {
        "name": "George"
    },
    ...
]

Connection Options

The core option names are standardized for ease of use.

• host - The host of the database.
• port - The port of the database.
• user - The user to connect with.
• password - The password to authenticate with.
• database - The name of the database to connect to. For SQLite, this will be a filesystem path. For Oracle, this would be the SID.

Other options that are supplied are passed query options if they are known, otherwise they are they ignored.

Alternatively, the dsn parameter can be specified which will used directly when opening a connection.

Ping Connection

To validate the connection, send a POST with the ?ping query parameter present and it will test the connection only.

Details

• Only SELECT statements are supported.
• Statements using parameters must use the :param syntax and must have a corresponding entry in the params map.

Constraints

• Columns must be uniquely named, otherwise the conversion into a map will include only one of the values.

Library

The SQL Agent library does not include any drivers by default. To add them include them like so:

package main

import (
	_ "github.com/alexbrainman/odbc"
	_ "github.com/denisenkom/go-mssqldb"
	_ "github.com/go-sql-driver/mysql"
	_ "github.com/lib/pq"
	_ "github.com/mattn/go-oci8"
	_ "github.com/mattn/go-sqlite3"
)

func main() {
    //...
}

Help

Install Oracle client libraries

General

In order to install the go-oci8 driver, you must install Oracle's client libraries.

Download the instantclient-basic and instantclient-sdk package from Oracle's website and uncompress to the same directory. Make sure that you selected the platform and architecture.

The installations instructions are listed at the bottom of the page with the download links.

Install pkg-config.

Create oci8.pc file in your $PKG_CONFIG_PATH (such as /usr/local/lib/pkgconfig) and add the below contents:

prefix=/usr/local/lib/instantclient_11_2
libdir=${prefix}
includedir=${prefix}/sdk/include/

Name: OCI
Description: Oracle database engine
Version: 11.2
Libs: -L${libdir} -lclntsh
Libs.private:
Cflags: -I${includedir}

Change the prefix to path to location of the Oracle libraries.

OS X specific Help

Assuming the instantclient_11_2 folder is located in /usr/loca/lib, link the following files:

ln /usr/local/lib/instantclient_11_2/libclntsh.dylib /usr/local/lib/libclntsh.dylib
ln /usr/local/lib/instantclient_11_2/libocci.dylib.* /usr/local/lib/libocci.dylib.*
ln /usr/local/lib/instantclient_11_2/libociei.dylib /usr/local/lib/libociei.dylib
ln /usr/local/lib/instantclient_11_2/libnnz11.dylib /usr/local/lib/libnnz11.dylib

Install pkg-config via Homebrew, brew install pkg-config.

Documentation

Index

• Variables
• func Connect(driver string, params map[string]interface{}) (*sqlx.DB, error)
• func EncodeCSV(w io.Writer, i *Iterator) error
• func EncodeJSON(w io.Writer, i *Iterator) error
• func EncodeLDJSON(w io.Writer, i *Iterator) error
• func PersistentConnect(driver string, params map[string]interface{}) (*sqlx.DB, error)
• func Shutdown()
• type Encoder
• type Iterator
  • func Execute(db *sqlx.DB, sql string, params map[string]interface{}) (*Iterator, error)
  • func (i *Iterator) Close()
  • func (i *Iterator) Next() bool
  • func (i *Iterator) Scan(r Record) error
  • func (i *Iterator) ScanRow(r []interface{}) error
• type Record

Variables

var (
	MaxIdleConns    = 10
	MaxConnLifetime = 10 * time.Minute
)

var Drivers = map[string]string{
	"postgresql": "postgres",
	"postgres":   "postgres",
	"mysql":      "mysql",
	"mariadb":    "mysql",
	"sqlite":     "sqlite3",
	"mssql":      "mssql",
	"sqlserver":  "mssql",
	"oracle":     "oci8",
	"snowflake":  "snowflake",
	"odbc":       "odbc",
	"presto":     "presto",
}

Drivers contains a map of public driver names to registered driver names.

var ErrUnknownDriver = errors.New("sqlagent: Unknown driver")

ErrUnknownDriver is returned when an unknown driver is used when attempting to connect.

Functions

func Connect

func Connect(driver string, params map[string]interface{}) (*sqlx.DB, error)

Connect connects to a database given a driver name and set of connection parameters. Each database supports a different set of connection parameters, however the few that are common are standardized.

- `host` - The database host. - `port` - The database port. - `user` - The username to authenticate with. - `password` - The password to authenticate with. - `database` - The database to connect to.

Other known database-specific parameters will be appended to the connection string and the remaining will be ignored.

func EncodeCSV

func EncodeCSV(w io.Writer, i *Iterator) error

func EncodeJSON

func EncodeJSON(w io.Writer, i *Iterator) error

EncodeJSON encodes the iterator as a JSON array of records.

func EncodeLDJSON

func EncodeLDJSON(w io.Writer, i *Iterator) error

EncodeLDJSON encodes the iterator as a line delimited stream of records.

func PersistentConnect

func PersistentConnect(driver string, params map[string]interface{}) (*sqlx.DB, error)

func Shutdown

func Shutdown()

Shutdown closes all persisted database connections.

Types

type Encoder

type Encoder func(io.Writer, *Iterator) error

Encoder provides an satisfies the encoder type.

type Iterator

type Iterator struct {
	Cols []string
	// contains filtered or unexported fields
}

Iterator provides a lazy access to the database rows.

func Execute

func Execute(db *sqlx.DB, sql string, params map[string]interface{}) (*Iterator, error)

Execute takes a database instance, SQL statement, and parameters and executes the query returning the resulting rows.

func (*Iterator) Close

func (i *Iterator) Close()

Close closes the iterator.

func (*Iterator) Next

func (i *Iterator) Next() bool

Next returns true if another row is available.

func (*Iterator) Scan

func (i *Iterator) Scan(r Record) error

Scan takes a record and scans the values of a row into the record.

func (*Iterator) ScanRow

func (i *Iterator) ScanRow(r []interface{}) error

type Record

type Record map[string]interface{}

Record is a database row keyed by column name. This requires the columns to be uniquely named.