# tutorials

path: /tutorials/explore
title: Exploring a database with Datasette
body:
  Tutorials > Exploring a database with Datasette

  Exploring a database with Datasette
  This tutorial will teach you how to use Datasette to explore a new database.
  Datasette offers flexible tools for exploring data tables. It's always worth spending time familiarizing yourself with data in its raw, tabula form before thinking about ways to apply more sophisticated analysis or visualization.
  The congress-legislators data
  We'll be using an example database of Members of the United States Congress, 1789 to present. I built this example using data from the unitedstates/congress-legislators project on GitHub, maintained by Joshua Tauberer, Eric Mill and over 100 other contributors.
  Access the example in Datasette here: https://datasette.io/legislators

  Understanding tables
  The key to understanding relational databases such as SQLite (which we are using here), MySQL, PostgreSQL, Oracle and so on is to learn to think in tables.
  Database tables are like spreadsheets - they have columns and rows, and each row provides a value for every column. Unlike spreadsheets you can't merge cells together or apply formatting - a database table is a straight-forward two-dimensional grid. It's just the data.
  Each column in a table has a type. In SQLite these types can be one of text, integer, real (for floating point values) and blob (for binary data). The type of the column matters because it affects what happens when you sort the table by that column, or which mathematical operations you can use against those values.  
  Browsing tables
  The tables in our example database are:

  legislators - over 12,000 rows - individuals who have served as senators or representatives
  legislator_terms - over 45,000 rows - terms served by legislators, back to 1789
  executives - 80 rows - individuals who have served as President or Vice President
  executive_terms - over 130 rows - terms served by executives
  offices - over 1,300 rows - district officers maintained by current legislators
  social_media - over 500 rows - social media accounts for current legislators

  Follow these links now and explore the tables to get a feel for the way the data is organized in this database.
  Tables can relate to each other
  Sometimes you'll see a row in a table that links to another table, for example the legislator_id column in the legislator_terms table:

  Clicking one of the linked names in that column will take you to a page for that individual legislator row that looks like this:

  The "Links from other tables" section shows how many rows in other tables refer back to this legislator.
  In a database these are called "foreign keys" - they work by storing the ID of a row from another table in a dedicated foreign key column.
  Foreign keys are key to understanding why relational databases are so much more powerful than separate, stand-alone data files stored in something like CSV.
  Using facets
  Facets are one of the most powerful Datasette features. They can help you take a table with thousands of rows and start quickly identifying interesting trends and patterns within that data.
  I'll show some examples using the executive_terms table.
  Facets can be applied in two ways: you can select an option from the "suggested facets" list, or you can select the "Facet by this" option from the cog menu next to each column.

  Once selected, the facet interface is shown above the table, like in this example:

  Each facet shows a list of the most common values for that column, with a total count number for each of those values.
  These numbers will update as you further filter the data: if you select "Democratic" you will see just the rows matching that party, and the "type" facet will update to show you that there have been 21 presidentals terms and 19 vice presidental terms for that party.

  Exercises

  Apply the type, state and party facets to the legislator_terms table
  Use them to filter just to Republican Senators from North Carolina (NC)
  Figure out which state has had the highest number of senatorial terms held by a member of the Republican party. (solution if you get stuck)

  Using filters
  When you select a facet, you're applying a filter to the data. These are reflected in the boxes at the top of the page:

  You don't have to use faceting for these - you can edit them directly.
  To see Democratic Vice Presidents just for the 1800s, we can add a filter for rows where the 'start' column begins with '18':

  Here's the result of that query.

  Exercises
  Using the legislator_terms table, find:

  Every senatorial term served in Delaware (DE)
  ... that started in a year starting with 18
  ... and then facet by party to see which parties had the most senatorial terms

  Sharing links
  Every page within Datasette is designed to be shared. Copy and paste the page URL to share it with someone else.
  This includes applied filters and facets - so if you find an interesting way to explore the data you can share that directly by sharing the URL.
  Some examples:

  Every senatorial term in the state of Texas
  Every executive term held by a member of the Democratic-Republican party

  Exercise: share some links!
  Use the above exercises to find something interesting in the data. Copy and paste that URL to your notes, or share it with a friend, or post about it on Twitter.
  Exporting data
  If you can see it in Datasette, you can export the raw data. This is a fundamental principle of the project.
  Look for the CSV and .json links on any page to export the data in those formats.

  The "Advanced export" box provides additional options. Use "download file" to download the CSV file to your computer. 
  The "expand labels" option will add an extra column with the label associated with any foreign key columns - in this example, it will add legislator_id_label to accompany the ID in legislator_id.
  Additional formats can be enabled using plugins. The example instance is running the datasette-copyable plugin, which provides a `"copyable" link which can be used to export the data in different formats using copy-and-paste!
  Datasette can also be configured to allow users to download the entire database as a single file. You can find that legislators.db download option at the bottom of https://datasette.io/legislators - the entire database is around 5.5MB.
  Exercises

  Download a CSV file of Democratic Vice Presidents in the 1800s, using the "download file" checkbox in the "Advanced export" box.
  Use the "copyable" option to copy and paste that data directly into Google Sheets, Excel or Numbers.

  Data visualization via plugins
  You may have noticed this already on the offices table, which shows an interactive map of all of the office locations:

  You can click on clusters of markers to zoom in, and click on individual markers to see the details of the row.
  It's fun to use these maps to spot obvious outliers - in this case, the office of representative Aumua Amata Radewagen in American Samoa instantly stands out.
  This is another example of a Datasette plugin - in this case datasette-cluster-map. Plugins can add additional features to Datasette, such as visualizations or alternative export methods (see "copyable" described above).
  For a larger example of a map visualization, take a look at this map of 33,000+ global power plants visualized using datasette-cluster-map.
  Next steps
  Now that you know how to explore tables using Datasette, the next covers how to use Datasette to learn SQL.


path: /tutorials/learn-sql
title: Learn SQL with Datasette
body:
  Tutorials > Learn SQL with Datasette

  Learn SQL with Datasette
  This tutorial follows the tutorial on exploring data with Datasette and shows how you can use Datasette to start learning to write custom SQL queries of your own.
  We will be using the same example database as that tutorial:

  https://datasette.io/legislators

  This database contains information about US Presidents, Vice Presidents and Members of Congress from 1789 to today. You can follow the previous tutorial to become familiar with the data.
  SQL and SQLite
  Datasette is software that runs on top of a SQL database. There are many different SQL database systems, such as MySQL, PostgreSQL, Microsoft SQL Server and Oracle. The database used by Datasette is called SQLite.
  You may not realize it, but you already use SQLite every day: it is built into many popular applications, including Google Chrome and Firefox, and runs on laptops, iPhones, Android phones and all sorts of other smaller devices.
  SQL stands for Structured Query Language - it is a text language for running queries against a database. Every database implements a slightly different dialect of SQL - for this tutorial I will try to keep to the subset of SQLite's dialect that is most likely to work across other databases as well.
  View and edit SQL
  Every table page in Datasette - such as this one - includes a "View and edit SQL" link that looks like this:

  Click that link to see the SQL query that was used for the current page - including any filters that have been applied, in this case the filter for bio_gender = 'F'.
  You can then edit that query to make it do something else!

  Exercises
  Start with that example, then:

  Click "Format SQL" to tidy this up and make it easier to read and edit
  Modify that query to return only the id and name columns
  Get it to order by name instead of sorting by id
  Change the p0 value to M to return male instead of female legislators
  Try replacing the list of columns with select * from - a shortcut for all columns

  The structure of a basic select query
  Here's the formatted version of the above query:
  select
    id,
    name,
    id_bioguide,
    id_govtrack,
    id_icpsr,
    id_wikipedia,
    id_wikidata,
    id_google_entity_id,
    name_first,
    name_last,
    bio_birthday,
    bio_gender,
    id_house_history,
    name_middle,
    name_nickname,
    id_ballotpedia,
    name_suffix,
    id_bioguide_previous,
    id_house_history_alternate,
    other_names,
    id_thomas,
    id_cspan,
    id_votesmart,
    id_lis,
    name_official_full,
    id_opensecrets,
    id_fec,
    id_maplight,
    leadership_roles,
    family
  from
    legislators
  where
    bio_gender = :p0
  order by
    id
  limit
    101

  select
  The select section specifies which columns you would like to return. Each column name is separated by a comma - but if you have a comma following the last column you you will get an error message.

  near  from: syntax error

  from
  The from section specifies which table the records should be selected from - here we want the legislators table.
  where
  The where section adds filter conditions. These can be combined using and, for example this query will select just legislators who are male and have a Jr. name suffix:
  where
    bio_gender = 'M'
    and name_suffix = 'Jr.'

  This section is optional - if you do not include a where clause you will get back every row in the table.
  order by
  The optional order by clause specifies the order you woud like the rows to be returned in. This would order them alphabetically by name:
  order by
    name

  Or add desc to reverse the order. This query returns the youngest legislators:
  select
    *
  from
    legislators
  order by
    bio_birthday desc

  limit
  The limit 101 clause limits the query to returning just the first 101 results. In most SQL databases omitting this will cause all results will be returned - but Datasette applies an additional limit of 1,000 (example here) to prevent large queries from causing performance issues.
  Named parameters
  In the previous tutorial we used filters to list presidental terms that occurred in the 1800s, by filtering on rows where the start column began with the string 18 and the type column equals prez.
  Here's that example as a filtered table.

  Clicking that "View and edit SQL" link (and then clicking "Format SQL") shows this query:

  select
    rowid,
    type,
    start,
    end,
    party,
    how,
    executive_id
  from
    executive_terms
  where
    start like :p0
    and type= :p1

  The :p0 and :p1 fields here correspond to "named parameters" - they provide a safe way to pass values to a SQL query, without having to worry about SQL injection.
  These field names are extracted from the query. If you change the where clause to look like this:
  where
    start like :century
    and type = :type

  Then fields called century and type will be displayed instead of p0 and p1.
  Since they are form fields, you can change those values too. Try changing prez to viceprez to see the vice presidential terms for the 1800s instead.
  If you weren't using named parameters, the where clause of the query could look like this instead:
  where
    start like '18%'
    and type = 'prez'

  String values like '18%' and 'prez' must be enclosed in single or double quotes (single quotes are preferred), if you are not using the :p0 syntax.
  The double quotes around the column names here are optional - they are only required if the column has a name that might clash with an existing SQL keyword, such as select or where - or if the column name contains a space. So the following where clause would work the same way:
  where
    start like '18%'
    and type = 'prez'

  SQL LIKE queries
  To find rows where the start column begins with the string 18 we use this where filter:
  where
    start like '18%'

  The SQL like operator applies wildcards to strings - % means "match anything" and _ (underscore) means "match a single character".
  Exercise

  Use a LIKE filter to find presidential terms that occurred in the 90s decade of any century - you'll need to use both _ and % for this. (solution)

  SQL Joins
  The executive_terms table has a column called executive_id which displays both a numeric ID and a link containing the name of the executive:

  But... when you click the "View and edit SQL" link the resulting query returns just the ID, not the name.

  A SQL join can be used to combine the data from multiple tables. Here's a query that uses a join to show the name pulled from the executives table:
  select
    executive_terms.type,
    executive_terms.start,
    executive_terms.end,
    executive_terms.party,
    executive_terms.how,
    executives.name
  from
    executive_terms
    join executives
      on executive_terms.executive_id = executives.id

  Try that here.

  Since there are now two tables involved in the query, it's a good idea to include explicit table names as part of the select clause:
  select
    executive_terms.type,
    executive_terms.start,
    executive_terms.end,
    executive_terms.party,
    executive_terms.how,
    executives.name

  If a column exists in only one of the two tables you can use its name without specifying the table, but this can quickly get confusing so it's better to always use the table names when you are executing a join.
  The from clause is where the join is defined. This describes how the two tables should be combined together:
  from
    executive_terms
    join executives
      on executive_terms.executive_id = executives.id

  We are joining executive_terms to executives, using the fact that the executive_id column in executive_terms contains values from the id column in executives.
  This is why IDs and foreign keys are important concepts to understand in SQL databases!
  This kind of join is also known as an "inner join" - it is the most commonly used join. Other join types include outer joins and full joins, but those are beyond the scope of this tutorial.
  GROUP BY / COUNT
  A common operation in SQL is to ask for a count of the most popular values in a column.
  Datasette exposes this capability in its faceting feature. Under the hood, that feature works by executing a group by / count query.
  The following query answers the question: which party has had the most presidental terms?
  select
    party,
    count(*)
  from
    executive_terms
  where
    type = 'prez'
  group by
    party

  Try that query here.

  The select clause asks for the party column and the result of running a count(*). count() is a SQL aggregate function, which operates on a group of results. So we need to define a group.
  The group by party clause at the end creates those groups. The combination of the two produces the desired result.
  You can add this order by clause at the end to see the groups sorted from highest to lowest:
  order by
    count(*) desc

  Here's the result.
  WHERE ... IN
  SQL queries can be nested together in a number of interesting ways.
  One of the most useful for ad-hoc data analysis is the where column in (select ...) pattern.
  Let's build a query to see everyone who has been president and has also been vice president.
  We'll start with a query to return the names of every vice president. This requires a join against executives, because the executive_terms table includes the presidential and vice presidential terms, but doesn't include the names of those individuals.
  select
    executives.name
  from
    executive_terms
    join executives
      on executive_terms.executive_id = executives.id
  where
    type = 'viceprez'

  This query returns 61 rows, including some duplicate rows for individuals who served more than one term as VP.
  We could use select distinct to remove those duplicates if we wanted to.
  If we want to see everyone who has been a president and ALSO been a vice-president, we can combine two queries together like this:
  select
    distinct executives.name
  from
    executive_terms
    join executives
      on executive_terms.executive_id = executives.id
  where
    type = 'prez'
    and name in (
      select
        executives.name
      from
        executive_terms
        join executives
          on executive_terms.executive_id = executives.id
      where
        type = 'viceprez'
    )

  This query returns 15 names, starting with John Adams and finishing with Joseph Biden.

  ⚠️ An earlier version of this example revealed a subtle bug: there were two separate individuals in the executives table with the same name, George Bush and George Bush! So even though the query appeared to return the correct results it actually contained a bug. This is a useful demonstration of how important it is to work with unique, de-duplicated identifiers where-ever possible, rather than assuming that things like names are unique.

  Common Table Expressions
  This is a more advanced SQL technique that I find myself using every day: it can make complex SQL queries much easier to write and understand.
  Common Table Expressions, or CTEs, allow you to define a temporary alias for a select that lasts for the duration of the current query. You can then treat it as if it was a regular table.
  Here's an example that creates two CTEs, one called presidents and one called vice_presidents, and then uses them to answer the earlier question about presidents who had also served as vice presidents:
  with presidents as (
    select
      executives.name
    from
      executive_terms
      join executives
        on executive_terms.executive_id = executives.id
    where
      executive_terms.type = 'prez'
  ),
  vice_presidents as (
    select
      executives.name
    from
      executive_terms
      join executives
        on executive_terms.executive_id = executives.id
    where
      executive_terms.type = 'viceprez'
  )
  select
    distinct name
  from
    presidents
  where name in vice_presidents

  Try this CTE query here.
  The two CTEs are defined using with alias_name as (select ...), second_alias as (select ...) - then the query that returns the final results is added on at the end.
  Creating bookmarkable apps
  Since every page on Datasette can be linked to (see Sharing links) and named parameters automatically add form input to the query page, you can combine these two features to create bookmarkable apps.
  This is particularly useful for collaborating with others who don't yet know SQL. You can write SQL queries that solve problems for them, add parameters that they can customize and then send them the entire custom application as a link that they can bookmark.
  Let's create an application that returns the president and vice president for any given date.
  Here's the SQL query we will be using:
  select
    executives.name,
    executive_terms.type,
    executive_terms.start,
    executive_terms.end,
    executive_terms.party
  from
    executive_terms
    join executives
      on executive_terms.executive_id = executives.id
  where
    start <= :date
    and end > :date

  This query takes a :date parameter, which should be of the format yyyy-mm-dd - for example 2016-01-01.
  It joins against the executives table to get their name, and then filters to rows where the term start is less than or equal to the specified date, and the end is greater than that date. We use <= for one of these to ensure there are no gaps or overlaps.
  If we execute the query without a date it will return 0 results, but give us a form field to enter the date:

  Entering a date - for example 2016-01-01 - returns the president and vice president for that date:

  We've built an application! We can send this link to anyone right now and they'll be able to run that same query.
  The SQL at the top of the page could feel a little intimidating though. That's what the "hide" link is for - clicking on that hides the SQL query, providing a new link that you can share that won't cause the user to scroll down past the SQL in order to interact with the query.
  One last note: the application we have built also doubles up as an API. Add .json to the path portion of the URL, or click the .json link, to get back the data as JSON. Add &_shape=array to the URL to get back a more compact form of JSON, ideal for integrating with other applications.
  Next steps
  Fully mastering SQL can take years. This tutorial has attempted to cover SQL basics and introduce some more advanced techniques to help manage larger queries, but there's plenty more to learn. The SQLite SELECT documentation offers a comprehensive reference to SQL as understood by SQLite, but the best way to learn is to keep on trying new things against different data, looking things up as you go along.


path: /tutorials/codespaces
title: Using Datasette in GitHub Codespaces
body:
  Tutorials > Using Datasette in GitHub Codespaces

  Using Datasette in GitHub Codespaces
  GitHub Codespaces is a free tool from GitHub that gives you a full Linux development environment running in the cloud, accessed entirely through your browser.
  It's a really neat way to get started with Datasette, in particular as a tool to build your own private Datasette instance and start using it to explore and analyze data.
  Please note that Codespaces is not designed to host public websites. Any Datasette instances you create within a Codespace will only be accessible as private instances for your personal use.
  This tutorial will show how to install and run Datasette in a Codespaces environment.
  Step 1: Create a Codespace
  Each Codespace needs to be attached to a GitHub repository.
  You can use any of your existing repositories, or create a brand new one.
  Or if you don't want to create your own repository you can use my simonw/codespaces-datasette repository instead.
  Use the "Code" button, select the "Codespaces" tab and then click "Create codespace on main" to start a new codespace.

  After a short delay while the codespace starts running you should see an interface that looks like this:

  We will be working exclusively in the Terminal panel at the bottom of the page.
  Step 2: Install Datasette
  Before we can run Datasette, we need to install it. We can do that using the following command:
  pipx install datasette

  This will install Datasette using pipx, which ensures it is kept in its own separate environment where it can't clash with any other Python code you may decide to run in your codespace.
  To confirm that Datasette has installed successfully, run this:
  datasette --version

  We also need to install a plugin:
  datasette install datasette-codespaces

  The datasette-codespaces plugin makes some small changes to Datasette to better run in the Codespaces environment. You can run Datasette without it but you may run into some frustrating issues, such as internal links not working correctly.
  Step 3: Start Datasette
  To start Datasette, run this command:
  datasette data.db --create

  This will start Datasette running against an empty SQLite database called data.db. If that database does not exist yet it will be created.
  You should see the following:

  Click that "Open in Browser" button to open the Datasette web interface in a new browser tab.
  If you can't see that button, check to see if the "Ports" tab is available in the Codespaces interface. You can use that to connect to your Datasette instance instead.
  Step 4: Import some data
  Your Datasette interface will start with an empty database. Let's add some data to it!
  We'll do that using the sqlite-utils command line tool.
  We can leave Datasette running in the background by starting a new terminal. Click on the word "python" at the top of the terminal window and select "Split terminal":

  You should now have two terminal windows next to each other.
  In the new terminal window, run this command to install sqlite-utils:
  pipx install sqlite-utils

  Then confirm installation with:
  sqlite-utils --version


  Let's fetch a CSV file from the web:
  wget https://static.simonwillison.net/static/2022/Manatee_Carcass_Recovery_Locations_in_Florida.csv
  And insert it into our database:
  sqlite-utils insert data.db locations \
      Manatee_Carcass_Recovery_Locations_in_Florida.csv \
      --csv -d
      This creates a new table called locations containing the data from that CSV file - manatee carcass recovery locations in Florida dating back to 1974.
  The --csv option tells sqlite-utils to treat the file as a CSV file, and the -d option tells it to infer the column types from the data (rather than treating every column as a text column).
  See the tutorial Cleaning data with sqlite-utils and Datasette for more about this example data.
  Step 5: Install some more plugins
  Datasette has over 100 available plugins, listed in the plugins directory.
  To install these, we first need to stop the Datasette server running. To do this first click back on the terminal window running Datasette and then hit Ctrl+C to stop the server.
  The datasette install ... command installs new plugins. Let's install a few of them:
  datasette install \
    datasette-vega \
    datasette-cluster-map \
    datasette-copyable \
    datasette-configure-fts \
    datasette-edit-schema \
    datasette-upload-csvs

  Here's what each of these plugins does:

  datasette-vega adds line, bar and scatter chart visualizations
  datasette-cluster-map adds a map visualization for any table with latitude and longitude columns
  datasette-copyable provides additional export options for copying and pasting data out of Datasette
  datasette-configure-fts helps you to configure full-text search for your tables
  datasette-edit-schema provides tools toedit the schema of your tables

  Start the server running again like this:
  datasette data.db
  We don't need the --create option any more as we know the database already exists - although it won't break anything if we do include it.
  Step 6: Explore those manatee locations on a map
  In Datasette, navigate to that "locations" table. It should have columns called X and Y which look like they might contain latitude and longitude data.
  The datasette-cluster-map plugin can render these all on a map... but only if the columns have the names latitude and longitude.
  We can use the datasette-edit-schema plugin to rename those columns.
  Click on the cog icon at the top of the table, then select "Edit table schema".

  Rename the X and Y columns to longitude and latitude respectively, then click "Apply changes".
  Return to the table page and you should see this map of Florida with manatee locations plotted on it:

  Next steps
  Try working through the rest of the Cleaning data with sqlite-utils and Datasette tutorial to learn more about how to use sqlite-utils and Datasette together.


path: /tutorials/clean-data
title: Cleaning data with sqlite-utils and Datasette
body:
  Tutorials > Cleaning data with sqlite-utils and Datasette

  Cleaning data with sqlite-utils and Datasette

  This tutorial will show you how to use the sqlite-utils command line tool import data into SQLite, clean it and then explore it with Datasette.

  The concepts in this tutorial are also demonstrated in this video of a talk I gave at the HYTRADBOI conference in April 2022.



  Start with some CSV data
  The Florida Fish and Wildlife Conservation Commission maintain a database of manatee carcass recovery locations, with details dating back to April 1974.
  You can request the latest copy as a CSV file from their website - or download this copy of the CSV: Manatee_Carcass_Recovery_Locations_in_Florida.csv generated on July 31st 2022.
  Install sqlite-utils
  Here are the installation instructions. If you are on a Mac with Homebrew installed you can run brew install sqlite-utils. Python users can run pip install sqlite-utils.
  Import the CSV into a SQLite database
  sqlite-utils is a command line tool, so you'll need to open up a terminal on your computer and change directory to the place where you saved the CSV file.
  You can create a new manatees.db SQLite database like this:
  sqlite-utils insert manatees.db locations \
    Manatee_Carcass_Recovery_Locations_in_Florida.csv --csv -d

  This sqlite-utils insert command inserts data into a database table, creating the table if necessary.

  manatees.db is the SQLite database file we are using - this will be created if it doesn't exist yet.
  locations is the name of the table we are creating
  Manatee_Carcass_Recovery_Locations_in_Florida.csv is the CSV file we are importing
  --csv tells the tool to treat the incoming data as CSV data
  The -d option tells the tool to automatically detect the types of data. Without this, every column will be treated as text. With this option columns that have numeric data in will be used to populate numeric columns.

  Having created the database, we can use the schema command to view its schema:
  sqlite-utils schema manatees.db

  The output of that command looks like this:
  CREATE TABLE "locations" (
     [X] FLOAT,
     [Y] FLOAT,
     [OBJECTID] INTEGER,
     [FIELDID] TEXT,
     [REPDATE] TEXT,
     [REPYEAR] INTEGER,
     [REPMONTH] INTEGER,
     [REPDAY] INTEGER,
     [SEX] TEXT,
     [TLENGTH] FLOAT,
     [STATE] TEXT,
     [COUNTY] TEXT,
     [LAT] FLOAT,
     [LONG_] FLOAT,
     [DCODE] INTEGER,
     [MORTALITY] TEXT,
     [created_user] TEXT,
     [created_date] TEXT,
     [last_edited_user] TEXT,
     [last_edited_date] TEXT
  );

  The locations table here has been created with columns that match the columns that were present in the CSV file. The columns have the appropriate types thanks to the -d option we passed to sqlite-utils insert.
  We can use the sqlite-utils tables command to see those tables:
  sqlite-utils tables manatees.db --counts

  Output is:
  [{"table": "locations", "count": 13568}]

  The --counts option adds a count of the rows in each table. That's over 13,000 deceased manatees!
  By default, the tables command outputs JSON. You can change this to be a table format using the --table or -t options:
  sqlite-utils tables manatees.db --counts -t
  table        count
  ---------  -------
  locations    13568

  Querying the data
  Let's run a SQL query against your data. The sqlite-utils command to do that looks like this:
  sqlite-utils manatees.db "select * from locations limit 2"

  This outputs JSON, showing the first two rows and every column (thanks to that select *):
  [{"X": -80.1176098026034, "Y": 26.1004059191504, "OBJECTID": 149433, "FIELDID": "M7401", "REPDATE": "1974/04/03 00:00:00+00", "REPYEAR": 1974, "REPMONTH": 4, "REPDAY": 3, "SEX": "F", "TLENGTH": 260.0, "STATE": "FL", "COUNTY": "Broward", "LAT": 26.100401, "LONG_": -80.117607, "DCODE": 9, "MORTALITY": "Undetermined: Other", "created_user": "FWC", "created_date": "2021/10/15 11:30:55+00", "last_edited_user": "FWC", "last_edited_date": "2021/10/15 11:30:55+00"},
   {"X": -80.1664354041335, "Y": 25.8337161801256, "OBJECTID": 149434, "FIELDID": "M7402", "REPDATE": "1974/06/27 00:00:00+00", "REPYEAR": 1974, "REPMONTH": 6, "REPDAY": 27, "SEX": "M", "TLENGTH": 290.0, "STATE": "FL", "COUNTY": "Miami-Dade", "LAT": 25.833711, "LONG_": -80.166433, "DCODE": 1, "MORTALITY": "Human Related: Watercraft Collision", "created_user": "FWC", "created_date": "2021/10/15 11:30:55+00", "last_edited_user": "FWC", "last_edited_date": "2021/10/15 11:30:55+00"}]

  Let's specify some columns, limit to 10 and add -t to output the results as a table.
  sqlite-utils manatees.db -t \
    "select REPDATE, MORTALITY, COUNTY from locations limit 10"

  This outputs:
  REPDATE                 MORTALITY                            COUNTY
  ----------------------  -----------------------------------  ------------
  1974/04/03 00:00:00+00  Undetermined: Other                  Broward
  1974/06/27 00:00:00+00  Human Related: Watercraft Collision  Miami-Dade
  1974/08/20 00:00:00+00  Human Related: Watercraft Collision  Miami-Dade
  1974/10/23 00:00:00+00  Human Related: Other                 Volusia
  1974/11/11 00:00:00+00  Human Related: Other                 Brevard
  1974/12/25 00:00:00+00  Human Related: Watercraft Collision  Citrus
  1974/12/27 00:00:00+00  Undetermined: Too Decomposed         Indian River
  1975/01/01 00:00:00+00  Verified: Not Necropsied             Brevard
  1975/01/19 00:00:00+00  Human Related: Watercraft Collision  Miami-Dade
  1975/01/31 00:00:00+00  Verified: Not Necropsied             Brevard

  Opening it in Datasette
  Datasette provides a browser-based interface for exploring a SQLite database. Install that, (brew install datasette works for Homebrew users) and run it against your database:
  datasette manatees.db

  Then navigate to http://localhost:8001/ to start exploring your data.
  Transforming the columns
  Exploring the data helps identify improvements we can make to the way it is structured.
  The LAT and LONG_ column look like they might be latitudes and longitudes. Renaming those columns to latitude and longitude will make that more obvious, and will also let us visualize the data using a Datasette plugin in a moment.
  The created_user, last_edited_user and STATE columns aren't interesting: they always store the same value.
  The X and Y columns are duplicates of the latitude and longitude.
  Finally, the FIELDID column looks to be a unique identifier for every row in the database. This would make a good primary key for our table.
  The sqlite-utils transform command can be used to apply transformations to a table - rename columns, dropping columns, assigning primary keys and more.
  The following command will make a bunch of changes to the table structure in one go:
  sqlite-utils transform manatees.db locations \
    --rename LAT latitude \
    --rename LONG_ longitude \
    --drop created_user \
    --drop last_edited_user \
    --drop X \
    --drop Y \
    --drop STATE \
    --drop OBJECTID \
    --pk FIELDID

  Now run sqlite-utils schema manatees.db to see the result of those changes:
  CREATE TABLE "locations" (
     [FIELDID] TEXT PRIMARY KEY,
     [REPDATE] TEXT,
     [REPYEAR] INTEGER,
     [REPMONTH] INTEGER,
     [REPDAY] INTEGER,
     [SEX] TEXT,
     [TLENGTH] FLOAT,
     [COUNTY] TEXT,
     [latitude] FLOAT,
     [longitude] FLOAT,
     [DCODE] INTEGER,
     [MORTALITY] TEXT,
     [created_date] TEXT,
     [last_edited_date] TEXT
  );

  Visualizing the locations on a map
  Datasette supports plugins, which enable extra features such as data visualizations.
  The datasette-cluster-map plugin runs against any table with a latitude and longitude column, displaying them as points on a map. This is why we renamed those LAT and LONG_ columns earlier!
  To install the plugin, quit the Datasette server (using Ctrl+C in the terminal window) and run the following:
  datasette install datasette-cluster-map

  Then start Datasette running again:
  datasette manatees.db

  If you visit http://localhost:8001/manatees/locations you will now see the first 1,000 rows as points on the map:

  Click the "load all" button to load and display all 13,000+ locations.
  Fixing the date columns
  The date columns currently look like this:
  2021/10/15 11:30:55+00

  This is using YYYY/MM/DD format (at least it's not that confusing - for Europeans - American MM/DD/YYYY thing). I prefer the ISO standard format YYYY-MM-DD - so let's convert it.
  The sqlite-utils transform command can be used to apply a transformation function to the values in one or more columns.
  Here's how to use that to parse miscellaneous date and time formats and turn them into that standard ISO format:
  sqlite-utils convert manatees.db locations \
    REPDATE created_date last_edited_date \
    'r.parsedatetime(value)'

  This runs against the REPDATE, created_date and last_edited_date columns. It will use the built-in r.parsedatetime() recipe (documented here) to convert those values and save them back to the same column.
  Run that command, then view the data in Datasette again. Those columns have now been converted!
  Extracting columns into a separate table
  There's one more improvement we will make to our data.
  Open the table in Datasette, then use either the suggested facets list or the cog menu at the top of the columns to facet by both DCODE and MORTALITY.

  The result counts shown for the two facets are the same. This suggests that these two columns represent the same data - a DCODE of 1 corresponds to a MORTALITY of "Human Related: Watercraft Collision", 2 means "Human Related: Flood Gate/Canal Lock" and so on.
  We can use the sqlite-utils extract command to extract these columns into a separate table.
  sqlite-utils extract manatees.db locations DCODE MORTALITY \
    --rename MORTALITY name \
    --table mortality

  Running this will create a new table called mortality and populate it with one row for each of the DCODE and MORTALITY pairs in the database. It removes those columns from the locations table and replaces them with a mortality_id foreign key column pointing to the new table.
  Here's the output of sqlite-utils schema manatees.db showing the impact of those changes on the schema:
  CREATE TABLE [mortality] (
    [id] INTEGER PRIMARY KEY,
    [DCODE] INTEGER,
    [name] TEXT
  );
  CREATE TABLE "locations" (
    [FIELDID] TEXT PRIMARY KEY,
    [REPDATE] TEXT,
    [REPYEAR] INTEGER,
    [REPMONTH] INTEGER,
    [REPDAY] INTEGER,
    [SEX] TEXT,
    [TLENGTH] FLOAT,
    [COUNTY] TEXT,
    [latitude] FLOAT,
    [longitude] FLOAT,
    [mortality_id] INTEGER,
    [created_date] TEXT,
    [last_edited_date] TEXT,
    FOREIGN KEY([mortality_id]) REFERENCES [mortality]([id])
  );
  CREATE UNIQUE INDEX [idx_mortality_DCODE_name]
     ON [mortality] ([DCODE], [name]);

  Further reading
  sqlite-utils offers a lot more tools for cleaning up data. Check out this documentation and these blog posts for more details.

  sqlite-utils command line tool documentation
  Executing advanced ALTER TABLE operations in SQLite
  Apply conversion functions to data in SQLite columns with the sqlite-utils CLI tool
  Refactoring databases with sqlite-utils extract


path: /tutorials/spatialite
title: Building a location to time zone API with SpatiaLite
body:
  Tutorials > Building a location to time zone API with SpatiaLite

  Building a location to time zone API with SpatiaLite
  The SpatiaLite extension for SQLite adds a large number of functions for geospatial analysis, which can be used with Datasette to build GIS (Geographic Information System) applications.
  This tutorial will show how SpatiaLite and Datasette can be combined to create a JSON API that can return the time zone for a specific latitude and longitude point on earth.
  What we are going to build
  You can try the API out here. Give it a latitude and longitude and it will return the corresponding time zone ID: https://timezones.datasette.io/timezones/by_point
  Some examples:


  Brighton, England is in Europe/London (in JSON)

  San Francisco, USA is in America/Los_Angeles (in JSON)

  Tokyo, Japan is Asia/Tokyo (in JSON)

  Setting up a development environment
  You will need two things in place for this tutorial:

  SpatiaLite installed on your system
  A Python installation that allows the sqlite3 module to load additional extensions

  Recommended: Use GitHub Codespaces
  GitHub Codespaces can provide you with a free development environment for this project accessible through your web browser, with all of the tools you need pre-installed.
  This is the easiest way to work through this tutorial.
  Visit this link to create a new Codespace with everything you will need for the rest of the tutorial.
  Using a Mac
  On a Mac you can install SpatiaLite and Datasette using Homebrew like so:
  brew install spatialite-tools datasette

  If you are on a Mac you may find that your installation of Python cannot load external SQLite modules. You can check by running the following:
  datasette --load-extension spatialite

  If you get an error message about enable_load_extension then consult this page for hints on how to fix the problem.
  Building the database
  To build this project we first need geographic shapes for all of the world's time zones.
  timezone-boundary-builder is a project by Evan Siroky which uses data from OpenStreetMap to create detailed time zone polygons, which he then releases in GeoJSON and Shapefile format. The result is made available under the Open Data Commons Open Database License (ODbL).
  Start by downloading the timezones-with-oceans.shapefile.zip file from the latest release:
  wget https://github.com/evansiroky/timezone-boundary-builder/releases/download/2022g/timezones-with-oceans.shapefile.zip
  (If you are using Codespaces you should run all of these commands in the "Terminal" tab of the Codespaces interface.)
  A Shapefile is a set of files that describe a collection of geographic features. There's no need to unzip this zip file - our tools can work directly with it.
  We can load the file into a new SpatiaLite database using the shapefile-to-sqlite command.
  If you are running this tutorial in Codespaces this has been installed already. Otherwise, you can install it like this:
  pip install shapefile-to-sqlite

  To load the Shapefile into a database, use the following:
  shapefile-to-sqlite timezones.db \
      timezones-with-oceans.shapefile.zip \
      --table timezones \
      --spatial-index

  This will create a new database file called timezones.db, and load the shapes from the Shapefile into a new table called timezones. It will also set up a spatial index on the timezones table, described later.
  The command will show a progress bar like this:
  zip://timezones-with-oceans.shapefile.zip
    [########----------------------------]   22%  00:00:38

  This make take a couple of minutes to complete.
  Browsing the database in Datasette
  We can now start Datasette against that file to browse the data:
  datasette timezones.db --load-extension spatialite

  You should see the following output:
  INFO:     Started server process [5385]
  INFO:     Waiting for application startup.
  INFO:     Application startup complete.
  INFO:     Uvicorn running on http://127.0.0.1:8001 (Press CTRL+C to quit)

  If you are using your own computer you can now visit http://127.0.0.1:8001 to see the Datasette interface.
  If you are running in Codespaces the tool should offer an "Open in Browser" button.
  You can now browse the database. There should be a timezones table with three columns: id, tzid and geometry
  There isn't much to see yet though! The geometry columns are just big binary blobs.
  Viewing the time zone shapes on a map
  To see the time zone geometries on a map, we can install a Datasette plugin.
  datasette-geojson-map by Chris Amico adds the ability to render GeoJSON and SpatiaLite geometries directly on a map.
  Hit Ctrl+C in the terminal to stop the Datasette server, then run the following:
  datasette install datasette-geojson-map

  Now start Datasette running again:
  datasette timezones.db \
    --load-extension spatialite \
    --setting default_page_size 10

  We're adding an extra setting here, to set the default page size to 10. This is because some of the time zone polygons are really large and the default page size of 100 can take a long time to render.
  Visit the timezones table again and you should see something like this:

  Finding the time zone for a point
  Now that we have a table full of time zone geometries we can construct a SQL query that can show us the time zone for any specific point on earth.
  We can do this using the SpatiaLite within() function, which takes two geometries and checks if one is contained within the other.
  First we're going to need a geometry that represents a specific latitude/longitude point.
  41.798, -87.696 is a point within the city of Chicago - see it here on Google Maps.
  We can use the SpatiaLite MakePoint(longitude, latitude) function (note that the latitude and longitude are reversed here) to create a geometry which can then be used with the within() function:
  select
    tzid
  from
    timezones
  where
    within(
      MakePoint(-87.696, 41.798),
      timezones.Geometry
    ) = 1
  within(geom1, geom2) returns 1 if the first geometry is contained within the second, and 0 if it is not.
  Sure enough, this query returns America/Chicago.
  Using parameters in the SQL query
  Datasette has a feature where SQL queries can contain named parameters :like_this which will be turned into form fields and used to feed new values to the queries.
  Try that with the following query:
  select
    tzid
  from
    timezones
  where
    within(
      MakePoint(:longitude, :latitude),
      timezones.Geometry
    ) = 1
  This looks like it should work... but if you try it with the previous coordinates you'll see that it returns no results.
  This is because MakePoint() needs to be given floating point values, but Datasette passes all parameters as strings.
  If you pass invalid types, MakePoint() returns null - and within(null, geometry) then returns -1 to specify an invalid result.
  We can fix this by casting the strings to floats, like this:
  select
    tzid
  from
    timezones
  where
    within(
      MakePoint(cast(:longitude as float), cast(:latitude as float)),
      timezones.Geometry
    ) = 1
  This query has the desired effect: given a latitude and longitude for any point on earth it will return the correct time zone.
  Speeding it up with an index
  There's one remaining catch with this query: it's relatively slow. In my testing I was seeing anything between 150ms and 750ms for the query to run, due to the need to compare the point with all 455 polygons in the database.
  We used the --spatial-index option when we first imported the data. Here's how to take advantage of that spatial index to speed things up:
  select tzid
  from
    timezones
  where
    within(
      MakePoint(cast(:longitude as float), cast(:latitude as float)),
      timezones.Geometry
    ) = 1
    and rowid in (
      select
        rowid
      from
        SpatialIndex
      where
        f_table_name = 'timezones'
        and search_frame = MakePoint(cast(:longitude as float), cast(:latitude as float))
    )
  The trick here is the extra and rowid in (...) subquery.
  SpatialIndex is a special table - it's a virtual table created by SpatiaLite.
  You can query that table by passing the name of another table that has a spatial index associated with it - in this case timezones. Then you pass in a geometry as search_frame and the table will return a list of rowid values representing any polygons with a rough bounding box that overlaps that of the geometry you passed in.
  Note that this is not an exact comparison: some of the row IDs you get back may not intersect exactly with the geometry.
  But it's a good enough approximation. You can combine these values with a more accurate within() check, which will then only have to run full calculations against a subset of the overall set of polygons.
  In my local testing this dropped the time taken for the query from 150ms to less than 8ms - a significant speedup!
  Adding country polygons
  Which time zones are relevant for a specific country?
  Our data at the moment can't tell us that. We can filter for just time zones that start with America/ but this will give us everything for both North and South America. Wouldn't it be neat if we could browse our time zones by country instead?
  We can try to answer that question by loading in polygons for every country in the world.
  datahub.io/core/geo-countries has the data we need for this, derived from Natural Earth and released under the PDDL license.
  We can download a GeoJSON file of countries like this:
  wget https://datahub.io/core/geo-countries/r/countries.geojson

  Now we can use geojson-to-sqlite to load it into a countries database table, again creating a spatial index:
  pip install geojson-to-sqlite

  geojson-to-sqlite timezones.db countries \
    countries.geojson --spatial-index 

  Datasette should pick up the new countries table, and datasette-geojson-map will now show rendered outlines of those countries.
  To find the time zones that intersect a specific country, we can use the following query:
  select
    tzid,
    geometry
  from
    timezones
  where intersects(
    timezones.geometry, (
      select geometry from countries where admin = 'United Kingdom'
    ) = 1
  )
  We are using a couple of additional tricks here.
  The intersects() function is a SpatiaLite function that checks if two geometries intersect each other in any way.
  We'e also using a subquery to access the geometry of the country that we are interested in:
  select geometry from countries where admin = 'United Kingdom'
  The admin column in countries has names of the countries - here we are pulling back the geometry for the United Kingdom.
  Here are the results of this query.
  Simplifying the polygons
  Try changing the country name to "United States of America" and you may run into a problem: the geometries for the time zones that intersect with the United States are so large that they may not be possible to render on the map!
  We can help solve that using another SpatiaLite function: simplify(). This function applies the Douglas–Peucker algorithm to simplify a polygon down to a smaller number of points, which makes it much easier to render.
  simplify() takes a geometry and a precision value. After some trial and error I found that a precision value of 0.05 worked well for these time zone polygons:
  select
    tzid,
    simplify(geometry, 0.05) as geometry
  from
    timezones
  where intersects(
    timezones.geometry, (
      select geometry from countries where admin = 'United States of America'
    )  = 1
  )
  It's necessary to use simplify(...) as geometry here because the datasette-geojson-map plugin will look for an output column called geometry to render on the map.
  Here's the results of this query for the United States.
  Speeding that up with an index
  We can use a spatial index in a similar way to the time zone query listed above:
  with country as (
    select
      geometry
    from
      countries
    where
      admin = :country
  )
  select
    timezones.tzid,
    simplify(timezones.geometry, 0.05) as geometry
  from
    timezones,
    country
  where
    timezones.id in (
      select
        SpatialIndex.rowid
      from
        SpatialIndex,
        country
      where
        f_table_name = 'timezones'
        and search_frame = country.geometry
    )
    and intersects(timezones.geometry, country.geometry) = 1
  This uses the same trick as before - a where timezones.id in (...) subselect that returns a list of likely rowid values from the SpatialIndex virtual table.
  If a table has an integer primary key - such as our timezones table here - SQLite will set the rowid to be the same value, which is why we can compare timezones.id with rowid in this query.
  We're using one more trick here. To avoid having to run that query to select the country geometry twice, we're instead bundling that into a CTE - a Common Table Expression - at the start of the query.
  The with country as (...) piece makes the result of that query available as a temporary table called country for the duration of the SQL query.
  Defining metadata with canned queries
  Now that we've figured out all of the queries needed to power our API, it's time to tie them together into a configuration that we can deploy to the internet.
  Datasette's metadata system can be used to provide extra information about the database, and it can also be used to configure canned queries - SQL queries with names, like the by_point example shown at the beginning of this tutorial.
  Here's a metadata.yml file which defines both the by_point query and a query for looking up time zones by country code.
  title: Time zones API
  description: |
    An API for looking up time zones by latitude/longitude
  about: simonw/timezones-api
  about_url: https://github.com/simonw/timezones-api
  license: ODbL
  license_url: http://opendatacommons.org/licenses/odbl/
  source: timezone-boundary-builder
  source_url: https://github.com/evansiroky/timezone-boundary-builder
  allow_sql: false
  databases:
    timezones:
      tables:
        countries:
          source: Natural Earth
          source_url: https://www.naturalearthdata.com/
          license: Open Data Commons Public Domain Dedication and License (PDDL) v1.0
          license_url: https://opendatacommons.org/licenses/pddl/1-0/
          about: geo-countries
          about_url: https://datahub.io/core/geo-countries
      queries:
        by_point:
          title: Find time zone by lat/lon
          sql: |
            select tzid
            from
              timezones
            where
              within(
                MakePoint(cast(:longitude as float), cast(:latitude as float)),
                timezones.Geometry
              ) = 1
              and rowid in (
                select
                  rowid
                from
                  SpatialIndex
                where
                  f_table_name = 'timezones'
                  and search_frame = MakePoint(cast(:longitude as float), cast(:latitude as float))
              )
        by_country:
          title: Find time zones that intersect a country
          sql: |
            with country as (
              select
                geometry
              from
                countries
              where
                admin = :country
            )
            select
              timezones.tzid,
              simplify(timezones.geometry, 0.05) as geometry
            from
              timezones,
              country
            where
              timezones.id in (
                select
                  SpatialIndex.rowid
                from
                  SpatialIndex,
                  country
                where
                  f_table_name = 'timezones'
                  and search_frame = country.geometry
              )
              and intersects(timezones.geometry, country.geometry) = 1
  In addition to providing canned queries called by_name and by_country, this file also includes metadata showing the source of the data we used for the database.
  It also sets one more important option:
  allow_sql: false
  This option prevents users from executing their own custom SQL queries against our published database. Only the canned queries we have defined will be available.
  We're using that option here because SpatiaLite has a large number of functions, some of which could crash the underlying Datasette instance.
  We can test our new metadata.yml file by starting Datasette like this:
  datasette timezones.db --load-extension spatialite -m metadata.yml

  Deploying the application to Fly
  The datasette publish command can be used to deploy Datasette instances to a variety of different hosting providers.
  Fly is an excellent choice for hosting this, since the API could attract a large amount of traffic.
  Google Cloud Run charges based on how much sustained traffic an instance gets, which could become expensive for this application.
  Fly charge a flat monthly rate for the instance, plus additional fees for bandwidth.
  Current Fly pricing can be found here. At the time of writing an instance with 256MB of RAM - enough to comfortably host this API - costs $1.94/month.
  You'll need to install the Fly CLI tool:
  curl -L https://fly.io/install.sh | sh

  Then run the following to authenticate with Fly:
  flyctl auth login

  You should also install the datasette-publish-fly plugin:
  pip install datasette-publish-fly

  With all of those pieces in place, you can deploy the application like this:
  datasette publish fly timezones.db \
    --app timezones-api \
    --setting default_page_size 10 \
    --install datasette-geojson-map \
    --metadata metadata.yml \
    --spatialite

  You need a unique --app name (I've already claimed timezones-api for this demo).
  The --spatialite flag ensures SpatiaLite is configured for the deployed application.
  The deploy command make take a few minutes to complete. Once it has finished you can visit https://your-app-name.fly.dev/ to see the finished application, live on the internet.
  Here's timezones.datasette.io, deployed using this exact command.
  Source code
  The source code for everything in this tutorial can be found in the simonw/timezones-api repository on GitHub.


path: /tutorials/data-analysis
title: Data analysis with SQLite and Python
body:
  Tutorials > Data analysis with SQLite and Python

  Data analysis with SQLite and Python
  A 2h45m video tutorial about SQLite, Python, sqlite-utils and Datasette presented at PyCon 2023.




      What you'll need

      python3 and pip

      Optional: GitHub Codespaces



      sqlite-utils

      Using the command-line tools to clean data



      Exploring data with Datasette

      Installing Datasette locally

      Try a database: legislators.db

      Install some plugins

      Learning SQL with Datasette



      Using sqlite-utils as a Python library, to import all the PEPs

      Enabling full-text search

      Publishing a database to Vercel

      Other publishing options



      Datasette Lite

      Loading SQLite, CSV and JSON data

      Installing plugins

      Further reading



      Advanced SQL

      Aggregations

      Subqueries

      CTEs



      JSON

      Window functions



      Baked Data

      Niche Museums and TILs

      Generating a newsletter with an Observable notebook



      More demos and further reading

      Fun demos

      SpatiaLite





  What you'll need
  python3 and pip
  For the first part of this tutorial, you'll need a Python 3 interpreter with the sqlite3 standard library module available.
  You can run this on your own computer, or use a browser-based environment.
  https://pyodide.org/en/stable/console.html will work for a purely browser-based (WebAssembly) environment.
  For the second part, you'll also need the ability to pip install Python packages.
  Python 3 on your own laptop (maybe in a fresh virtual environment) is a good option here. You could also use GitHub Codespaces or Google Colab or Jupyter Hub or your online notebook solution of choice.
  Optional: GitHub Codespaces
  I'll be working through the tutorial using GitHub Codespaces, using https://github.com/github/codespaces-jupyter

  sqlite-utils
  sqlite-utils provides "CLI tool and Python utility functions for manipulating SQLite databases".
  You can install it the same way as Datasette:
  pip install sqlite-utils


  Or with pipx:
  pipx install sqlite-utils


  Or with Homebrew:
  brew install sqlite-utils


  It works as both a CLI tool and a Python library.
  Using the command-line tools to clean data
  We'll follow this tutorial next: Cleaning data with sqlite-utils and Datasette
  Exploring data with Datasette
  Datasette is "an open source multi-tool for exploring and publishing data".
  Installing Datasette locally
  pip install datasette


  Or if you prefer pipx:
  pipx install datasette


  Or Homebrew (on macOS):
  brew install datasette


  More installations options.
  In Codespaces you should also install the datasette-codespaces plugin:
  datasette install datasette-codespaces


  Try a database: legislators.db
  wget https://datasette.io/legislators.db


  This is a database of US legislators, presidents and vice presidents.
  You can explore it online at https://datasette.io/legislators
  Open it in Datasette like this:
  datasette legislators.db


  We'll follow this tutorial to explore Datasette's features: Exploring a database with Datasette
  Install some plugins
  Datasette has over a hundred plugins: https://datasette.io/plugins
  You can pip install them, but it's better to use datasette install as that ensures they will go in the correct virtual environment, especially useful if you used pipx or Homebrew to install Datasette itself.
  datasette install datasette-cluster-map


  Now restart Datasette and visit the "offices" table to see the result.
  You can review what plugins are installed with:
  datasette plugins


  Or by visiting the /-/plugins page in Datasette.
  Plugins can be uninstalled with:
  datasette uninstall datasette-cluster-map


  Learning SQL with Datasette
  The "✎ View and edit SQL" link is a quick way to start learning basic SQL queries.
  We'll follow this tutorial next: Learn SQL with Datasette
  Using sqlite-utils as a Python library, to import all the PEPs
  Let's take our PEPs example from earlier and implement it again, but better, using sqlite-utils.
  I'll do this in a notebook.
  !git clone https://github.com/python/peps /tmp/peps


  We now have ALL of the PEPs in /tmp/peps.
  import pathlib

  files = list(pathlib.Path("/tmp/peps").glob("pep-*.txt"))


  And parse them with our function from earlier:
  def parse_pep(s):
      intro, body = s.split("\n\n", 1)
      pep = {}
      current_key = None
      current_value = None
      for line in intro.split("\n"):
          # If the line starts with whitespace, it's a continuation of the previous value
          if line.startswith(" ") or line.startswith("\t"):
              if current_key is not None:
                  current_value += " " + line.strip()
                  pep[current_key] = current_value.strip()
          else:
              # Split the line into key and value
              parts = line.split(": ", 1)
              if len(parts) == 2:
                  key, value = parts
                  # Update the current key and value
                  current_key = key
                  current_value = value
                  # Add the key-value pair to the pep dictionary
                  pep[current_key] = current_value.strip()
      pep["Body"] = body.strip()
      return pep


  peps = []
  for file in files:
      peps.append(parse_pep(file.read_text()))


  We now have a list of dictionaries. Let's load them into SQLite:
  %pip install sqlite-utils


  import sqlite_utils
  db = sqlite_utils.Database("/tmp/peps.db")
  db["peps"].insert_all(peps, pk="PEP", replace=True)


  I got this error:
  OperationalError: table peps has no column named PEP-Delegate


  To fix that, use alter=True to automatically add any missing columns:
  db["peps"].insert_all(peps, pk="PEP", alter=True, replace=True)
  print(db["peps"].count)
  # Outputs 429 


  Enabling full-text search
  SQLite has surprisingly good full-text search built in.
  sqlite-utils can help you enable it:
  db["peps"].enable_fts(["Title", "Body"])


  Datasette will detect this and add a search box to the top of the table page.
  To run searches in relevance order you'll need to execute a custom SQL query:
  select
    PEP,
    peps.Title,
    Version,
    Author,
    Status,
    Type,
    Created,
    peps.Body,
    peps_fts.rank
  from
    peps
  join
    peps_fts on peps.rowid = peps_fts.rowid
  where
    peps_fts match :search
  order by
    peps_fts.rank
  limit
    20


  Publishing a database to Vercel
  First, install both Vercel and the datasette-publish-vercel plugin.
  https://vercel.com/docs/cli has documentation for installing the Vercel CLI.
  On macOS:
  brew install vercel-cli


  Or use one of these:
  npm i -g vercel


  Or:
  pnpm i -g vercel


  Now run this command to login:
  vercel login


  Install the plugin:
  datasette install datasette-publish-vercel


  And deploy the database:
  datasette publish vercel /tmp/peps.db --project python-peps


  Other publishing options
  Datasette can publish to the following providers:

  Heroku (datasette publish heroku)
  Google Cloud Run (datasette publish cloudrun)
  Vercel (with datasette-publish-vercel)
  Fly (with datasette-publish-fly)

  Further deployment options are described in the documentation.
  Datasette Lite
  It's Datasette... running entirely in your web browser with WebAssembly and Pyodide!
  https://lite.datasette.io/
  Loading SQLite, CSV and JSON data

  SQLite: https://lite.datasette.io/?url=https://github.com/simonw/scrape-hmb-traffic/blob/main/hmb.db?&install=datasette-copyable#/hmb?sql=with+item1+as+(%0A++select%0A++++time(datetime(commits.commit_at%2C+'-7+hours'))+as+t%2C%0A++++duration_in_traffic+%2F+60+as+mins_in_traffic%0A++from%0A++++item_version%0A++++join+commits+on+item_version._commit+%3D+commits.id%0A++order+by%0A++++commits.commit_at%0A)%2C%0Aitem2+as+(%0A++select%0A++++time(datetime(commits.commit_at%2C+'-7+hours'))+as+t%2C%0A++++duration_in_traffic+%2F+60+as+mins_in_traffic%0A++from%0A++++item2_version%0A++++join+commits+on+item2_version._commit+%3D+commits.id%0A++order+by%0A++++commits.commit_at%0A)%0Aselect%0A++item1.*%2C%0A++item2.mins_in_traffic+as+mins_in_traffic_other_way%0Afrom%0A++item1%0A++join+item2+on+item1.t+%3D+item2.t - see Measuring traffic during the Half Moon Bay Pumpkin Festival
  CSV: https://lite.datasette.io/?csv=https://raw.githubusercontent.com/fivethirtyeight/data/master/fight-songs/fight-songs.csv
  JSON: https://lite.datasette.io/?json=https://gist.github.com/simonw/73d15c0dd1025d1196829740bacf4464

  Installing plugins
  Add ?install=name-of-plugin to pip install that plugin into your browser's environment!
  This only works with a subset of plugins.

  https://lite.datasette.io/?install=datasette-copyable&json=https://gist.github.com/simonw/73d15c0dd1025d1196829740bacf4464

  Further reading

  Datasette Lite: a server-side Python web application running in a browser
  Plugin support for Datasette Lite
  Joining CSV files in your browser using Datasette Lite

  Advanced SQL
  Aggregations
  The simplest form of aggregation is the one Datasette does to implement its own faceting feature.
  select
    party,
    count(*)
  from
    executive_terms
  where
    type = 'prez'
  group by
    party


  Try that query here.
  The group by creates groups of rows, then the aggregation functions calculate a value across that entire group.
  The most common aggregation functions are:

  count(*) - count the number of rows in each group
  max(column) - maximum value for a column
  min(column) - minimum value for a column
  sum(column) - sum up the values in the column

  Here's an example of sum() and count() from What's in the RedPajama-Data-1T LLM training set:
  select
    top_folders,
    sum(size_gb) as total_gb,
    count(*) as num_files
  from raw
  group by top_folders
  order by sum(size_gb) desc


  Run that in Datasette Lite.
  Change the total_gb line to this to round it to the nearest integer:
    cast (sum(size_gb) as integer) as total_gb,


  Subqueries
  SQLite has excellent support for subqueries. You can use them in where X in clauses:
  select html_url from releases where repo in (
    select id from repos where full_name in (
      select repo from plugin_repos
    )
  )
  order by created_at desc


  Run that on datasette.io. Sometimes I find these to be more readable than joins!
  You can also use them directly in select clauses:
  select
    full_name,
    (
      select
        html_url
      from
        releases
      where
        releases.repo = repos.id
      order by
        created_at desc
      limit
        1
    ) as latest_release
  from
    repos


  Run that here.
  CTEs
  CTE is a terrible name for an incredibly powerful feature. It stands for Common Table Expressions. Think of it as a way of creating an alias to a temporary table for the duration of a query.
  with presidents as (
    select
      executives.name
    from
      executive_terms
      join executives
        on executive_terms.executive_id = executives.id
    where
      executive_terms.type = 'prez'
  ),
  vice_presidents as (
    select
      executives.name
    from
      executive_terms
      join executives
        on executive_terms.executive_id = executives.id
    where
      executive_terms.type = 'viceprez'
  )
  select
    distinct name
  from
    presidents
  where name in vice_presidents


  Try this CTE query here.
  JSON
  SQLite has excellent JSON functionality built in. Store JSON in a text column and you can query it using json_extract() - you can also build JSON values in select queries.
  Returning related rows in a single SQL query using JSON shows some advanced tricks you can do with this.
  select
    legislators.id,
    legislators.name,
    json_group_array(json_object(
      'type', legislator_terms.type,
      'state', legislator_terms.state,
      'start', legislator_terms.start,
      'end', legislator_terms.end,
      'party', legislator_terms.party
     )) as terms,
     count(*) as num_terms
  from
    legislators join legislator_terms on legislator_terms.legislator_id = legislators.id
    group by legislators.id
  order by
    id
  limit
    10


  Run that query.
  Paul Ford said about SQLite's JSON support:

  The JSON interface is like, “we save the text and when you retrieve it we parse the JSON at several hundred MB/s and let you do path queries against it please stop overthinking it, this is filing cabinet.”

  Window functions
  I wanted to run a query that would return the following:

  The repository name
  The date of the most recent release from that repository (the releases table is a many-to-one against repos)
  The total number of releases
  The three most recent releases (as a JSON array of objects)

  with cte as (
    select
      repos.full_name,
      releases.created_at,
      releases.id as rel_id,
      releases.name as rel_name,
      releases.created_at as rel_created_at,
      rank() over (partition by repos.id order by releases.created_at desc) as rel_rank
    from repos
      left join releases on releases.repo = repos.id
  )
  select
    full_name,
    max(created_at) as max_created_at,
    count(rel_id) as releases_count,
    json_group_array(
      json_object(
        'id', rel_id,
        'name', rel_name,
        'created_at', rel_created_at
      )
    ) filter (where rel_id is not null and rel_rank <= 3) as recent_releases
  from cte
  group by full_name
  order by releases_count desc


  Run that query here.
  Running this smaller query helps show what's going on with that rel_rank column:
  with cte as (
    select
      repos.full_name,
      releases.created_at,
      releases.id as rel_id,
      releases.name as rel_name,
      releases.created_at as rel_created_at,
      rank() over (partition by repos.id order by releases.created_at desc) as rel_rank
    from repos
      left join releases on releases.repo = repos.id
  )
  select * from cte


  Baked Data
  The Baked Data architectural pattern describes this approach, which is key to taking full advantage of SQLite and Datasette.
  I like to build my databases in GitHub Actions.
  Niche Museums and TILs

  https://www.niche-museums.com/ is published from the https://github.com/simonw/museums repository - one big YAML file for the content.
  https://til.simonwillison.net/ is published https://github.com/simonw/til - separate Markdown files for each item.

  Both of these sites have Atom feeds that are defined using a Datasette canned query, in conjunction with the datasette-atom plugin.

  https://www.niche-museums.com/browse/feed
  https://til.simonwillison.net/tils/feed

  Generating a newsletter with an Observable notebook
  I wrote about this in Semi-automating a Substack newsletter with an Observable notebook:

  https://datasette.simonwillison.net/simonwillisonblog is a Datasette/SQLite copy of my Django blog, created using db-to-sqlite by my https://github.com/simonw/simonwillisonblog-backup GitHub repository.
  https://observablehq.com/@simonw/blog-to-newsletter is my Observable notebook that assembles a newsletter from that data.
  https://simonw.substack.com/ is the Substack newsletter that I copy that content into.

  More demos and further reading
  Fun demos
  I post a lot of Datasette projects on my blog. Some of my recent favourites:

  Exploring MusicCaps, the evaluation data released to accompany Google’s MusicLM text-to-music model shows how Datasette can be used to explore data used as part of training a machine learning model.
  Analyzing ScotRail audio announcements with Datasette—from prototype to production explains the scotrail.datasette.io project.
  I built a ChatGPT plugin to answer questions about data hosted in Datasette is one of my earlier explorations at the intersection of LLM AI and Datasette.

  SpatiaLite

  Building a location to time zone API with SpatiaLite shows how to use SpatiaLite and Datasette to create an API for looking up time zones based on latitude/longitude points.
  Drawing shapes on a map to query a SpatiaLite database introduces the datasette-leaflet-freedraw plugin and introduces the calands.datasettes.com demo.
  Joining CSV and JSON data with an in-memory SQLite database


# blog_posts

path: /blog/2026/api-extras/
year: 2026
slug: api-extras
source_path: blog-content/api-extras.md
title: Datasette 1.0a33 with JSON extras in the API
datetime_utc: 2026-06-11 17:26:45
author: Simon Willison
author_url: https://simonwillison.net
body:
  The stable release of Datasette 1.0 has been in the pipeline for a *long* time. Today's release of [1.0a33](https://docs.datasette.io/en/latest/changelog.html#a33-2026-06-11) incorporates significant progress on one of the key blockers for that release: providing a stable, fully documented JSON API for accessing data hosted in Datasette.

  The key problem with the 0.x API is its verbosity: Datasette usually returns more JSON than the client needs, often wasting SQL queries to generate data that is not used.

  The `?_extra=` mechanism, first introduced for tables [in Datasette 1.0a3](https://docs.datasette.io/en/latest/changelog.html#a3-2023-08-09), is designed to address that. 1.0a33 extends that pattern to rows and queries. Quoting the [1.0a33 release notes](https://docs.datasette.io/en/latest/changelog.html#a33-2026-06-11):

  > **`?_extra=` support for row and query pages**
  > 
  > Row and query JSON pages now support the same `?_extra=` mechanism as table pages. Row pages can request extras such as `foreign_key_tables`, `query`, `metadata` and `database_color`; arbitrary SQL and stored query pages can request extras such as `columns`, `query`, `metadata` and `private`. The implementation was refactored into a registry of extra classes shared by all three page types.
  >
  > New generated reference documentation describes every `?_extra=` parameter available on table, row and query JSON pages, with example output captured from a live Datasette instance at documentation build time. See [Expanding JSON responses](https://docs.datasette.io/en/latest/json_api.html#json-api-extra) for the full list.

  The minimal default response for a table page such as `/fixtures/facetable.json` in Datasette 1.0 [starts like this](https://latest.datasette.io/fixtures/facetable.json):

  ```json
  {
    "ok": true,
    "next": null,
    "rows": [
      {
        "pk": 1,
        "created": "2019-01-14 08:00:00",
        "planet_int": 1,
        "on_earth": 1,
        "state": "CA",
        "_city_id": 1,
        "_neighborhood": "Mission",
        "tags": "[\"tag1\", \"tag2\"]",
        "complex_array": "[{\"foo\": \"bar\"}]",
        "distinct_some_null": "one",
        "n": "n1"
      }
  ```

  We can add `?_extra=` parameters to request extras. Here's an example that requests several:

  [/fixtures/facetable.json?_size=1&_facet=state&_extra=count&_extra=count_sql&_extra=expandable_columns&_extra=facet_results](https://latest.datasette.io/fixtures/facetable.json?_size=1&_facet=state&_extra=count&_extra=count_sql&_extra=expandable_columns&_extra=facet_results)

  Which returns this JSON, with each requested extra corresponding to a key in the resulting object:

  ```json
  {
    "ok": true,
    "next": "1",
    "count": 15,
    "count_sql": "select count(*) from facetable ",
    "expandable_columns": [
      [
        {
          "column": "_city_id",
          "other_table": "facet_cities",
          "other_column": "id"
        },
        "name"
      ]
    ],
    "facet_results": {
      "results": {
        "state": {
          "name": "state",
          "type": "column",
          "hideable": true,
          "toggle_url": "/fixtures/facetable.json?_size=1&_extra=count&_extra=count_sql&_extra=expandable_columns&_extra=facet_results",
          "results": [
            {
              "value": "CA",
              "label": "CA",
              "count": 10,
              "toggle_url": "https://latest.datasette.io/fixtures/facetable.json?_size=1&_facet=state&_extra=count&_extra=count_sql&_extra=expandable_columns&_extra=facet_results&state=CA",
              "selected": false
            },
            {
              "value": "MI",
              "label": "MI",
              "count": 4,
              "toggle_url": "https://latest.datasette.io/fixtures/facetable.json?_size=1&_facet=state&_extra=count&_extra=count_sql&_extra=expandable_columns&_extra=facet_results&state=MI",
              "selected": false
            },
            {
              "value": "MC",
              "label": "MC",
              "count": 1,
              "toggle_url": "https://latest.datasette.io/fixtures/facetable.json?_size=1&_facet=state&_extra=count&_extra=count_sql&_extra=expandable_columns&_extra=facet_results&state=MC",
              "selected": false
            }
          ],
          "truncated": false
        }
      },
      "timed_out": [
      ]
    },
    "rows": [
      {
        "pk": 1,
        "created": "2019-01-14 08:00:00",
        "planet_int": 1,
        "on_earth": 1,
        "state": "CA",
        "_city_id": 1,
        "_neighborhood": "Mission",
        "tags": "[\"tag1\", \"tag2\"]",
        "complex_array": "[{\"foo\": \"bar\"}]",
        "distinct_some_null": "one",
        "n": "n1"
      }
    ],
    "truncated": false
  }
  ```

  You can explore the full set of available extras in the [Datasette extras API explorer tool](https://tools.simonwillison.net/datasette-extras-explorer):

  ![Screenshot of a web application titled "Datasette extras explorer". A URL input field contains https://latest.datasette.io/fixtures/facetable.json with a teal Explore button next to it. Below, a left panel labeled EXTRAS (30) lists checkboxes: all_columns - All columns in the table, regardless of _col/_nocol filtering; column_types - Column type assignments for this table; columns (checked) - Column names returned by this query; count - Total count of rows matching these filters; count_sql - SQL query used to calculate the total count; custom_table_templates - Custom template names considered for this table; database - Database name; database_color - Color assigned to the database. A right panel labeled RESPONSE shows GET /fixtures/fac… with Copy JSON and Copy URL buttons, then a dark JSON viewer showing 200 - 9.9 KB - 114ms and JSON: "ok": true, "next": null, "columns": (highlighted array) "pk", "created", "planet_int", "on_earth", "state", "_city_id", "_neighborhood", "tags", "complex_array", "distinct_some_null", "n", "rows": list of objects.](https://datasette.io/static/blog/2026/extras-explorer.png)

  Here's that same API explorer for the [query results page](https://tools.simonwillison.net/datasette-extras-explorer#url=https%3A%2F%2Flatest.datasette.io%2Ffixtures%2F-%2Fquery.json%3Fsql%3Dselect%2B*%2Bfrom%2Bfacetable%2Blimit%2B1), and the [individual row page](https://tools.simonwillison.net/datasette-extras-explorer#url=https%3A%2F%2Flatest.datasette.io%2Ffixtures%2Ffacetable%2F1.json).

html:
  <p>The stable release of Datasette 1.0 has been in the pipeline for a <em>long</em> time. Today's release of <a href="https://docs.datasette.io/en/latest/changelog.html#a33-2026-06-11">1.0a33</a> incorporates significant progress on one of the key blockers for that release: providing a stable, fully documented JSON API for accessing data hosted in Datasette.</p>
  <p>The key problem with the 0.x API is its verbosity: Datasette usually returns more JSON than the client needs, often wasting SQL queries to generate data that is not used.</p>
  <p>The <code>?_extra=</code> mechanism, first introduced for tables <a href="https://docs.datasette.io/en/latest/changelog.html#a3-2023-08-09">in Datasette 1.0a3</a>, is designed to address that. 1.0a33 extends that pattern to rows and queries. Quoting the <a href="https://docs.datasette.io/en/latest/changelog.html#a33-2026-06-11">1.0a33 release notes</a>:</p>
  <blockquote>
  <p><strong><code>?_extra=</code> support for row and query pages</strong></p>
  <p>Row and query JSON pages now support the same <code>?_extra=</code> mechanism as table pages. Row pages can request extras such as <code>foreign_key_tables</code>, <code>query</code>, <code>metadata</code> and <code>database_color</code>; arbitrary SQL and stored query pages can request extras such as <code>columns</code>, <code>query</code>, <code>metadata</code> and <code>private</code>. The implementation was refactored into a registry of extra classes shared by all three page types.</p>
  <p>New generated reference documentation describes every <code>?_extra=</code> parameter available on table, row and query JSON pages, with example output captured from a live Datasette instance at documentation build time. See <a href="https://docs.datasette.io/en/latest/json_api.html#json-api-extra">Expanding JSON responses</a> for the full list.</p>
  </blockquote>
  <p>The minimal default response for a table page such as <code>/fixtures/facetable.json</code> in Datasette 1.0 <a href="https://latest.datasette.io/fixtures/facetable.json">starts like this</a>:</p>
  <div class="codehilite"><pre><span></span><code><span class="p">{</span>
  <span class="w">  </span><span class="nt">&quot;ok&quot;</span><span class="p">:</span><span class="w"> </span><span class="kc">true</span><span class="p">,</span>
  <span class="w">  </span><span class="nt">&quot;next&quot;</span><span class="p">:</span><span class="w"> </span><span class="kc">null</span><span class="p">,</span>
  <span class="w">  </span><span class="nt">&quot;rows&quot;</span><span class="p">:</span><span class="w"> </span><span class="p">[</span>
  <span class="w">    </span><span class="p">{</span>
  <span class="w">      </span><span class="nt">&quot;pk&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;created&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;2019-01-14 08:00:00&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;planet_int&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;on_earth&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;state&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;CA&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;_city_id&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;_neighborhood&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;Mission&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;tags&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;[\&quot;tag1\&quot;, \&quot;tag2\&quot;]&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;complex_array&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;[{\&quot;foo\&quot;: \&quot;bar\&quot;}]&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;distinct_some_null&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;one&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;n&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;n1&quot;</span>
  <span class="w">    </span><span class="p">}</span>
  </code></pre></div>

  <p>We can add <code>?_extra=</code> parameters to request extras. Here's an example that requests several:</p>
  <p><a href="https://latest.datasette.io/fixtures/facetable.json?_size=1&amp;_facet=state&amp;_extra=count&amp;_extra=count_sql&amp;_extra=expandable_columns&amp;_extra=facet_results">/fixtures/facetable.json?_size=1&amp;_facet=state&amp;_extra=count&amp;_extra=count_sql&amp;_extra=expandable_columns&amp;_extra=facet_results</a></p>
  <p>Which returns this JSON, with each requested extra corresponding to a key in the resulting object:</p>
  <div class="codehilite"><pre><span></span><code><span class="p">{</span>
  <span class="w">  </span><span class="nt">&quot;ok&quot;</span><span class="p">:</span><span class="w"> </span><span class="kc">true</span><span class="p">,</span>
  <span class="w">  </span><span class="nt">&quot;next&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;1&quot;</span><span class="p">,</span>
  <span class="w">  </span><span class="nt">&quot;count&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">15</span><span class="p">,</span>
  <span class="w">  </span><span class="nt">&quot;count_sql&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;select count(*) from facetable &quot;</span><span class="p">,</span>
  <span class="w">  </span><span class="nt">&quot;expandable_columns&quot;</span><span class="p">:</span><span class="w"> </span><span class="p">[</span>
  <span class="w">    </span><span class="p">[</span>
  <span class="w">      </span><span class="p">{</span>
  <span class="w">        </span><span class="nt">&quot;column&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;_city_id&quot;</span><span class="p">,</span>
  <span class="w">        </span><span class="nt">&quot;other_table&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;facet_cities&quot;</span><span class="p">,</span>
  <span class="w">        </span><span class="nt">&quot;other_column&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;id&quot;</span>
  <span class="w">      </span><span class="p">},</span>
  <span class="w">      </span><span class="s2">&quot;name&quot;</span>
  <span class="w">    </span><span class="p">]</span>
  <span class="w">  </span><span class="p">],</span>
  <span class="w">  </span><span class="nt">&quot;facet_results&quot;</span><span class="p">:</span><span class="w"> </span><span class="p">{</span>
  <span class="w">    </span><span class="nt">&quot;results&quot;</span><span class="p">:</span><span class="w"> </span><span class="p">{</span>
  <span class="w">      </span><span class="nt">&quot;state&quot;</span><span class="p">:</span><span class="w"> </span><span class="p">{</span>
  <span class="w">        </span><span class="nt">&quot;name&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;state&quot;</span><span class="p">,</span>
  <span class="w">        </span><span class="nt">&quot;type&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;column&quot;</span><span class="p">,</span>
  <span class="w">        </span><span class="nt">&quot;hideable&quot;</span><span class="p">:</span><span class="w"> </span><span class="kc">true</span><span class="p">,</span>
  <span class="w">        </span><span class="nt">&quot;toggle_url&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;/fixtures/facetable.json?_size=1&amp;_extra=count&amp;_extra=count_sql&amp;_extra=expandable_columns&amp;_extra=facet_results&quot;</span><span class="p">,</span>
  <span class="w">        </span><span class="nt">&quot;results&quot;</span><span class="p">:</span><span class="w"> </span><span class="p">[</span>
  <span class="w">          </span><span class="p">{</span>
  <span class="w">            </span><span class="nt">&quot;value&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;CA&quot;</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;label&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;CA&quot;</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;count&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">10</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;toggle_url&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;https://latest.datasette.io/fixtures/facetable.json?_size=1&amp;_facet=state&amp;_extra=count&amp;_extra=count_sql&amp;_extra=expandable_columns&amp;_extra=facet_results&amp;state=CA&quot;</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;selected&quot;</span><span class="p">:</span><span class="w"> </span><span class="kc">false</span>
  <span class="w">          </span><span class="p">},</span>
  <span class="w">          </span><span class="p">{</span>
  <span class="w">            </span><span class="nt">&quot;value&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;MI&quot;</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;label&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;MI&quot;</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;count&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">4</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;toggle_url&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;https://latest.datasette.io/fixtures/facetable.json?_size=1&amp;_facet=state&amp;_extra=count&amp;_extra=count_sql&amp;_extra=expandable_columns&amp;_extra=facet_results&amp;state=MI&quot;</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;selected&quot;</span><span class="p">:</span><span class="w"> </span><span class="kc">false</span>
  <span class="w">          </span><span class="p">},</span>
  <span class="w">          </span><span class="p">{</span>
  <span class="w">            </span><span class="nt">&quot;value&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;MC&quot;</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;label&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;MC&quot;</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;count&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;toggle_url&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;https://latest.datasette.io/fixtures/facetable.json?_size=1&amp;_facet=state&amp;_extra=count&amp;_extra=count_sql&amp;_extra=expandable_columns&amp;_extra=facet_results&amp;state=MC&quot;</span><span class="p">,</span>
  <span class="w">            </span><span class="nt">&quot;selected&quot;</span><span class="p">:</span><span class="w"> </span><span class="kc">false</span>
  <span class="w">          </span><span class="p">}</span>
  <span class="w">        </span><span class="p">],</span>
  <span class="w">        </span><span class="nt">&quot;truncated&quot;</span><span class="p">:</span><span class="w"> </span><span class="kc">false</span>
  <span class="w">      </span><span class="p">}</span>
  <span class="w">    </span><span class="p">},</span>
  <span class="w">    </span><span class="nt">&quot;timed_out&quot;</span><span class="p">:</span><span class="w"> </span><span class="p">[</span>
  <span class="w">    </span><span class="p">]</span>
  <span class="w">  </span><span class="p">},</span>
  <span class="w">  </span><span class="nt">&quot;rows&quot;</span><span class="p">:</span><span class="w"> </span><span class="p">[</span>
  <span class="w">    </span><span class="p">{</span>
  <span class="w">      </span><span class="nt">&quot;pk&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;created&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;2019-01-14 08:00:00&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;planet_int&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;on_earth&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;state&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;CA&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;_city_id&quot;</span><span class="p">:</span><span class="w"> </span><span class="mi">1</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;_neighborhood&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;Mission&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;tags&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;[\&quot;tag1\&quot;, \&quot;tag2\&quot;]&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;complex_array&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;[{\&quot;foo\&quot;: \&quot;bar\&quot;}]&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;distinct_some_null&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;one&quot;</span><span class="p">,</span>
  <span class="w">      </span><span class="nt">&quot;n&quot;</span><span class="p">:</span><span class="w"> </span><span class="s2">&quot;n1&quot;</span>
  <span class="w">    </span><span class="p">}</span>
  <span class="w">  </span><span class="p">],</span>
  <span class="w">  </span><span class="nt">&quot;truncated&quot;</span><span class="p">:</span><span class="w"> </span><span class="kc">false</span>
  <span class="p">}</span>
  </code></pre></div>

  <p>You can explore the full set of available extras in the <a href="https://tools.simonwillison.net/datasette-extras-explorer">Datasette extras API explorer tool</a>:</p>
  <p><img alt="Screenshot of a web application titled &quot;Datasette extras explorer&quot;. A URL input field contains https://latest.datasette.io/fixtures/facetable.json with a teal Explore button next to it. Below, a left panel labeled EXTRAS (30) lists checkboxes: all_columns - All columns in the table, regardless of _col/_nocol filtering; column_types - Column type assignments for this table; columns (checked) - Column names returned by this query; count - Total count of rows matching these filters; count_sql - SQL query used to calculate the total count; custom_table_templates - Custom template names considered for this table; database - Database name; database_color - Color assigned to the database. A right panel labeled RESPONSE shows GET /fixtures/fac… with Copy JSON and Copy URL buttons, then a dark JSON viewer showing 200 - 9.9 KB - 114ms and JSON: &quot;ok&quot;: true, &quot;next&quot;: null, &quot;columns&quot;: (highlighted array) &quot;pk&quot;, &quot;created&quot;, &quot;planet_int&quot;, &quot;on_earth&quot;, &quot;state&quot;, &quot;_city_id&quot;, &quot;_neighborhood&quot;, &quot;tags&quot;, &quot;complex_array&quot;, &quot;distinct_some_null&quot;, &quot;n&quot;, &quot;rows&quot;: list of objects." src="https://datasette.io/static/blog/2026/extras-explorer.png" /></p>
  <p>Here's that same API explorer for the <a href="https://tools.simonwillison.net/datasette-extras-explorer#url=https%3A%2F%2Flatest.datasette.io%2Ffixtures%2F-%2Fquery.json%3Fsql%3Dselect%2B*%2Bfrom%2Bfacetable%2Blimit%2B1">query results page</a>, and the <a href="https://tools.simonwillison.net/datasette-extras-explorer#url=https%3A%2F%2Flatest.datasette.io%2Ffixtures%2Ffacetable%2F1.json">individual row page</a>.</p>

summary:
  The stable release of Datasette 1.0 has been in the pipeline for a long time. Today's release of 1.0a33 incorporates significant progress on one of the key blockers for that release: providing a stable, fully documented JSON API for accessing data hosted in Datasette.


path: /blog/2026/datasette-agent/
year: 2026
slug: datasette-agent
source_path: blog-content/datasette-agent.md
title: Datasette Agent, an extensible AI assistant for Datasette
datetime_utc: 2026-05-21 17:53:55
author: Simon Willison
author_url: https://simonwillison.net
body:
  Today we are releasing [Datasette Agent](https://agent.datasette.io/), an open source plugin for Datasette that provides an extensible AI assistant for interacting with your SQLite databases.

  Datasette Agent integrates [LLM](https://llm.datasette.io/) into Datasette, providing support for hundreds of different tool-calling models - from frontier vendors like OpenAI, Anthropic and Google Gemini, through to open weight models that you can run on your own hardware.

  The result is a conversational interface for answering questions about your data in SQLite, enhanced by additional plugins to add visualizations and other custom tools.

  ## Datasette Agent in action

  This [video demo](https://www.youtube.com/watch?v=AFZKp6hbFjI) shows how Datasette Agent works and what you can do with it.

  <lite-youtube videoid="AFZKp6hbFjI" playlabel="Play: Datasette Agent demo" style="background-image: url('https://i.ytimg.com/vi/AFZKp6hbFjI/maxresdefault.jpg');"></lite-youtube>

  You can try the demo yourself [on agent.datasette.io](https://agent.datasette.io/). This requires you to sign in with a GitHub account to help prevent abuse.

  The demo uses [Gemini 3.1 Flash-Lite](https://ai.google.dev/gemini-api/docs/models/gemini-3.1-flash-lite) via the [llm-gemini](https://github.com/simonw/llm-gemini) plugin. That model is fast and inexpensive, and our testing has shown it to be proficient at the SQLite queries needed by Datasette Agent.

  Datasette Agent includes an "export Markdown" option. Here's [the Markdown export](https://gist.github.com/simonw/a46d17b69659a4866adb1d868280091d) of the conversation I recorded for the demo.

  One of the questions I asked was this:

  > when did Simon most recently see a pelican?

  Which ran [this SQL query](https://datasette.simonwillison.net/simonwillisonblog?sql=SELECT+title%2C+commentary%2C+created+FROM+blog_beat+WHERE+beat_type+%3D+%27sighting%27+AND+%28title+LIKE+%27%25pelican%25%27+OR+commentary+LIKE+%27%25pelican%25%27%29+ORDER+BY+created+DESC+LIMIT+5):

  ```sql
  SELECT
    title, commentary, created
  FROM
    blog_beat
  WHERE
    beat_type = 'sighting'
    AND (title LIKE '%pelican%' OR commentary LIKE '%pelican%')
  ORDER BY
    created DESC
  LIMIT
    5
  ```

  And replied:

  > The most recent sighting of a pelican by Simon was recorded on **May 20, 2026**.
  >
  > The observation included a California Brown Pelican, along with a Common Loon, Canada Goose, Striped Shore Crab, and a California Sea Lion.

  Here's [that sighting on my blog](https://simonwillison.net/2026/May/20/sighting-363395265/).

  ## Running Datasette Agent locally

  If you want to try out Datasette Agent yourself, the easiest way to do so is using `uv`.

  Here's how to run it with your own OpenAI API key. First grab a demo database:
  ```bash
  wget https://datasette.io/legislators.db
  ```
  Then set your API key as an environment variable:
  ```bash
  export OPENAI_API_KEY="sk-..."
  ```
  And start Datasette running like this:
  ```bash
  uvx --prerelease=allow --with datasette-agent \
    datasette -s plugins.datasette-llm.default_model gpt-5.5 \
    --internal internal.db --root legislators.db
  ```
  This will provide you with a link to sign in as root on your local machine, and set `gpt-5.5` as the default model.

  If you have OpenAI Codex installed and configured, you can use [llm-openai-via-codex](https://github.com/simonw/llm-openai-via-codex) to avoid needing a separate API key and to bill your experiments to your existing ChatGPT subscription:

  ```bash
  uvx --prerelease=allow --with datasette-agent --with llm-openai-via-codex \
    datasette -s plugins.datasette-llm.default_model openai-codex/gpt-5.5 \
    --internal internal.db --root legislators.db
  ```
  Datasette Agent also works with local models. Here's how to run it with Qwen 3.5 9B served via a local installation of [LM Studio](https://lmstudio.ai/):
  ```bash
  uvx --prerelease=allow \
    --with datasette-agent --with llm-lmstudio \
    datasette --internal internal.db --root \
    legislators.db \
    -s plugins.datasette-llm.default_model lmstudio/qwen3.5-9b
  ```

  ## Plugins for your agent

  The Datasette ecosystem is built on plugins, and Datasette Agent continues that tradition. We are launching with three plugins:

  - [datasette-agent-charts](https://github.com/datasette/datasette-agent-charts), shown in the video, adds charts to Datasette Agent, powered by [Observable Plot](https://observablehq.com/plot/).
  - [datasette-agent-openai-imagegen](https://github.com/datasette/datasette-agent-openai-imagegen) adds an image generation tool to Datasette Agent using [ChatGPT Images 2.0](https://openai.com/index/introducing-chatgpt-images-2-0/).
  - [datasette-agent-sprites](https://github.com/datasette/datasette-agent-sprites) provides tools for executing code in a [Fly Sprites](https://sprites.dev/) persistent sandbox.

  Plugins are easy to build. The README includes [detailed documentation](https://github.com/datasette/datasette-agent/blob/main/README.md#registering-additional-tools-from-plugins), and we've found that coding agents like Codex and Claude Code can spin up new plugins really quickly.

  We have several exciting plugins in the pipeline - expect to hear more about these over the next few days and weeks.

  ## Come hack with us

  Today's alpha release is just the start. We're actively iterating on Datasette Agent and extra plugins for it right now - come and join us on Discord in the [#datasette-agent channel](https://discord.gg/hdxyusUFv) to learn more and share what you are planning to build.

html:
  <p>Today we are releasing <a href="https://agent.datasette.io/">Datasette Agent</a>, an open source plugin for Datasette that provides an extensible AI assistant for interacting with your SQLite databases.</p>
  <p>Datasette Agent integrates <a href="https://llm.datasette.io/">LLM</a> into Datasette, providing support for hundreds of different tool-calling models - from frontier vendors like OpenAI, Anthropic and Google Gemini, through to open weight models that you can run on your own hardware.</p>
  <p>The result is a conversational interface for answering questions about your data in SQLite, enhanced by additional plugins to add visualizations and other custom tools.</p>
  <h2>Datasette Agent in action</h2>
  <p>This <a href="https://www.youtube.com/watch?v=AFZKp6hbFjI">video demo</a> shows how Datasette Agent works and what you can do with it.</p>
  <p><lite-youtube videoid="AFZKp6hbFjI" playlabel="Play: Datasette Agent demo" style="background-image: url('https://i.ytimg.com/vi/AFZKp6hbFjI/maxresdefault.jpg');"></lite-youtube></p>
  <p>You can try the demo yourself <a href="https://agent.datasette.io/">on agent.datasette.io</a>. This requires you to sign in with a GitHub account to help prevent abuse.</p>
  <p>The demo uses <a href="https://ai.google.dev/gemini-api/docs/models/gemini-3.1-flash-lite">Gemini 3.1 Flash-Lite</a> via the <a href="https://github.com/simonw/llm-gemini">llm-gemini</a> plugin. That model is fast and inexpensive, and our testing has shown it to be proficient at the SQLite queries needed by Datasette Agent.</p>
  <p>Datasette Agent includes an "export Markdown" option. Here's <a href="https://gist.github.com/simonw/a46d17b69659a4866adb1d868280091d">the Markdown export</a> of the conversation I recorded for the demo.</p>
  <p>One of the questions I asked was this:</p>
  <blockquote>
  <p>when did Simon most recently see a pelican?</p>
  </blockquote>
  <p>Which ran <a href="https://datasette.simonwillison.net/simonwillisonblog?sql=SELECT+title%2C+commentary%2C+created+FROM+blog_beat+WHERE+beat_type+%3D+%27sighting%27+AND+%28title+LIKE+%27%25pelican%25%27+OR+commentary+LIKE+%27%25pelican%25%27%29+ORDER+BY+created+DESC+LIMIT+5">this SQL query</a>:</p>
  <div class="codehilite"><pre><span></span><code><span class="k">SELECT</span>
  <span class="w">  </span><span class="n">title</span><span class="p">,</span><span class="w"> </span><span class="n">commentary</span><span class="p">,</span><span class="w"> </span><span class="n">created</span>
  <span class="k">FROM</span>
  <span class="w">  </span><span class="n">blog_beat</span>
  <span class="k">WHERE</span>
  <span class="w">  </span><span class="n">beat_type</span><span class="w"> </span><span class="o">=</span><span class="w"> </span><span class="s1">&#39;sighting&#39;</span>
  <span class="w">  </span><span class="k">AND</span><span class="w"> </span><span class="p">(</span><span class="n">title</span><span class="w"> </span><span class="k">LIKE</span><span class="w"> </span><span class="s1">&#39;%pelican%&#39;</span><span class="w"> </span><span class="k">OR</span><span class="w"> </span><span class="n">commentary</span><span class="w"> </span><span class="k">LIKE</span><span class="w"> </span><span class="s1">&#39;%pelican%&#39;</span><span class="p">)</span>
  <span class="k">ORDER</span><span class="w"> </span><span class="k">BY</span>
  <span class="w">  </span><span class="n">created</span><span class="w"> </span><span class="k">DESC</span>
  <span class="k">LIMIT</span>
  <span class="w">  </span><span class="mi">5</span>
  </code></pre></div>

  <p>And replied:</p>
  <blockquote>
  <p>The most recent sighting of a pelican by Simon was recorded on <strong>May 20, 2026</strong>.</p>
  <p>The observation included a California Brown Pelican, along with a Common Loon, Canada Goose, Striped Shore Crab, and a California Sea Lion.</p>
  </blockquote>
  <p>Here's <a href="https://simonwillison.net/2026/May/20/sighting-363395265/">that sighting on my blog</a>.</p>
  <h2>Running Datasette Agent locally</h2>
  <p>If you want to try out Datasette Agent yourself, the easiest way to do so is using <code>uv</code>.</p>
  <p>Here's how to run it with your own OpenAI API key. First grab a demo database:</p>
  <div class="codehilite"><pre><span></span><code>wget<span class="w"> </span>https://datasette.io/legislators.db
  </code></pre></div>

  <p>Then set your API key as an environment variable:</p>
  <div class="codehilite"><pre><span></span><code><span class="nb">export</span><span class="w"> </span><span class="nv">OPENAI_API_KEY</span><span class="o">=</span><span class="s2">&quot;sk-...&quot;</span>
  </code></pre></div>

  <p>And start Datasette running like this:</p>
  <div class="codehilite"><pre><span></span><code>uvx<span class="w"> </span>--prerelease<span class="o">=</span>allow<span class="w"> </span>--with<span class="w"> </span>datasette-agent<span class="w"> </span><span class="se">\</span>
  <span class="w">  </span>datasette<span class="w"> </span>-s<span class="w"> </span>plugins.datasette-llm.default_model<span class="w"> </span>gpt-5.5<span class="w"> </span><span class="se">\</span>
  <span class="w">  </span>--internal<span class="w"> </span>internal.db<span class="w"> </span>--root<span class="w"> </span>legislators.db
  </code></pre></div>

  <p>This will provide you with a link to sign in as root on your local machine, and set <code>gpt-5.5</code> as the default model.</p>
  <p>If you have OpenAI Codex installed and configured, you can use <a href="https://github.com/simonw/llm-openai-via-codex">llm-openai-via-codex</a> to avoid needing a separate API key and to bill your experiments to your existing ChatGPT subscription:</p>
  <div class="codehilite"><pre><span></span><code>uvx<span class="w"> </span>--prerelease<span class="o">=</span>allow<span class="w"> </span>--with<span class="w"> </span>datasette-agent<span class="w"> </span>--with<span class="w"> </span>llm-openai-via-codex<span class="w"> </span><span class="se">\</span>
  <span class="w">  </span>datasette<span class="w"> </span>-s<span class="w"> </span>plugins.datasette-llm.default_model<span class="w"> </span>openai-codex/gpt-5.5<span class="w"> </span><span class="se">\</span>
  <span class="w">  </span>--internal<span class="w"> </span>internal.db<span class="w"> </span>--root<span class="w"> </span>legislators.db
  </code></pre></div>

  <p>Datasette Agent also works with local models. Here's how to run it with Qwen 3.5 9B served via a local installation of <a href="https://lmstudio.ai/">LM Studio</a>:</p>
  <div class="codehilite"><pre><span></span><code>uvx<span class="w"> </span>--prerelease<span class="o">=</span>allow<span class="w"> </span><span class="se">\</span>
  <span class="w">  </span>--with<span class="w"> </span>datasette-agent<span class="w"> </span>--with<span class="w"> </span>llm-lmstudio<span class="w"> </span><span class="se">\</span>
  <span class="w">  </span>datasette<span class="w"> </span>--internal<span class="w"> </span>internal.db<span class="w"> </span>--root<span class="w"> </span><span class="se">\</span>
  <span class="w">  </span>legislators.db<span class="w"> </span><span class="se">\</span>
  <span class="w">  </span>-s<span class="w"> </span>plugins.datasette-llm.default_model<span class="w"> </span>lmstudio/qwen3.5-9b
  </code></pre></div>

  <h2>Plugins for your agent</h2>
  <p>The Datasette ecosystem is built on plugins, and Datasette Agent continues that tradition. We are launching with three plugins:</p>
  <ul>
  <li><a href="https://github.com/datasette/datasette-agent-charts">datasette-agent-charts</a>, shown in the video, adds charts to Datasette Agent, powered by <a href="https://observablehq.com/plot/">Observable Plot</a>.</li>
  <li><a href="https://github.com/datasette/datasette-agent-openai-imagegen">datasette-agent-openai-imagegen</a> adds an image generation tool to Datasette Agent using <a href="https://openai.com/index/introducing-chatgpt-images-2-0/">ChatGPT Images 2.0</a>.</li>
  <li><a href="https://github.com/datasette/datasette-agent-sprites">datasette-agent-sprites</a> provides tools for executing code in a <a href="https://sprites.dev/">Fly Sprites</a> persistent sandbox.</li>
  </ul>
  <p>Plugins are easy to build. The README includes <a href="https://github.com/datasette/datasette-agent/blob/main/README.md#registering-additional-tools-from-plugins">detailed documentation</a>, and we've found that coding agents like Codex and Claude Code can spin up new plugins really quickly.</p>
  <p>We have several exciting plugins in the pipeline - expect to hear more about these over the next few days and weeks.</p>
  <h2>Come hack with us</h2>
  <p>Today's alpha release is just the start. We're actively iterating on Datasette Agent and extra plugins for it right now - come and join us on Discord in the <a href="https://discord.gg/hdxyusUFv">#datasette-agent channel</a> to learn more and share what you are planning to build.</p>

summary:
  Today we are releasing Datasette Agent, an open source plugin for Datasette that provides an extensible AI assistant for interacting with your SQLite databases.


path: /blog/2026/jump-menu/
year: 2026
slug: jump-menu
source_path: blog-content/jump-menu.md
title: The extensible "Jump to" menu in Datasette 1.0a30
datetime_utc: 2026-05-24 23:45:15
author: Simon Willison
author_url: https://simonwillison.net
body:
  Datasette 1.0a30, released today, provides a new "Jump menu" for quickly navigating your Datasette instance, plus mechanisms for plugins to extend and customize that menu to incorporate their own custom features.

  To activate the new menu, either select "Jump to..." from the main application menu, or hit the `/` key while focused on Datasette. This will bring up a modal panel where you can type to search - it looks like this:

  <center><img src="https://datasette.io/static/blog/2026/menu.gif" alt="Animated demo - the Jump to menu appears, and as the user types it filters to specific databases and tables and debug options" style="max-width: 100%"></center>

  You can try out that demo on [latest.datasette.io](https://latest.datasette.io/).

  Out of the box the search will cover all databases, tables, views, and canned queries that are available to your user. If you sign in as root or a user with the [debug_menu](https://docs.datasette.io/en/latest/authentication.html#debug-menu) permission you'll be able to access various debug features as well.

  The menu keeps track of the most recently selected items in your browser's `localStorage`, letting you quickly navigate to items that you use frequently.

  A version of the menu has been available since [1.0a20](https://docs.datasette.io/en/latest/changelog.html#a20-2025-11-03), but that version only covered tables and was available via just that hidden keyboard shortcut.

  The new menu is more discoverable, covers additional content types and can be extended by plugins.
  ## Adding items with plugins

  Datasette plugins can add their own managed content to the set of things that are searched by the Jump menu, using the new [jump_items_sql()](https://docs.datasette.io/en/latest/plugin_hooks.html#jump-items-sql-datasette-actor-request) plugin hook.

  The Jump menu works by running queries against the `/-/jump?q=...` JSON API. That API endpoint runs SQL queries to find matching items, initially against the catalog tables in Datasette's own [internal SQLite database](https://docs.datasette.io/en/latest/internals.html#datasette-s-internal-database).

  The new plugin hook allows plugins to add their own additional SQL queries, which will then be included in a big `UNION` query and filtered using the user's search term.

  Here's an example of what a plugin might look like - in this case an imaginary dashboards plugin that adds the ability for users to search and then navigate to dashboards that have been created using that plugin and belong to the current actor:

  ```python
  from datasette import hookimpl
  from datasette.jump import JumpSQL


  @hookimpl
  def jump_items_sql(datasette, actor, request):
      if not actor:
          return None
      return JumpSQL(
          sql="""
          SELECT
              'dashboard' AS type,
              slug AS label,
              description,
              json_object(
                  'method', 'path',
                  'path', '/-/dashboards/' || slug
              ) AS url,
              slug || ' ' || COALESCE(title, '') || ' ' || COALESCE(description, '') AS search_text,
              title AS display_name
          FROM dashboards
          WHERE owner_id = :actor_id
          """,
          params={"actor_id": actor["id"]},
          database="content",
      )
  ```

  The SQL query defined by plugins should always return the same set of columns: `type`, `label`, `description`, `url`, `search_text`, and `display_name`. The [plugin documentation](https://docs.datasette.io/en/latest/plugin_hooks.html#jump-items-sql-datasette-actor-request) describes these in detail, including the option to return JSON for the `url` in order to hook into Datasette's [URL generation routines](https://docs.datasette.io/en/latest/internals.html#datasette-urls).

  Plugins can also influence the initial display of the menu when it first opens, thanks to the new [makeJumpSections()](https://docs.datasette.io/en/latest/javascript_plugins.html#makejumpsections) JavaScript plugin hook. The [datasette-agent](https://agent.datasette.io/) plugin now uses that hook to add a form for kicking off a new agent session, which looks like this:

  <center><img src="https://datasette.io/static/blog/2026/menu-agent.gif" alt="Animated demo - this time the demo starts on agent.datasette.io and when the menu opens it has a new Start chat box below the search box - entering 'count entries' and hitting the button causes it to start an agent conversation that counts the number of entries and returns 3300." style="max-width: 100%"></center>

html:
  <p>Datasette 1.0a30, released today, provides a new "Jump menu" for quickly navigating your Datasette instance, plus mechanisms for plugins to extend and customize that menu to incorporate their own custom features.</p>
  <p>To activate the new menu, either select "Jump to..." from the main application menu, or hit the <code>/</code> key while focused on Datasette. This will bring up a modal panel where you can type to search - it looks like this:</p>
  <center><img src="https://datasette.io/static/blog/2026/menu.gif" alt="Animated demo - the Jump to menu appears, and as the user types it filters to specific databases and tables and debug options" style="max-width: 100%"></center>

  <p>You can try out that demo on <a href="https://latest.datasette.io/">latest.datasette.io</a>.</p>
  <p>Out of the box the search will cover all databases, tables, views, and canned queries that are available to your user. If you sign in as root or a user with the <a href="https://docs.datasette.io/en/latest/authentication.html#debug-menu">debug_menu</a> permission you'll be able to access various debug features as well.</p>
  <p>The menu keeps track of the most recently selected items in your browser's <code>localStorage</code>, letting you quickly navigate to items that you use frequently.</p>
  <p>A version of the menu has been available since <a href="https://docs.datasette.io/en/latest/changelog.html#a20-2025-11-03">1.0a20</a>, but that version only covered tables and was available via just that hidden keyboard shortcut.</p>
  <p>The new menu is more discoverable, covers additional content types and can be extended by plugins.</p>
  <h2>Adding items with plugins</h2>
  <p>Datasette plugins can add their own managed content to the set of things that are searched by the Jump menu, using the new <a href="https://docs.datasette.io/en/latest/plugin_hooks.html#jump-items-sql-datasette-actor-request">jump_items_sql()</a> plugin hook.</p>
  <p>The Jump menu works by running queries against the <code>/-/jump?q=...</code> JSON API. That API endpoint runs SQL queries to find matching items, initially against the catalog tables in Datasette's own <a href="https://docs.datasette.io/en/latest/internals.html#datasette-s-internal-database">internal SQLite database</a>.</p>
  <p>The new plugin hook allows plugins to add their own additional SQL queries, which will then be included in a big <code>UNION</code> query and filtered using the user's search term.</p>
  <p>Here's an example of what a plugin might look like - in this case an imaginary dashboards plugin that adds the ability for users to search and then navigate to dashboards that have been created using that plugin and belong to the current actor:</p>
  <div class="codehilite"><pre><span></span><code><span class="kn">from</span><span class="w"> </span><span class="nn">datasette</span><span class="w"> </span><span class="kn">import</span> <span class="n">hookimpl</span>
  <span class="kn">from</span><span class="w"> </span><span class="nn">datasette.jump</span><span class="w"> </span><span class="kn">import</span> <span class="n">JumpSQL</span>


  <span class="nd">@hookimpl</span>
  <span class="k">def</span><span class="w"> </span><span class="nf">jump_items_sql</span><span class="p">(</span><span class="n">datasette</span><span class="p">,</span> <span class="n">actor</span><span class="p">,</span> <span class="n">request</span><span class="p">):</span>
      <span class="k">if</span> <span class="ow">not</span> <span class="n">actor</span><span class="p">:</span>
          <span class="k">return</span> <span class="kc">None</span>
      <span class="k">return</span> <span class="n">JumpSQL</span><span class="p">(</span>
          <span class="n">sql</span><span class="o">=</span><span class="s2">&quot;&quot;&quot;</span>
  <span class="s2">        SELECT</span>
  <span class="s2">            &#39;dashboard&#39; AS type,</span>
  <span class="s2">            slug AS label,</span>
  <span class="s2">            description,</span>
  <span class="s2">            json_object(</span>
  <span class="s2">                &#39;method&#39;, &#39;path&#39;,</span>
  <span class="s2">                &#39;path&#39;, &#39;/-/dashboards/&#39; || slug</span>
  <span class="s2">            ) AS url,</span>
  <span class="s2">            slug || &#39; &#39; || COALESCE(title, &#39;&#39;) || &#39; &#39; || COALESCE(description, &#39;&#39;) AS search_text,</span>
  <span class="s2">            title AS display_name</span>
  <span class="s2">        FROM dashboards</span>
  <span class="s2">        WHERE owner_id = :actor_id</span>
  <span class="s2">        &quot;&quot;&quot;</span><span class="p">,</span>
          <span class="n">params</span><span class="o">=</span><span class="p">{</span><span class="s2">&quot;actor_id&quot;</span><span class="p">:</span> <span class="n">actor</span><span class="p">[</span><span class="s2">&quot;id&quot;</span><span class="p">]},</span>
          <span class="n">database</span><span class="o">=</span><span class="s2">&quot;content&quot;</span><span class="p">,</span>
      <span class="p">)</span>
  </code></pre></div>

  <p>The SQL query defined by plugins should always return the same set of columns: <code>type</code>, <code>label</code>, <code>description</code>, <code>url</code>, <code>search_text</code>, and <code>display_name</code>. The <a href="https://docs.datasette.io/en/latest/plugin_hooks.html#jump-items-sql-datasette-actor-request">plugin documentation</a> describes these in detail, including the option to return JSON for the <code>url</code> in order to hook into Datasette's <a href="https://docs.datasette.io/en/latest/internals.html#datasette-urls">URL generation routines</a>.</p>
  <p>Plugins can also influence the initial display of the menu when it first opens, thanks to the new <a href="https://docs.datasette.io/en/latest/javascript_plugins.html#makejumpsections">makeJumpSections()</a> JavaScript plugin hook. The <a href="https://agent.datasette.io/">datasette-agent</a> plugin now uses that hook to add a form for kicking off a new agent session, which looks like this:</p>
  <center><img src="https://datasette.io/static/blog/2026/menu-agent.gif" alt="Animated demo - this time the demo starts on agent.datasette.io and when the menu opens it has a new Start chat box below the search box - entering 'count entries' and hitting the button causes it to start an agent conversation that counts the number of entries and returns 3300." style="max-width: 100%"></center>

summary:
  Datasette 1.0a30, released today, provides a new "Jump menu" for quickly navigating your Datasette instance, plus mechanisms for plugins to extend and customize that menu to incorporate their own custom features.


path: /blog/2026/new-blog/
year: 2026
slug: new-blog
source_path: blog-content/new-blog.md
title: Welcome to the Datasette blog
datetime_utc: 2026-05-13 20:58:56
summary: It's about time the Datasette project had its own blog.
author: Simon Willison
author_url: https://simonwillison.net
body:
  It's about time the Datasette project had its own blog.

  In addition to announcements about Datasette itself, we also plan to include posts from community members highlighting their own projects that fit into the wider Datasette ecosystem.

  You can [subscribe to the Atom feed](https://datasette.io/content/feed.atom), follow Datasette on [Bluesky](https://bsky.app/profile/datasette.io) or [Mastodon](https://fosstodon.org/@datasette) or [Twitter](https://x.com/datasetteproj), or [subscribe via email](https://datasette.substack.com) to stay updated on what's going on.

html:
  <p>It's about time the Datasette project had its own blog.</p>
  <p>In addition to announcements about Datasette itself, we also plan to include posts from community members highlighting their own projects that fit into the wider Datasette ecosystem.</p>
  <p>You can <a href="https://datasette.io/content/feed.atom">subscribe to the Atom feed</a>, follow Datasette on <a href="https://bsky.app/profile/datasette.io">Bluesky</a> or <a href="https://fosstodon.org/@datasette">Mastodon</a> or <a href="https://x.com/datasetteproj">Twitter</a>, or <a href="https://datasette.substack.com">subscribe via email</a> to stay updated on what's going on.</p>


path: /blog/2026/sql-write-queries/
year: 2026
slug: sql-write-queries
source_path: blog-content/sql-write-queries.md
title: SQL write queries and stored queries in Datasette 1.0a31
datetime_utc: 2026-05-29 05:14:42
author: Simon Willison
author_url: https://simonwillison.net
body:
  With [Datasette 1.0a31](https://docs.datasette.io/en/latest/changelog.html#a31-2026-05-28), Datasette now offers users with the necessary permissions the ability to both **execute write queries** against their database and **save stored queries** (renamed from "canned queries"), either privately and shared with other members of their Datasette instance.

  Here's an animated demo of the new SQL write interface:

  ![The user starts on the data database page, selects actions and "Execute write SQL", then selects the insert document template on the next page and executes it with a title of "My document!". Also demonstrates that a create table statement cannot be executed because the user does not have create-table permission.](https://datasette.io/static/blog/2026/sql-write-ui.gif)

  Users with the new `execute-write-sql` permission gain an "Execute write SQL" database action. This takes them to a page where they can either enter a SQL query to execute or pick from a set of templates, which provide insert, update and delete queries for tables that they have permission to modify - based on their `insert-row`, `update-row` and `delete-row` permissions.

  Datasette analyzes the SQL query and checks which tables it will modify and which relevant Datasette permissions the user needs to have. This works for `create-table` and `alter-table` as well - the full list of permissions can be found [in the documentation](https://docs.datasette.io/en/latest/authentication.html#built-in-actions).

  In addition to executing queries directly, Datasette users with the new `store-query` permission can now store both read- and write-queries in the new `queries` table in Datasette's [internal SQLite database](https://docs.datasette.io/en/latest/internals.html#datasette-s-internal-database). You'll need to run Datasette with the `--internal path/to/internal.db` option in order for stored queries to persist across server restarts.

  Stored queries can be marked private, in which case they'll only be visible to the user who created them, or they can be shared with other users of the same Datasette instance via Datasette's existing `view-query` permission mechanism.

  Learn more:

  - The [Datasette 1.0a31 release notes](https://docs.datasette.io/en/latest/changelog.html#a31-2026-05-28)
  - Documentation for the [read query, write query and stored query](https://docs.datasette.io/en/latest/pages.html#pages-custom-sql-queries) pages
  - Details of the new [Executing write SQL JSON API](https://docs.datasette.io/en/latest/json_api.html#executewriteview)

html:
  <p>With <a href="https://docs.datasette.io/en/latest/changelog.html#a31-2026-05-28">Datasette 1.0a31</a>, Datasette now offers users with the necessary permissions the ability to both <strong>execute write queries</strong> against their database and <strong>save stored queries</strong> (renamed from "canned queries"), either privately and shared with other members of their Datasette instance.</p>
  <p>Here's an animated demo of the new SQL write interface:</p>
  <p><img alt="The user starts on the data database page, selects actions and &quot;Execute write SQL&quot;, then selects the insert document template on the next page and executes it with a title of &quot;My document!&quot;. Also demonstrates that a create table statement cannot be executed because the user does not have create-table permission." src="https://datasette.io/static/blog/2026/sql-write-ui.gif" /></p>
  <p>Users with the new <code>execute-write-sql</code> permission gain an "Execute write SQL" database action. This takes them to a page where they can either enter a SQL query to execute or pick from a set of templates, which provide insert, update and delete queries for tables that they have permission to modify - based on their <code>insert-row</code>, <code>update-row</code> and <code>delete-row</code> permissions.</p>
  <p>Datasette analyzes the SQL query and checks which tables it will modify and which relevant Datasette permissions the user needs to have. This works for <code>create-table</code> and <code>alter-table</code> as well - the full list of permissions can be found <a href="https://docs.datasette.io/en/latest/authentication.html#built-in-actions">in the documentation</a>.</p>
  <p>In addition to executing queries directly, Datasette users with the new <code>store-query</code> permission can now store both read- and write-queries in the new <code>queries</code> table in Datasette's <a href="https://docs.datasette.io/en/latest/internals.html#datasette-s-internal-database">internal SQLite database</a>. You'll need to run Datasette with the <code>--internal path/to/internal.db</code> option in order for stored queries to persist across server restarts.</p>
  <p>Stored queries can be marked private, in which case they'll only be visible to the user who created them, or they can be shared with other users of the same Datasette instance via Datasette's existing <code>view-query</code> permission mechanism.</p>
  <p>Learn more:</p>
  <ul>
  <li>The <a href="https://docs.datasette.io/en/latest/changelog.html#a31-2026-05-28">Datasette 1.0a31 release notes</a></li>
  <li>Documentation for the <a href="https://docs.datasette.io/en/latest/pages.html#pages-custom-sql-queries">read query, write query and stored query</a> pages</li>
  <li>Details of the new <a href="https://docs.datasette.io/en/latest/json_api.html#executewriteview">Executing write SQL JSON API</a></li>
  </ul>

summary:
  With Datasette 1.0a31, Datasette now offers users with the necessary permissions the ability to both execute write queries against their database and save stored queries (renamed from "canned queries"), either privately and shared with other members of their Datasette instance.


# news

date: 2026-04-19
body:
  Here's a new TIL on [using SQL functions in Google Sheets to fetch data from Datasette](https://til.simonwillison.net/google-sheets/datasette-sql).


date: 2026-04-15
body:
  [Datasette 1.0a27](https://docs.datasette.io/en/latest/changelog.html#a27-2026-04-15) changes how CSRF protection works in a way that simplifies form and API integration, and introduces a new `RenameTableEvent` for when a table is renamed by a SQL query.


date: 2026-03-18
body:
  [Datasette 1.0a26](https://docs.datasette.io/en/latest/changelog.html#a26-2026-03-18) adds a `column_types` system allowing columns to carry additional type information such as `email` or `url`, or extra types defined by plugins. This alpha also adds a UI for selecting and sorting visible columns on a table page.


date: 2026-02-25
body:
  [Datasette 1.0a25](https://docs.datasette.io/en/latest/changelog.html#a25-2026-02-25) adds [write_wrapper()](https://docs.datasette.io/en/latest/plugin_hooks.html#plugin-hook-write-wrapper) and [register_token_handler()](https://docs.datasette.io/en/latest/plugin_hooks.html#plugin-hook-register-token-handler) plugin hooks.


date: 2026-01-29
body:
  [Datasette 1.0a24](https://docs.datasette.io/en/latest/changelog.html#a24-2026-01-29) introduces a new `await request.form(files=True)` method for accepting file uploads, plus various other changes.


date: 2025-11-05
body:
  [Datasette 0.65.2](https://docs.datasette.io/en/stable/changelog.html#v0-65-2) and [Datasette 1.0a21](https://docs.datasette.io/en/latest/changelog.html#a21-2025-11-05) have been released with a **security fix** for an open redirect error, see [this advisory](https://github.com/simonw/datasette/security/advisories/GHSA-w832-gg5g-x44m). The latest Datasette alphas also include a breaking change to Datasette's permission system, described in detail in [A new SQL-powered permissions system in Datasette 1.0a20](https://simonwillison.net/2025/Nov/4/datasette-10a20/).


date: 2025-02-06
body:
  [Datasette 1.0a17](https://docs.datasette.io/en/latest/changelog.html#a17-2025-02-06) is the latest Datasette 1.0 alpha release, with bug fixes and small feature improvements from the last few months.


date: 2024-10-07
body:
  [Python 3.13](https://docs.python.org/3.13/whatsnew/3.13.html) was released today. [Datasette 1.0a16](https://docs.datasette.io/en/latest/changelog.html#a16-2024-09-05) is compatible with Python 3.13, but Datasette 0.64.8 was not. The new [Datasette 0.65](https://docs.datasette.io/en/stable/changelog.html#v0-65) release fixes compatibility with the new version of Python.


date: 2024-08-05
body:
  [Datasette 1.0a14](https://docs.datasette.io/en/latest/changelog.html#a14-2024-08-05) includes some breaking changes to how metadata works for plugins, described in detail in the new [upgrade guide](https://docs.datasette.io/en/latest/upgrade_guide.html). See also the [annotated release notes](https://simonwillison.net/2024/Aug/5/datasette-1a14/) that accompany this release.


date: 2024-02-18
body:
  [Datasette 1.0a10](https://docs.datasette.io/en/latest/changelog.html#a10-2024-02-17) is a focused alpha that changes some internal details about how Datasette handles transactions. The `datasette.execute_write_fn()` internal method now wraps the function in a database transaction unless you pass `transaction=False`.


date: 2024-02-16
body:
  [Datasette 1.0a9](https://docs.datasette.io/en/latest/changelog.html#a9-2024-02-16) adds basic alter table support to the JSON API, tweaks how permissions works and introduces some new plugin debugging utilities.


date: 2024-02-07
body:
  [Datasette 1.0a8](https://docs.datasette.io/en/1.0a8/changelog.html#a8-2024-02-07) introduces several new plugin hooks, a JavaScript plugin system and moves plugin configuration from `metadata.yaml` to `datasette.yaml`. Read more about the release in [the annotated release notes for 1.0a8](https://simonwillison.net/2024/Feb/7/datasette-1a8/).


date: 2023-12-01
body:
  [Datasette Enrichments](https://enrichments.datasette.io/) is a new feature for Datasette that supports enriching data by running custom code against every selected row in a table. Read [Datasette Enrichments: a new plugin framework for augmenting your data](https://simonwillison.net/2023/Dec/1/datasette-enrichments/) for more details, plus a video demo of enrichments for geocoding addresses and processing text and images using GPT-4.


date: 2023-11-30
body:
  [datasette-comments](https://datasette.io/plugins/datasette-comments) is a new plugin by Alex Garcia which adds collaborative commenting to Datasette. Alex built the plugin for Datasette Cloud, but it's also available as an open source package for people who are hosting their own Datasette instances. See [Annotate and explore your data with datasette-comments](https://www.datasette.cloud/blog/2023/datasette-comments/) on the Datasette Cloud blog for more details.


date: 2023-08-22
body:
  [Datasette 1.0a4](https://docs.datasette.io/en/1.0a4/changelog.html#a4-2023-08-21) has a fix for a security vulnerability in the Datasette 1.0 alpha series: the API explorer interface exposed the names of private databases and tables in public instances that were protected by a plugin such as [datasette-auth-passwords](https://datasette.io/plugins/datasette-auth-passwords), though not the actual content of those tables. See the [security advisory](https://github.com/simonw/datasette/security/advisories/GHSA-7ch3-7pp7-7cpq) for more details and workarounds for if you can't upgrade immediately. The [latest edition of the Datasette Newsletter](https://datasette.substack.com/p/datasette-cloud-and-the-datasette) also talks about this issue.


date: 2023-08-15
body:
  [datasette-write-ui: a Datasette plugin for editing, inserting, and deleting rows](https://www.datasette.cloud/blog/2023/datasette-write-ui/) introduces a new plugin adding add/edit/delete functionality to Datasette, developed by Alex Garcia. Alex built this for [Datasette Cloud](https://www.datasette.cloud/), and this post is the first announcement made on the new Datasette Cloud blog - see also [Welcome to Datasette Cloud](https://www.datasette.cloud/blog/2023/welcome/).


date: 2023-08-09
body:
  [Datasette 1.0a3](https://docs.datasette.io/en/latest/changelog.html#a3-2023-08-09) is an alpha release of Datasette that previews the new default JSON API design that’s coming in version 1.0 - the single most significant change planned for that 1.0 release.


date: 2023-07-01
body:
  New tutorial: [Data analysis with SQLite and Python](https://datasette.io/tutorials/data-analysis). This tutorial, originally presented at PyCon 2023, includes a 2h45m video and an extensive handout that should be useful with or without the video. Topics covered include Python's `sqlite3` module, `sqlite-utils`, Datasette, Datasette Lite, advanced SQL patterns and more.


date: 2023-03-24
body:
  [I built a ChatGPT plugin to answer questions about data hosted in Datasette](https://simonwillison.net/2023/Mar/24/datasette-chatgpt-plugin/) describes a new experimental Datasette plugin to enable people to query data hosted in a Datasette interface via ChatGPT, asking human language questions that are automatically converted to SQL and used to generate a readable response.


date: 2023-02-23
body:
  [Using Datasette in GitHub Codespaces](https://datasette.io/tutorials/codespaces) is a new tutorial showing how Datasette can be run in GitHub's free Codespaces browser-based development environments, using the new [datasette-codespaces plugin](https://datasette.io/plugins/datasette-codespaces).


date: 2023-01-28
body:
  [Examples of sites built using Datasette](https://datasette.io/examples) now includes screenshots of Datasette deployments that illustrate a variety of problems that can be addressed using Datasette and its plugins.


date: 2023-01-13
body:
  [Semantic search answers: Q&A against documentation with GPT3 + OpenAI embeddings](https://simonwillison.net/2023/Jan/13/semantic-search-answers/) shows how Datasette can be used to implement semantic search and build a system for answering questions against an existing corpus of text, using two new plugins: [datasette-openai](https://datasette.io/plugins/datasette-openai) and [datasette-faiss](https://datasette.io/plugins/datasette-faiss), and a new tool: [openai-to-sqlite](https://datasette.io/tools/openai-to-sqlite).


date: 2023-01-09
body:
  [Datasette 0.64](https://docs.datasette.io/en/stable/changelog.html#v0-64) is out, and includes [a strong warning](https://docs.datasette.io/en/stable/spatialite.html#warning) against running SpatiaLite in production without disabling arbitrary SQL queries, plus a new [--setting default_allow_sql off](https://docs.datasette.io/en/stable/settings.html#setting-default-allow-sql) setting to make it easier to do that. See [Datasette 0.64, with a warning about SpatiaLite](https://simonwillison.net/2023/Jan/9/datasette-064/) for more about this release. A new tutorial, [Building a location to time zone API with SpatiaLite](https://datasette.io/tutorials/spatialite), describes how to safely use SpatiaLite and Datasette to build and deploy an API for looking up time zones for a latitude/longitude location.


date: 2022-12-15
body:
  [Datasette 1.0a2: Upserts and finely grained permissions](https://simonwillison.net/2022/Dec/15/datasette-1a2/) describes the new upsert API and much improved permissions capabilities introduced in the latest [Datasette 1.0a2 alpha](https://docs.datasette.io/en/latest/changelog.html#a2-2022-12-14) release.


date: 2022-12-02
body:
  [Datasette’s new JSON write API: The first alpha of Datasette 1.0](https://simonwillison.net/2022/Dec/2/datasette-write-api/) introduces the new write API shipped in the first of the Datasette 1.0 alpha series of releases, including detailed descriptions of two demos that show how the API can be used.


date: 2022-10-27
body:
  [Datasette 0.63](https://docs.datasette.io/en/stable/changelog.html#v0-63) is out. Here are the [annotated release notes](https://simonwillison.net/2022/Oct/27/datasette-0-63/).


date: 2022-09-08
body:
  [Exploring the training data behind Stable Diffusion](https://simonwillison.net/2022/Sep/5/laion-aesthetics-weeknotes/) describes the process of building and deploying a 4GB searchable SQLite database using Datasette, starting with Parquet data that was used to train the Stable Diffusion image generation model. See also [Exploring 12 Million of the 2.3 Billion Images Used to Train Stable Diffusion’s Image Generator](https://waxy.org/2022/08/exploring-12-million-of-the-images-used-to-train-stable-diffusions-image-generator/).


date: 2022-08-21
body:
  [Analyzing ScotRail audio announcements with Datasette—from prototype to production](https://simonwillison.net/2022/Aug/21/scotrail/) provides a detailed walk-through of the process of constructing an initial rapid prototype using Datasette Lite, extending it with a custom plugin and then deploying it as a full Datasette instance using GitHub Actions and Vercel.


date: 2022-08-14
body:
  [Datasette 0.62](https://docs.datasette.io/en/stable/changelog.html#v0-62) introduces compatibility with Pyodide for [Datasette Lite](https://lite.datasette.io/), and incorporates a number of bug fixes, plugin hook upgrades and other improvements.


date: 2022-07-31
body:
  New tutorial and accompanying ten minute video: [Cleaning data with sqlite-utils and Datasette](https://datasette.io/tutorials/clean-data).


date: 2022-06-30
body:
  [s3-ocr](https://datasette.io/tools/s3-ocr) is a new tool which can run OCR (via Amazon Textract) against every PDF file in an S3 bucket and write the results to a searchable SQLite database, ready to use with Datasette. Read more about it in [s3-ocr: Extract text from PDF files stored in an S3 bucket](https://simonwillison.net/2022/Jun/30/s3-ocr/).


date: 2022-05-05
body:
  [Datasette Lite](https://lite.datasette.io/) is a new way to run Datasette: entirely in your browser, thanks to the [Pyodide](https://pyodide.org/) project which provides a full Python environment compiled to WebAssembly. You can use it to explore any SQLite database file hosted on a CORS-enabled static hosting provider, which includes GitHub and GitHub Pages. Read more about this project in [Datasette Lite: a server-side Python web application running in a browser](https://simonwillison.net/2022/May/4/datasette-lite/).


date: 2022-04-12
body:
  [Datasette for geospatial analysis](https://datasette.io/for/geospatial) describes how Datasette can be used in conjunction with SpatiaLite to work with geospatial data, including details of several geospatial plugins and tools from the Datasette ecosystem.


date: 2022-03-23
body:
  [Datasette 0.61](https://docs.datasette.io/en/stable/changelog.html#v0-61) introduces two potentially backwards-incompatible changes in preparation for the forthcoming 1.0 release: hashed URL mode has been moved to [a new plugin](https://datasette.io/plugins/datasette-hashed-urls), and the way URLs are generated to tables or databases containing special characters such as `.` or `/` has changed. [Datasette 0.61.1](https://docs.datasette.io/en/stable/changelog.html#v0-61-1) fixes a small bug in that release. See also [the annotated release notes](https://simonwillison.net/2022/Mar/24/datasette-061/) for these two versions.


date: 2022-02-27
body:
  The first two of an ongoing series of official Datasette tutorials are now available: [Exploring a database with Datasette](/tutorials/explore) introduces the Datasette web interface and shows how it can be used to explore a new database, and [Learn SQL with Datasette](/tutorials/learn-sql) provides an introduction to SQL using Datasette as a learning environment.


date: 2022-01-13
body:
  [Datasette 0.60](https://docs.datasette.io/en/stable/changelog.html#v0-60) adds a new `filters_from_request` plugin hook, new internal methods for writing to the database, better performance and various faceting improvements. See also [the annotated release notes](https://simonwillison.net/2022/Jan/14/datasette-060/).


date: 2021-12-05
body:
  [Observable](https://observablehq.com/) notebooks recently added a SQL cell type, allowing SQL queries to be executed as part of an interactive notebook workflow. Alex Garcia built a [Datasette Client](https://observablehq.com/@ambassadors/datasette-client) for these which allows you to excute queries against any Datasette instance and explore and visualize the results using JavaScript code running in a notebook.


date: 2021-10-14
body:
  [Datasette 0.59](https://docs.datasette.io/en/stable/changelog.html#v0-59) adds column descriptions in metadata, a new `register_command` plugin hook, enhanced `--cors` support and a bunch of other fixes and documentation improvements. See also [the annotated release notes](https://simonwillison.net/2021/Oct/19/datasette-059/).


date: 2021-09-08
body:
  [Datasette Desktop](https://datasette.io/desktop) is a new macOS desktop application version of Datasette, which supports opening SQLite files on your computer, importing CSV files and installing plugins. I wrote more about how it works in [Datasette Desktop—a macOS desktop application for Datasette](https://simonwillison.net/2021/Sep/8/datasette-desktop/).


date: 2021-07-28
body:
  [The Baked Data architectural pattern](https://simonwillison.net/2021/Jul/28/baked-data/) describes a pattern commonly used with Datasette where the content for a site is bundled inside a SQLite database file and included alongside templates and application code in a deployment to a serverless hosting provider.


date: 2021-07-15
body:
  [Datasette 0.58](https://docs.datasette.io/en/stable/changelog.html#v0-58) has new plugin hooks, a huge performance improvement for faceting, support for Unix domain sockets and several other improvements. Read [the annotated release notes](https://simonwillison.net/2021/Jul/16/datasette-058/) for extra background and context on the release.


date: 2021-06-05
body:
  [Datasette 0.57](https://docs.datasette.io/en/stable/changelog.html#v0-57) is out with an important [security patch](https://github.com/simonw/datasette/security/advisories/GHSA-xw7c-jx9m-xh5g) plus a number of new features and bug fixes. Datasette 0.56.1, also out today, provides the security patch for users who are not yet ready to upgrade to the latest version.


date: 2021-05-10
body:
  [Django SQL Dashboard](https://simonwillison.net/2021/May/10/django-sql-dashboard/) is a new tool that brings a useful authenticated subset of Datasette to Django projects that are built on top of PostgreSQL.


date: 2021-03-28
body:
  [Datasette 0.56](https://docs.datasette.io/en/stable/changelog.html#v0-56) has bug fixes and documentation improvements, plus some new documented internal APIs for plugin authors and SpatiaLite 5 bundled with the official [Datasette Docker container](https://docs.datasette.io/en/stable/installation.html#using-docker).


date: 2021-02-18
body:
  [Datasette 0.55](https://docs.datasette.io/en/stable/changelog.html#v0-55) adds support for cross-database SQL queries. You can now run `datasette --crossdb one.db two.db` and then run queries that join data from tables in both of those database files - see [cross-database queries](https://docs.datasette.io/en/stable/sql_queries.html#cross-database-queries) in the documentation for more details.

  [sqlite-utils 3.6](https://sqlite-utils.datasette.io/en/stable/changelog.html#v3-6) adds similar features: a `db.attach(alias, filepath)` Python API method and `--attach alias filepath.db` command-line option, both for attaching additional databases in order to execute cross-database queries.


date: 2021-02-07
body:
  This new [Video introduction to Datasette and sqlite-utils](https://simonwillison.net/2021/Feb/7/video/) provides a full introduction to both Datasette and [sqlite-utils](https://sqlite-utils.datasette.io/) in 17 minutes, including a live demo of creating a database from a CSV file and publishing it to Google Cloud Run.


date: 2021-02-03
body:
  [Serving map tiles from SQLite with MBTiles and datasette-tiles](https://simonwillison.net/2021/Feb/4/datasette-tiles/). [datasette-tiles](https://datasette.io/plugins/datasette-tiles) is a new plugin that adds a tile server to Datasette, serving map tiles from databases that conform to [the MBTiles specification](https://github.com/mapbox/mbtiles-spec). [download-tiles](https://datasette.io/tools/download-tiles) is a tool for building these databases, and [datasette-basemap](https://datasette.io/plugins/datasette-basemap) is a plugin that bundles a 22MB SQLite database with OpenStreetMap tiles covering zoom levels 0-6 for the entire world.


date: 2021-01-25
body:
  [Datasette 0.54](https://docs.datasette.io/en/stable/changelog.html#v0-54) is out today. Highlights include the new `_internal` in-memory database exposing details of connected tables, plus support for JavaScript modules in plugins and add-on scripts. More commentary on this release is available in the [annotated release notes](https://simonwillison.net/2021/Jan/25/datasette/).


date: 2021-01-24
body:
  [Drawing shapes on a map to query a SpatiaLite database](https://simonwillison.net/2021/Jan/24/drawing-shapes-spatialite/) introduces the new [datasette-leaflet-freedraw](https://datasette.io/plugins/datasette-leaflet-freedraw) plugin, which adds support for drawing shapes on a map to specify a GeoJSON MultiPolygon that can be used to query SpatiaLite databases.


date: 2021-01-07
body:
  [APIs from CSS without JavaScript: the datasette-css-properties plugin](https://simonwillison.net/2021/Jan/7/css-apis-no-javascript/) introduces [datasette-css-properties](https://datasette.io/plugins/datasette-css-properties), a highly experimental plugin that can output table rows and SQL query results as CSS stylesheets defining custom properties that can then be used to customize a static HTML page.


date: 2020-12-19
body:
  New on this site: a [Datasette Tools](https://datasette.io/tools) directory and a [search engine](https://datasette.io/-/beta) that covers documentation, tools, plugins, releases and more. The search engine uses [Dogsheep Beta](https://github.com/dogsheep/dogsheep-beta) - I wrote about how that works in [Building a search engine for datasette.io](https://simonwillison.net/2020/Dec/19/dogsheep-beta/).


date: 2020-12-13
body:
  I wrote more about how this site works in [Weeknotes: datasette.io, an official project website for Datasette](https://simonwillison.net/2020/Dec/13/datasette-io/).


date: 2020-12-10
body:
  [Datasette 0.53](https://docs.datasette.io/en/stable/changelog.html#v0-53) - `datasette serve --create` option, `?column__arraynotcontains=` table filter, `?_header=off` CSV option and updated links to reflect the new project website.


date: 2020-12-10
body:
  [New Datasette project website](https://datasette.io/) - three years after the first release Datasette finally has its own website! The site includes project [news](https://datasette.io/news), a [plugin directory](https://datasette.io/plugins), [use-cases](https://datasette.io/for) and [examples](https://datasette.io/examples). The site is implemented using Datasette custom templates, and the source code is available [on GitHub](https://github.com/simonw/datasette.io).


date: 2020-11-28
body:
  [Datasette 0.52](https://docs.datasette.io/en/stable/changelog.html#v0-52) - `--config` is now `--setting`, new `database_actions` plugin hook, `datasette publish cloudrun --apt-get-install` option and several bug fixes.


date: 2020-10-31
body:
  [Datasette 0.51](https://docs.datasette.io/en/stable/changelog.html#v0-51) - A new visual design, plugin hooks for adding navigation options, better handling of binary data, URL building utility methods and better support for running Datasette behind a proxy. [Annotated release notes](https://simonwillison.net/2020/Nov/1/datasette-0-51/).


date: 2020-10-09
body:
  [Datasette 0.50](https://docs.datasette.io/en/stable/changelog.html#v0-50) - New column actions menu. `datasette.client` object for plugins to make internal API requests. Improved documentation on deploying Datasette. [Annotated release notes](https://simonwillison.net/2020/Oct/9/datasette-0-50/).


date: 2020-09-14
body:
  [Datasette 0.49](https://docs.datasette.io/en/stable/changelog.html#v0-49) - JSON API for writable canned queries, path parameters for custom pages. See also [Datasette 0.49: The annotated release notes](https://simonwillison.net/2020/Sep/15/datasette-0-49/).


date: 2020-08-16
body:
  [Datasette 0.48](https://docs.datasette.io/en/stable/changelog.html#v0-48) - Documentation now lives at [docs.datasette.io](https://docs.datasette.io/), improvements to the `extra_template_vars`, `extra_css_urls`, `extra_js_urls` and `extra_body_script` plugin hooks.


date: 2020-08-11
body:
  [Datasette 0.47](https://docs.datasette.io/en/stable/changelog.html#v0-47) - Datasette can now be installed using Homebrew! `brew install simonw/datasette/datasette`. Also new: `datasette install name-of-plugin` and `datasette uninstall name-of-plugin` commands, and `datasette --get '/-/versions.json'` to output the result of Datasette HTTP calls on the command-line.


date: 2020-08-09
body:
  [Datasette 0.46](https://docs.datasette.io/en/stable/changelog.html#v0-46) - security fix relating to CSRF protection for writable canned queries, a new logo, new debugging tools, improved file downloads and more.


date: 2020-08-06
body:
  [GraphQL in Datasette with the new datasette-graphql plugin](https://simonwillison.net/2020/Aug/7/datasette-graphql/)


date: 2020-07-24
body:
  Two new plugins: [datasette-copyable and datasette-insert-api](https://simonwillison.net/2020/Jul/23/datasette-copyable-datasette-insert-api/). `datasette-copyable` adds copy-and-paste export options, and `datasette-insert-api` lets you create tables and insert or update data by POSTing JSON directly to Datasette.


date: 2020-07-01
body:
  [Datasette 0.45](https://docs.datasette.io/en/stable/changelog.html#v0-45) - [Magic parameters for canned queries](https://docs.datasette.io/en/stable/sql_queries.html#canned-queries-magic-parameters), a log out feature, improved plugin documentation and four new plugin hooks. See also [Datasette 0.45: The annotated release notes](https://simonwillison.net/2020/Jul/1/datasette-045/).


date: 2020-06-20
body:
  [A cookiecutter template for writing Datasette plugins](https://simonwillison.net/2020/Jun/20/cookiecutter-plugins/)


date: 2020-06-11
body:
  [Datasette 0.44](https://docs.datasette.io/en/stable/changelog.html#v0-44) - [Authentication and permissions](https://docs.datasette.io/en/stable/authentication.html), [writable canned queries](https://docs.datasette.io/en/stable/sql_queries.html#writable-canned-queries), flash messages, new plugin hooks and much, much more.


date: 2020-05-28
body:
  [Datasette 0.43](https://docs.datasette.io/en/stable/changelog.html#v0-43) - Redesigned [register_output_renderer](https://docs.datasette.io/en/stable/plugins.html#plugin-register-output-renderer) plugin hook and various small improvements and fixes.


date: 2020-05-08
body:
  [Datasette 0.42](https://docs.datasette.io/en/stable/changelog.html#v0-42) - Documented internal methods for plugins to execute read queries against a database.


date: 2020-05-06
body:
  [Datasette 0.41](https://docs.datasette.io/en/stable/changelog.html#v0-41) - New mechanism for [creating custom pages](https://docs.datasette.io/en/0.41/custom_templates.html#custom-pages), new [configuration directory mode](https://docs.datasette.io/en/0.41/config.html#configuration-directory-mode), new `?column__notlike=` table filter and various other smaller improvements.


date: 2020-04-21
body:
  [Datasette 0.40](https://docs.datasette.io/en/stable/changelog.html#v0-40) - Metadata can now be provided as YAML instead of JSON. Publishing to Zeit Now v1 is no longer supported, but Now v2 support is provided by the new [datasette-publish-now](https://github.com/simonw/datasette-publish-now) plugin. Various bug fixes.


date: 2020-03-24
body:
  [Datasette 0.39](https://docs.datasette.io/en/stable/changelog.html#v0-39) - New `base_url` configuration option for running Datasette under a different URL prefix, `"sort"` and `"sort_desc"` metadata options for setting a default sort order for a table.


date: 2020-03-08
body:
  [Datasette 0.38](https://docs.datasette.io/en/stable/changelog.html#v0-38) - New `--memory` option for `datasete publish cloudrun`, [Docker image](https://hub.docker.com/r/datasetteproject/datasette) upgraded to SQLite 3.31.1.


date: 2020-02-25
body:
  [Datasette 0.37](https://docs.datasette.io/en/stable/changelog.html#v0-37) - new internal APIs enabling plugins to safely write to databases. Read more here: [Datasette Writes](https://simonwillison.net/2020/Feb/26/weeknotes-datasette-writes/).


date: 2020-02-21
body:
  [Datasette 0.36](https://docs.datasette.io/en/stable/changelog.html#v0-36) - new internals documentation for plugins, `prepare_connection()` now accepts optional `database` and `datasette` arguments.


date: 2020-02-04
body:
  [Datasette 0.35](https://docs.datasette.io/en/stable/changelog.html#v0-35) - new `.render_template()` method for plugins.


date: 2020-01-29
body:
  [Datasette 0.34](https://docs.datasette.io/en/stable/changelog.html#v0-34) - improvements to search, `datasette publish cloudrun` and `datasette package`.


date: 2020-01-21
body:
  [Deploying a data API using GitHub Actions and Cloud Run](https://simonwillison.net/2020/Jan/21/github-actions-cloud-run/) - how to use GitHub Actions and Google Cloud Run to automatically scrape data and deploy the result as an API with Datasette.


date: 2019-12-22
body:
  [Datasette 0.33](https://docs.datasette.io/en/stable/changelog.html#v0-33) - various small improvements.


date: 2019-12-19
body:
  [Building tools to bring data-driven reporting to more newsrooms](https://medium.com/jsk-class-of-2020/building-tools-to-bring-data-driven-reporting-to-more-newsrooms-4520a0c9b3f2) - some notes on my JSK fellowship so far.


date: 2019-12-02
body:
  [Niche Museums](https://www.niche-museums.com/) is a new site entirely powered by Datasette, using custom templates and plugins. [niche-museums.com, powered by Datasette](https://simonwillison.net/2019/Nov/25/niche-museums/) describes how the site works, and [datasette-atom: Define an Atom feed using a custom SQL query](https://simonwillison.net/2019/Dec/3/datasette-atom/) describes how the new [datasette-atom plugin](https://github.com/simonw/datasette-atom) was used to add an Atom syndication feed to the site.


date: 2019-11-14
body:
  [Datasette 0.32](https://docs.datasette.io/en/stable/changelog.html#v0-32) now uses asynchronous rendering in Jinja templates, which means template functions can perform asynchronous operations such as executing SQL queries. [datasette-template-sql](https://github.com/simonw/datasette-template-sql) is a new plugin uses this capability to add a new custom `sql(sql_query)` template function.


date: 2019-11-11
body:
  [Datasette 0.31](https://docs.datasette.io/en/stable/changelog.html#v0-31) - the first version of Datasette to support Python 3.8, which means dropping support for Python 3.5.


date: 2019-07-13
body:
  [Single sign-on against GitHub using ASGI middleware](https://simonwillison.net/2019/Jul/14/sso-asgi/) talks about the implementation of [datasette-auth-github](https://github.com/simonw/datasette-auth-github) in more detail.


date: 2019-07-07
body:
  [Datasette 0.29](https://docs.datasette.io/en/stable/changelog.html#v0-29) - ASGI, new plugin hooks,
  facet by date and much, much more...

  * [datasette-auth-github](https://github.com/simonw/datasette-auth-github) - a new plugin for Datasette
  0.29 that lets you require users to authenticate against GitHub before accessing your Datasette instance.
  You can whitelist specific users, or you can restrict access to members of specific GitHub organizations
  or teams.
  * [datasette-cors](https://github.com/simonw/datasette-cors) - a plugin that lets you configure
  CORS access from a list of domains (or a set of domain wildcards) so you can make JavaScript calls
  to a Datasette instance from a specific set of other hosts.


date: 2019-06-23
body:
  [Porting Datasette to ASGI, and Turtles all the way down](https://simonwillison.net/2019/Jun/23/datasette-asgi/)


date: 2019-05-21
body:
  The anonymized raw data from [the Stack Overflow Developer Survey 2019](https://stackoverflow.blog/2019/05/21/public-data-release-of-stack-overflows-2019-developer-survey/) has been [published in partnership with Glitch](https://glitch.com/culture/discover-insights-explore-developer-survey-results-2019/), powered by Datasette.


date: 2019-05-19
body:
  [Datasette 0.28](https://docs.datasette.io/en/stable/changelog.html#v0-28) - a salmagundi of new features!

  * No longer immutable! Datasette now supports [databases that change](https://docs.datasette.io/en/stable/changelog.html#supporting-databases-that-change).
  * [Faceting improvements](https://docs.datasette.io/en/stable/changelog.html#faceting-improvements-and-faceting-plugins)
  including facet-by-JSON-array and the ability to define custom faceting using plugins.
  * [datasette publish cloudrun](https://docs.datasette.io/en/stable/changelog.html#datasette-publish-cloudrun)
  lets you publish databases to Google's new Cloud Run hosting service.
  * New [register_output_renderer](https://docs.datasette.io/en/stable/changelog.html#register-output-renderer-plugins)
  plugin hook for adding custom output extensions to Datasette in addition to the default `.json` and `.csv`.
  * Dozens of other smaller features and tweaks - see [the release notes](https://docs.datasette.io/en/stable/changelog.html#v0-28) for full details.
  * Read more about this release here: [Datasette 0.28 - and why master should always be releasable](https://simonwillison.net/2019/May/19/datasette-0-28/)


date: 2019-02-24
body:
  [sqlite-utils: a Python library and CLI tool for building SQLite databases](https://simonwillison.net/2019/Feb/25/sqlite-utils/) - a partner tool for easily creating SQLite databases for use with Datasette.


date: 2019-01-31
body:
  [Datasette 0.27](https://docs.datasette.io/en/stable/changelog.html#v0-27) - `datasette plugins` command, newline-delimited JSON export option, new documentation on [The Datasette Ecosystem](https://docs.datasette.io/en/stable/ecosystem.html).


date: 2019-01-10
body:
  [Datasette 0.26.1](https://docs.datasette.io/en/stable/changelog.html#v0-26-1) - SQLite upgrade in Docker image, `/-/versions` now shows SQLite compile options.


date: 2019-01-02
body:
  [Datasette 0.26](https://docs.datasette.io/en/stable/changelog.html#v0-26) - minor bug fixes, `datasette publish now --alias` argument.


date: 2018-12-18
body:
  [Fast Autocomplete Search for Your Website](https://simonwillison.net/2018/Dec/19/fast-autocomplete-search/) - a new tutorial on using Datasette to build a JavaScript autocomplete search engine.


date: 2018-10-03
body:
  [The interesting ideas in Datasette](https://simonwillison.net/2018/Oct/4/datasette-ideas/) - a write-up of some of the less obvious interesting ideas embedded in the Datasette project.


date: 2018-09-19
body:
  [Datasette 0.25](https://docs.datasette.io/en/stable/changelog.html#v0-25) - New plugin hooks, improved database view support and an easier way to use more recent versions of SQLite.


date: 2018-07-23
body:
  [Datasette 0.24](https://docs.datasette.io/en/stable/changelog.html#v0-24) - a number of small new features


date: 2018-06-29
body:
  [datasette-vega](https://github.com/simonw/datasette-vega), a new plugin for visualizing data as bar, line or scatter charts


date: 2018-06-18
body:
  [Datasette 0.23: CSV, SpatiaLite and more](https://docs.datasette.io/en/stable/changelog.html#v0-23) - CSV export, foreign key expansion in JSON and CSV, new config options, improved support for SpatiaLite and a bunch of other improvements


date: 2018-05-23
body:
  [Datasette 0.22.1 bugfix](https://github.com/simonw/datasette/releases/tag/0.22.1) plus we now use [versioneer](https://github.com/warner/python-versioneer)


date: 2018-05-05
body:
  [Datasette 0.21: New _shape=, new _size=, search within columns](https://github.com/simonw/datasette/releases/tag/0.21)


date: 2018-04-25
body:
  [Exploring the UK Register of Members Interests with SQL and Datasette](https://simonwillison.net/2018/Apr/25/register-members-interests/) - a tutorial describing how [register-of-members-interests.datasettes.com](https://register-of-members-interests.datasettes.com/) was built ([source code here](https://github.com/simonw/register-of-members-interests))


date: 2018-04-20
body:
  [Datasette plugins, and building a clustered map visualization](https://simonwillison.net/2018/Apr/20/datasette-plugins/) - introducing Datasette's new plugin system and [datasette-cluster-map](https://pypi.org/project/datasette-cluster-map/), a plugin for visualizing data on a map


date: 2018-04-20
body:
  [Datasette 0.20: static assets and templates for plugins](https://github.com/simonw/datasette/releases/tag/0.20)


date: 2018-03-28
body:
  [Baltimore Sun Public Salary Records](https://simonwillison.net/2018/Mar/28/datasette-in-the-wild/) - a data journalism project from the Baltimore Sun powered by Datasette - source code [is available here](https://github.com/baltimore-sun-data/salaries-datasette)


date: 2018-03-27
body:
  [Cloud-first: Rapid webapp deployment using containers](https://wwwf.imperial.ac.uk/blog/research-software-engineering/2018/03/27/cloud-first-rapid-webapp-deployment-using-containers/) - a tutorial covering deploying Datasette using Microsoft Azure by the Research Software Engineering team at Imperial College London


date: 2018-01-28
body:
  [Analyzing my Twitter followers with Datasette](https://simonwillison.net/2018/Jan/28/analyzing-my-twitter-followers/) - a tutorial on using Datasette to analyze follower data pulled from the Twitter API


date: 2018-01-17
body:
  [Datasette Publish: a web app for publishing CSV files as an online database](https://simonwillison.net/2018/Jan/17/datasette-publish/)


date: 2017-12-12
body:
  [Building a location to time zone API with SpatiaLite, OpenStreetMap and Datasette](https://simonwillison.net/2017/Dec/12/building-a-location-time-zone-api/)


date: 2017-11-25
body:
  [New in Datasette: filters, foreign keys and search](https://simonwillison.net/2017/Nov/25/new-in-datasette/)


date: 2017-11-13
body:
  [Datasette: instantly create and publish an API for your SQLite databases](https://simonwillison.net/2017/Nov/13/datasette/)


# example_csvs

url: https://data.cityofnewyork.us/api/views/vfnx-vebw/rows.csv?accessType=DOWNLOAD
name: 2018 Central Park Squirrel Census
table_name: squirrel_census_2018
source: NYC Open Data
source_url: https://data.cityofnewyork.us/Environment/2018-Central-Park-Squirrel-Census-Squirrel-Data/vfnx-vebw
about: Squirrel Census
about_url: https://www.thesquirrelcensus.com/
description:
  Data for each of 3,023 squirrel sightings in Central Park, including location coordinates, age, primary and secondary fur color, elevation, activities, communications, and interactions between squirrels and with humans.


name: Global Power Plant Database
table_name: global_power_plants
source: WRI
source_url: https://github.com/wri/global-power-plant-database
about: Global Power Plant Database
about_url: https://datasets.wri.org/dataset/globalpowerplantdatabase
url:
  https://raw.githubusercontent.com/wri/global-power-plant-database/master/output_database/global_power_plant_database.csv

description:
  An open database of all the power plants in the world. The result of a large collaboration involving many partners, coordinated by the World Resources Institute and Google Earth Outreach.


