Make the “semantic web” web 3.0 again – with the help of SQLite
I&#x27;ve been exploring the idea of using SQLite to publish data online via my Datasette project for a few years now: <a href="https:&#x2F;&#x2F;datasette.io&#x2F;" rel="nofollow">https:&#x2F;&#x2F;datasette.io&#x2F;</a><p>Similar to the OP, one of the things I&#x27;ve realized is that while the dream of getting everyone to use the exact same standards for their data has proved almost impossible to achieve, having a SQL-powered API actually provides a really useful alternative.<p>The great thing about SQL APIs is that you can use them to alter the shape of the data you are querying.<p>Let&#x27;s say there&#x27;s a database with power plants in it. You need them as &quot;name, lat, lng&quot; - but the database you are querying has &quot;latitude&quot; and &quot;longitude&quot; columns.<p>If you can query it with a SQL query, you can do this:<p><pre><code>    select name, latitude as lat, longitude as lng from [global-power-plants]
</code></pre>
Here&#x27;s a demo using exactly that query: <a href="https:&#x2F;&#x2F;global-power-plants.datasettes.com&#x2F;global-power-plants?sql=select+name,+latitude+as+lat,+longitude+as+lng+from+[global-power-plants]+limit+100" rel="nofollow">https:&#x2F;&#x2F;global-power-plants.datasettes.com&#x2F;global-power-plan...</a><p>That URL gives you back an HTML page, but if you change the extension to .json you get back JSON data:<p><a href="https:&#x2F;&#x2F;global-power-plants.datasettes.com&#x2F;global-power-plants.json?sql=select+name,+latitude+as+lat,+longitude+as+lng+from+[global-power-plants]+limit+100&amp;_shape=array" rel="nofollow">https:&#x2F;&#x2F;global-power-plants.datasettes.com&#x2F;global-power-plan...</a><p>Or use .csv to get back the data as CSV:<p><a href="https:&#x2F;&#x2F;global-power-plants.datasettes.com&#x2F;global-power-plants.csv?sql=select+name,+latitude+as+lat,+longitude+as+lng+from+[global-power-plants]+limit+100" rel="nofollow">https:&#x2F;&#x2F;global-power-plants.datasettes.com&#x2F;global-power-plan...</a><p>But what if you need some other format, like Atom or ICS or RDF?<p>Datasette supports plugins which let you do that. I&#x27;m running the <a href="https:&#x2F;&#x2F;datasette.io&#x2F;plugins&#x2F;datasette-atom" rel="nofollow">https:&#x2F;&#x2F;datasette.io&#x2F;plugins&#x2F;datasette-atom</a> datasette-atom plugin on this other site. That plugin lets you define atom feeds using a SQL query like this one:<p><pre><code>    select
      issues.updated_at as atom_updated,
      issues.id as atom_id,
      issues.title as atom_title,
      issues.body as atom_content,
      repos.html_url  || &#x27;&#x2F;issues&#x2F;&#x27; || number as atom_link
    from
      issues join repos on issues.repo = repos.id
    order by
      issues.updated_at desc
    limit
      30
</code></pre>
Try that query here: <a href="https:&#x2F;&#x2F;github-to-sqlite.dogsheep.net&#x2F;github?sql=select
++issues.updated_at+as+atom_updated,
++issues.id+as+atom_id,
++issues.title+as+atom_title,
++issues.body+as+atom_content,
++repos.html_url++||+'/issues/'+||+number+as+atom_link
from
++issues+join+repos+on+issues.repo+=+repos.id
order+by
++issues.updated_at+desc
limit
++30" rel="nofollow">https:&#x2F;&#x2F;github-to-sqlite.dogsheep.net&#x2F;github?sql=select
%0...</a><p>The plugin notices that columns with those names are returned, and adds a link to the .atom feed. Here&#x27;s that URL - you can subscribe to that in your feed reader to get a feed of new GitHub issues across all of the projects I&#x27;m tracking in that Datasette instance: <a href="https:&#x2F;&#x2F;github-to-sqlite.dogsheep.net&#x2F;github.atom?sql=select
++issues.updated_at+as+atom_updated,
++issues.id+as+atom_id,
++issues.title+as+atom_title,
++issues.body+as+atom_content,
++repos.html_url++||+'/issues/'+||+number+as+atom_link
from
++issues+join+repos+on+issues.repo+=+repos.id
order+by
++issues.updated_at+desc
limit
++30" rel="nofollow">https:&#x2F;&#x2F;github-to-sqlite.dogsheep.net&#x2F;github.atom?sql=select...</a><p>As you can see, there&#x27;s a LOT of power in being able to use SQL as an API language to reshape data into the format that you need to consume.