Bulk query SQLite database over the network

Overview

SQLiteQueryServer

Bulk query SQLite database over the network.
Way faster than SQLiteProxy!

CircleCI Coverage Status Go Report Card

Installation

  • Download a precompiled binary from https://github.com/assafmo/SQLiteQueryServer/releases

  • Or use go get:

    go get -u github.com/assafmo/SQLiteQueryServer

    This package uses github.com/mattn/go-sqlite3. Compilation errors might be resolved by reading https://github.com/mattn/go-sqlite3#compilation.

  • Or use Ubuntu PPA:

    curl -SsL https://assafmo.github.io/ppa/ubuntu/KEY.gpg | sudo apt-key add -
    sudo curl -SsL -o /etc/apt/sources.list.d/assafmo.list https://assafmo.github.io/ppa/ubuntu/assafmo.list
    sudo apt update
    sudo apt install sqlitequeryserver

Usage

Usage of SQLiteQueryServer:
  -db string
        Filesystem path of the SQLite database
  -port uint
        HTTP port to listen on (default 80)
  -query string
        SQL query to prepare for

Note: SQLiteQueryServer is optimized for the SELECT command. Other commands such as INSERT, UPDATE, DELETE, CREATE, etc might be slow because SQLiteQueryServer doesn't use transactions (yet). Also, the response format and error messages from these commands may be odd or unexpected.

Examples

Creating a server

SQLiteQueryServer --db "$DB_PATH" --query "$PARAMETERIZED_SQL_QUERY" --port "$PORT"
SQLiteQueryServer --db ./test_db/ip_dns.db --query "SELECT * FROM ip_dns WHERE dns = ?" --port 8080

This will expose the ./test_db/ip_dns.db database with the query SELECT * FROM ip_dns WHERE dns = ? on port 8080.
Requests will need to provide the query parameters.

Querying the server

echo -e "github.com\none.one.one.one\ngoogle-public-dns-a.google.com" | curl "http://localhost:8080/query" --data-binary @-
echo -e "$QUERY1_PARAM1,$QUERY1_PARAM2\n$QUERY2_PARAM1,$QUERY2_PARAM2" | curl "http://$ADDRESS:$PORT/query" --data-binary @-
curl "http://$ADDRESS:$PORT/query" -d "$PARAM_1,$PARAM_2,...,$PARAM_N"
  • Request must be a HTTP POST to "http://$ADDRESS:$PORT/query".
  • Request body must be a valid CSV.
  • Request body must not have a CSV header.
  • Each request body line is a different query.
  • Each param in a line corresponds to a query param (a question mark in the query string).
  • Static query (without any query params):
    • The request must be a HTTP GET to "http://$ADDRESS:$PORT/query".
    • The query executes only once.

Getting a response

echo -e "github.com\none.one.one.one\ngoogle-public-dns-a.google.com" | curl "http://localhost:8080/query" --data-binary @-
[
  {
    "in": ["github.com"],
    "headers": ["ip", "dns"],
    "out": [["192.30.253.112", "github.com"], ["192.30.253.113", "github.com"]]
  },
  {
    "in": ["one.one.one.one"],
    "headers": ["ip", "dns"],
    "out": [["1.1.1.1", "one.one.one.one"]]
  },
  {
    "in": ["google-public-dns-a.google.com"],
    "headers": ["ip", "dns"],
    "out": [["8.8.8.8", "google-public-dns-a.google.com"]]
  }
]
  • If response status is 200 (OK), response is a JSON array (Content-Type: application/json).
  • Each element in the array:
    • Is a result of a query
    • Has an "in" field which is an array of the input params (a request body line).
    • Has an "headers" field which is an array of headers of the SQL query result.
    • Has an "out" field which is an array of arrays of results. Each inner array is a result row.
  • Element #1 is the result of query #1, Element #2 is the result of query #2, and so forth.
  • Static query (without any query params):
    • The response JSON has only one element.

Static query

SQLiteQueryServer --db ./test_db/ip_dns.db --query "SELECT * FROM ip_dns" --port 8080
curl "http://localhost:8080/query"
[
  {
    "in": [],
    "headers": ["ip", "dns"],
    "out": [
      ["1.1.1.1", "one.one.one.one"],
      ["8.8.8.8", "google-public-dns-a.google.com"],
      ["192.30.253.112", "github.com"],
      ["192.30.253.113", "github.com"]
    ]
  }
]
Issues
  • Handle zero query params / static query

    Handle zero query params / static query

    If the query is static (0 params) and a request has e.g. 2 lines than the query needs to be executed twice.
    Right now the csv reader ignores empty lines. This causes static queries to not be executed with empty csv line.

    bug 
    opened by assafmo 1
  • Support request and response streaming

    Support request and response streaming

    We want to read a request line, execute the query, flush the response and continue to the next request line. This will prevent huge requests to buffer in the application's RAM.

    The native go http server closes the request body after the first byte is flushed to the response.
    So to flush the response as soon as possible we need to read the entire request, and to read the request line by line we need to buffer the entire response.
    In both situations huge requests or responses will cause huge RAM usage.

    enhancement 
    opened by assafmo 1
