On this page:
1.1 What is Plisqin?
1.2 Installation
1.3 Motivation
1.4 Project Scope
1.5 Variants
1.6 Usage Patterns
1.7 What Should I Read Next?
7.8

1 Read Me First

1.1 What is Plisqin?

Both of these statements are a valid mental model. If you are very familiar with SQL, you might prefer the former; if not, you will probably prefer the latter. Eventually you will find both mental models useful.

Also:

As far as I know, my ideal query language has not yet been developed. Plisqin is a step in the right direction.

1.2 Installation

  1. Intall Racket. If you’ve never used Racket before, follow these instructions to install Racket and learn the basics

  2. From your shell, run raco pkg install plisqin

  3. Using #lang racket, verify that the following code works:

(require plisqin
         (prefix-in aw: plisqin-examples/adventure-works)
         plisqin-examples/adventure-works/schema)

> (aw:show-table
   (from p Product
         (limit 3)
         (select (ProductName p))
         (select (CategoryName p))
         (select (TotalSales p))
         (order-by 'desc (TotalSales p))))

Show TableShow SQL

select p.Name as ProductName

  , cat.Name as CategoryName

  , round(detailsG.__INJECT1, 2) as TotalSales

from Product p

left join (

  select detailsG.ProductID as __INJECT0

    , sum(detailsG.LineTotal) as __INJECT1

  from SalesOrderDetail detailsG

  group by detailsG.ProductID

) detailsG

   on (detailsG.__INJECT0 = p.ProductID)

left join ProductSubcategory subcat

   on (p.ProductSubcategoryID is not null and (subcat.ProductSubcategoryID = p.ProductSubcategoryID))

left join ProductCategory cat

   on (subcat.ProductCategoryID is not null and (cat.ProductCategoryID = subcat.ProductCategoryID))

order by round(detailsG.__INJECT1, 2) desc

limit 3

ProductName

CategoryName

TotalSales

Mountain-200 Black, 38

Bikes

4400592.8

Mountain-200 Black, 42

Bikes

4009494.76

Mountain-200 Silver, 38

Bikes

3693678.03

Click on "Show SQL" and you might be surprised! The generated SQL is much larger than the Plisqin query. This may seem like "too much magic" right now, but once you learn how it works it is actually pretty formulaic.

1.3 Motivation

During my software development career, many of my tasks could be described as "fetch data from an SQL database and display that data." Some tasks have very little display logic, such as a report that simply shows an SQL result set verbatim. Other tasks have more complicated display logic, such as a web page that shows a musical artist, their albums, and the track listings for each album. But regardless of how complex the display logic might be, I found that my favorite strategy for these tasks is usually
  1. Imagine the ideal result set(s) for the task.

  2. Use SQL to produce those result sets.

  3. Use something else to display the results.

It is step 2 that Plisqin aims to improve. SQL and all the SQL alternatives I have tried are lacking in some way. I always end up duplicating fragments of queries and joins all over the place. Using Plisqin allows me to reduce duplication to what I suspect is the theoretical minimum.

1.4 Project Scope

Plisqin is now stable but incomplete.

Plisqin’s ideal scope includes all "application-level SQL", basically equivalent to the CRUD operations (create, read, update, delete). All "DBA-level SQL", such as creating tables, indexes, logins, etc... is explicitly a non-goal.

Right now Plisqin only supports queries. Create, update, and delete operations are not provided yet. If you have an urgent need, please open an issue.

1.5 Variants

Plisqin has two variants. The unsafe variant does very little argument validation and may allow SQL injection if you are not careful. The strict variant does much more argument validation and protects against SQL injection. By convention, everything that is unsafe is required with the prefix %%, for example %%join-on or %%+. You can mix and match variants at will, as the following example demonstrates:

> (aw:show-table
   (from cat ProductCategory
         (select (Name cat))
         (%%select "substr("(Name cat)", 2) as SubstringDemo")))

Show TableShow SQL

select cat.Name as Name

  , substr(cat.Name, 2) as SubstringDemo

from ProductCategory cat

Name

SubstringDemo

Bikes

ikes

Components

omponents

Clothing

lothing

Accessories

ccessories

I suggest that you use the strict variant most of the time, and resort to the unsafe variant only when the strict variant fails you. But using only the unsafe variant is also a valid approach, especially in situations where SQL injection is impossible such as static SQL generation.

1.6 Usage Patterns

When and how does Plisqin generate SQL? What am I supposed to do with the SQL after it is generated? Plisqin is flexible, and can be used:
  • As a static SQL generator. Simply use Plisqin’s to-sql and Racket’s file IO to generate SQL files, then do whatever you want with those SQL files. Any application that consumes the SQL isn’t even aware that Plisqin exists.

  • As a "Racket-aware" static SQL generator. Use compile-statements to generate the SQL and hide it inside a normal Racket procedure. This procedure cooperates with the db module so that your Racket application can fetch data at runtime.

  • As a dynamic SQL generator. In this situation, you construct queries and call to-sql at runtime. I have not tested this usage pattern and don’t recommend it, but sometimes it is unavoidable. Be very careful – even using the strict variant to avoid SQL injection does not guarantee that you are safe. For example, an attacker might be able to construct a computationally expensive query that renders your database unresponsive.

1.7 What Should I Read Next?

You might want to peek at Modules and Prefixes to get the lay of the land and see some other prefix conventions that this documentation uses. Then continue reading at Using define-schema.