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
  • gRPC support

    gRPC support

    Thanks for the great package!

    I'll just ask the obligatory question: any plans to support gRPC?

    opened by sudomabider 4
  • Where to put sqlconnection string and driver ?

    Where to put sqlconnection string and driver ?

    In the documentation , there's no place to specify driver and sql connection string. Or am i missing something here ? Thanks you very much.

    question 
    opened by revskill10 4
  • 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
  • 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
  • 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
  • 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
  • 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
  • How to use multi-level routing ?

    How to use multi-level routing ?

    test/play

    opened by gaoconggit 0
Releases(v2.2)
Owner
Mohammed Al Ashaal
a software engineer 🤓
Mohammed Al Ashaal
xorm是一个简单而强大的Go语言ORM库,通过它可以使数据库操作非常简便。本库是基于原版xorm的定制增强版本,为xorm提供类似ibatis的配置文件及动态SQL支持,支持AcitveRecord操作

xorm xorm是一个简单而强大的Go语言ORM库. 通过它可以使数据库操作非常简便。 说明 本库是基于原版 xorm:https://github.com/go-xorm/xorm 的定制增强版本,由于本定制版有第三方库依赖(原版xorm无任何第三方库依赖),原版xorm要保持对第三方库零依赖特

null 1.5k Oct 21, 2021
A MySQL-compatible relational database with a storage agnostic query engine. Implemented in pure Go.

go-mysql-server go-mysql-server is a SQL engine which parses standard SQL (based on MySQL syntax) and executes queries on data sources of your choice.

DoltHub 634 Oct 23, 2021
write APIs using direct SQL queries with no hassle, let's rethink about SQL

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 val

Mohammed Al Ashaal 1.9k Oct 18, 2021
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
A fully-featured AWS Athena database driver (+ athenareader https://github.com/uber/athenadriver/tree/master/athenareader)

?? athenadriver - A fully-featured AWS Athena database driver for Go ?? athenareader - A moneywise command line utililty to query athena in command li

Uber Open Source 87 Oct 7, 2021
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 343 Sep 15, 2021
go mysql driver, support distributed transaction

Go-MySQL-Driver A MySQL-Driver for Go's database/sql package Features Requirements Installation Usage DSN (Data Source Name) Password Protocol Address

Open Transaction 26 Sep 22, 2021
Universal command-line interface for SQL databases

usql A universal command-line interface for PostgreSQL, MySQL, Oracle Database, SQLite3, Microsoft SQL Server, and many other databases including NoSQ

XO 6.8k Oct 16, 2021
A Go SQL query builder and struct mapper.

godb - a Go query builder and struct mapper godb is a simple Go query builder and struct mapper, not a full-featured ORM. godb does not manage relatio

Samuel GAY 673 Oct 14, 2021
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 2.5k Oct 18, 2021
MySQL Storage engine conversion,Support mutual conversion between MyISAM and InnoDB engines.

econvert MySQL Storage engine conversion 简介 此工具用于MySQL存储引擎转换,支持CTAS和ALTER两种模式,目前只支持MyISAM和InnoDB存储引擎相互转换,其它引擎尚不支持。 注意:当对表进行引擎转换时,建议业务停止访问或者极少量访问时进行。 原

null 5 Sep 5, 2021
Goose database migration tool - fork of https://bitbucket.org/liamstask/goose

goose Goose is a database migration tool. Manage your database schema by creating incremental SQL changes or Go functions. Goals of this fork github.c

Pressly Inc. 1.9k Oct 14, 2021
Workshop: Building data-intensive apps with SingleStore, Redpanda, and Golang

This repo provides a starting point for building applications using SingleStore, Redpanda (by Vectorized), and the Go language. SingleStore is a scale-out relational database built for data-intensive workloads. Redpanda is a Kafka API compatible streaming platform for mission-critical workloads created by the team at Vectorized.

SingleStore Labs 9 Oct 1, 2021
sqlx is a library which provides a set of extensions on go's standard database/sql library

sqlx is a library which provides a set of extensions on go's standard database/sql library. The sqlx versions of sql.DB, sql.TX, sql.Stmt, et al. all leave the underlying interfaces untouched, so that their interfaces are a superset on the standard ones. This makes it relatively painless to integrate existing codebases using database/sql with sqlx.

Jason Moiron 10.9k Oct 23, 2021
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 Oct 21, 2020
ClickHouse http proxy and load balancer

chproxy English | 简体中文 Chproxy, is an http proxy and load balancer for ClickHouse database. It provides the following features: May proxy requests to

Vertamedia 725 Oct 12, 2021
Go package for sharding databases ( Supports every ORM or raw SQL )

Octillery Octillery is a Go package for sharding databases. It can use with every OR Mapping library ( xorm , gorp , gorm , dbr ...) implementing data

BlasTrain Co., Ltd. 145 Oct 11, 2021
Zero boilerplate database operations for Go

(Now compatible with MySQL and PostgreSQL!) Everyone knows that performing simple DATABASE queries in Go takes numerous lines of code that is often re

null 318 Oct 16, 2021
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 649 Oct 21, 2021