Skip to content

Http server to monitoring SQL databases

License

Notifications You must be signed in to change notification settings

little-brother/httpsql

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

1 Commit
 
 
 
 
 
 
 
 

Repository files navigation

Httpsql is a http server to provides a simple way of monitoring SQL databases via a http request by predefined queries.

How to build app

  1. Download and install Golang
  2. Download dependencies
    go get -v -d ./
  3. Build application
    go build -ldflags="-s -w"

Also you can download binary.

How to use

Before continue you must create config.json in app folder. Below is an example:

{
  "port": "9000",
  "databases": {
  
    "demo": {
      "driver": "mysql",
      "dns": "myuser@tcp(192.168.0.101)/mydb",
      "metrics": ["now", "count", "minmax", "getbyid"]
    },
    
    "demo2": {
      "driver": "postgres",
      "dns": "host=192.168.0.101 user=home password=password dbname=mydb2 sslmode=disable",
      "metrics": ["count", "minmax"]
    }
  },

  "metrics": {
  
    "now": {
      "query": "select now()",
      "description": "Params: none. Returns current date and time."
    },

    "count": {
      "query": "select count(1) as count from #table",
      "description": "Params: table. Returns row count of table."
    },
    
    "minmax": {
      "query": "select min(#column) min, max(#column) max from #table",
      "description": "Params: table, column. Returns max and min value."
    },
    
    "getbyid": {
      "query": "select * from #table where id = $id",
      "description": "Params: table, id. Returns row with requested id."
    }
  }
}

The following links will be available for this configuration:

  • / returns all database aliases: demo and demo2
  • /demo returns all available metrics for database demo and their description
  • /demo/now returns mydb date and time
  • /demo/count?table=orders returns row count for mydb.orders
  • /demo/minmax?table=orders&column=price returns minimal and maximum price in mydb.orders
  • /demo/getbyid?table=orders&id=10 returns order detail with id = 10
  • /demo2/count?table=customers returns customer count in mydb2.customers
  • /demo2/minmax?table=...

In query #param defines a url parameter param that value will be substituted directly into the query. To avoid sql injections, all characters except a-Z0-9_.$ will be removed from value and length is limited to 64 characters. $param defines a placeholder parameter and can contains any symbols.

Request result is json or text(csv). By default data format defines by http Accept header. You can lock format by adding json or text to requested url e.g. /demo2/count?table=customers&text.

One permanent connection is used for each database. If necessary, the connection will be restored.

Supported databases

DBMS Driver Dns example
MySQL mysql myuser@tcp(192.168.0.101)/mydb
PosgreSQL postgres host=192.168.0.101 user=home password=password dbname=mydb sslmode=disable
MSSQL mssql sqlserver://username:password@host/instance?param1=value&param2=value
sqlserver://username:password@host:port?param1=value&param2=value
ADODB adodb Provider=Microsoft.Jet.OLEDB.4.0;Data Source=my.mdb;
ODBC odbc Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;Persist Security Info=False;Trusted_Connection=Yes
ClickHouse clickhouse tcp://127.0.0.1:9000?username=&debug=true
Firebird firebirdsql user:password@servername/foo/bar.fdb
SQLite3 sqlite3 D:/aaa/bbb/mydb.sqlite

Notice: most databases require additional configuration for remote connections

You can add other drivers but some of them requires additional software.