write APIs using direct SQL queries with no hassle, let's rethink about SQL

Overview

SQLer

SQL-er is a tiny portable server enables you to write APIs using SQL query to be executed when anyone hits it, also it enables you to define validation rules so you can validate the request body/query params, as well as data transformation using simple javascript syntax. sqler uses nginx style configuration language (HCL) amd javascript engine for custom expressions.

Table Of Contents

Features

  • Standalone with no dependencies.
  • Works with most of SQL databases out there including (SQL Server, MYSQL, SQLITE, PostgreSQL, Cockroachdb)
  • Built-in RESTful server
  • Built-in RESP Redis Protocol, you connect to SQLer using any redis client
  • Built-in Javascript interpreter to easily transform the result
  • Built-in Validators
  • Automatically uses prepared statements
  • Uses (HCL) configuration language
  • You can load multiple configuration files not just one, based on unix glob style pattern
  • Each SQL query could be named as Macro
  • Uses Javascript custom expressions.
  • Each macro has its own Context (query params + body params) as .Input which is map[string]interface{}, and .Utils which is a list of helper functions, currently it contains only SQLEscape.
  • You can define authorizers, an authorizer is just a simple webhook that enables sqler to verify whether the request should be done or not.
  • Trigger a webhook or another macro when a specific macro get executed.
  • Schedule specific macros to run at specific time using simple cron syntax.

Quick Tour

  • You install sqler using the right binary for your os from the releases page.
  • Let's say that you downloaded sqler_darwin_amd64
  • Let's rename it to sqler, and copy it to /usr/local/bin
  • Now just run sqler -h, you will the next
                         ____   ___  _
                        / ___| / _ \| |    ___ _ __
                        \___ \| | | | |   / _ \ '__|
                         ___) | |_| | |__|  __/ |
                        |____/ \__\_\_____\___|_|

        turn your SQL queries into safe valid RESTful apis.


  -config string
        the config file(s) that contains your endpoints configs, it accepts comma seprated list of glob style pattern (default "./config.example.hcl")
  -driver string
        the sql driver to be used (default "mysql")
  -dsn string
        the data source name for the selected engine (default "root:[email protected](127.0.0.1)/test?multiStatements=true")
  -resp string
        the resp (redis protocol) server listen address (default ":3678")
  -rest string
        the http restful api listen address (default ":8025")
  -workers int
        the maximum workers count (default 4)
  • you can specifiy multiple files for -config as configuration, i.e -config="/my/config/dir/*.hcl,/my/config/dir2/*.hcl"
  • you need specify which driver you need and its dsn from the following:
Driver DSN
mysql usrname:[email protected](server:port)/dbname?option1=value1&...
postgres postgresql://username:[email protected]:port/dbname?option1=value1
sqlite3 /path/to/db.sqlite?option1=value1
sqlserver sqlserver://username:[email protected]/instance?param1=value&param2=value
sqlserver://username:[email protected]:port?param1=value&param2=value
sqlserver://[email protected]/SQLExpress?database=master&connection+timeout=30
mssql server=localhost\\SQLExpress;user id=sa;database=master;app name=MyAppName
server=localhost;user id=sa;database=master;app name=MyAppName
odbc:server=localhost\\SQLExpress;user id=sa;database=master;app name=MyAppName
odbc:server=localhost;user id=sa;database=master;app name=MyAppName
hdb (SAP HANA) hdb://user:[email protected]:port
clickhouse (Yandex ClickHouse) tcp://host1:9000?username=user&password=qwerty&database=clicks&read_timeout=10&write_timeout=20&alt_hosts=host2:9000,host3:9000

Supported DBMSs

  • MYSQL, TiDB, MariaDB, Percona and any MYSQL compatible server uses mysql driver.
  • PostgreSQL, CockroachDB and any PostgreSQL compatible server uses postgres driver.
  • SQL Server, MSSQL, ADO, ODBC uses sqlserver or mssql driver.
  • SQLITE, uses sqlite3 driver.
  • HANA (SAP), uses hdb driver.
  • Clickhouse, uses clickhouse driver.

Docker

SQLer has a docker image called alash3al/sqler it is an automated build, you can use it like the following:

# run the help message
docker run --rm alash3al/sqler --help

# connect to a local mysql
docker run --network=host alash3al/sqler -driver=mysql -dsn=usr:[email protected](127.0.0.1:3306)/dbname

