DEV Community

Cover image for Create an SQL Report API in 5 minutes
Thomas Hansen
Thomas Hansen

Posted on • Originally published at aista.com

 

Create an SQL Report API in 5 minutes

In addition to generating CRUD APIs, Aista Magic Cloud also allows you to generate API endpoints using nothing but SQL. The generator will take care of stuff such as authorisation, logging, generating the boiler plate code, etc. All you need to worry about is your SQL.

SQL report API created declaratively

The whole point about Hyperlambda is that it's a declarative programming language. When you have a declarative programming language, the computer can automatically generate a large portion of your code, through a process that's similar to how you do it as you copy and paste code from StackOverflow. This implies that even though you can't code in anything but SQL, you can still create Web API endpoints - Because Magic does everything for you, as long as you provide some SQL to it.

In the video below I am demonstrating the whole process. If you want to reproduce what I'm doing, signup at Hub and create your own cloudlet.

The code that Magic produces can be found below.

/*
 * Template for custom SQL HTTP request.
 * This file was automatically generated using Magic's CRUDifier.
 */
.arguments
   filter:string
.type:sql

// Opening up a database connection
data.connect:chinook
   database-type:sqlite

   // Parametrizing [data.select].
   add:x:./*/data.select
      get-nodes:x:@.arguments/*

   // Evaluating [xxx.select] slot.
   data.select:"\n/*\n * Script to aggregate records for each artist in chinook database.\n * Make sure you select the chinook database before execxuting the script.\n */\nselect ar.Name, count(*) as count\n  from Album al, Artist ar where al.ArtistId = ar.ArtistId and ar.Name like @filter\n  group by al.ArtistId\n  order by count desc\n  limit 25\n"
      database-type:sqlite

   /*
    * Checking if we should return a list of items, or only a
    * single item.
    */
   if
      .is-list:bool:true
      .lambda

         // Returning a list of items to caller.
         return-nodes:x:@data.select/*

   // Returning a single result to caller.
   return-nodes:x:@data.select/*/*
Enter fullscreen mode Exit fullscreen mode

Thank you for reading :)

Top comments (2)

Collapse
 
bias profile image
Tobias Nickel

i have a question, it sounds mean, but i mean it.

is it possible that you optimize the wrong part? creating an API or a report using php, java/spring or node.js/express is easy and can also be done quite fast.

what takes much longer is deciting with a client what datafields do include, and how to format them. specially when working with people who are ignorant of data, who see data as some blob that engineers have to handle.

what are your thought on this and how could hyerlambda help?

Collapse
 
polterguy profile image
Thomas Hansen

It's a great question. I've got some ideas to also generate the frontend parts. It may not eliminate the discussion with the client, but it will make it easier for the client to see the end result, creating faster iterations.

Here is a post you might want to check out:

Regex for lazy developers

regex for lazy devs

Sorry for the callout 😆