Generate APIs from SQL Queries
Squealy lets you build readonly REST APIs from template-based SQL or JSON queries. It supports most relational databases, and some NoSQL databases like ElasticSearch.
The SQL query is written as jinja template. You can embed API parameters and user attributes directly into your query, and use conditional logic & looping constructs. In addtion, you can reuse code snipppets, create custom filters and define macros.
Squealy is free from sql injection. It is safe to embed api parameters directly in the query. The templates are evaluated server side, not client side. Internally, squealy uses JinjaSQL to bind the parameters.
Developers write the SQL query plus some meta-data in a yaml file. At run time, Squealy uses these yaml files to serve the APIs. Squealy has no other metadata other than these yaml files. If you version control the yaml files, you get a completely reproducible setup.
The generated APIs support authentication via JWT, fine-grained authorization, row level security, API parameters and custom validation. The APIs setup appropriate CORS headers and Cache-Control headers as well.
Squealy is completely stateless and has no runtime dependencies. It runs in a stateless docker container, executes sql queries against the database(s) you configure, and returns the data as JSON.
Squealy supports the following databases -
Pre-requisites: You must have docker and docker-compose installed
TBD
We built Squealy primarily for embedded analytics - i.e. when you want dashboards and charts as part of an existing application. A typical example is a line of business application for employees or vendors. These users would like to see a dashboard with some metrics / kpi as part of the application.
Using a standard BI tool like tableau is very costly, because these tools typically charge per user. Also, the user experience is poor, because the user has to use two different applications.
With Squealy, you would do the following -
Using such an approach, the application has complete control on the look and feel of the dashboard, giving a rich user experience.
Postgrest/pREST automatically create CRUD APIs for tables & views. At the moment, Postgrest/pREST only works with Postgres. Squealy focusses only on read APIs, primarily meant for analytics, reporting and business intelligence use cases.
For security, Postgrest relies on postgres roles. To achieve row level security, you need to rely on Postres Row Level Security, which is tricky to setup correctly. In Squealy, you can directly embed user attributes and api parameters into the sql query. This makes it super simple to achieve fine grained security.
In Postgres/pREST, the data is fetched from a single view or table. This is good for CRUD use cases. But when it comes to reporting or analytics, you typically want to do complex joins, window functions and aggregations - and that's when Squealy comes in handly.
Summary: Use Postgrest/pREST if your use case is transactional, or you want CRUD operations. Use Squealy for read-only use cases, specially in analytics, reporting & business intelligence applications.
Yes, it is.
Suppose your template sql query is this
SELECT month, sum(revenue)
FROM sales s
where s.region = {{ user.region }}
{% if params.month %} and s.month = {{ params.month }} {% endif %}
If month
parameter is provided at runtime, Squealy will execute the following query
SELECT month, sum(revenue)
FROM sales s
where s.region = ?
and s.month = ?
If month
parameter is not provided, the following query will be execute:
SELECT month, sum(revenue)
FROM sales s
where s.region = ?
In both cases, Squealy will bind the parameters and send them to the database. For more details on how this works, see JinjaSQL.
It should be possible to use Squealy as a library, though we don't support that use case at the moment. As of now, the recommended approach is to deploy Squealy as a separate container using the provided Docker image.
Let's create an API that returns monthly sales aggregate data. The API should take an optional parameter month
to filter records. Additionally, the API must return records from region(s) that the authenticated user has access to.
This YAML file
kind: resource
path: /reports/monthly-sales
datasource: salesdb
authentication:
requires_authentication: true
formatter: JsonFormatter
query: |
SELECT month, sum(sales) as sales from (
SELECT 'jan' as month, 'north' as region, 10 as sales UNION ALL
SELECT 'feb' as month, 'north' as region, 20 as sales UNION ALL
SELECT 'jan' as month, 'south' as region, 30 as sales UNION ALL
SELECT 'feb' as month, 'south' as region, 40 as sales
) s
WHERE s.region in {{user.regions | inclause }}
{% if params.month %} and s.month = {{ params.month }} {% endif %}
GROUP BY month
ORDER BY month desc
Notice the following in the yaml file:
params
object contains all the query parameters passed to the API at runtime.user
object represents the authenticated user calling the API. Squealy supports JWT tokens for authentication. The user object is created from the JWT token, and hence attributes inside the user object cannot be manipulated.Based on this, Squealy creates an API at /reports/monthly-sales
. This API:
month
.Authorization
HTTP header or an accessToken
query parameterregion
attribute.Depending on the user's region that is set in the JWT token:
The response also depends on the month
parameter:
month
paramater is provided, the data will be restricted to that month onlymonth
parameter is absent, you will see data from all the months.The response of the API will be something like this -
{
"data": [
{
"month": "jan",
"sales": 10
},
{
"month": "feb",
"sales": 20
}
]
}
But if you change the formatter to SeriesFormatter
, the response format will be something more suitable to generating charts using javascript libraries.
{
"data": {
"month": ["jan", "feb"],
"sales": [10, 20]
}
}
Squealy has several other features, such as -
openssl genrsa -out private.pem 1024
openssl rsa -in private.pem -outform PEM -pubout -out public.pem