DEV Community

Cover image for Manually creating your first Hyperlambda endpoint
Thomas Hansen
Thomas Hansen

Posted on

Manually creating your first Hyperlambda endpoint

This is the first article in our series where we for real actually start writing Hyperlambda ourselves. The article assumes you've already followed the hands on parts we went through in the first article in the series. If you haven't followed that part please go back and read that article and come back afterwards. This article is also accompanied by a YouTube video you can find below where I walk you through everything if you prefer to watch me do it before you do things yourself.

SQL

Magic is heavily centred around relational database systems, and Magic makes creating HTTP endpoints for such RDB systems much easier. RDB systems again are controlled by SQL, meaning "Structured Query Language", which is the language we use to extract data from our database. Below I have loaded up one of the example SQL scripts that comes with the Chinook DB plugin to illustrate some SQL.

SQL joins in SQLite

What we want to do is to wrap this SQL inside a Hyperlambda HTTP endpoint, allowing us to retrieve the data over an HTTP connection, that we can later connect to some frontend type of application, written in for instance Angular, React, Vue, or Swift. Create a "test1.get.hl" file inside your "tutorials101" like I illustrate in the video above, and put the following code into your file.

sqlite.connect:chinook

   sqlite.select:@"select distinct c.Email, c.FirstName, c.LastName, g.name
  from Customer c
    inner join Invoice i on c.CustomerId = i.CustomerId
    inner join InvoiceLine ii on i.InvoiceId = ii.InvoiceId
    inner join Track t ON ii.TrackId = t.TrackId
    inner join Genre g ON t.GenreId = g.GenreId
  where g.Name = ""Rock""
  order by c.Email"
   return:x:-/*
Enter fullscreen mode Exit fullscreen mode

The first line of code above connects you to the Chinook database. The second line of code tells Hyperlambda to select all records matching the SQL statement, implying all customers in your database who are listening to rock. The third line of code returns your selected records over the HTTP connection, converting the result to JSON in the process. If you save the file (ALT+S) and click F5, you can already invoke your endpoint and see the result of your efforts. Below is a screenshot of how things should look like if you got things right.

Invoking your SQL endpoint

Arguments

In the last parts of our video we declare an [.arguments] collection, allowing us to pass in types arguments to our endpoint, to dynamically parametrise our SQL. At this point the file ends up as follows.

.arguments
   genre:string

sqlite.connect:chinook

   sqlite.select:@"select distinct c.Email, c.FirstName, c.LastName, g.name
  from Customer c
    inner join Invoice i on c.CustomerId = i.CustomerId
    inner join InvoiceLine ii on i.InvoiceId = ii.InvoiceId
    inner join Track t ON ii.TrackId = t.TrackId
    inner join Genre g ON t.GenreId = g.GenreId
  where g.Name = @genre
  order by c.Email"
      @genre:x:@.arguments/*/genre

   return:x:-/*
Enter fullscreen mode Exit fullscreen mode

What is happening in the above piece of Hyperlambda is basically that Magic will use your input argument to the endpoint, and add it as a parameter to your SQL dynamically, allowing you to pass in genres such as for instance.

  • Rock
  • Jazz
  • Pop

In later articles we will continue to edit our endpoint, to add authorisation, authentication, and other types of Hyperlambda constructs, to continue building upon the above code.

Discussion (4)

Collapse
mshafiey profile image
Mohsen

related to this feature you can run SQL code directly, can you tell me what you think about the SQL injection protection? how is it secure?

Collapse
polterguy profile image
Thomas Hansen Author • Edited on

SQL injections are a serious matter, and one of the top ranking security threats for Web APIs. Magic solves this by automatically wrapping everything inside of SQL parameters for you, as illustrated in this article where I parametrise the [sqlite.select] slot with a [@genre] argument.

The "genre" argument in this article will in fact automatically be considered an SQL parameter, and hence guard you automatically against SQL injections without any extra effort required from your side ^_^

Collapse
mshafiey profile image
Mohsen

Your approach is smart

Thread Thread
polterguy profile image
Thomas Hansen Author

Thank you Mohsen :)