/vulcan-sql

Analytics APIs for Snowflake, BigQuery, DuckDB, PosgreSQL

Primary LanguageTypeScriptMIT LicenseMIT

What is VulcanSQL

VulcanSQL is an Analytics API generator that helps data engineers to build scalable analytics APIs using only SQL without writing any backend code.

Why VulcanSQL?

APIs are still the primary programming interface for data consumers to utilize data in their daily business applications, such as BI, reports, dashboards, spreadsheets, and web applications. However, data stored in data warehouses are not accessible for those users and tools without an API consumption layer.

VulcanSQL aims to solve that problem by translating SQL into flexible APIs; it is contextual in that it can translate APIs into the corresponding SQL based on different user personas and business contexts. It is also extendable with custom business logic and complex SQL translation.

When use VulcanSQL?

When scaling data usages outside the traditional data team to business users and application developers using APIs. VulcanSQL is the perfect solution for data using in applications.

Features

  • Parameterized SQL into scalable and secure APIs
  • Built-in API access and version control
  • Built-in self-generated API documentation
  • Integrate with existing toolsets, such as Excel / Google spreadsheet, Zapier, Retool, etc.

Data sources support

  • PosgreSQL
  • DuckDB
  • Snowflake
  • BigQuery

How VulcanSQL works?

Step 1: Parameterized your SQL.

Build APIs with just parameterized your SQL and with extendability.

Example: passing parameters from URL

select * from public.users where id = {{ context.params.userId }}

You can build an API endpoint /users with userId as input easily, You can immediately get data through API as below.

GET /users?userId=1

Response
[{
  "name": "wwwy3y3",
  "age": 30
}]

Other Examples:

1. Error Handling

If you want to throw errors based on business logic. for example, run a query first, if no data return, throw 404 not found.

{% req user %}
select * from public.users where userName = {{ context.parames.userName }} limit 1;
{% endreq %}

{% if user.value().length == 0 %}
  {% error "user not found" %}
{% endif %}

select * from public.groups where userId = {{ user.value()[0].id }};
2. Authorization

You can pass in user attributes to achieve user access control. We will build the corresponding SQL on the fly.

select
  --- masking address if query user is not admin
  {% if context.user.name == 'ADMIN' %}
    {% "address" %}
  {% elif %}
    {% "masking(address)" %}
  {% endif %},

  orderId,
  amount
from orders

--- limit the data to the store user belongs to.
where store = {{ context.user.attr.store }}
3. Validation

You can add a number validator on userId input.

  • SQL 
    select * from public.users where id = {{ context.params.userId }}
  • Schema yaml parameters: userId: in: query validators: # set your validator here. - name: 'number'

Step 2: Build self-serve documentation and catalog

VulcanSQL will automatically build documentation and catalog for you.

  • API Catalog: VulcanSQL will build an API catalog page for data consumers to learn how to use the APIs and explore data in the dashboard.

  • API Documentation: VulcanSQL will build a Swagger API page for backend engineers.

Step 3: Connect from framework & applications

On API catalog page, you can preview data or read from your applications.

  • You can Copy API URL and use it in your frontend/backend applications.
  • You can download the selected data as CSV or JSON.

  • You can follow the steps to read from Excel/Google Spreadsheet/Zapier/Retools.

Installation

Visit the documentation for installation guide.

Demo Screenshot

🔑 Login Page: Users will need to login.

📖 Catalog: After logged-in, users can see what endpoints are available to be used.

✅ Endpoint: Users can view the details of one endpoint and preview the data.

🔌 Connect: Users will be able to follow the guide and connect from their applications.

Community

Special Thanks