HTTP interface for executing ad-hoc SQL queries.
Switch branches/tags
Nothing to show
Clone or download
Pull request Compare This branch is 2 commits ahead, 33 commits behind chop-dbhi:master.
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
cmd/sql-agent
.gitignore
LICENSE
Makefile
README.md
connect.go
driver.go
encoder.go

README.md

SQL Agent

GoDoc

This is a very simple fork of the original sql-agent package to remove Oracle support to make building easier on some of our test platforms since we don't need Oracle support.

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
  • Microsoft SQL Server
  • SQLite

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

{
    "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.

Details

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

Constraints

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

Development

Setup