Simon Willison's Weblog: alex-garcia

Six short video demos of LLM and Datasette projects

2025-01-22T02:09:54+00:00

Last Friday Alex Garcia and I hosted a new kind of Datasette Public Office Hours session, inviting members of the Datasette community to share short demos of projects that they had built. The session lasted just over an hour and featured demos from six different people.

We broadcast live on YouTube, but I've now edited the session into separate videos. These are listed below, along with project summaries and show notes for each presentation.

You can also watch all six videos in this YouTube playlist.

llm-logs-feedback by Matthias Lübken

llm-logs-feedback is a plugin by Matthias Lübken for LLM which adds the ability to store feedback on prompt responses, using new llm feedback+1 and llm feedback-1 commands. These also accept an optional comment, and the feedback is stored in a feedback table in SQLite.

You can install the plugin from PyPI like this:

llm install llm-logs-feedback

The full plugin implementation is in the llm_logs_feedback.py file in Matthias' GitHub repository.

llm-model-gateway and llm-consortium by Thomas Hughes

Tommy Hughes has been developing a whole array of LLM plugins, including his llm-plugin-generator which is a plugin that can help write new plugins!

He started by demonstrating llm-model-gateway, a plugin that adds a llm serve command which starts a localhost server running an imitation of the OpenAI API against LLM models:

llm install llm-model-gateway
llm serve

Tommy's main demo was of llm-consortium, a plugin which can use a consortium of collaborating LLM models working together to solve problems.

llm install llm-consortium
llm consortium "Best way to prepare avocado toast" \
  --models claude-3.5-sonnet \
  --models gpt-4o \
  --arbiter gegemini-1.5-pro-latest \
  --confidence-threshold 0.8 \
  --max-iterations 3 \
  --output results.json

I ran this and got back:

The best way to prepare avocado toast involves selecting quality ingredients and careful preparation. Start with a thick slice of good quality bread, such as sourdough or whole grain. Toast it to your preferred level of crispness. While the bread is toasting, prepare a ripe avocado. Cut it in half, remove the pit, and scoop the flesh into a bowl. Mash the avocado with a fork, leaving some chunks for texture. Season with salt, black pepper, and a squeeze of fresh lemon juice to prevent browning. Optional additions include a touch of red pepper flakes.

Once the toast is ready, let it cool slightly before spreading the seasoned avocado evenly over it. Consider lightly rubbing a cut garlic clove on the warm toast for an extra layer of flavor (optional).

Enhance your avocado toast with your favorite toppings. Popular choices include: everything bagel seasoning, sliced tomatoes, radishes, a poached or fried egg (for added protein), microgreens, smoked salmon (for a more savory option), feta cheese crumbles, or a drizzle of hot sauce. For a finishing touch, drizzle with high-quality olive oil and sprinkle with sesame or chia seeds for added texture.

Consider dietary needs when choosing toppings. For example, those following a low-carb diet might skip the tomatoes and opt for more protein and healthy fats.

Finally, pay attention to presentation. Arrange the toppings neatly for a visually appealing toast. Serve immediately to enjoy the fresh flavors and crispy toast.

But the really interesting thing is the full log of the prompts and responses sent to Claude 3.5 Sonnet and GPT-4o, followed by a combined prompt to Gemini 1.5 Pro to have it arbitrate between the two responses. You can see the full logged prompts and responses here. Here's that results.json output file.

Congressional Travel Explorer with Derek Willis

Derek Willis teaches data journalism at the Philip Merrill College of Journalism at the University of Maryland. For a recent project his students built a Congressional Travel Explorer interactive using Datasette, AWS Extract and Claude 3.5 Sonnet to analyze travel disclosures from members of Congress.

One of the outcomes from the project was this story in Politico: Members of Congress have taken hundreds of AIPAC-funded trips to Israel in the past decade.

llm-questioncache with Nat Knight

llm-questioncache builds on top of https://llm.datasette.io/ to cache answers to questions, using embeddings to return similar answers if they have already been stored.

Using embeddings for de-duplication of similar questions is an interesting way to apply LLM's embeddings feature.

Improvements to Datasette Enrichments with Simon Willison

I've demonstrated improvements I've been making to Datasette's Enrichments system over the past few weeks.

Enrichments allow you to apply an operation - such as geocoding, a QuickJS JavaScript transformation or an LLM prompt - against selected rows within a table.

The latest release of datasette-enrichments adds visible progress bars and the ability to pause, resume and cancel an enrichment job that is running against a table.

Datasette comments, pins and write UI with Alex Garcia

We finished with three plugin demos from Alex, showcasing collaborative features we have been developing for Datasette Cloud:

datasette-write-ui provides tools for editing and adding data to Datasette tables. A new feature here is the ability to shift-click a row to open the editing interface for that row.
datasette-pins allows users to pin tables and databases to their Datasette home page, making them easier to find.
datasette-comments adds a commenting interface to Datasette, allowing users to leave comments on individual rows in a table.

Tags: community, data-journalism, demos, derek-willis, youtube, ai, datasette, alex-garcia, generative-ai, llms, llm, enrichments, datasette-public-office-hours

Visualizing local election results with Datasette, Observable and MapLibre GL

2024-11-09T23:32:06+00:00

Alex Garcia and myself hosted the first Datasette Open Office Hours on Friday - a live-streamed video session where we hacked on a project together and took questions and tips from community members on Discord.

We didn't record this one (surprisingly not a feature that Discord offers) but we hope to do more of these and record them in the future.

This post is a detailed write-up of what we built during the session.

San Mateo County election results

I live in El Granada, a tiny town just north of Half Moon Bay in San Mateo County, California.

Every county appears to handle counting and publishing election results differently. For San Mateo County the results are published on this page, and detailed per-precinct and per-candidate breakdowns are made available as a CSV file.

(I optimistically set up a Git scraper for these results in simonw/scrape-san-mateo-county-election-results-2024 only to learn that the CSV is updated just once a day, not continually as the ballots are counted.)

I'm particularly invested in the results of the Granada Community Services District board member elections. Our little town of El Granada is in "unincorporated San Mateo County" which means we don't have a mayor or any local officials, so the closest we get to hyper-local government is the officials that run our local sewage and parks organization! My partner Natalie ran the candidate forum event (effectively the debate) featuring three of the four candidates running for the two open places on the board.

Let's explore the data for that race using Datasette.

Importing CSV data into Datasette

I ran my part of the demo using Datasette Cloud, the beta of my new hosted Datasette service.

I started by using the pre-configured datasette-import plugin to import the data from the CSV file into a fresh table:

Modifying the schema

The table imported cleanly, but all of the columns from the CSV were still being treated as text. I used the datasette-edit-schema plugin to switch the relevant columns to integers so that we could run sums and sorts against them.

(I also noted that I really should add a "detect column types" feature to that plugin!)

The resulting 15,589 rows represent counts from individual precincts around the county for each of the races and measures on the ballot, with a row per precinct per candidate/choice per race.

Faceting and filtering the table

Since I'm interested in the Granada Community Services District election, I applied a facet on "Contest_title" and then used that to select that specific race.

I applied additional facets on "candidate_name" and "Precinct name".

This looks right to me: we have 7 precincts and 4 candidates for 28 rows in total.

Importing geospatial precinct shapes

Those precinct names are pretty non-descriptive! What does 33001 mean?

To answer that question, I added a new table.

San Mateo County offers precinct maps in the form of 23 PDF files. Our precincts are in the "Unincorporated Coastside" file:

Thankfully the county also makes that data available as geospatial data, hosted using Socrata with an option to export as GeoJSON.

The datasette-import plugin can handle JSON files... and if a JSON file contains a top-level object with a key that is an array of objects, it will import those objects as a table.

Dragging that file into Datasette is enough to import it as a table with a properties JSON column containing properties and a geometry JSON columnn with the GeoJSON geometry.

Here's where another plugin kicks in: datasette-leaflet-geojson looks for columns that contain valid GeoJSON geometries and... draws them on a map!

So now we can see the shape of the individual geometries.

Enriching that data to extract the precinct IDs

The precinctid is present in the data, but it's tucked away in a JSON object in that properties JSON blob. It would be more convenient if it was a top-level column.

Datasette's enrichments feature provides tools for running operations against every row in a table and adding new columns based on the results.

My Datasette Cloud instance was missing the datasette-enrichments-quickjs plugin that would let me run JavaScript code against the data. I used my privileged access on Datasette Cloud to add that plugin to my requirements and restarted the instance to install it.

I used that to run this JavaScript code against every row in the table and saved the output in a new precinct_id column:

function enrich(row) {
  return JSON.parse(row.properties).precinctid;
}

This took less than a second to run, adding and populating a new precinct_id column for the table.

Running a join

I demonstrated how to run a join between the election results and the precincts table using the Datasette SQL query editor.

I tried a few different things, but the most interesting query was this one:

select
  Precinct_name,
  precincts.geometry,
  total_ballots,
  json_group_object(
    candidate_name,
    total_votes
  ) as votes_by_candidate
from
  election_results 
  join precincts on election_results.Precinct_name = precincts.precinct_id
where 
  Contest_title = "Granada Community Services District Members, Board of Directors"
group by 
  Precinct_name,
  precincts.geometry,
  total_ballots;

Creating an API token to access the data

I was nearly ready to hand over to Alex for the second half of our demo, where he would use Observable Notebooks to build some custom visualizations on top of the data.

