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.
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.
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.
Top comments (4)
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?
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 ^_^
Your approach is smart
Thank you Mohsen :)