How to connect to a database using CSS

By the start of the course Full stack development in Python we share material on how to use modern CSS and JS features – worklets and the Houdini API – to connect to a database and execute queries against it. For details, we invite under cat.

As is typical of tweets, this tweet floated around the net that week:

It’s been a long time since I came across a decent shieldposting projects – since the time when I didn’t even know the word “shieldpost”. That’s partly why I was inspired by a previous project based on a blockchain startup, when its investors wanted to capture their faces. on 3D cubes.

It reminds me of the good old days in the early days of the internet, when everything was a curiosity. But I’m not looking for prescriptions, so I’ll spare you the story of my life. I’d rather talk about how I coped with my new project – sqlcss.xyz:

It’s about how to connect to the database from CSS. Works only in Chrome, but you can query any SQLite database via CSS. How exactly? Set of new Houdini API allows the browser to manage CSS using JavaScript and the browser object model: create your CSS styles, add your properties, etc.

Perhaps the most important feature of Houdini is CSS Paint Worklet. It allows you to “draw” on an element, as in a Canvas, working with it in the browser, as with an image in CSS. You can play with the examples at houdini.how. But this worklet is only part of the worker API, the canvas context itself is also heavily curtailed, and the sandbox for drawing in the CSS code is smaller than you might expect.

This means that there is no network access You can say goodbye to fetch and XmlHttpRequest. Also, there is no drawText in the drawing context. Just in case you were hoping to get around these issues, the other JS APIs are gone too. But not all is lost. Let’s break down the process step by step.

1. Database installation

It should be the first step, because you need to understand if a proof of concept is even possible.

There is sql.js library. This is literally a version of SQLite compiled to WebAssembly and emscripten to good old ASM.js. The WASM version cannot be used: it must receive the binary over the network. The ASM version does not have this restriction, and all code is available in one module.

Although PaintWorklet has limited access to the network inside the worker, you can import code in an ES6 module. In other words, there must be an export statement inside the file. But there is no ES6-only build in sql.js, so the script had to be changed.

And now the main question: is it possible to install a database in a worklet?

const SQL = await initSqlJs({
  locateFile: file => `./${file}`,
});

const DB = new SQL.Database();

Happened! There are no errors. But there is no data either, so let’s add them.

2. Requests to the database

The easiest thing to do in the beginning is to add dummy data. Sql.js has a couple of functions for just that:

DB.run('CREATE TABLE test (name TEXT NOT NULL)')
DB.run(
  'INSERT INTO test VALUES (?), (?), (?), (?)',
  ['A', 'B', 'C', 'D']
)

Now we have a test table with values. Not sure how the result will be structured though. Let’s send a request and get the values:

const result = DB.exec('SELECT * FROM test')
console.log(result)

Now it’s good to visualize the results.

3. Easy way to visualize

I thought it was as easy as writing text in canvas:

class SqlDB {
  async paint(ctx, geom, properties) {
    const result = DB.exec('SELECT * FROM test');
    ctx.font="32px monospace";
    ctx.drawText(JSON.stringify(result), 0, 0, geom.width);
  }
}

But no, that would be too easy. The context here is not the same as the element in the Canvas, that is, only part of the context remains. You can draw contours and curves, so the lack of a convenient API is an obstacle, but it does not hurt the cause.

4. Text Without Text API

Fortunately, opentype.js library offers a solution. It allows you to parse a font file and then, given a string of text, generate the letterforms of each character. The result of this operation is a path object with a string that can be displayed in the drawing context.

This time, you don’t need to make any changes to import the opentype.js library: it’s already in JSPM. Specify the JSPM npm package and it will automatically create an ES6 module that can be imported directly into the browser. This is great, because I didn’t want to mess around with a batch tool for a joke project.

import opentype from 'https://ga.jspm.io/npm:opentype.js@1.3.4/dist/opentype.module.js'

opentype.load('fonts/firasans.otf')

Here’s the problem, because the OpenType font needs to be downloaded over the network. I can not do it. It broke again! Or can I? There is a parse method that takes an array buffer. We just base64 encode the font and decode it in the module:

import opentype from 'https://ga.jspm.io/npm:opentype.js@1.3.4/dist/opentype.module.js'
import base64 from 'https://ga.jspm.io/npm:base64-js@1.5.1/index.js'

