Instant JSON,CSV,XML & JSV data APIs around configured RDBMS tables

_db\_table.html

{{ {namedConnection:db} |> if (db && db != 'main') |> useDb  }}

```code|quiet
var ignore = ['db','fields','format','skip','take','orderBy']
var fields = qs.fields ? qs.fields.split(',').map(x => sqlQuote(x)).join(',') : '*'
var sql = `SELECT ${fields} FROM ${sqlQuote(table)}`
var filters = []
var queryMap = qs.toObjectDictionary().withoutKeys(ignore)
#each queryMap.Keys.toList()
    var search = queryMap[it.sqlVerifyFragment()].sqlVerifyFragment();
    #if search == '=null' || search == '!=null'
        `${sqlQuote(it)} ${search=='=null' ? 'IS' : 'IS NOT'} NULL` |> addTo => filters
        queryMap[it] = null
    else if search.startsWith('=')
        `${sqlQuote(it)} = @${it}` |> addTo => filters
        queryMap[it] = search.substring(1).coerce()
    else if search.startsWith('<=') || search.startsWith('>=') || search.startsWith('<>') || search.startsWith('!=')
        `${sqlQuote(it)} ${search.substring(0,2)} @${it}` |> addTo => filters
        queryMap[it] = search.substring(2).coerce()
    else if search.startsWith('<') || search.startsWith('>')
        `${sqlQuote(it)} ${search.substring(0,1)} @${it}` |> addTo => filters
        queryMap[it] = search.substring(1).coerce()
    else if search.endsWith(',')
        `${sqlQuote(it)} IN (${search.trimEnd(',').split(',').map(i => i.toLong()).join(',')})` |> addTo => filters
        queryMap[it] = null
    else if search.startsWith('%') || search.endsWith('%')
        `${sqlQuote(it).sqlCast('varchar')} LIKE @${it}` |> addTo => filters
    else
        `${sqlQuote(it).sqlCast('varchar')} = @${it}` |> addTo => filters
    /if
/each
#if !filters.isEmpty()
    sql = `${sql} WHERE ${filters.join(' AND ')}`
/if
#if qs.orderBy
    sql = `${sql} ORDER BY ${sqlOrderByFields(qs.orderBy)}`
/if
#if qs.skip || qs.take
    sql = `${sql} ${sqlLimit(qs.skip,qs.take)}`
/if
sql |> dbSelect(queryMap) |> return
```
{{ ifError |> show(sql) }}
{{htmlError}}

_table.html

{{ {namedConnection:db} |> if (db && db != 'main') |> useDb  }}

```code|quiet
var ignore = ['db','fields','format','skip','take','orderBy']
var fields = qs.fields ? qs.fields.split(',').map(x => sqlQuote(x)).join(',') : '*'
var sql = `SELECT ${fields} FROM ${sqlQuote(table)}`
var filters = []
var queryMap = qs.toObjectDictionary().withoutKeys(ignore)
#each queryMap.Keys.toList()
    var search = queryMap[it.sqlVerifyFragment()].sqlVerifyFragment();
    #if search == '=null' || search == '!=null'
        `${sqlQuote(it)} ${search=='=null' ? 'IS' : 'IS NOT'} NULL` |> addTo => filters
        queryMap[it] = null
    else if search.startsWith('=')
        `${sqlQuote(it)} = @${it}` |> addTo => filters
        queryMap[it] = search.substring(1).coerce()
    else if search.startsWith('<=') || search.startsWith('>=') || search.startsWith('<>') || search.startsWith('!=')
        `${sqlQuote(it)} ${search.substring(0,2)} @${it}` |> addTo => filters
        queryMap[it] = search.substring(2).coerce()
    else if search.startsWith('<') || search.startsWith('>')
        `${sqlQuote(it)} ${search.substring(0,1)} @${it}` |> addTo => filters
        queryMap[it] = search.substring(1).coerce()
    else if search.endsWith(',')
        `${sqlQuote(it)} IN (${search.trimEnd(',').split(',').map(i => i.toLong()).join(',')})` |> addTo => filters
        queryMap[it] = null
    else if search.startsWith('%') || search.endsWith('%')
        `${sqlQuote(it).sqlCast('varchar')} LIKE @${it}` |> addTo => filters
    else
        `${sqlQuote(it).sqlCast('varchar')} = @${it}` |> addTo => filters
    /if
/each
#if !filters.isEmpty()
    sql = `${sql} WHERE ${filters.join(' AND ')}`
/if
#if qs.orderBy
    sql = `${sql} ORDER BY ${sqlOrderByFields(qs.orderBy)}`
/if
#if qs.skip || qs.take
    sql = `${sql} ${sqlLimit(qs.skip,qs.take)}`
/if
sql |> dbSelect(queryMap) |> return
```
{{ ifError |> show(sql) }}
{{htmlError}}

app.settings

debug true
name Sharp Data

# Configure below. Supported dialects: sqlite, mysql, postgres, sqlserver
db DIALECT
db.connection CONNECTION_STRING
# db.connections[namedDb] { db:DIALECT, connection:namedDbConnection }