A great pattern for this is to host the data in Datasette and then fetch it into Observable via the Datasette JSON API.

Since Datasette Cloud instances are private by default we would need to create an API token that could do this.

I used this interface (from the datasette-auth-tokens plugin) to create a new token with read-only access to all databases and tables in the instance:

Since we're running a dedicated instance just for Datasette Public Office Hours there's no reason not to distribute that read-only token in publically accessible code.

Getting CORS working

Embarrassingly, I had forgotten that we would need CORS headers in order to access the data from an Observable notebook. Thankfully we have another plugin for that: datasette-cors. I installed that quickly and we confirmed that it granted access to the API from Observable as intended.

I handed over to Alex for the next section of the demo.

Working with Datasette in Observable

Alex started by running a SQL query from client-side JavaScript to pull in the joined data for our specific El Granada race:

sql = `
select
  Precinct_name,
  precincts.geometry,
  Split_name,
  Reporting_flag,
  Update_count,
  Pct_Id,
  Pct_seq_nbr,
  Reg_voters,
  Turn_Out,
  Contest_Id,
  Contest_seq_nbr,
  Contest_title,
  Contest_party_name,
  Selectable_Options,
  candidate_id,
  candidate_name,
  Candidate_Type,
  cand_seq_nbr,
  Party_Code,
  total_ballots,
  total_votes,
  total_under_votes,
  total_over_votes,
  [Vote Centers_ballots],
  [Vote Centers_votes],
  [Vote Centers_under_votes],
  [Vote Centers_over_votes],
  [Vote by Mail_ballots],
  [Vote by Mail_votes],
  [Vote by Mail_under_votes],
  [Vote by Mail_over_votes]
from
  election_results join precincts on election_results.Precinct_name = precincts.precinct_id
where "Contest_title" = "Granada Community Services District Members, Board of Directors"
limit 101;`

And in the next cell:

raw_data = fetch(
  `https://datasette-public-office-hours.datasette.cloud/data/-/query.json?_shape=array&sql=${encodeURIComponent(
    sql
  )}`,
  {
    headers: {
      Authorization: `Bearer ${secret}`
    }
  }
).then((r) => r.json())

Note the ?_shape=array parameter there, which causes Datasette to output the results directly as a JSON array of objects.

That's all it takes to get the data into Observable. Adding another cell like this confirms that the data is now available:

Inputs.table(raw_data)

Visualizing those with MapLibre GL

There are plenty of good options for visualizing GeoJSON data using JavaScript in an Observable notebook.

Alex started with MapLibre GL, using the excellent OpenFreeMap 3D tiles:

viewof map = {
  const container = html`<div style="height:800px;">`;
  yield container;
  const map = (container.value = new maplibregl.Map({
    container,
    zoom: 2,
    //style: "https://basemaps.cartocdn.com/gl/voyager-gl-style/style.json",
    style: "https://tiles.openfreemap.org/styles/liberty",
    scrollZoom: true
  }));
  yield container;

  map.on("load", function () {
    map.fitBounds(d3.geoBounds(data), { duration: 0 });
    map.addSource("precincts", {
      type: "geojson",
      data: data
    });
    map.addLayer({
      id: "precincts",
      type: "fill",
      source: "precincts",
      paint: {
        "fill-opacity": 0.4,
        "fill-color": [
          "case",
          ["==", ["get", "ratio"], null], "#000000",
          [
            "interpolate",
            ["linear"],
            ["get", "ratio"],
            0.0, "#0000ff",
            0.5, "#d3d3d3",
            1.0, "#ff0000"
          ]
        ]
      }
    });
    map.on("click", "precincts", (e) => {
      const { precinct, ratio } = e.features[0].properties;
      const description = JSON.stringify();

      new maplibregl.Popup()
        .setLngLat(e.lngLat)
        .setHTML(description)
        .addTo(map);
    });
  });
  invalidation.then(() => map.remove());
}