const font="T1RUTwAKAIAAAwA ... 3 days later ... wAYABkAGgAbABwAIAKM"

export default opentype.parse(base64.toByteArray(font).buffer)

Did I mention there is no API for processing base64 strings in worklet? Not even atob and btoa. I had to find an implementation in plain JS. I put this code in a separate file: it’s not very … ergonomic – to work with a 200 KB encoded font line along with the rest of the code.

Here’s how, by abusing the ES module, I loaded the font.

5. Display results, another easy way

Now the opentype library does all the hard work. All it takes is a bit of math to nicely trim the code:

import font from './font.js'

const SQL = await initSqlJs({
  locateFile: file => `./${file}`,
});

const DB = new SQL.Database();

DB.run('CREATE TABLE test (name TEXT NOT NULL)')
DB.run(
  'INSERT INTO test VALUES (?), (?), (?), (?)',
  ['A', 'B', 'C', 'D']
)

class SqlDB {
  async paint(ctx, geom, properties) {
    const query = DB.exec('SELECT * FROM test')
    const result = query[0].values.join(', ')

    const size = 48
    const width = font.getAdvanceWidth(queryResults, size)
    const point = {
      x: (geom.width / 2) - (width / 2),
      y: geom.height / 2
    }

    const path = font.getPath(result, point.x, point.y, size)
    path.draw(ctx)
  }
}

registerPaint('sql-db', SqlDb)

Let’s tweak the HTML and CSS and see what happens:

<html>
  <head>
    <script>
      CSS.paintWorklet.addModule('./cssdb.js')
    </script>
    <style>
      main {
        width: 100vw;
        height: 100vh;
        background: paint(sql-db);
      }
    </style>
  </head>
  <body>
    <main></main>
  </body>
</html>

It works, but there is not enough CSS, and I hardcoded the request.

6. Database queries via CSS

DB queries are best done with CSS: this is actually the only way to interact with a paint worker outside of its context – this worker does not have a messaging API like regular workers do.

To make a request, you need a CSS property. Defining inputProperties, let’s subscribe to changes in the new property: when the value of the property changes, it will be displayed again. Event listeners are not needed:

class SqlDb {
  static get inputProperties() {
    return [
      '--sql-query',
    ]
  }

  async paint(ctx, geom, properties) {
    // ...
    const query = DB.exec(String(properties.get('--sql-query')))
  }
}

These are typed CSS properties, but they are placed in the CSSProperty class, which is not particularly useful on its own. To use it as shown above, you’ll have to manually convert it to a string, a number, or whatever. Let’s tweak the CSS a bit again:

main {
  // ...
  --sql-query: SELECT name FROM test;
}

Quotes are removed on purpose, otherwise they would have to be removed from the string before it was transferred to the database. Everything works, mission accomplished. If you have already tried sqlcss.xyz, you probably noticed that I did not stop there – after a little refactoring, I made a couple more changes.

7. Local database file

The concept is proven, but more can be done. Hardcoding the database schema and the data itself is bad. It’s great to query any database once you have its file. You just need to read it and base64 encode it like a font file:

const fileInput = document.getElementById('db-file')
fileInput.onchange = () => {
  const reader = new FileReader()
  reader.readAsDataURL(fileInput.files[0])

  reader.onload = () => {
    document.documentElement.style.setProperty(
        '--sql-database',
        `url('${reader.result}')`
    )
  }
}

To do this, I created a CSS property where the SQLite database is specified as a URI from the data in base64 format. The data URI is here only to confirm its validity from the point of view of the DOM – the parsing will be done on the side of the worker. It remains to simplify the execution of queries, otherwise you will have to dive into the debugger to work with the CSS of the element.

8. Writing database queries

This is possibly the easiest part of the project. Semicolons in our CSS property are not a big problem. SQLite doesn’t care about it. If it is found in the input data, it is easier to remove it:

const queryInput = document.getElementById('db-query')
queryInput.onchange = () => {
  let query = queryInput.value;
  if (query.endsWith(';')) {
    query = query.slice(0, -1)
  }

    document.documentElement.style.setProperty(
    '--sql-query',
    queryInput.value
  )
}

Now you can use CSS to import and view the local database. But how to display the results beautifully when there are a lot of them and everything needs to be divided into separate lines? This is another topic, but if you want to understand it, here all project code.

You can continue your immersion in CSS and other web technologies in our courses:

Learn more here.