Embedded SQL in Hugo with sqlime

Another short(-ish) post. I found a neat tutorial/example page at: https://sqlime.org/examples.html. It shows an embedded sqlite database, using web assembly and javascript to run interactive SQL examples without needing to hit a third-party hosted script (assuming you’re hosting it yourself).

This lets you do something like:

SELECT
  rank() over w as "rank",
  name, department, salary
FROM employees
WINDOW W as (order by salary desc)
ORDER BY "rank", id
LIMIT 15

This is fully interactable sqlite data - feel free to SELECT * FROM EMPLOYEES;, it’s only 20 rows. Also feel free to try some badly formatted SQL to see the resulting errors.

I thought it’d be neat to be able to quickly use this example for posts about SQL, so I edited the example code a bit, making it a bit easier to futz with the CSS, and then created a hugo shortcode, sqlime.html:

{{ $db_name := .Get "db" }}
{{ $db_path := .Get "path" }}
{{ $editable := .Get "editable" | default true }}

<script>

    const scriptPaths = [
        "/js/sqlite3.js",
        "/js/sqlime-db.js",
        "/js/sqlime-examples.js"
      ];

      function loadScript(src) {
        var script = document.createElement("script");
        script.src = src;
        document.head.appendChild(script);
      }

      scriptPaths.forEach(function(path) {
        if (!document.querySelector(`script[src="${path}"]`)) {
          loadScript(path);
        }
      });

</script>

<div class="bordered">
    <div class="code">
    <pre class="sqlime">{{- .Inner -}}</pre></div>
    <noscript><p> To run this SQL statement, please enable javascript.</p></noscript>
</div>


  <sqlime-db name="{{ $db_name }}" path="{{ $db_path }}"></sqlime-db>
{{ if (eq $editable true) }}
  <sqlime-examples db="{{ $db_name }}" selector="pre.sqlime" editable></sqlime-examples>
{{ else }}
  <sqlime-examples db="{{ $db_name }}" selector="pre.sqlime"></sqlime-examples>
{{ end }}

I host the script files in /static/js/* as the static folder is the default storage. As seen below I put the actual data in /static/data Which can then be used in your site, via standard shortcode methods:

{{< sqlime db="employees" path="/data/employees.sql" editable="true">}}
SELECT
  rank() over w as "rank",
  name, department, salary
FROM employees
WINDOW W as (order by salary desc)
ORDER BY "rank", id
LIMIT 15
{{< /sqlime >}}

And for you ox-hugo / org-mode users out there, with #+hugo_paired_shortcodes: sqlime in your top-level (or property in a sub-tree):

#+attr_shortcode: :db employees :path /data/employees.sql
#+begin_sqlime
SELECT
  rank() over w as "rank",
  name, department, salary
FROM employees
WINDOW W as (order by salary desc)
ORDER BY "rank", id
LIMIT 15
#+end_sqlime

The one gotcha for ox-hugo is I believe it likes to auto-escape *’s, so if your SQL statement has a SELECT * then you will need to remove the \.

I would like to note that most of the code available for this will be on the github for my site if you’re curious. I know it’s not a “best practice” - but I un-minified the javascript, so feel free to grab my copy of it.

In summation, I would like to thank the people at https://sqlime.org for doing the hard work of assembling the javascript to interact with the sqlite WASM. Hopefully this gives more people with static sites ideas around interactive SQL demos. As a data scientist, I think SQL will be one of those skills that sticks around for a long while and there’s never enough demos.