My translator is a front-end engineer of the Strange Dance Company
Yes, I can connect to a DB in CSS
By Memeloper
The original address: www.leemeichin.com/posts/yes-i…
The story background
The recruitment requirements of a company are as follows:
We are looking for a front-end partner that can connect to the database using CSS
It’s been a long time since I last started a quality “post,” in fact, it’s been a long time since I probably didn’t have the word “water post” in my vocabulary.
To that end, I was inspired by an early project based on blockchain-based startups projecting investors’ faces onto 3D cubes that reminded me of the old Internet, when everything was weird.
A good man never speaks of his courage. So today, I’ll discuss how to manage my own new project: SQLCSs.xyz
As the name suggests, this is how you connect to a database using CSS. Unfortunately, it only works in Chrome, but you can provide any SQLite database you like and query it through CSS.
How does it work?
First we need to use a set of apis affectionately known as Houdini, which allows your browser to control CSS through the Javascript object model. In other words, this means you can customize CSS styles, add custom properties, and so on.
Probably the biggest feature of this work is the CSS Paint Worklet, which allows you to “draw” on an element, just like the canvas you know and love, and have the browser treat it as an image in CSS. Here are some examples you can use to demonstrate Houdini.
However, this working set only provides a subset of the Worker API, and the canvas context itself is largely stripped away. The practical result of this is that your custom CSS rendering code provides a smaller sandbox than you expected.
What does that mean? No network access, so say goodbye to FETCH and XmlHttpRequest. There is no drawText function on the drawing context. Various other JS apis are also gone, just in case you want to solve these problems.
But don’t worry. All is not lost. Let’s break it down into steps.
1. Set the database
This must be the first step in understanding whether a proof of concept is feasible.
First we’ll use sqL.js. It’s actually a version of SQLite compiled via Emscripten into WebAssembly and old ASM.js. Unfortunately, we can’t use the WASM version because it has to fetch binaries over the network. The ASM version does not have this limitation because all code can be used in one module.
Although PaintWorklet restricts network access within workers, you can still import code as long as it is an ES6 module. This means that there must be an export statement in the file. Unfortunately, THERE is no ES6 version of SQl. js, so I modified sql.js myself so that it can be imported into the project smoothly.
Now comes the crunch: Can I build a database in my work package?
const SQL = await initSqlJs({
locateFile: file= > `. /${file}`});const DB = new SQL.Database();
Copy the code** did it! ** But there isn’t any data, so let’s solve this problem.
2. Query the database
The easiest way to start is to set up some fake data, and SQL.js has two functions to do this.
DB.run('CREATE TABLE test (name TEXT NOT NULL)')
DB.run(
'INSERT INTO test VALUES (?), (?), (?), (?)',
['A', 'B', 'C', 'D']
)
Copy the codeI have the test table, and I have some values in it. I should be able to query this and get these values, although I’m not sure what structured query results I get.
const result = DB.exec('SELECT * FROM test')
console.log(result)
Copy the codeAs expected, the results are in. However, it would be better to render to show the results of querying the database through CSS.
3. Render the result, the easiest way
I think it’s like writing text on a canvas. How hard is that, right?
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); }}Copy the codeNo, that would be too easy. The context here is different from the context of the canvas element, which provides only a subset of functionality.
Of course, it can still draw paths and curves, so the lack of a convenient API is a hindrance, but none of this is a problem.
4. Create text without using text API
Fortunately, opentype.js provides a solution. It can parse a font file and then, given a text string, generate an alphabetic representation of each character. The actual result of this operation is a path object representing a string, which can then be rendered into the context.
I don’t have to modify the Opentype library to import it this time because it’s already available from JSPM. So, if you give JSPM an NPM package, it will automatically generate an ES6 module that you can import directly into your browser. This was great because I really didn’t want to use a packaging tool for a fun project.
The import opentype from 'https://ga.jspm.io/npm:[email protected]/dist/opentype.module.js' opentype.load('fonts/firasans.otf')Copy the codeBut there’s a problem — it wants to load fonts over the network, and I can’t do that! Hey, defeat!
… And? It also has a parsing method that takes an array buffer. I’ll encode the font in Base64 and then decode it in my module.
The import opentype from 'https://ga.jspm.io/npm:[email protected]/dist/opentype.module.js' import base64 the from 'https://ga.jspm.io/npm:[email protected]/index.js' const font =' T1RUTwAKAIAAAwA... 3 days later ... wAYABkAGgAbABwAIAKM' export default opentype.parse(base64.toByteArray(font).buffer)Copy the codeDid I tell you that Worklet doesn’t have an API for handling Base64 strings, either? Neither ATOB nor BTOA! I also had to find a generic JS implementation for this.
I put this code in its own file because it’s not very ergonomic… You must use about 200KB of encoded font strings next to the rest of the code.
This is how I abused the ES module to load my fonts.
5. Render results, another easy way
From now on, all the heavy lifting will be done by the Opentype library, so all I need to do is use a little math to align.
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)
Copy the codeBetter have some MORE HTML and CSS and see what’s going on.
<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>
Copy the codeSuccess! But there isn’t enough CSS, and the queries are hard-coded.
6. Query information using the CSS
If you have to use CSS to query the database, so much the better. In fact, this is the only way we can communicate with the Paint Worker outside of its context, because there is no messaging API like there is for Web workers.
To do this, you need a custom CSS property. The advantage of defining inputProperties is that you can subscribe to changes to the property, so if the value of the property changes, it will be rerendered. There is no need to set up any subscribers themselves.
class SqlDb {
static get inputProperties() {
return [
'--sql-query',
]
}
async paint(ctx, geom, properties) {
// ...
const query = DB.exec(String(properties.get('--sql-query')))
}
}
Copy the codeThese CSS properties are called typed properties, but they are essentially encapsulated in a special CSSProperty class, which by itself isn’t very useful. Therefore, you must manually convert it to a string or number or something similar to use it, as described above.
Now make a quick change to the CSS.
main {
// ...
--sql-query: SELECT name FROM test;
}
Copy the codeThe quotes are deliberately omitted here, because otherwise I would have to remove them from the string before passing them to the database. In other words, it works!
Mission accomplished!
If you’ve played with SQLCSS. You’ll notice I’m not satisfied with that. After some refactoring, some changes were made.
7. Import the database file
Hard coding the database schema and the actual data is kind of bad. It proves the concept, but we can certainly do better.
It would be great if you could query any database you liked, as long as you had the database files handy. I just need to read the file and base64 encode it, just like I did with the 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}')`
)
}
}
Copy the codeI made an additional CSS property for this, in which you can provide the SQLite database as a Base64-encoded data URI. The data URI is just for display and to ensure that it is valid for the DOM; I’ll parse these things at the Worker level.
The final step is to make it easier to query, because otherwise you have to go into the debugger to manipulate the element’s CSS.
8. Write query statements
This is probably the easiest part of the project. Custom attributes have a bit of a problem with semicolons, and SQLite doesn’t care if the trailing semicolon is omitted, so the easiest thing to do is to delete it if you find it in the input.
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 ) }Copy the codeFrom now on, you can import and browse your own database using CSS!
One thing I’m missing is the question of how best to render the presentation when all these queries have a lot of results. If the query results are many, they need to be split into separate rows. This doesn’t have much to do with the topic of this article — using CSS to connect to a database — so I don’t think it’s appropriate to talk about it here, but if you want to take a closer look at this “ridiculous” concept, the code on Git is available.
Translators supplement
- SQLite is an embedded database written in C, which is widely used in IOS and Android apps. Those familiar with Python may know that SQLite3 is built into Python. If you have a WEB front end and want to use SQLite on the browser side, we recommend SQLite’s Webassembly version, SQL.js, which is basically the same as SQLite.
- Some demos are as follows:
- JSPM provides module CDN and package management for import mapping, allowing any package from NPM to be loaded directly in the browser without further tools.