# connect to another mysql container
docker run -link mysql alash3al/sqler -driver=mysql -dsn=usr:[email protected](mysql:3306)/dbname

Configuration Overview

// create a macro/endpoint called "_boot",
// this macro is private "used within other macros" 
// because it starts with "_".
_boot {
    // the query we want to execute
    exec = <<SQL
        CREATE TABLE IF NOT EXISTS `users` (
            `ID` INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
            `name` VARCHAR(30) DEFAULT "@anonymous",
            `email` VARCHAR(30) DEFAULT "@anonymous",
            `password` VARCHAR(200) DEFAULT "",
            `time` INT UNSIGNED
        );
    SQL
}

// adduser macro/endpoint, just hit `/adduser` with
// a `?user_name=&user_email=` or json `POST` request
// with the same fields.
adduser {
    validators {
        user_name_is_empty = "$input.user_name && $input.user_name.trim().length > 0"
        user_email_is_empty = "$input.user_email && $input.user_email.trim(' ').length > 0"
        user_password_is_not_ok = "$input.user_password && $input.user_password.trim(' ').length > 5"
    }

    bind {
        name = "$input.user_name"
        email = "$input.user_email"
        password = "$input.user_password"
    }

    methods = ["POST"]

    authorizer = <<JS
        (function(){
            log("use this for debugging")
            token = $input.http_authorization
            response = fetch("http://requestbin.fullcontact.com/zxpjigzx", {
                headers: {
                    "Authorization": token
                }
            })
            if ( response.statusCode != 200 ) {
                return false
            }
            return true
        })()
    JS

    // include some macros we declared before
    include = ["_boot"]

    exec = <<SQL
        INSERT INTO users(name, email, password, time) VALUES(:name, :email, :password, UNIX_TIMESTAMP());
        SELECT * FROM users WHERE id = LAST_INSERT_ID();
    SQL
}

// list all databases, and run a transformer function
databases {
    exec = "SHOW DATABASES"
    cron = "* * * * *"
    trigger {
        webhook = "http://some.url/hook"
    }
}

// list all tables from all databases
tables {
    exec = "SELECT `table_schema` as `database`, `table_name` as `table` FROM INFORMATION_SCHEMA.tables"
}

// a macro that aggregates `databases` macro and `tables` macro into one macro
databases_tables {
    aggregate = ["databases", "tables"]
}

REST vs RESP

RESTful server could be used to interact directly with i.e mobile, browser, ... etc, in this mode SQLer is protected by authorizers, which gives you the ability to check authorization against another 3rd-party api.
Each macro you add to the configuration file(s) you can access to it by issuing a http request to /<macro-name>, every query param and json body will be passed to the macro .Input.

RESP server is just a basic REDIS compatible server, you connect to it using any REDIS client out there, even redis-cli, just open redis-cli -p 3678 list to list all available macros (commands), you can execute any macro as a redis command and pass the arguments as a json encoded data, i.e redis-cli -p 3678 adduser "{\"user_name\": \"u6\", \"user_email\": \"[email protected]\", \"user_password\":\"[email protected]\"}".

Sanitization

SQLer uses prepared statements, you can bind use inputs like the following:

addpost {
    // $input is a global variable holds all request inputs,
    // including the http headers too (prefixed with `http_`)
    // all http header keys are normalized to be in this form 
    // `http_x_header_example`, `http_authorization` ... etc in lower case.
    bind {
        title = "$input.post_title"
        content = "$input.post_content"
        user_id = "$input.post_user"
    }

    exec = <<SQL
        INSERT INTO posts(user_id, title, content) VALUES(:user_id, :title, :content);
        SELECT * FROM posts WHERE id = LAST_INSERT_ID();
    SQL
}

Validation

Data validation is very easy in SQLer, it is all about simple javascript expression like this:

addpost {
    // if any rule returns false, 
    // SQLer will return 422 code, with invalid rules.
    // 
    // $input is a global variable holds all request inputs,
    // including the http headers too (prefixed with `http_`)
    // all http header keys are normalized to be in this form 
    // `http_x_header_example`, `http_authorization` ... etc in lower case.
    validators {
        post_title_length = "$input.post_title && $input.post_title.trim().length > 0"
        post_content_length = "$input.post_content && $input.post_content.length > 0"
        post_user = "$input.post_user"
    }

    bind {
        title = "$input.post_title"
        content = "$input.post_content"
        user_id = "$input.post_user"
    }

    exec = <<SQL
        INSERT INTO posts(user_id, title, content) VALUES(:user_id, :title, :content);
        SELECT * FROM posts WHERE id = LAST_INSERT_ID();
    SQL
}

