Blazer JSON API

An extension to Blazer to enable exposing your queries as JSON via API, so that they can be consumed outside of Blazer by your application. Use Blazer for powering in app charts using a charting library of your choice.

Features

Powered by SQL Author APIs quickly using Blazers SQL based IDE. Particular useful for private/internal APIs that fall outside your standard API endpoints or where responses need to be taylored to suit a specific charting library.
No deploy APIs Experimental APIs can be authored and iterated on quickly via Blazer without the need to do a deploy. Were a team is split between frontend and backend, this greatly increases collaboration and speed.
Flexible structure JSON response structure can be controlled directly in SQL by using a column naming convention (double underscore __ denotes a nesting by default, but can be overridden)
Security You'll likely want to lock down API access so APIs are authenticated separately to the standard Blazer auth model, so authentication is enabled using HTTP basic authentication to avoid granting everyone with access to Blazer also access to your APIs.
URL parameters URL parameters are also supported via Blazers query variables meaning the APIs can be highly dynamic and flexible
Pagination Pagination can be controlled using query variables in combination with limits and offsets
Multiple data sources Blazer supports multiple data sources meaning you can potentially build APIs that access beyond the applications' database (e.g. ElasticSearch, Google BigQuery, SalesForce etc)
Permissions Use Blazers basic permissions mode with your own naming conventions to control access and visibility of API based queries.

Installation

Follow the installation steps described to get Blazer up and running.

Then, add this line to your application's Gemfile:

gem 'blazer_json_api'

And then execute:

$ bundle

And mount the engine in your config/routes.rb:

mount BlazerJsonAPI::Engine, at: 'blazer-api'

Authentication

Don’t forget to protect your Blazer APIs in production.

Basic authentication

Configure authentication in an initializer as follows (e.g. in initializers/blazer_json_api.rb)

BlazerJsonAPI.configure do |config|
  config.username = 'api-username'
  config.password = 'api-password'
end

Devise

Or alternatively, if you use devise, you can conditionally mount the engine using a policy or some user roles.

authenticate :user, ->(user) { user.admin? } do
  mount BlazerJsonAPI::Engine, at: 'blazer-api'
end

Usage

Create queries as normal via Blazer and use the query identifier to render the JSON via the mounted location.

e.g. /blazer-api/queries/1-all-users or /blazer-api/queries/1

URL params can be added where necessary also

e.g. /blazer-api/queries/1-all-users?username=blazer_user

Example queries

A simple index like request

Fetching specifics of all users as follows:

SELECT id, username, first_name, last_name, email, country
FROM users

This would result in the following API response

[
  {
    "id":1,
    "username":"blazer_tommy",
    "first_name":"Tom",
    "last_name":"Carey",
    "email":"[email protected]",
    "country":"Ireland"
  },
  {
    "id":2,
    "username":"blazer_john",
    "first_name":"John",
    "last_name":"Doyle",
    "email":"[email protected]",
    "country":"USA"
  }
]

A simple single resource GET request using a variable

Using a variable, a specific resource can be fetched.

Note: the use of LIMIT 1 can be used to be explicit in desiring a single record in the response, as opposed to a collection

SELECT id, username, first_name, last_name, email, country
FROM users
WHERE username={username}
LIMIT 1

Now, the username can be passed as a URL parameter to the API to fetch the relevant record.

e.g. /blazer-api/queries/1-all-users?username=blazer_john

It would result in the following response.

Using a variable/url parameter conditionally (e.g. optional filter)

It's possible to make a filter optional directly in SQL as follows

SELECT id, username, first_name, last_name, email, country
FROM users
WHERE {username} IS NULL OR username={username}

In this scenario, the url parameter/filter for username is optional.

If provided it will apply the filter, if not provided, it will return an unfiltered response.

Controlling response structure

Standard queries return flat JSON responses that correspond to the results table from executing the SQL.

It's possible to control the JSON structure by using double underscores to denote the desired nesting

Take, for example, the following query:

SELECT users.id, username, first_name, last_name, teams.name as team__name, teams.location as team__location, email
FROM users
JOIN users ON users.team_id = teams.id

Would result in the following structure in the response:

[
  {
    "id":1,
    "username":"blazer_tommy",
    "first_name":"Tom",
    "last_name":"Carey",
    "team":{
      "name":"defenders",
      "location":"Dublin"
    },
    "email":"[email protected]"
  },
  {
    "id":2,
    "username":"blazer_john",
    "first_name":"John",
    "last_name":"Doyle",
    "team":{
      "name":"responders",
      "location":"London"
    },
    "email":"[email protected]"
  }
]

Deeper nesting is also possible, just continue the pattern e.g. a__deeper__nested__value

Paginating potentially large responses

If your query could return a large response, it's generally a good idea to paginate it.

Pagination can be achieved in many ways, but a basic example can be done as follows using a combination of variables in the query and LIMIT and OFFSET.

SELECT id, username, first_name, last_name, email, country
FROM users
LIMIT {per_page}
OFFSET ({page}-1)*{per_page}

Using this technique, URL params can be used by the requester to control pagination.

In this example, page corresponds to the desired page in the paginated collection and per_page corresponds to the desired size of records in each page

This technique can be used in combination with some default settings for these parameters in blazers config file blazer.yml.

Having defaults means if they are not specified by the requester, the defaults will automatically be applied.

    variable_defaults:
      # pagination defaults
      per_page: 30
      page: 1

Requests can then be as follows:

/blazer-api/queries/1-all-users = returns first 30 users (pagination defaults apply automatically)

/blazer-api/queries/1-all-users?page=2 = returns second page containing 30 users

/blazer-api/queries/1-all-users?page=1&per_page=90 = returns first page containing 90 users

etc...

Contributing

Want to improve this library, please do!

Report bugs
Fix bugs and submit pull requests
Write, clarify, or fix documentation
Suggest or add new features

License

The gem is available as open source under the terms of the MIT License.