Erlang with SQL & JSON

How to return formatted JSON responses from SQL in Erlang

Recently, I have been playing around with Erlang’s Cowboy & Yaws. Great fun but I really got stuck when I tried to build a JSON response from the erlang:odbc engine’s output. The Erlang odbc package sends back SQL in the form of Columns as strings, & Rows as tuples.

So after a few days of searching for a complete library that abstracts this out entirely under the hood (think Python’s SqlAlchemy), I realised I just have to get cracking & find a solution myself.

This example uses Erlang’s Cowboy but will work Yaws etc.

First, let’s create the boiley plate to handle a request in Cowboy:

init(Req0, State) ->
  ResData = get_data(),
  Req = cowboy_req:reply(200,
    #{<<"content-type">> => <<"application/json">>},
    ResData,
    Req0),
  {ok, Req, State}.

Above, we are setting the response headers to <<”content-type”>> => <<”application/json”>> and also return ResData from the get_data/0 function to make the initial SQL query, below:

get_data() ->
  Sql = "SELECT * FROM alloys;",
  Ref = connect(),
  case odbc:sql_query(Ref, Sql) of
    {selected, Columns, Results} ->
      set_json_from_sql(Columns, Results, []);
    {error, Reason} ->
      {error, Reason}
  end.

Pretty standard stuff if you’re familiar with Cowboy.

Now let’s look at a solution to returning formatted JSON from SQL in Erlang. Lets create a few functions, the first of these will use recursion to pattern-match against a list of “objects”, set_json_from_sql is the main entry function, it has an arity of 3 which accepts Columns, Rows and an empty list. For each recursion we are appending the new object to the main list with lists:append([TupleRow], Data) . Also note, I’m using the jsone library: https://github.com/sile/jsone but you could use jerry or jsx.

set_json_from_sql([], [], Data) -> jsone:encode([{<<"data">>, lists:reverse(Data)}]);

set_json_from_sql(Columns, [H], Data) ->
  NewData = set_json_merge(H, Columns, Data),
  set_json_from_sql([], [], NewData);

set_json_from_sql(Columns, [H|T], Data) ->
  NewData = set_json_merge(H, Columns, Data),
  set_json_from_sql(Columns, T, NewData).

The set_json_from_sql/3 function calls set_json_merge/3 to return each row in the table, which eventually, in turn, gets serialised to a JSON object.

set_json_merge(Row, Columns, Data) ->
  TupleRow = json_by_type(tuple_to_list(Row), Columns, []),
  lists:append([TupleRow], Data).

json_by_type/3 is a function for creating the actual list of tuple pairs, which eventually get formatted to key values:

json_by_type([H], [Hc], Data) ->
  NewH = format_by_type(H),
  set_json_flatten(Data, Hc, NewH);

json_by_type([H|T], [Hc|Tc], Data) ->
  NewH = format_by_type(H),
  NewData = set_json_flatten(Data, Hc, NewH),
  json_by_type(T, Tc, NewData).

set_json_flatten/3 is a helper called from json_by_type to merge the new list of tuple pairs (the SQL row) into the main list.

set_json_flatten(Data, Column, Row) ->
  ResTuple = {list_to_binary(Column), Row},
  lists:flatten(Data, [ResTuple]).

Also, note that, because I’m using format specifiers, I need to make sure which type gets formatted. Although, atoms & numbers do not need converting:

format_by_type(Item) ->
  if
    is_list(Item) -> list_to_binary(io_lib:format("~s", [Item]));
    is_integer(Item) -> Item;
    is_boolean(Item) -> io_lib:format("~a", [Item]);
    is_atom(Item) -> Item
  end.

Here is the complete code:

%%%-------------------------------------------------------------------
%%% @author joegasewicz
%%% @copyright (C) 2019, <COMPANY>
%%% @doc
%%%
%%% @end
%%% Created : 13. Jul 2019 13:31
%%%-------------------------------------------------------------------
-module(index_handler).
-author("joegasewicz").
%% API
-export([init/2, get_data/0]).
-import(account_manager_api_app, [connect/0]).

get_data() ->
  Sql = "SELECT * FROM alloys;",
  Ref = connect(),
  case odbc:sql_query(Ref, Sql) of
    {selected, Columns, Results} ->
      set_json_from_sql(Columns, Results, []);
    {error, Reason} ->
      {error, Reason}
  end.

%%% Check the type Item -> format specifier OR its int/atom
format_by_type(Item) ->
  if
    is_list(Item) -> list_to_binary(io_lib:format("~s", [Item]));
    is_integer(Item) -> Item;
    is_boolean(Item) -> io_lib:format("~a", [Item]);
    is_atom(Item) -> Item
  end.

json_by_type([H], [Hc], Data) ->
  NewH = format_by_type(H),
  set_json_flatten(Data, Hc, NewH);

json_by_type([H|T], [Hc|Tc], Data) ->
  NewH = format_by_type(H),
  NewData = set_json_flatten(Data, Hc, NewH),
  json_by_type(T, Tc, NewData).

set_json_flatten(Data, Column, Row) ->
  ResTuple = {list_to_binary(Column), Row},
  lists:flatten(Data, [ResTuple]).

set_json_from_sql([], [], Data) -> jsone:encode([{<<"data">>, lists:reverse(Data)}]);

set_json_from_sql(Columns, [H], Data) ->
  NewData = set_json_merge(H, Columns, Data),
  set_json_from_sql([], [], NewData);

set_json_from_sql(Columns, [H|T], Data) ->
  NewData = set_json_merge(H, Columns, Data),
  set_json_from_sql(Columns, T, NewData).

set_json_merge(Row, Columns, Data) ->
  TupleRow = json_by_type(tuple_to_list(Row), Columns, []),
  lists:append([TupleRow], Data).

init(Req0, State) ->
  ResData = get_data(),
  Req = cowboy_req:reply(200,
    #{<<"content-type">> => <<"application/json">>},
    ResData,
    Req0),
  {ok, Req, State}.

Well, that’s all for now, if anyone is stuck with this problem, I hope this solution is useful. I plan to embark on a lot more Erlang adventures, so please follow to hear more about Erlang!