Authorization

If you want to expose SQLer as a direct api to API consumers, you will need to add an authorization layer on top of it, let's see how to do that

addpost {
    authorizer = <<JS
        (function(){
            // $input is a global variable holds all request inputs,
            // including the http headers too (prefixed with `http_`)
            // all http header keys are normalized to be in this form 
            // `http_x_header_example`, `http_authorization` ... etc in lower case.
            token = $input.http_authorization
            response = fetch("http://requestbin.fullcontact.com/zxpjigzx", {
                headers: {
                    "Authorization": token
                }
            })
            if ( response.statusCode != 200 ) {
                return false
            }
            return true
        })()
    JS
}

using that trick, you can use any third-party Authentication service that will remove that hassle from your code.

Data Transformation

In some cases we need to transform the resulted data into something more friendly to our API consumers, so I added javascript interpreter to SQLer so we can transform our data, each js code has a global variable called $result, it holds the result of the exec section, you should write your code like the following:

// list all databases, and run a transformer function
databases {
    exec = "SHOW DATABASES"

    transformer = <<JS
        // there is a global variable called `$result`,
        // `$result` holds the result of the sql execution.
        (function(){
            newResult = []

            for ( i in $result ) {
                newResult.push($result[i].Database)
            }

            return newResult
        })()
    JS
}

Aggregators

SQLer helps you to merge multiple macros into one to minimize the API calls number, see the example bellow

databases {
    exec = "SHOW DATABASES"

    transformer = <<JS
        // there is a global variable called `$result`,
        // `$result` holds the result of the sql execution.
        (function(){
            newResult = []

            for ( i in $result ) {
                newResult.push($result[i].Database)
            }

            return newResult
        })()
    JS
}

tables {
    exec = "SELECT `table_schema` as `database`, `table_name` as `table` FROM INFORMATION_SCHEMA.tables"
    transformer = <<JS
        (function(){
            $ret = {}
            for (  i in $result ) {
                if ( ! $ret[$result[i].database] ) {
                    $ret[$result[i].database] = [];
                }
                $ret[$result[i].database].push($result[i].table)
            }
            return $ret
        })()
    JS
}

databasesAndTables {
    aggregate {
        databases = "current_databases"
        tables = "current_tables"
    }
}

Issue/Suggestion/Contribution ?

SQLer is your software, feel free to open an issue with your feature(s), suggestions, ... etc, also you can easily contribute even you aren't a Go developer, you can write wikis it is open for all, let's make SQLer more powerful.

Author

I'm Mohamed Al Ashaal, just a problem solver :), you can view more projects from me here, and here is my email [email protected]

License

Copyright 2019 The SQLer Authors. All rights reserved. Use of this source code is governed by a Apache 2.0 license that can be found in the LICENSE file.