Owner
Assaf Morami
Assaf Morami
A Go (golang) package that enhances the standard database/sql package by providing powerful data retrieval methods as well as DB-agnostic query building capabilities.

ozzo-dbx Summary Description Requirements Installation Supported Databases Getting Started Connecting to Database Executing Queries Binding Parameters

Ozzo Framework 551 Jun 3, 2022
Command line tool to generate idiomatic Go code for SQL databases supporting PostgreSQL, MySQL, SQLite, Oracle, and Microsoft SQL Server

About xo xo is a command-line tool to generate Go code based on a database schema or a custom query. xo works by using database metadata and SQL intro

XO 3.1k Jul 1, 2022
SQL builder and query library for golang

__ _ ___ __ _ _ _ / _` |/ _ \ / _` | | | | | (_| | (_) | (_| | |_| | \__, |\___/ \__, |\__,_| |___/ |_| goqu is an expressive SQL bu

Doug Martin 1.6k Jun 24, 2022
SQL query builder for Go

GoSQL Query builder with some handy utility functions. Documentation For full documentation see the pkg.go.dev or GitBook. Examples // Open database a

Travis Harmon 23 Apr 25, 2022
Type safe SQL builder with code generation and automatic query result data mapping

Jet Jet is a complete solution for efficient and high performance database access, consisting of type-safe SQL builder with code generation and automa

null 723 Jun 24, 2022
Type safe SQL query builder and struct mapper for Go

sq (Structured Query) ?? ?? sq is a code-generated, type safe query builder and struct mapper for Go. ?? ?? Documentation • Reference • Examples This

null 162 Jun 24, 2022
Fast SQL query builder for Go

sqlf A fast SQL query builder for Go. sqlf statement builder provides a way to: Combine SQL statements from fragments of raw SQL and arguments that ma

Vlad Glushchuk 70 Jun 24, 2022
Query AWS Athena and download the result as CSV.

Overview This tool can download an Athena SQL query results in CSV format. Installation Using Homebrew: $ brew tap flowerinthenight/tap $ brew install

null 5 Nov 11, 2021
Query git repositories with SQL. Generate reports, perform status checks, analyze codebases. 🔍 📊

askgit askgit is a command-line tool for running SQL queries on git repositories. It's meant for ad-hoc querying of git repositories on disk through a

Augmentable 3.1k Jul 4, 2022
Simple query builder for MongoDB

?? greenleaf - simple, type safe and easy to use query builder for MongoDB Installation To install use: go get github.com/slavabobik/greenleaf Quick

Slava 72 Jun 22, 2022
Easy JSON Query Processor with a Lispy syntax in Go

jql Hey there! You're probably here cause you're fed up with other json query processors being too complicated to use for anything surpassing simple s

Jacob Martin 863 Jun 21, 2022
gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

gosq is a parsing engine for a simplicity-focused, template-based SQL query builder for Go.

Sang-gon Lee 49 Apr 26, 2022
SQL query helper

SQL query helper

Aleksey Nikitin 1 Nov 7, 2021
Bluge, will this document match this query?

sour Will this bluge.Document match this bluge.Query? This library allows you to efficiently answer this question. s := sour.New(bluge.InMemoryOnlyCo

Bluge Labs 3 Jun 27, 2022
Tag based url Query parameters Constructor.

taqc ?? Tag based url Query parameters Constructor. (This is pronounced as same as "taxi") Synopsis type Query struct { Foo string `ta

moznion 2 Jan 11, 2022
qclean lets you to clean up search query in japanese.

qclean qclean lets you to clean up search query in japanese. This is mainly used to remove wasted space. Quick Start package main var cleaner *qclean

po3rin 0 Jan 4, 2022
Querydecoder - Optional query parameter decoder for Golang

Optional query parameter decoder for Golang Example import ( "github.com/ritwic

Ritwick Dey 8 Jun 10, 2022
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 342 Jun 16, 2022
Database Abstraction Layer (dbal) for Go. Support SQL builder and get result easily (now only support mysql)

godbal Database Abstraction Layer (dbal) for go (now only support mysql) Motivation I wanted a DBAL that No ORM、No Reflect、Concurrency Save, support S

徐佳军 53 Jan 23, 2022