Manually creating your first Hyperlambda endpoint
Tags: webdev, sql, tutorial
Published: 2022-06-21 18:25:32

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](https://dev.to/polterguy/create-a-web-api-backend-in-seconds-no-coding-required-2mji). 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.

{% embed https://www.youtube.com/watch?v=xeGrLQAcnhw %}

## 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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/uwljzymqhm6syj9nm74w.jpg)

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:-/*
```

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/96473brzpj7bwciz3ftx.png)

## 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:-/*
```

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.