Issues
  • sqlite3 is not supported ?

    sqlite3 is not supported ?

    It does not appear that sqlite3 is supported under Linux. I downloaded the latest vesion (2.0) onto my Linux workstation and attempted to run the following

    $ sqler -driver sqlite3 -dsn /home/restagner/sqlite/roberts.db -config /home/restagner/SQLer/config.hcl 
    [sqlite3] /home/restagner/sqlite/roberts.db - connection error - (Binary was compiled with 'CGO_ENABLED=0', go-sqlite3 requires cgo to work. This is a stub)
    

    Am I missing something obvious, here?

    opened by restagner 7
  • how can I get the POST json?

    how can I get the POST json?

    When I POST the json payload:

    ` import requests

    payload = {'a':'v1','u':'v2'}

    r = requests.post("http://127.0.0.1:8025/call", data=payload) `

    I got $input :

    {"http_content_type":"application/x-www-form-urlencoded","http_user_agent":"python-requests/2.22.0","http_accept_encoding":"gzip, deflate","http_accept":"*/*","http_connection":"keep-alive","http_content_length":"9"}

    How can I get the http_content?

    Thanks.

    opened by majj 4
  • use different sql command separator

    use different sql command separator

    Current separator ; causes problem when using BEGIN TRANSACTION or CREATE TRIGGER, example (using sqlite3):

    _boot {
        exec = <<SQL
    
            CREATE TABLE IF NOT EXISTS "Bucket" (
                "name" VARCHAR(255) NOT NULL PRIMARY KEY,
                "dir" VARCHAR(255) UNIQUE NOT NULL,
                "createdAt" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );
            CREATE TRIGGER IF NOT EXISTS "trig1" 
            BEFORE INSERT ON "Bucket"
            BEGIN
                SELECT RAISE(ABORT, 'Dir can not be /dev') WHERE NEW.dir like '/dev%';
            END;
       SQL
    }
    

    It generates sql commands like these:

    CREATE TRIGGER IF NOT EXISTS "trig1" 
            BEFORE INSERT ON "Bucket"
            BEGIN
                SELECT RAISE(ABORT, 'Dir can not be /dev') WHERE NEW.dir like '/dev%';
    

    and

    END;
    
    opened by masgari 3
  • Cannot get source to build due to everything being in main package

    Cannot get source to build due to everything being in main package

    I have downloaded the source, and I wanted to see if I could build it (go get github.com/alash3al/sqler && cd ~/go/src/github.com/alash3al/sqler && go build main.go) so that I could either Dockerize it or build from source since I am on a Mac. I wasn't able to do so as all of the constants are in different files, but under the same main which causes build errors. Is there a way I can submit a PR organizing the files better into packages and adding a MakeFile or some other way to so that not only will these errors stop popping up when building from source, but also so that the build process/install process is easier on you and the community?

    opened by ryan-berger 3
  • Is it possible make config more flexible with input?

    Is it possible make config more flexible with input?

    If I want cols and table pass from input.

    And how to pass array to config?

    some idea from sigil, maybe useful? https://github.com/gliderlabs/sigil

    
    get {
    	bind {
    		table = "$input.table"
                     cols = "$input.cols"
    	}
    
    	methods = ["GET"]
    
    	exec = <<SQL
    		SELECT :cols FROM :table;
    	SQL
    }
    
    
    opened by HillLiu 3
  • QUESTION  :  Stored Procedures and Scalability

    QUESTION : Stored Procedures and Scalability

    At first, I would like to say thanks for all your efforts :tada: :tada: :tada: :tada:. You have built a fantastic product.

    I would like to ask 2 questions

    1. I want to use it just for calling stored procedures, How do I call it from MSSQL?
    2. I would like to use it for huge systems like full-fledged ERP (db would be MSSQL only), so do I need to worry about scalability?

    Thanks again for your all efforts and support.

    opened by aniruddhha 2
  • Support data migrations

    Support data migrations

    I presume this is needed as a formal method because you need to check what version the db is at.

    Normally data migrations check versions so that they know which migration scripts should be run. There are other approaches too like naming each script with a daytime stamp and internally storing the datetine inside the db so that a user can just through all scripts at the db and it only runs the scripts after its internal datetime stamp.

    opened by ghost 2
  • result column order is not respected

    result column order is not respected

    In my sqler.hcl is the stanza

    logins {
        methods = ["GET"]
        exec = "SELECT id,user,pass,source,time FROM logins ORDER BY id DESC LIMIT 50;"
    }
    

    however the resulting json has the columns in alphabetical order:-

    id, pass, source, time, user
    
    question 
    opened by Chaz6 2
  • Authorizer calls

    Authorizer calls

    Hello, Are the authorize headers correctly used when contacting an authorizer endpoint? To me it seems the Authorize: BEARER etc isn't passed on to the endpoint.

    opened by onimatrix81 2
  • Error reporting (Question)

    Error reporting (Question)

    Can SQLer be used to run a sql script (.sql file) that include multiple sql statements (including temp tables, views, etc.)?

    If so, how does it return errors if they occur in some of the statements?

    Thanks.

    question 
    opened by puruzio 2
  • Best practise for having sqler always running on the server

    Best practise for having sqler always running on the server

    Hi, just wondering what's the best practise for always running the sql on a windows server.

    I can setup a batch file to run the sql with the connections & config files and set a windows task to execute it on windows login, but wondering if there is a better way of doing this?

    Thanks

    opened by nikolai-belkin 1
Releases(v2.2)
Owner
Mohammed Al Ashaal
a software engineer 🤓
Mohammed Al Ashaal
Parses a file and associate SQL queries to a map. Useful for separating SQL from code logic

goyesql This package is based on nleof/goyesql but is not compatible with it any more. This package introduces support for arbitrary tag types and cha