(This is just one of several iterations, I didn't capture detailed notes of every change Alex made to the code.)

Observable Plot

Observable notebooks come pre-loaded with the excellent Observable Plot charting library - Mike Bostock's high-level charting tool built on top of D3.

Alex used that to first render the shapes of the precincts directly, without even needing a tiled basemap:

Plot.plot({
  width,
  height: 600,
  legend: true,
  projection: {
    type: "conic-conformal",
    parallels: [37 + 4 / 60, 38 + 26 / 60],
    rotate: [120 + 30 / 60, 0],
    domain: data
  },
  marks: [
    Plot.geo(data, {
      strokeOpacity: 0.1,
      fill: "total_votes",
      title: (d) => JSON.stringify(d.properties),
      tip: true
    })
  ]
})

The parallels and rotate options there come from the handy veltman/d3-stateplane repo, which lists recommended settings for the State Plane Coordinate System used with projections in D3. Those values are for California Zone 3.

Bringing it all together

For the grand finale, Alex combined everything learned so far to build an interactive map allowing a user to select any of the 110 races on the ballot and see a heatmap of results for any selected candidate and option:

You can try this out in Alex's notebook. Here's the relevant code (Observable cells are divided by // --- comments). Note that Observable notebooks are reactive and allow variables to be referenced out of order.

// Select the contest
viewof contest = Inputs.select(contests, { label: "Choose a contest" })

// ---

// And the candidate
viewof candidate = Inputs.radio(
  candidates,
  {
    label: "Choose a candidate",
    value: candidates[0]
  }
)

// ---

// Show the map itself
Plot.plot({
  width,
  height: 600,
  legend: true,
  color: { scheme: "blues", legend: true },
  projection: {
    type: "mercator",
    domain: data2
  },
  marks: [
    Plot.geo(data2, {
      strokeOpacity: 0.1,
      fill: "ratio",
      tip: true
    })
  ]
})
# ---
data2 = ({
  type: "FeatureCollection",
  features: raw_data2.map((d) => ({
    type: "Feature",
    properties: {
      precinct: d.Precinct_name,
      total_ballots: d.total_ballots,
      ratio: JSON.parse(d.votes_by_candidate)[candidate] / d.total_ballots
    },
    geometry: JSON.parse(d.geometry)
  }))
})

// ---

raw_data2 = query(
  `select
  Precinct_name,
  precincts.geometry,
  total_ballots,
  json_grop_object(
    candidate_name,
    total_votes
  ) as votes_by_candidate
from
  election_results 
  join precincts on election_results.Precinct_name = precincts.precinct_id
where Contest_title = :contest
group by 
  Precinct_name,
  precincts.geometry,
  total_ballots;`,
  { contest }
)

// ---

raw_data2 = query(
  `select
  Precinct_name,
  precincts.geometry,
  total_ballots,
  json_group_object(
    candidate_name,
    total_votes
  ) as votes_by_candidate
from
  election_results 
  join precincts on election_results.Precinct_name = precincts.precinct_id
where Contest_title = :contest
group by 
  Precinct_name,
  precincts.geometry,
  total_ballots;`,
  { contest }
)

// ---

// Fetch the available contests
contests = query("select distinct Contest_title from election_results").then(
  (d) => d.map((d) => d.Contest_title)
)

// ---

// Extract available candidates for selected contest

candidates = Object.keys(
  JSON.parse(raw_data2[0].votes_by_candidate)
)

// ---

function query(sql, params = {}) {
  return fetch(
    `https://datasette-public-office-hours.datasette.cloud/data/-/query.json?${new URLSearchParams(
      { sql, _shape: "array", ...params }
    ).toString()}`,
    {
      headers: {
        Authorization: `Bearer ${secret}`
      }
    }
  ).then((r) => r.json());
}

We'll be doing this again

This was our first time trying something like this and I think it worked really well. We're already thinking about ways to improve it next time:

I want to record these sessions and make them available on YouTube for people who couldn't be there live
It would be fun to mix up the format. I'm particularly keen on getting more people involved giving demos - maybe having 5-10 minute lightning demo slots so we can see what other people are working on

Keep an eye on this blog or on the Datasette Discord for news about future sessions.

Tags: geospatial, mapping, politics, projects, datasette, datasette-cloud, alex-garcia, datasette-public-office-hours, leaflet

Datasette Public Office Hours, Friday Nov 8th at 2pm PT

2024-11-07T19:10:10+00:00

Datasette Public Office Hours, Friday Nov 8th at 2pm PT

Tomorrow afternoon (Friday 8th November) at 2pm PT we'll be hosting the first Datasette Public Office Hours - a livestream video session on Discord where Alex Garcia and myself will live code on some Datasette projects and hang out to chat about the project.

This is our first time trying this format. If it works out well I plan to turn it into a series.

Tags: open-source, datasette, discord, alex-garcia, datasette-public-office-hours

Hybrid full-text search and vector search with SQLite

2024-10-04T16:22:09+00:00

Hybrid full-text search and vector search with SQLite

As part of Alex’s work on his sqlite-vec SQLite extension - adding fast vector lookups to SQLite - he’s been investigating hybrid search, where search results from both vector similarity and traditional full-text search are combined together.

The most promising approach looks to be Reciprocal Rank Fusion, which combines the top ranked items from both approaches. Here’s Alex’s SQL query:

-- the sqlite-vec KNN vector search results
with vec_matches as (
  select
    article_id,
    row_number() over (order by distance) as rank_number,
    distance
  from vec_articles
  where
    headline_embedding match lembed(:query)
    and k = :k
),
-- the FTS5 search results
fts_matches as (
  select
    rowid,
    row_number() over (order by rank) as rank_number,
    rank as score
  from fts_articles
  where headline match :query
  limit :k
),
-- combine FTS5 + vector search results with RRF
final as (
  select
    articles.id,
    articles.headline,
    vec_matches.rank_number as vec_rank,
    fts_matches.rank_number as fts_rank,
    -- RRF algorithm
    (
      coalesce(1.0 / (:rrf_k + fts_matches.rank_number), 0.0) * :weight_fts +
      coalesce(1.0 / (:rrf_k + vec_matches.rank_number), 0.0) * :weight_vec
    ) as combined_rank,
    vec_matches.distance as vec_distance,
    fts_matches.score as fts_score
  from fts_matches
  full outer join vec_matches on vec_matches.article_id = fts_matches.rowid
  join articles on articles.rowid = coalesce(fts_matches.rowid, vec_matches.article_id)
  order by combined_rank desc
)
select * from final;

I’ve been puzzled in the past over how to best do that because the distance scores from vector similarity and the relevance scores from FTS are meaningless in comparison to each other. RRF doesn’t even attempt to compare them - it uses them purely for row_number() ranking within each set and combines the results based on that.

Tags: full-text-search, search, sql, sqlite, alex-garcia, vector-search, embeddings, rag

Using sqlite-vec with embeddings in sqlite-utils and Datasette

2024-08-11T23:37:42+00:00

Using sqlite-vec with embeddings in sqlite-utils and Datasette

My notes on trying out Alex Garcia's newly released sqlite-vec SQLite extension, including how to use it with OpenAI embeddings in both Datasette and sqlite-utils.

Tags: sqlite, datasette, sqlite-utils, openai, alex-garcia, embeddings

Introducing sqlite-lembed: A SQLite extension for generating text embeddings locally

2024-07-25T20:30:01+00:00

Introducing sqlite-lembed: A SQLite extension for generating text embeddings locally

Alex Garcia's latest SQLite extension is a C wrapper around the llama.cpp that exposes just its embedding support, allowing you to register a GGUF file containing an embedding model:

INSERT INTO temp.lembed_models(name, model)
  select 'all-MiniLM-L6-v2',
  lembed_model_from_file('all-MiniLM-L6-v2.e4ce9877.q8_0.gguf');

And then use it to calculate embeddings as part of a SQL query:

select lembed(
  'all-MiniLM-L6-v2',
  'The United States Postal Service is an independent agency...'
); -- X'A402...09C3' (1536 bytes)

all-MiniLM-L6-v2.e4ce9877.q8_0.gguf here is a 24MB file, so this should run quite happily even on machines without much available RAM.

What if you don't want to run the models locally at all? Alex has another new extension for that, described in Introducing sqlite-rembed: A SQLite extension for generating text embeddings from remote APIs. The rembed is for remote embeddings, and this extension uses Rust to call multiple remotely-hosted embeddings APIs, registered like this:

INSERT INTO temp.rembed_clients(name, options)
  VALUES ('text-embedding-3-small', 'openai');
select rembed(
  'text-embedding-3-small',
  'The United States Postal Service is an independent agency...'
); -- X'A452...01FC', Blob<6144 bytes>

Here's the Rust code that implements Rust wrapper functions for HTTP JSON APIs from OpenAI, Nomic, Cohere, Jina, Mixedbread and localhost servers provided by Ollama and Llamafile.

Both of these extensions are designed to complement Alex's sqlite-vec extension, which is nearing a first stable release.

Via @alexgarciaxyz

Tags: c, sqlite, rust, alex-garcia, embeddings, llama-cpp

sqlite-jiff

2024-07-23T03:53:52+00:00

sqlite-jiff

I linked to the brand new Jiff datetime library yesterday. Alex Garcia has already used it for an experimental SQLite extension providing a timezone-aware jiff_duration() function - a useful new capability since SQLite's built in date functions don't handle timezones at all.

select jiff_duration(
  '2024-11-02T01:59:59[America/Los_Angeles]',
  '2024-11-02T02:00:01[America/New_York]',
  'minutes'
) as result; -- returns 179.966

The implementation is 65 lines of Rust.

Via @agarcia_me

Tags: sqlite, timezones, rust, alex-garcia

datasette-pins — a new Datasette plugin for pinning tables and queries

2024-05-09T18:29:03+00:00

datasette-pins — a new Datasette plugin for pinning tables and queries

Alex Garcia built this plugin for Datasette Cloud, and as with almost every Datasette Cloud features we're releasing it as an open source package as well.

datasette-pins allows users with the right permission to "pin" tables, databases and queries to their homepage. It's a lightweight way to customize that homepage, especially useful as your Datasette instance grows to host dozens or even hundreds of tables.

Tags: plugins, datasette, datasette-cloud, alex-garcia

I'm writing a new vector search SQLite Extension

2024-05-03T03:16:39+00:00

I'm writing a new vector search SQLite Extension

Alex Garcia is working on sqlite-vec, a spiritual successor to his sqlite-vss project. The new SQLite C extension will have zero other dependencies (sqlite-vss used some tricky C++ libraries) and will work using virtual tables, storing chunks of vectors in shadow tables to avoid needing to load everything into memory at once.

Tags: c, sqlite, vectors, alex-garcia, vector-search, embeddings

datasette-plot - a new Datasette Plugin for building data visualizations

2023-12-31T05:04:19+00:00

datasette-plot - a new Datasette Plugin for building data visualizations

I forgot to link to this here last week: Alex Garcia released the first version of datasette-plot, a brand new Datasette visualization plugin built on top of the Observable Plot charting library. We plan to use this as the new, updated alternative to my older datasette-vega plugin.

Tags: plugins, visualization, datasette, observable, alex-garcia, observable-plot

Annotate and explore your data with datasette-comments

2023-11-30T21:59:54+00:00

Annotate and explore your data with datasette-comments

New plugin for Datasette and Datasette Cloud: datasette-comments, providing tools for collaborating on data exploration with a team through posting comments on individual rows of data.

Alex Garcia built this for Datasette Cloud but as with almost all of our work there it’s also available as an open source Python package.

Tags: collaboration, projects, datasette, datasette-cloud, alex-garcia

Weeknotes: the Datasette Cloud API, a podcast appearance and more

2023-10-01T00:03:53+00:00

Datasette Cloud now has a documented API, plus a podcast appearance, some LLM plugins work and some geospatial excitement.

The Datasette Cloud API

My biggest achievement this week is that I documented and announced the API for Datasette Cloud.

I wrote about this at length in Getting started with the Datasette Cloud API on the Datasette Cloud blog. I also used this as an opportunity to start a documentation site for the service, now available at datasette.cloud/docs.

The API is effectively the Datasette 1.0 alpha write API, described here previously. You can use the API to both read and write data to a Datasette Cloud space, with finely-grained permissions (powered by the datasette-auth-tokens plugin) so you can create tokens that are restricted to actions just against specified tables.

The blog entry about it doubles as a tutorial, describing how I wrote code to import the latest documents from the US Government Federal Register into a Datasette Cloud space, using a dependency-free Python script and GitHub Actions.

You can see that code in the new federal-register-to-datasette GitHub repository. It's pretty small - just 70 lines of Python and 22 of YAML.

The more time I spend writing code against the Datasette API the more confident I get that it's shaped in the right way. I'm happy to consider it stable for the 1.0 release now.

Talking Large Language Models with Rooftop Ruby

I recorded a podcast episode this week for Rooftop Ruby with Collin Donnell and Joel Drapper. It was a really high quality conversation - we went for about an hour and 20 minutes and covered a huge amount of ground.

After the podcast came out I took the MP3, ran it through MacWhisper and then spent several hours marking up speakers and editing the resulting text. I also added headings corresponding to the different topics we covered, along with inline links to other relevant material.

I'm really pleased with the resulting document, which you can find at Talking Large Language Models with Rooftop Ruby. It was quite a bit of work but I think it was worthwhile - I've since been able to answer some questions about LLMs on Mastodon and Twitter by linking directly to the point within the transcript that discussed those points.

I also dropped in my own audio player, developed with GPT-4 assistance, and provided links from the different transcript sessions that would jump the audio to that point in the conversation.

Also this week: while closing a bunch of VS Code tabs I stumbled across a partially written blog entry about Things I've learned about building CLI tools in Python, so I finished that up and published it.

I'm trying to leave less unfinished projects lying around on my computer, so if something is 90% finished I'll try to wrap it up and put it out there to get it off my ever-expanding plate.

llm-llama-cpp

LLM has started to collect a small but healthy community on Discord, which is really exciting.

My absolute favourite community project so far is Drew Breunig's Facet Finder, which he described in Finding Bathroom Faucets with Embeddings. He used llm-clip to calculate embeddings for 20,000 pictures of faucets, then ran both similarity and text search against them to help renovate his bathroom. It's really fun!

I shipped a new version of the llm-llama-cpp plugin this week which was mostly written by other people: llm-llama-cpp 0.2b1. Alexis Métaireau and LoopControl submitted fixes to extend the default max token limit (fixing a frustrating issue with truncated responses) and to allow for increasing the number of GPU layers used to run the models.

I also shipped LLM 0.11, the main feature of which was support for the new OpenAI gpt-3.5-turbo-instruct model. I really need to split the OpenAI support out into a separate plugin so I can ship fixes to that without having to release the core LLM package.

And I put together an llm-plugin cookiecutter template, which I plan to use for all of my plugins going forward.

Getting excited about TG and sqlite-tg

TG is a brand new C library from Tile38 creator Josh Baker. It's really exciting: it provides a set of fast geospatial operations - the exact subset I usually find myself needing, based around polygon intersections, GeoJSON, WKT, WKB and geospatial indexes - implemented with zero external dependencies. It's shipped as a single C file, reminiscent of the SQLite amalgamation.

I noted in a few places that it could make a great SQLite extension... and Alex Garcia fell victim to my blatant nerd-sniping and built the first version of sqlite-tg within 24 hours!

I wrote about my own explorations of Alex's work in Geospatial SQL queries in SQLite using TG, sqlite-tg and datasette-sqlite-tg. I'm thrilled at the idea of having a tiny, lightweight alternative to SpatiaLite as an addition to the Datasette ecosystem, and the SQLite world in general.

Two tiny Datasette releases

I released dot-releases for Datasette:

Both of these feature the same fix, described in Issue 2189: Server hang on parallel execution of queries to named in-memory databases.

Short version: it turns out the experimental work I did a while ago to try running SQL queries in parallel was causing threading deadlock issues against in-memory named SQLite databases. No-one had noticed because those are only available within Datasette plugins, but I'd started to experience them as I started writing my own plugins that used that feature.

ChatGPT in the newsroom

I signed up for a MOOC (Massive Open Online Courses) about journalism and ChatGPT!

How to use ChatGPT and other generative AI tools in your newsrooms is being taught by Aimee Rinehart and Sil Hamilton for the Knight Center.

I actually found out about it because people were being snarky about it on Twitter. That's not a big surprise - there are many obvious problems with applying generative AI to journalism.

As you would hope, this course is not a hype-filled pitch for writing AI-generated news stories. It's a conversation between literally thousands of journalists around the world about the ethical and practical implications of this technology.

I'm really enjoying it. I'm learning a huge amount about how people experience AI tools, the kinds of questions they have about them and the kinds of journalism problems that make sense for them to solve.

Releases this week

datasette-remote-actors 0.1a2 - 2023-09-28
Datasette plugin for fetching details of actors from a remote endpoint
llm-llama-cpp 0.2b1 - 2023-09-28
LLM plugin for running models using llama.cpp
datasette-auth-tokens 0.4a4 - 2023-09-26
Datasette plugin for authenticating access using API tokens
datasette 1.0a7 - 2023-09-21
An open source multi-tool for exploring and publishing data
datasette-upload-dbs 0.3.1 - 2023-09-20
Upload SQLite database files to Datasette
datasette-mask-columns 0.2.2 - 2023-09-20
Datasette plugin that masks specified database columns
llm 0.11 - 2023-09-19
Access large language models from the command-line

TIL this week

Understanding the CSS auto-resizing textarea trick - 2023-09-30
Snapshot testing with Syrupy - 2023-09-26
Geospatial SQL queries in SQLite using TG, sqlite-tg and datasette-sqlite-tg - 2023-09-25
Trying out the facebook/musicgen-small sound generation model - 2023-09-23

Tags: journalism, projects, sqlite, ai, datasette, weeknotes, datasette-cloud, alex-garcia, generative-ai, llms, llm

Geospatial SQL queries in SQLite using TG, sqlite-tg and datasette-sqlite-tg

2023-09-25T19:45:03+00:00

Geospatial SQL queries in SQLite using TG, sqlite-tg and datasette-sqlite-tg

Alex Garcia built sqlite-tg—a SQLite extension that uses the brand new TG geospatial library to provide a whole suite of custom SQL functions for working with geospatial data.

Here are my notes on trying out his initial alpha releases. The extension already provides tools for converting between GeoJSON, WKT and WKB, plus the all important tg_intersects() function for testing if a polygon or point overlap each other.

It’s pretty useful already. Without any geospatial indexing at all I was still able to get 700ms replies to a brute-force point-in-polygon query against 150MB of GeoJSON timezone boundaries stored as JSON text in a table.

Tags: geospatial, sqlite, geojson, datasette, alex-garcia, tg

Weeknotes: Embeddings, more embeddings and Datasette Cloud

2023-09-17T05:10:13+00:00

Since my last weeknotes, a flurry of activity. LLM has embeddings support now, and Datasette Cloud has driven some major improvements to the wider Datasette ecosystem.

Embeddings in LLM

LLM gained embedding support in version 0.9, and then got binary embedding support (for CLIP) in version 0.10. I wrote about those releases in detail in:

Embeddings are a fascinating tool. If you haven't got your head around them yet the first of my blog entries tries to explain why they are so interesting.

There's a lot more I want to built on top of embeddings - most notably, LLM (or Datasette, or likely a combination of the two) will be growing support for Retrieval Augmented Generation on top of the LLM embedding mechanism.

Annotated releases

I always include a list of new releases in my weeknotes. This time I'm going to use those to illustrate the themes I've been working on.

The first group of release relates to LLM and its embedding support. LLM 0.10 extended that support:

llm 0.10 - 2023-09-12
Access large language models from the command-line

Embedding models can now be built as LLM plugins. I've released two of those so far:

llm-sentence-transformers 0.1.2 - 2023-09-13
LLM plugin for embeddings using sentence-transformers
llm-clip 0.1 - 2023-09-12
Generate embeddings for images and text using CLIP with LLM

The CLIP one is particularly fun, because it genuinely allows you to build a sophisticated image search engine that runs entirely on your own computer!

symbex 1.4 - 2023-09-05
Find the Python code for specified symbols

Symbex is my tool for extracting symbols - functions, methods and classes - from Python code. I introduced that in Symbex: search Python code for functions and classes, then pipe them into a LLM.

Symbex 1.4 adds a tiny but impactful feature: it can now output a list of symbols as JSON, CSV or TSV. These output formats are designed to be compatible with the new llm embed-multi command, which means you can easily create embeddings for all of your functions:

symbex '*' '*:*' --nl | \
  llm embed-multi symbols - \
  --format nl --database embeddings.db --store

I haven't fully explored what this enables yet, but it should mean that both related functions and semantic function search ("Find my a function that downloads a CSV") are now easy to build.

llm-cluster 0.2 - 2023-09-04
LLM plugin for clustering embeddings

Yet another thing you can do with embeddings is use them to find clusters of related items.

The neatest feature of llm-cluster is that you can ask it to generate names for these clusters by sending the names of the items in each cluster through another language model, something like this:

llm cluster issues 10 \
  -d issues.db \
  --summary \
  --prompt 'Short, concise title for this cluster of related documents'

One last embedding related project: datasette-llm-embed is a tiny plugin that adds a select llm_embed('sentence-transformers/all-mpnet-base-v2', 'This is some text') SQL function. I built it to support quickly prototyping embedding-related ideas in Datasette.

datasette-llm-embed 0.1a0 - 2023-09-08
Datasette plugin adding a llm_embed(model_id, text) SQL function

Spending time with embedding models has lead me to spend more time with Hugging Face. I realized last week that the Hugging Face all models sorted by downloads page doubles as a list of the models that are most likely to be easy to use.

One of the models I tried out was Salesforce BLIP, an astonishing model that can genuinely produce usable captions for images.

It's really easy to work with. I ended up building this tiny little CLI tool that wraps the model:

blip-caption 0.1 - 2023-09-10
Generate captions for images with Salesforce BLIP

Releases driven by Datasette Cloud

Datasette Cloud continues to drive improvements to the wider Datasette ecosystem as a whole.

It runs on the latest Datasette 1.0 alpha series, taking advantage of the JSON write API.

This also means that it's been highlighting breaking changes in 1.0 that have caused old plugins to break, either subtly or completely.

This has driven a bunch of new plugin releases. Some of these are compatible with both 0.x and 1.x - the ones that only work with the 1.x alphas are themselves marked as alpha releases.

datasette-export-notebook 1.0.1 - 2023-09-15
Datasette plugin providing instructions for exporting data to Jupyter or Observable
datasette-cluster-map 0.18a0 - 2023-09-11
Datasette plugin that shows a map for any data with latitude/longitude columns
datasette-graphql 3.0a0 - 2023-09-07
Datasette plugin providing an automatic GraphQL API for your SQLite databases

Datasette Cloud's API works using database-backed access tokens, to ensure users can revoke tokens if they need to (something that's not easily done with purely signed tokens) and that each token can record when it was most recently used.

I've been building that into the existing datasette-auth-tokens plugin:

datasette-auth-tokens 0.4a3 - 2023-08-31
Datasette plugin for authenticating access using API tokens

Alex Garcia has been working with me building out features for Datasette Cloud, generously sponsored by Fly.io.

We're beginning to build out social features for Datasette Cloud - feature that will help teams privately collaborate on data investigations together.

Alex has been building datasette-short-links as an experimental link shortener. In building that, we realized that we needed a mechanism for resolving actor IDs displayed in a list (e.g. this link created by X) to their actual names.

Datasette doesn't dictate the shape of actor representations, and there's no guarantee that actors would be represented in a predictable table.

So... we needed a new plugin hook. I released Datasette 1.06a with a new hook, actors_from_ids(actor_ids), which can be used to answer the question "who are the actors represented by these IDs".

Alex is using this in datasette-short-links, and I built two plugins to work with the new hook as well:

datasette 1.0a6 - 2023-09-08
An open source multi-tool for exploring and publishing data
datasette-debug-actors-from-ids 0.1a1 - 2023-09-08
Datasette plugin for trying out the actors_from_ids hook
datasette-remote-actors 0.1a1 - 2023-09-08
Datasette plugin for fetching details of actors from a remote endpoint

Datasette Cloud lets users insert, edit and delete rows from their tables, using the plugin Alex built called datasette-write-ui which he introduced on the Datasette Cloud blog.

This inspired me to finally put out a fresh release of datasette-edit-schema - the plugin which provides the ability to edit table schemas - adding and removing columns, changing column types, even altering the order columns are stored in the table.

datasette-edit-schema 0.6 is a major release, with three significant new features:

You can now create a brand new table from scratch!
You can edit the table's primary key
You can modify the foreign key constraints on the table

Those last two became important when I realized that Datasette's API is much more interesting if there are foreign key relationships to follow.

Combine that with datasette-write-ui and Datasette Cloud now has a full set of features for building, populating and editing tables - backed by a comprehensive JSON API.

sqlite-migrate 0.1a2 - 2023-09-03
A simple database migration system for SQLite, based on sqlite-utils

sqlite-migrate is still marked as an alpha, but won't be for much longer: it's my attempt at a migration system for SQLite, inspired by Django migrations but with a less sophisticated set of features.

I'm using it in LLM now to manage the schema used to store embeddings, and it's beginning to show up in some Datasette plugins as well. I'll be promoting this to non-alpha status pretty soon.

sqlite-utils 3.35.1 - 2023-09-09
Python CLI utility and library for manipulating SQLite databases

A tiny fix in this, which with hindsight was less impactful than I thought.

I spotted a bug on Datasette Cloud when I configured full-text search on a column, then edited the schema and found that searches no longer returned the correct results.

It turned out the rowid column in SQLite was being rewritten by calls to the sqlite-utils table.transform() method. FTS records are related to their underlying row by rowid, so this was breaking search!

I pushed out a fix for this in 3.35.1. But then... I learned that rowid in SQLite has always been unstable - they are rewritten any time someone VACUUMs a table!

I've been designing future features for Datasette that assume that rowid is a useful stable identifier for a row. This clearly isn't going to work! I'm still thinking through the consequences of it, but I think there may be Datasette features (like the ability to comment on a row) that will only work for tables with a proper foreign key.

sqlite-chronicle

sqlite-chronicle 0.1 - 2023-09-11
Use triggers to track when rows in a SQLite table were updated or deleted

This is very early, but I'm excited about the direction it's going in.

I keep on finding problems where I want to be able to synchronize various processes with the data in a table.

I built sqlite-history a few months ago, which uses SQLite triggers to create a full copy of the updated data every time a row in a table is edited.

That's a pretty heavy-weight solution. What if there was something lighter that could achieve a lot of the same goals?

sqlite-chronicle uses triggers to instead create what I'm calling a "chronicle table". This is a shadow table that records, for every row in the main table, four integer values:

added_ms - the timestamp in milliseconds when the row was added
updated_ms - the timestamp in milliseconds when the row was last updated
version - a constantly incrementing version number, global across the entire table
deleted - set to 1 if the row has been deleted

Just storing four integers (plus copies of the primary key) makes this a pretty tiny table, and hopefully one that's cheap to update via triggers.

But... having this table enables some pretty interesting things - because external processes can track the last version number that they saw and use it to see just which rows have been inserted and updated since that point.

I gave a talk at DjangoCon a few years ago called the denormalized query engine pattern, describing the challenge of syncing an external search index like Elasticsearch with data held in a relational database.

These chronicle tables can solve that problem, and can be applied to a whole host of other problems too. So far I'm thinking about the following:

Publishing SQLite databases up to Datasette, sending only the rows that have changed since the last sync. I wrote a prototype that does this and it seems to work very well.
Copying a table from Datasette Cloud to other places - a desktop copy, or another instance, or even into an alternative database such as PostgreSQL or MySQL, in a way that only copies and deletes rows that have changed.
Saved search alerts: run a SQL query against just rows that were modified since the last time that query ran, then send alerts if any rows are matched.
Showing users a note that "34 rows in this table have changed since your last visit", then displaying those rows.

I'm sure there are many more applications for this. I'm looking forward to finding out what they are!

sqlite-utils-move-tables 0.1 - 2023-09-01
sqlite-utils plugin adding a move-tables command

I needed to fix a bug in Datasette Cloud by moving a table from one database to another... so I built a little plugin for sqlite-utils that adds a sqlite-utils move-tables origin.db destination.db tablename command. I love being able to build single-use features as plugins like this.

And some TILs

Embedding paragraphs from my blog with E5-large-v2 - 2023-09-08

This was a fun TIL exercising the new embeddings feature in LLM. I used Django SQL Dashboardto break up my blog entries into paragraphs and exported those as CSV which could then be piped into llm embed-multi, then used that to build a CLI-driven semantic search engine for my blog.

Using llama-cpp-python grammars to generate JSON - 2023-09-13

llama-cpp has grammars now, which enable you to control the exact output format of the LLM. I'm optimistic that these could be used to implement an equivalent to OpenAI Functions on top of Llama 2 and similar models. So far I've just got them to output arrays of JSON objects.

Summarizing Hacker News discussion themes with Claude and LLM - 2023-09-09

I'm using this trick a lot at the moment. I have API access to Claude now, which has a 100,000 token context limit (GPT-4 is just 8,000 by default). That's enough to summarize 100+ comment threads from Hacker News, for which I'm now using this prompt:

Summarize the themes of the opinions expressed here, including quotes (with author attribution) where appropriate.

The quotes part has been working really well - it turns out summaries of themes with illustrative quotes are much more interesting, and so far my spot checks haven't found any that were hallucinated.

Trying out cr-sqlite on macOS - 2023-09-13

cr-sqlite adds full CRDTs to SQLite, which should enable multiple databases to accept writes independently and then seamlessly merge them together. It's a very exciting capability!

Running Datasette on Hugging Face Spaces - 2023-09-08

It turns out Hugging Faces offer free scale-to-zero hosting for demos that run in Docker containers on machines with a full 16GB of RAM! I'm used to optimizing Datasette for tiny 256MB containers, so having this much memory available is a real treat.

And the rest:

Limited JSON API for Google searches using Programmable Search Engine - 2023-09-17
Running tests against multiple versions of a Python dependency in GitHub Actions - 2023-09-15
Remember to commit when using datasette.execute_write_fn() - 2023-08-31

Tags: plugins, projects, datasette, weeknotes, datasette-cloud, sqlite-utils, alex-garcia, embeddings, llm

Introducing datasette-litestream: easy replication for SQLite databases in Datasette

2023-09-13T19:28:37+00:00

Introducing datasette-litestream: easy replication for SQLite databases in Datasette

We use Litestream on Datasette Cloud for streaming backups of user data to S3. Alex Garcia extracted out our implementation into a standalone Datasette plugin, which bundles the Litestream Go binary (for the relevant platform) in the package you get when you run “datasette install datasette-litestream”—so now Datasette has a very robust answer to questions about SQLite disaster recovery beyond just the Datasette Cloud platform.

Tags: plugins, sqlite, datasette, datasette-cloud, litestream, alex-garcia

Datasette 1.0a4 and 1.0a5, plus weeknotes

2023-08-30T14:33:35+00:00

Two new alpha releases of Datasette, plus a keynote at WordCamp, a new LLM release, two new LLM plugins and a flurry of TILs.

Datasette 1.0a5

Released this morning, Datasette 1.0a5 has some exciting new changes driven by Datasette Cloud and the ongoing march towards Datasette 1.0.

Alex Garcia is working with me on Datasette Cloud and Datasette generally, generously sponsored by Fly.

Two of the changes in 1.0a5 were driven by Alex:

New datasette.yaml (or .json) configuration file, which can be specified using datasette -c path-to-file. The goal here to consolidate settings, plugin configuration, permissions, canned queries, and other Datasette configuration into a single single file, separate from metadata.yaml. The legacy settings.json config file used for Configuration directory mode has been removed, and datasette.yaml has a "settings" section where the same settings key/value pairs can be included. In the next future alpha release, more configuration such as plugins/permissions/canned queries will be moved to the datasette.yaml file. See #2093 for more details.

Right from the very start of the project, Datasette has supported specifying metadata about databases - sources, licenses, etc, as a metadata.json file that can be passed to Datasette like this:

datasette data.db -m metadata.json

Over time, the purpose and uses of that file has expanded in all kinds of different directions. It can be used for plugin settings, and to set preferences for a table default page size, default facets etc), and even to configure access permissions for who can view what.

