Annotated version of this introductory video
Datasette is a tool for exploring and publishing data. It helps people take data of any shape or size, analyze and explore it, and publish it as an interactive website and accompanying API.
Datasette is aimed at data journalists, museum curators, archivists, local governments, scientists, researchers and anyone else who has data that they wish to share with the world. It is part of a wider ecosystem of tools and plugins dedicated to making working with structured data as productive as possible.
New: Datasette Desktop - a macOS desktop application for easily running Datasette on your own computer!
Import data from CSVs, JSON, database connections and more. Datasette will automatically show you patterns in your data and help you share your findings with your colleagues.
datasette publish lets you instantly publish your data to hosting providers like Google Cloud Run, Heroku or Vercel.
Spin up a JSON API for any data in minutes. Use it to prototype and prove your ideas without building a custom backend.
13th January 2022 #
Datasette 0.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.
5th December 2021 #
14th October 2021 #
Datasette 0.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.
8th September 2021 #
Datasette 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.
28th July 2021 #
The Baked Data architectural pattern 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.
15th July 2021 #
Datasette 0.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 for extra background and context on the release.
5th June 2021 #
Datasette 0.57 is out with an important security patch 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.
10th May 2021 #
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.
28th March 2021 #
18th February 2021 #
Datasette 0.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 in the documentation for more details.
sqlite-utils 3.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.
7th February 2021 #
This new Video introduction to Datasette and sqlite-utils provides a full introduction to both Datasette and sqlite-utils in 17 minutes, including a live demo of creating a database from a CSV file and publishing it to Google Cloud Run.
3rd February 2021 #
Serving map tiles from SQLite with MBTiles and datasette-tiles. datasette-tiles is a new plugin that adds a tile server to Datasette, serving map tiles from databases that conform to the MBTiles specification. download-tiles is a tool for building these databases, and datasette-basemap is a plugin that bundles a 22MB SQLite database with OpenStreetMap tiles covering zoom levels 0-6 for the entire world.
25th January 2021 #
Datasette 0.54 is out today. Highlights include the new
24th January 2021 #
Drawing shapes on a map to query a SpatiaLite database introduces the new 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.
7th January 2021 #
21st January 2022
- Fixed a bug where installation on Python 3.6 stopped working due to a change to an underlying dependency. This release can now be installed on Python 3.6, but is the last release of Datasette that will support anything less than Python 3.7. (#1609)
18th January 2022
--prefix myprefix/option to
s3-credentials create, which configures the credentials to only allow access to keys within the S3 bucket that start with the provided prefix. #12
s3-credentials policy --prefix myprefix/command for generating and outputting a JSON policy that is restricted to the specified prefix. You can see examples in the README.
list-bucketcommand for listing the contents of a specified bucket. #28
list-bucketcommand all default to outputting an indented JSON array - previously the outputted indented JSON objects separated by newlines. The
--nloption can be used to return newline-delimited single line JSON objects. The new
--tsvoptions can be used to return CSV or TSV output. #48
14th January 2022
Plugins and internals
- New plugin hook: filters_from_request(request, database, table, datasette), which runs on the table page and can be used to support new custom query string parameters that modify the SQL query. (#473)
- Added two additional methods for writing to the database: await db.execute_write_script(sql, block=True) and await db.execute_write_many(sql, params_seq, block=True). (#1570)
- The db.execute_write() internal method now defaults to blocking until the write operation has completed. Previously it defaulted to queuing the write and then continuing to run code while the write was in the queue. (#1579)
- Database write connections now execute the prepare_connection(conn, database, datasette) plugin hook. (#1564)
Datasette()constructor no longer requires the
files=argument, and is now documented at Datasette class. (#1563)
- The tracing feature now traces write queries, not just read queries. (#1568)
- The query string variables exposed by
request.argswill now include blank strings for arguments such as
?foo=&bar=1rather than ignoring those parameters entirely. (#1551)
- The number of unique values in a facet is now always displayed. Previously it was only displayed if the user specified
- Facets of type
arraycan now be configured in
metadata.json, see Facets in metadata.json. Thanks, David Larlet. (#1552)
?_nosuggest=1parameter for table views, which disables facet suggestion. (#1557)
- Fixed bug where
?_facet_array=tags&_facet=tagswould only display one of the two selected facets. (#625)
Other small fixes
- Made several performance improvements to the database schema introspection code that runs when Datasette first starts up. (#1555)
- Label columns detected for foreign keys are now case-insensitive, so
TITLEwill be detected in the same way as
- Upgraded Pluggy dependency to 1.0. (#1575)
- Now using Plausible analytics for the Datasette documentation.
explain query planis now allowed with varying amounts of whitespace in the query. (#1588)
- New CLI reference page showing the output of
--helpfor each of the
datasettesub-commands. This lead to several small improvements to the help copy. (#1594)
- Fixed bug where writable canned queries could not be used with custom templates. (#1547)
- Improved fix for a bug where columns with a underscore prefix could result in unnecessary hidden form fields. (#1527)
12th January 2022
- Fixes broken demo links in the README. #86
11th January 2022
- New CLI reference documentation page, listing the output of
--helpfor every one of the CLI commands. (#383)
sqlite-utils rowsnow has
--offsetoptions for paginating through data. (#381)
sqlite-utils rowsnow has
-poptions for filtering the table using a
WHEREquery, see Returning all rows in a table. (#382)
CLI and Python library improvements to help run ANALYZE after creating indexes or inserting rows, to gain better performance from the SQLite query planner when it runs against indexes.
Three new CLI commands:
More details and examples can be found in the annotated release notes.
sqlite-utils create-databasecommand for creating new empty database files. (#348)
- New Python methods for running
ANALYZEagainst a database, table or index:
table.analyze(), see Optimizing index usage with ANALYZE. (#366)
- New sqlite-utils analyze command for running
ANALYZEusing the CLI. (#379)
upsertcommands now have a new
--analyzeoption for running
ANALYZEafter the command has completed. (#379)
- New sqlite-utils bulk command which can import records in the same way as
sqlite-utils insert(from JSON, CSV or TSV) and use them to bulk execute a parametrized SQL query. (#375)
- The CLI tool can now also be run using
python -m sqlite_utils. (#368)
--table, so you don't need to pass both options. (#374)
--convertfunction applied to rows can now modify the row in place. (#371)
- The insert-files command supports two new columns:
--nlimport option now ignores blank lines in the input. (#376)
- Fixed bug where streaming input to the
--batch-size 1would appear to only commit after several rows had been ingested, due to unnecessary input buffering. (#364)
6th January 2022
sqlite-utils insert ... --linesto insert the lines from a file into a table with a single
linecolumn, see Inserting unstructured data with --lines and --text.
sqlite-utils insert ... --textto insert the contents of the file into a table with a single
textcolumn and a single row.
sqlite-utils insert ... --convertallows a Python function to be provided that will be used to convert each row that is being inserted into the database. See Applying conversions while inserting data, including details on special behavior when combined with
sqlite-utils convertnow accepts a code value of
-to read code from standard input. (#353)
sqlite-utils convertalso now accepts code that defines a named
convert(value)function, see Converting data in columns.
db.supports_strictproperty showing if the database connection supports SQLite strict tables.
table.strictproperty (see .strict) indicating if the table uses strict mode. (#344)
- Fixed bug where
sqlite-utils upsert ... --detect-typesignored the
23rd December 2021
- Initial release.