null 0 Oct 20, 2021
pggen - generate type safe Go methods from Postgres SQL queries

pggen - generate type safe Go methods from Postgres SQL queries pggen is a tool that generates Go code to provide a typesafe wrapper around Postgres q

Joe Schafer 171 Jun 18, 2022
A tool to run queries in defined frequency and expose the count as prometheus metrics. Supports MongoDB and SQL

query2metric A tool to run db queries in defined frequency and expose the count as prometheus metrics. Why ? Product metrics play an important role in

S Santhosh Nagaraj 19 Jul 1, 2022
CLI tool that can execute SQL queries on CSV, LTSV, JSON and TBLN. Can output to various formats.

trdsql CLI tool that can execute SQL queries on CSV, LTSV, JSON and TBLN. It is a tool like q, textql and others. The difference from these tools is t

Noboru Saito 1.2k Jun 22, 2022
sqlbench measures and compares the execution time of one or more SQL queries.

sqlbench sqlbench measures and compares the execution time of one or more SQL queries. The main use case is benchmarking simple CPU-bound query varian

Felix Geisendörfer 350 May 9, 2022
SQL API is designed to be able to run queries on databases without any configuration by simple HTTP call.

SQL API SQL API is designed to be able to run queries on databases without any configuration by simple HTTP call. The request contains the DB credenti

Çiçeksepeti Tech 24 Jun 21, 2022
Get data from .csv files use SQL-like queries.

csvql Get data from .csv files use SQL-like queries. Задание Необходимо написать консольную программу, которая по заданному клиентом запросу осуществл

Mikhail Belov 0 Dec 7, 2021
Opionated sql formatter for use with .go files containing backticked queries

fumpt-the-sql Opionated sql formatter for use with .go files containing backticked queries. Uses https://sqlformat.darold.net/ for the actual sql form

Geoff Cowan 1 Dec 10, 2021
Run SQL queries against JSON, CSV, Excel, Parquet, and more.

Run SQL queries against JSON, CSV, Excel, Parquet, and more This is a CLI companion to DataStation (a GUI) for running SQL queries against data files.

Multiprocess Labs LLC 2.4k Jul 3, 2022
Querycrate - A simple library that loads and keeps SQL queries from files

QueryCrate A simple library for loading & getting string queries from files. How

A Really Fake Floordiv Since 4th January, 2022 1 Feb 15, 2022
Mergestat - a command-line tool for running SQL queries on git repositories and related data sources

Query git repositories with SQL. Generate reports, perform status checks, analyze codebases. ?? ??

MergeStat 3.1k Jul 3, 2022
A database connection wrapper to cache prepared statements by transforming queries to use with array arguments.

sqlpp sqlpp is a sql(MySQL and PostgreSQL) database connection wrapper to cache prepared statements by transforming queries ("...in (?)...", []) to us

Nazım Parlar 9 Feb 9, 2022
A reverse proxy for postgres which rewrites queries.

pg-rewrite-proxy A reverse proxy for postgres which rewrites queries. Arbitrary rewriting is supported by supplying an LUA script to the proxy applica

Patients Know Best 8 Jun 16, 2022
A simple Golang-based application that queries a PostgreSQL database

Qwik-E-Mart Demo App A simple Golang-based application that queries a PostgreSQL database named qwikemart to read and return customer data stored in t

Joe Garcia 0 Nov 6, 2021
This Service collects slow queries and returns them in response.

pgsql-api-go This Service collects slow queries and returns them in response. Status This service is the very first version of the project. App is up

ngh 0 Dec 30, 2021
Go-postgres - go-postgres library provide NoSQL functionality which can execute queries with pool of connections

GO Postgres go-postgres library provide NoSQL functionality which can execute queries with pool of connections. What is this repository for? Establish

Damindu Lakmal 0 Dec 31, 2021
Database wrapper that manage read write connections

rwdb Database wrapper that manage read write connections Install go get github.com/andizzle/rwdb Create connections package main import "github.com/

Andy Zhang 14 Jan 23, 2022
Go-sql-reader - Go utility to read the externalised sql with predefined tags

go-sql-reader go utility to read the externalised sql with predefined tags Usage

null 0 Jan 25, 2022
OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases and file formats using SQL.

OctoSQL OctoSQL is a query tool that allows you to join, analyse and transform data from multiple databases, streaming sources and file formats using

Jacob Martin 3.1k Jun 27, 2022