The name metadata.json is entirely inappropriate for what the file actually does. It's a mess.

I've always had a desire to fix this before Datasette 1.0, but it never quite got high up enough the priority list for me to spend time on it.

Alex expressed interest in fixing it, and has started to put a plan into motion for cleaning it up.

More details in the issue.

The Datasette _internal database has had some changes. It no longer shows up in the datasette.databases list by default, and is now instead available to plugins using the datasette.get_internal_database(). Plugins are invited to use this as a private database to store configuration and settings and secrets that should not be made visible through the default Datasette interface. Users can pass the new --internal internal.db option to persist that internal database to disk. (#2157).

This was the other initiative driven by Alex. In working on Datasette Cloud we realized that it's actually quite common for plugins to need somewhere to store data that shouldn't necessarily be visible to regular users of a Datasette instance - things like tokens created by datasette-auth-tokens, or the progress bar mechanism used by datasette-upload-csvs.

Alex pointed out that the existing _internal database for Datasette could be expanded to cover these use-cases as well. #2157 has more details on how we agreed this should work.

The other changes in 1.0a5 were driven by me:

When restrictions are applied to API tokens, those restrictions now behave slightly differently: applying the view-table restriction will imply the ability to view-database for the database containing that table, and both view-table and view-database will imply view-instance. Previously you needed to create a token with restrictions that explicitly listed view-instance and view-database and view-table in order to view a table without getting a permission denied error. (#2102)

I described finely-grained permissions for access tokens in my annotated release notes for 1.0a2.

They provide a mechanism for creating an API token that's only allowed to perform a subset of actions on behalf of the user.

In trying these out for Datasette Cloud I came across a nasty usability flaw. You could create a token that was restricted to view-table access for a specific table... and it wouldn't work. Because the access code for that view would check for view-instance and view-database permission first.

1.0a5 fixes that, by adding logic that says that if a token can view-table that implies it can view-database for the database containing that table, and view-instance for the overall instance.

This change took quite some time to develop, because any time I write code involving permissions I like to also include extremely comprehensive automated tests.

The -s/--setting option can now take dotted paths to nested settings. These will then be used to set or over-ride the same options as are present in the new configuration file. (#2156)

This is a fun little detail inspired by Alex's configuration work.

I run a lot of different Datasette instances, often on an ad-hoc basis.

I sometimes find it frustrating that to use certain features I need to create a metadata.json (soon to be datasette.yml) configuration file, just to get something to work.

Wouldn't it be neat if every possible setting for Datasette could be provided both in a configuration file or as command-line options?

That's what the new --setting option aims to solve. Anything that can be represented as a JSON or YAML configuration can now also be represented as key/value pairs on the command-line.

Here's an example from my initial issue comment:

datasette \
  -s settings.sql_time_limit_ms 1000 \
  -s plugins.datasette-auth-tokens.manage_tokens true \
  -s plugins.datasette-auth-tokens.manage_tokens_database tokens \
  -s plugins.datasette-ripgrep.path "/home/simon/code-to-search" \
  -s databases.mydatabase.tables.example_table.sort created \
  mydatabase.db tokens.db

Once this feature is complete, the above will behave the same as a datasette.yml file containing this:

plugins:
  datasette-auth-tokens:
    manage_tokens: true
    manage_tokens_database: tokens
  datasette-ripgrep:
    path: /home/simon/code-to-search
databases:
  mydatabase:
    tables:
      example_table:
        sort: created
settings:
  sql_time_limit_ms: 1000

I've experimented with ways of turning key/value pairs into nested JSON objects before, with my json-flatten library.

This time I took a slightly different approach. In particular, if you need to pass a nested JSON object (such as an array) which isn't easily represented using key.nested notation, you can pass it like this instead:

datasette data.db \
  -s plugins.datasette-complex-plugin.configs \
  '{"foo": [1,2,3], "bar": "baz"}'

Which would convert to the following equivalent YAML:

plugins:
  datasette-complex-plugin:
    configs:
      foo:
        - 1
        - 2
        - 3
      bar: baz

These examples don't quite work yet, because the plugin configuration hasn't migrated to datasette.yml - but it should work for the next alpha.

New --actor '{"id": "json-goes-here"}' option for use with datasette --get to treat the simulated request as being made by a specific actor, see datasette --get. (#2153)

This is a fun little debug helper I built while working on restricted tokens.

The datasette --get /... option is a neat trick that can be used to simulate an HTTP request through the Datasette instance, without even starting a server running on a port.

I use it for things like generating social media card images for my TILs website.

The new --actor option lets you add a simulated actor to the request, which is useful for testing out things like configured authentication and permissions.

A security fix in Datasette 1.0a4

Datasette 1.0a4 has a security fix: I realized that the API explorer I added in the 1.0 alpha series was exposing the names of databases and tables (though not their actual content) to unauthenticated users, even for Datasette instances that were protected by authentication.

I issued a GitHub security advisory for this: Datasette 1.0 alpha series leaks names of databases and tables to unauthenticated users, which has since been issued a CVE, CVE-2023-40570 - GitHub is a CVE Numbering Authority which means their security team are trusted to review such advisories and issue CVEs where necessary.

I expect the impact of this vulnerability to be very small: outside of Datasette Cloud very few people are running the Datasette 1.0 alphas on the public internet, and it's possible that the set of those users who are also authenticating their instances to provide authenticated access to private data - especially where just the database and table names of that data is considered sensitive - is an empty set.

Datasette Cloud itself has detailed access logs primarily to help evaluate this kind of threat. I'm pleased to report that those logs showed no instances of an unauthenticated user accessing the pages in question prior to the bug being fixed.

A keynote at WordCamp US

Last Friday I gave a keynote at WordCamp US on the subject of Large Language Models.

I used MacWhisper and my annotated presentation tool to turn that into a detailed transcript, complete with additional links and context: Making Large Language Models work for you.

llm-openrouter and llm-anyscale-endpoints

I released two new plugins for LLM, which lets you run large language models either locally or via APIs, as both a CLI tool and a Python library.

Both plugins provide access to API-hosted models:

llm-openrouter provides access to models hosted by OpenRouter. Of particular interest here is Claude - I'm still on the waiting list for the official Claude API, but in the meantime I can pay for access to it via OpenRouter and it works just fine. Claude has a 100,000 token context, making it a really great option for working with larger documents.
llm-anyscale-endpoints is a similar plugin that instead works with Anyscale Endpoints. Anyscale provide Llama 2 and Code Llama at extremely low prices - between $0.25 and $1 per million tokens, depending on the model.

These plugins were very quick to develop.

Both OpenRouter and Anyscale Endpoints provide API endpoints that emulate the official OpenAI APIs, including the way the handle streaming tokens.

LLM already has code for talking to those endpoints via the openai Python library, which can be re-pointed to another backend using the officially supported api_base parameter.

So the core code for the plugins ended up being less than 30 lines each: llm_openrouter.py and llm_anyscale_endpoints.py.

llm 0.8

I shipped LLM 0.8 a week and a half ago, with a bunch of small changes.

The most significant of these was a change to the default llm logs output, which shows the logs (recorded in SQLite) of the previous prompts and responses you have sent through the tool.

This output used to be JSON. It's now Markdown, which is both easier to read and can be pasted into GitHub Issue comments or Gists or similar to share the results with other people.

The release notes for 0.8 describe all of the other improvements.

sqlite-utils 3.35

The 3.35 release of sqlite-utils was driven by LLM.

sqlite-utils has a mechanism for adding foreign keys to an existing table - something that's not supported by SQLite out of the box.

That implementation used to work using a deeply gnarly hack: it would switch the sqlite_master table over to being writable (using PRAGMA writable_schema = 1), update that schema in place to reflect the new foreign keys and then toggle writable_schema = 0 back again.

It turns out there are Python installations out there - most notably the system Python on macOS - which completely disable the ability to write to that table, no matter what the status of the various pragmas.

I was getting bug reports from LLM users who were running into this. I realized that I had a solution for this mostly implemented already: the sqlite-utils transform() method, which can apply all sorts of complex schema changes by creating a brand new table, copying across the old data and then renaming it to replace the old one.

So I dropped the old writable_schema mechanism entirely in favour of .transform() - it's slower, because it requires copying the entire table, but it doesn't have weird edge-cases where it doesn't work.

Since sqlite-utils supports plugins now, I realized I could set a healthy precedent by making the removed feature available in a new plugin: sqlite-utils-fast-fks, which provides the following command for adding foreign keys the fast, old way (provided your installation supports it):

sqlite-utils install sqlite-utils-fast-fks
sqlite-utils fast-fks my_database.db places country_id country id

I've always admired how jQuery uses plugins to keep old features working on an opt-in basis after major version upgrades. I'm excited to be able to apply the same pattern for sqlite-utils.

paginate-json 1.0

paginate-json is a tiny tool I first released a few years ago to solve a very specific problem.

There's a neat pattern in some JSON APIs where the HTTP link header is used to indicate subsequent pages of results.

The best example I know of this is the GitHub API. Run this to see what it looks like here I'm using the events API):

curl -i \
  https://api.github.com/users/simonw/events

Here's a truncated example of the output:

HTTP/2 200 
server: GitHub.com
content-type: application/json; charset=utf-8
link: <https://api.github.com/user/9599/events?page=2>; rel="next", <https://api.github.com/user/9599/events?page=9>; rel="last"

[
  {
    "id": "31467177730",
    "type": "PushEvent",

The link header there specifies a next and last URL that can be used for pagination.

To fetch all available items, you can follow the next link repeatedly until it runs out.

My paginate-json tool can follow these links for you. If you run it like this:

paginate-json \
  https://api.github.com/users/simonw/events

It will output a single JSON array consisting of the results from every available page.

The 1.0 release adds a bunch of small features, but also marks my confidence in the stability of the design of the tool.

The Datasette JSON API has supported link pagination for a while - you can use paginate-json with Datasette like this, taking advantage of the new --key option to paginate over the array of objects returned in the "rows" key:

paginate-json \
  'https://datasette.io/content/pypi_releases.json?_labels=on' \
  --key rows \
  --nl

The --nl option here causes paginate-json to output the results as newline-delimited JSON, instead of bundling them together into a JSON array.

Here's how to use sqlite-utils insert to insert that data directly into a fresh SQLite database:

paginate-json \
  'https://datasette.io/content/pypi_releases.json?_labels=on' \
  --key rows \
  --nl | \
    sqlite-utils insert data.db releases - \
      --nl --flatten

Releases this week

paginate-json 1.0 - 2023-08-30
Command-line tool for fetching JSON from paginated APIs
datasette-auth-tokens 0.4a2 - 2023-08-29
Datasette plugin for authenticating access using API tokens
datasette 1.0a5 - 2023-08-29
An open source multi-tool for exploring and publishing data
llm-anyscale-endpoints 0.2 - 2023-08-25
LLM plugin for models hosted by Anyscale Endpoints
datasette-jellyfish 2.0 - 2023-08-24
Datasette plugin adding SQL functions for fuzzy text matching powered by Jellyfish
datasette-configure-fts 1.1.2 - 2023-08-23
Datasette plugin for enabling full-text search against selected table columns
datasette-ripgrep 0.8.1 - 2023-08-21
Web interface for searching your code using ripgrep, built as a Datasette plugin
datasette-publish-fly 1.3.1 - 2023-08-21
Datasette plugin for publishing data using Fly
llm-openrouter 0.1 - 2023-08-21
LLM plugin for models hosted by OpenRouter
llm 0.8 - 2023-08-21
Access large language models from the command-line
sqlite-utils-fast-fks 0.1 - 2023-08-18
Fast foreign key addition for sqlite-utils
datasette-edit-schema 0.5.3 - 2023-08-18
Datasette plugin for modifying table schemas
sqlite-utils 3.35 - 2023-08-18
Python CLI utility and library for manipulating SQLite databases

TIL this week

Streaming output of an indented JSON array - 2023-08-30
Downloading partial YouTube videos with ffmpeg - 2023-08-26
Compile and run a new SQLite version with the existing sqlite3 Python library on macOS - 2023-08-22
Configuring Django SQL Dashboard for Fly PostgreSQL - 2023-08-22
Calculating the size of a SQLite database file using SQL - 2023-08-21
Updating stable docs in ReadTheDocs without pushing a release - 2023-08-21
A shell script for running Go one-liners - 2023-08-20
A one-liner to output details of the current Python's SQLite - 2023-08-19
A simple pattern for inlining binary content in a Python script - 2023-08-19
Running multiple servers in a single Bash script - 2023-08-17

Tags: plugins, projects, datasette, weeknotes, sqlite-utils, annotated-release-notes, alex-garcia, llm

Introducing datasette-write-ui: a Datasette plugin for editing, inserting, and deleting rows

2023-08-16T01:48:07+00:00

Introducing datasette-write-ui: a Datasette plugin for editing, inserting, and deleting rows

Alex García is working with me on Datasette Cloud for the next few months, graciously sponsored by Fly. We will be working in public, releasing open source code and documenting how to build a multi-tenant SaaS product using Fly Machines.

Alex’s first project is datasette-write-ui, a plugin that finally lets you directly edit data stored inside Datasette. Alex wrote about the plugin on our new Datasette Cloud blog.

Tags: plugins, datasette, datasette-cloud, fly, alex-garcia

sqlite-utils now supports plugins

2023-07-24T17:06:23+00:00

sqlite-utils 3.34 is out with a major new feature: support for plugins.

sqlite-utils is my combination Python library and command-line tool for manipulating SQLite databases. It recently celebrated its fifth birthday, and has had over 100 releases since it first launched back in 2018.

The new plugin system is inspired by similar mechanisms in Datasette and LLM. It lets developers add new features to sqlite-utils without needing to get their changes accepted by the core project.

I love plugin systems. As an open source maintainer they are by far the best way to encourage people to contribute to my projects - I can genuinely wake up in the morning and my software has new features, and I didn't even need to review a pull request.

Plugins also offer a fantastic medium for exploration and experimentation. I can try out new ideas without committing to supporting them in core, and without needing to tie improvements to them to the core release cycle.

Version 3.34 adds two initial plugin hooks: register_commands() and prepare_connection(). These are both based on the equivalent hooks in Datasette.

I planned to just ship register_commands(), but Alex Garcia spotted my activity on the repo and submitted a PR adding prepare_connection() literally minutes before I had intended to ship the release!

register_commands()

The register_commands() hook lets you add new commands to the sqlite-utils command-line tool - so users can run sqlite-utils your-new-command to access your feature.

I've learned from past experience that you should never ship a plugin hook without also releasing at least one plugin that uses it. I've built two so far for register_commands():

sqlite-utils-shell adds a simply interactive shell, accessed using sqlite-utils shell for an in-memory database or sqlite-utils shell data.db to run it against a specific database file.
sqlite-migrate is my first draft of a database migrations system for SQLite, loosely inspired by Django migrations and previewed by the migration mechanism I added to LLM.

Try out the shell plugin like this:

sqlite-utils install sqlite-utils-shell
sqlite-utils shell

The interface looks like this:

In-memory database, content will be lost on exit
Type 'exit' to exit.
sqlite-utils> select 3 + 5;
  3 + 5
-------
      8
sqlite-utils>

prepare_connection()

This hook, contributed by Alex, lets you modify the connection object before it is used to execute any SQL. Most importantly, this lets you register custom SQLite functions.

I expect this to be the most common category of plugin. I've built one so far: sqlite-utils-dateutil, which adds functions for parsing dates and times using the dateutil library.

It lets you do things like this:

sqlite-utils install sqlite-utils-dateutil
sqlite-utils memory "select dateutil_parse('3rd october')" -t

Output:

dateutil_parse('3rd october')
-------------------------------
2023-10-03T00:00:00

This works inside sqlite-shell too.

Plugins that you install also become available in the Python API interface to sqlite-utils:

>>> import sqlite_utils
>>> db = sqlite_utils.Database(memory=True)
>>> list(db.query("select dateutil_parse('3rd october')"))
[{"dateutil_parse('3rd october')": '2023-10-03T00:00:00'}]

You can opt out of executing installed plugins by passing execute_plugins=False to the Database() constructor:

>>> db = sqlite_utils.Database(memory=True, execute_plugins=False)
>>> list(db.query("select dateutil_parse('3rd october')"))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File ".../site-packages/sqlite_utils/db.py", line 494, in query
    cursor = self.execute(sql, params or tuple())
  File ".../site-packages/sqlite_utils/db.py", line 512, in execute
    return self.conn.execute(sql, parameters)
sqlite3.OperationalError: no such function: dateutil_parse

sqlite-ml by Romain Clement

I quietly released sqlite-utils 3.34 on Saturday. The community has already released several plugins for it!

Romain Clement built sqlite-utils-ml, a plugin wrapper for his sqlite-ml project.

This adds custom SQL functions for training machine learning models and running predictions, entirely within SQLite, using algorithms from scikit-learn.

Here's what that looks like running inside sqlite-utils shell:

sqlite-utils install sqlite-utils-shell sqlite-utils-ml
sqlite-utils shell ml.db

Attached to ml.db
Type 'exit' to exit.
sqlite-utils> select sqml_load_dataset('iris') as dataset;
dataset
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"table": "dataset_iris", "feature_names": ["sepal length (cm)", "sepal width (cm)", "petal length (cm)", "petal width (cm)"], "target_names": ["setosa", "versicolor", "virginica"], "size": 150}
sqlite-utils> select sqml_train(
         ...>   'Iris prediction',
         ...>   'classification',
         ...>   'logistic_regression',
         ...>   'dataset_iris',
         ...>   'target'
         ...> ) as training;
training
--------------------------------------------------------------------------------------------------------------------------------------------------------------
{"experiment_name": "Iris prediction", "prediction_type": "classification", "algorithm": "logistic_regression", "deployed": true, "score": 0.9736842105263158}
sqlite-utils> select
         ...>   dataset_iris.*,
         ...>   sqml_predict(
         ...>     'Iris prediction',
         ...>     json_object(
         ...>       'sepal length (cm)', [sepal length (cm)],
         ...>       'sepal width (cm)', [sepal width (cm)],
         ...>       'petal length (cm)', [petal length (cm)],
         ...>       'petal width (cm)', [petal width (cm)]
         ...>     )
         ...>   ) as prediction
         ...> from dataset_iris
         ...> limit 1;
  sepal length (cm)    sepal width (cm)    petal length (cm)    petal width (cm)    target    prediction
-------------------  ------------------  -------------------  ------------------  --------  ------------
                5.1                 3.5                  1.4                 0.2         0             0

SQLite extensions by Alex Garcia

Alex Garcia has a growing collection of SQLite extensions, many of which are written in Rust but are packaged as wheels for ease of installation using Python.

Alex released five plugins for SQLite corresponding to five of his existing extensions:

sqlite-utils-sqlite-regex
sqlite-utils-sqlite-path
sqlite-utils-sqlite-url
sqlite-utils-sqlite-ulid
sqlite-utils-sqlite-lines

Here's an example of sqlite-utils-sqlite-ulid in action:

sqlite-utils install sqlite-utils-sqlite-ulid
sqlite-utils memory 'select ulid() u1, ulid() u2, ulid() u3' | jq

Output:

[
  {
    "u1": "01h64d1ysg1rx63z1gwy7nah4n",
    "u2": "01h64d1ysgd7vx04sc9pncqh10",
    "u3": "01h64d1ysgz1sy7njkqt86dkq9"
  }
]

I've started a sqlite-utils plugin directory with a list of all of the plugins so far.

Building your own plugin

If you want to try building your own plugin, the documentation includes a simple step-by-step guide. A plugin can be built with as little as two files: a Python module implementing the hooks, and a pyproject.toml module with metadata about how it should be installed.

I've also released a new cookiecutter template: simonw/sqlite-utils-plugin. Here's how to use that to get started building a plugin:

cookiecutter gh:simonw/sqlite-utils-plugin

Answer the form fields like this:

plugin_name []: rot13
description []: select rot13('text') as a sqlite-utils plugin
hyphenated [rot13]: 
underscored [rot13]: 
github_username []: your-username
author_name []: your-name

Change directory into the new folder and use sqlite-utils install -e to install an editable version of your plugin, so changes you make will be reflected when you run the tool:

cd sqlite-utils-rot13
sqlite-utils install -e .

Run this command to confirm the plugin has been installed:

sqlite-utils plugins

You should see this:

[
  {
    "name": "sqlite-utils-rot13",
    "hooks": [
      "prepare_connection"
    ],
    "version": "0.1"
  }
]

Now drop this code into the sqlite_utils_rot13.py file:

import sqlite_utils


def rot13(s):
    chars = []
    for v in s:
        c = ord(v)
        if c >= ord("a") and c <= ord("z"):
            if c > ord("m"):
                c -= 13
            else:
                c += 13
        elif c >= ord("A") and c <= ord("Z"):
            if c > ord("M"):
                c -= 13
            else:
                c += 13
        chars.append(chr(c))

    return "".join(chars)


@sqlite_utils.hookimpl
def prepare_connection(conn):
    conn.create_function("rot13", 1, rot13)

And try it out like this:

sqlite-utils memory "select rot13('hello world')"

Output:

[{"rot13('hello world')": "uryyb jbeyq"}]

And to reverse that:

sqlite-utils memory "select rot13('uryyb jbeyq')"

Output:

[{"rot13('uryyb jbeyq')": "hello world"}]

As you can see, building plugins can be done with very little code. I'm excited to see what else people build with this new capability!

Tags: plugins, projects, sqlite, sqlite-utils, alex-garcia

sqlite-vss v0.1.1 Annotated Release Notes

2023-07-20T17:48:50+00:00

sqlite-vss v0.1.1 Annotated Release Notes

Alex Garcia’s sqlite-vss adds vector search directly to SQLite through a custom extension. It’s now easily installed for Python, Node.js, Deno, Elixir, Go, Rust and Ruby (“gem install sqlite-vss”), and is being used actively by enough people that Alex is getting actionable feedback, including fixes for memory leaks spotted in production.

Via @agarcia_me

Tags: sqlite, annotated-release-notes, alex-garcia, vector-search

Making SQLite extensions npm install'able for Node.js, and on deno.land/x for Deno

2023-03-29T22:13:21+00:00

Making SQLite extensions npm install'able for Node.js, and on deno.land/x for Deno

Alex Garcia figured out how to get his “pip install X” trick for distributing compiled SQLite extensions to work for Node too! Now you can “npm install” 10 of his extensions, including sqlite-regex and sqlite-xsv and sqlite-http and sqlite-html and more, and attach them to a node-sqlite3 or better-sqlite3 connection. He’s bundled them for Deno too!

Via Datasette Discord

Tags: nodejs, sqlite, npm, deno, alex-garcia

Introducing sqlite-vss: A SQLite Extension for Vector Search

2023-02-10T22:53:14+00:00

Introducing sqlite-vss: A SQLite Extension for Vector Search

This latest SQLite extension from Alex Garcia is possibly his best yet: it adds FAISS-powered vector similarity search directly to SQLite, enabling fast KNN similarity lookups against a virtual table that feels a lot like SQLite’s own built-in full text search feature. This write-up includes interactive demos using Datasette called from an Observable notebook, running similarity searches against an index of 200,000 news headlines and summaries in less than 50ms.

Via @simon on Mastodon

Tags: sqlite, datasette, observable, alex-garcia, vector-search

Making SQLite extensions pip install-able

2023-02-06T19:44:10+00:00

Making SQLite extensions pip install-able

Alex Garcia figured out how to bundle a compiled SQLite extension in a Python wheel (building different wheels for different platforms) and publish them to PyPI. This is a huge leap forward in terms of the usability of SQLite extensions, which have previously been pretty difficult to actually install and run. Alex also created Datasette plugins that depend on his packages, so you can now “datasette install datasette-sqlite-regex” (or datasette-sqlite-ulid, datasette-sqlite-fastrand, datasette-sqlite-jsonschema) to gain access to his custom SQLite extensions in your Datasette instance. It even works with “datasette publish --install” to deploy to Vercel, Fly.io and Cloud Run.

Via Datasette Discord

Tags: pip, plugins, python, sqlite, datasette, alex-garcia

sqlite-jsonschema

2023-01-28T03:50:46+00:00

sqlite-jsonschema

“A SQLite extension for validating JSON objects with JSON Schema”, building on the jsonschema Rust crate. SQLite and JSON are already a great combination—Alex suggests using this extension to implement check constraints to validate JSON columns before inserting into a table, or just to run queries finding existing data that doesn’t match a given schema.

Tags: json, jsonschema, sqlite, rust, alex-garcia

sqlite-ulid

2023-01-28T03:45:36+00:00

sqlite-ulid

Alex Garcia’s sqlite-ulid adds lightning-fast SQL functions for generating ULIDs—Universally Unique Lexicographically Sortable Identifiers. These work like UUIDs but are smaller and faster to generate, and can be canonically encoded as a URL-safe 26 character string (UUIDs are 36 characters). Again, this builds on a Rust crate—ulid-rs—and can generate 1 million byte-represented ULIDs with the ulid_bytes() function in just 88.4ms.

Tags: sqlite, uuid, rust, alex-garcia

sqlite-fastrand

2023-01-28T03:41:50+00:00

sqlite-fastrand

Alex Garcia just dropped three new SQLite extensions, and I’m going to link to all of them. The first is sqlite-fastrand, which adds new functions for generating random numbers (and alphanumeric characters too). Impressively, these out-perform the default SQLite random() and randomblob() functions by about 1.6-2.6x, thanks to being built on the Rust fastrand crate which builds on wyhash, an extremely fast (though not cryptographically secure) hashing function.

Tags: sqlite, rust, alex-garcia

Introducing sqlite-xsv: The Fastest CSV Parser for SQLite

2023-01-14T21:54:05+00:00

Introducing sqlite-xsv: The Fastest CSV Parser for SQLite

Alex Garcia continues to push the boundaries of SQLite extensions. This new extension in Rust wraps the lightning fast Rust csv crate and provides a new csv_reader() virtual table that can handle regular, gzipped and zstd compressed files.

Tags: csv, sqlite, rust, alex-garcia, zstd

Introducing sqlite-loadable-rs: A framework for building SQLite Extensions in Rust

2022-12-07T23:08:49+00:00

Introducing sqlite-loadable-rs: A framework for building SQLite Extensions in Rust

Alex Garcia has built a new Rust library for creating SQLite extensions—initially supporting custom scalar functions, virtual tables and table functions and with more types of extension coming soon. This looks very easy to use, partly because the documentation and examples are already delightfully thorough, especially for an initial release.

Tags: sqlite, rust, alex-garcia

Introducing sqlite-http: A SQLite extension for making HTTP requests

2022-08-10T22:22:42+00:00

Introducing sqlite-http: A SQLite extension for making HTTP requests

Characteristically thoughtful SQLite extension from Alex, following his sqlite-html extension from a few days ago. sqlite-http lets you make HTTP requests from SQLite—both as a SQL function that returns a string, and as a table-valued SQL function that lets you independently access the body, headers and even the timing data for the request.

This write-up is excellent: it provides interactive demos but also shows how additional SQLite extensions such as the new-to-me “define” extension can be combined with sqlite-http to create custom functions for parsing and processing HTML.

Via @agarcia_me

Tags: http, sqlite, alex-garcia

Introducing sqlite-html: query, parse, and generate HTML in SQLite

2022-08-03T17:31:16+00:00

Introducing sqlite-html: query, parse, and generate HTML in SQLite

Another brilliant SQLite extension module from Alex Garcia, this time written in Go. sqlite-html adds a whole family of functions to SQLite for parsing and constructing HTML strings, built on the Go goquery and cascadia libraries. Once again, Alex uses an Observable notebook to describe the new features, with embedded interactive examples that are backed by a Datasette instance running in Fly.

Via My TIL on Trying out SQLite extensions on macOS

Tags: go, html, sqlite, datasette, alex-garcia

Introducing sqlite-lines - a SQLite extension for reading files line-by-line

2022-07-30T19:18:53+00:00

Introducing sqlite-lines - a SQLite extension for reading files line-by-line

Alex Garcia wrote a brilliant C module for SQLIte which adds functions (and a table-valued function) for efficiently reading newline-delimited text into SQLite. When combined with SQLite’s built-in JSON features this means you can read a huge newline-delimited JSON file into SQLite in a streaming fashion so it doesn’t exhaust memory for a large file. Alex also compiled the extension to WebAssembly, and his post here is an Observable notebook post that lets you exercise the code directly.

Via @agarcia_me

Tags: json, sqlite, observable, webassembly, alex-garcia