recent_releases (view)

This release introduces the sqlite-utils memory command, which can be used to load CSV or JSON data into a temporary in-memory database and run SQL queries (including joins across multiple files) directly against that data.

Also new: sqlite-utils insert --detect-types, sqlite-utils dump, table.use_rowid plus some smaller fixes.

sqlite-utils memory

This example of sqlite-utils memory retrieves information about the all of the repositories in the Dogsheep organization on GitHub using this JSON API, sorts them by their number of stars and outputs a table of the top five (using -t): ``` $ curl -s 'https://api.github.com/users/dogsheep/repos'\ | sqlite-utils memory - ' select full_name, forks_count, stargazers_count from stdin order by stargazers_count desc limit 5 ' -t full_name forks_count stargazers_count

dogsheep/twitter-to-sqlite 12 225 dogsheep/github-to-sqlite 14 139 dogsheep/dogsheep-photos 5 116 dogsheep/dogsheep.github.io 7 90 dogsheep/healthkit-to-sqlite 4 85 The tool works against files on disk as well. This example joins data from two CSV files: $ cat creatures.csv species_id,name 1,Cleo 2,Bants 2,Dori 2,Azi $ cat species.csv id,species_name 1,Dog 2,Chicken $ sqlite-utils memory species.csv creatures.csv ' select * from creatures join species on creatures.species_id = species.id ' [{"species_id": 1, "name": "Cleo", "id": 1, "species_name": "Dog"}, {"species_id": 2, "name": "Bants", "id": 2, "species_name": "Chicken"}, {"species_id": 2, "name": "Dori", "id": 2, "species_name": "Chicken"}, {"species_id": 2, "name": "Azi", "id": 2, "species_name": "Chicken"}] `` Here thespecies.csvfile becomes thespeciestable, thecreatures.csvfile becomes thecreatures` table and the output is JSON, the default output format.

You can also use the --attach option to attach existing SQLite database files to the in-memory database, in order to join data from CSV or JSON directly against your existing tables.

Full documentation of this new feature is available in Querying data directly using an in-memory database. (#272)

sqlite-utils insert --detect-types

The sqlite-utils insert command can be used to insert data from JSON, CSV or TSV files into a SQLite database file. The new --detect-types option (shortcut -d), when used in conjunction with a CSV or TSV import, will automatically detect if columns in the file are integers or floating point numbers as opposed to treating everything as a text column and create the new table with the corresponding schema. See Inserting CSV or TSV data for details. (#282)

Other changes

• Bug fix: table.transform(), when run against a table without explicit primary keys, would incorrectly create a new version of the table with an explicit primary key column called rowid. (#284)
• New table.use_rowid introspection property, see .use_rowid. (#285)
• The new sqlite-utils dump file.db command outputs a SQL dump that can be used to recreate a database. (#274)
• -h now works as a shortcut for --help, thanks Loren McIntyre. (#276)
• Now using pytest-cov and Codecov to track test coverage - currently at 96%. (#275)
• SQL errors that occur when using sqlite-utils query are now displayed as CLI errors.

• Fixed bug where searches consisting only of whitespace caused a server error. #33

• New feature: yaml-to-sqlite dogs.db dogs.yaml --single-column=name can be used to import YAML files consisting of a list of items into a single table with a single primary key column. #5
• Importing YAML files with new columns will now alter the table to add those columns. #4

• BREAKING CHANGE: The option order for this command is now markdown-to-sqlite database.db tablename file1.md file2.md - prior to version 1.0 the order was markdown-to-sqlite file1.md file2.md database.db tablename. This change was made for improved consistency with other x-to-sqlite tools.

• Fixed bug when using table.upsert_all() to create a table with only a single column that is treated as the primary key. (#271)

• Spatialite's geometry blob is now automatically parsed into GeoJSON, so the same query will output GeoJSON with and without Spatialite.

• New sqlite-utils schema command showing the full SQL schema for a database, see Showing the schema (CLI). (#268)
• db.schema introspection property exposing the same feature to the Python library, see Showing the schema (Python library).

• New --postgres-schema option for specifying a different PostgreSQL schema, thanks Jeremy Dormitzer. #29
• Correctly handles table names that need escaping, such as user in PostgreSQL. #25
• Empty tables are now created in the SQLite database with a matching schema, rather than being skipped. #28
• Connection strings starting with postgres:// are treated as if they started with postgresql://. #34

• No longer pins a specific version of Datasette.

• The menu_links(), table_actions() and database_actions() plugin hooks all gained a new optional request argument providing access to the current request. (#1371)

• First working version

• Fixed visual display glitch with global navigation menu. (#1367)
• No longer truncates the list of table columns displayed on the /database page. (#1364)

• Fixed bug using this plugin with Datasette 0.54 or higher. #19

• This release includes a small potentially backwards-incompatible change: the description field for a saved dashboard is now treated as Markdown and rendered as such when the saved dashboard is displayed. It is very unlikely that this will affect any of your existing dashboards but you should still check before applying the upgrade. Thanks, Atul Varma. #115

• Now depends on datasette>=0.56.1, to avoid a Datasette security vulnerability.

Warning: This release fixes a reflected cross-site scripting security hole with the ?_trace=1 feature. You should upgrade to this version, or to Datasette 0.56.1, as soon as possible. (#1360)

In addition to the security fix, this release includes ?_col= and ?_nocol= options for controlling which columns are displayed for a table, ?_facet_size= for increasing the number of facet results returned, re-display of your SQL query should an error occur and numerous bug fixes.

New features

• If an error occurs while executing a user-provided SQL query, that query is now re-displayed in an editable form along with the error message. (#619)
• New ?_col= and ?_nocol= parameters to show and hide columns in a table, plus an interface for hiding and showing columns in the column cog menu. (#615)
• A new ?_facet_size= parameter for customizing the number of facet results returned on a table or view page. (#1332)
• ?_facet_size=max sets that to the maximum, which defaults to 1,000 and is controlled by the the max_returned_rows setting. If facet results are truncated the ... at the bottom of the facet list now links to this parameter. (#1337)
• ?_nofacet=1 option to disable all facet calculations on a page, used as a performance optimization for CSV exports and ?_shape=array/object. (#1349, #263)
• ?_nocount=1 option to disable full query result counts. (#1353)
• ?_trace=1 debugging option is now controlled by the new trace_debug setting, which is turned off by default. (#1359)

Bug fixes and other improvements

• Custom pages now work correctly when combined with the base_url setting. (#1238)
• Fixed intermittent error displaying the index page when the user did not have permission to access one of the tables. Thanks, Guy Freeman. (#1305)
• Columns with the name "Link" are no longer incorrectly displayed in bold. (#1308)
• Fixed error caused by tables with a single quote in their names. (#1257)
• Updated dependencies: pytest-asyncio, Black, jinja2, aiofiles, click, and itsdangerous.
• The official Datasette Docker image now supports apt-get install. (#1320)
• The Heroku runtime used by datasette publish heroku is now python-3.8.10.

• Fix for a security hole: reflected cross-site scripting in the ?_trace=1 feature. #1360

• Fixed 500 error caused when markdown widget attempted to render a null value. #124

• New sqlite-utils indexes command to list indexes in a database, see Listing indexes. (#263)
• table.xindexes introspection property returning more details about that table's indexes, see .xindexes. (#261)

• New table.pks_and_rows_where() method returning (primary_key, row_dictionary) tuples - see Listing rows with their primary keys. (#240)
• Fixed bug with table.add_foreign_key() against columns containing spaces. (#238)
• table_or_view.drop(ignore=True) option for avoiding errors if the table or view does not exist. (#237)
• sqlite-utils drop-view --ignore and sqlite-utils drop-table --ignore options. (#237)
• Fixed a bug with inserts of nested JSON containing non-ascii strings - thanks, Dylan Wu. (#257)
• Suggest --alter if an error occurs caused by a missing column. (#259)
• Support creating indexes with columns in descending order, see API documentation and CLI documentation. (#260)
• Correctly handle CSV files that start with a UTF-8 BOM. (#250)

• Docker image now allows apt-get install. #1320
• ?_col= and ?_nocol= options to show and hide columns in a table, plus UI for doing this in the column cog menu. #615
• New ?_facet_size=max option, linked to from the … on truncated facets. #1337
• Heroku runtime used by datasette publish heroku is now python-3.8.10.
• Multi-arch support for built Docker images using Buildx - thanks, Blair Drummand. #1319

• Configuration instructions for people using dj-database-url and django-configurations - thanks, Paolo Melchiorre. #110
• Added a useful SQL pattern to the progress bar widget documentation.
• SQL queries on saved dashboards are now hidden behind a details/summary element. #117
• Documentation now covers the execute_sql permission. #112

Mainly dependency bumps, plus a new ?_facet_size= argument.

• Updated dependencies: pytest-asyncio, Black, jinja2, aiofiles, itsdangerous
• Fixed bug where columns called "Link" were incorrectly displayed in bold. (#1308)
• New ?_facet_size= argument for customizing the number of facet results returned on a page. (#1332)

• Fixed bug where the id column did not come first in the table schema. #20

• New --nl option for consuming newline-delimited GeoJSON - thanks, Chris Amico! #13
• New --spatial-index option which creates the table using SpatiaLite and adds a spatial index for the geometry column. #17

• Fixed a security and permissions flaw, where users without the execute_sql permission could still run custom queries by editing saved dashboards using the Django admin interface. #94
• Bar charts now preserve the order in which the data is returned by the query. #106
• Example select statements now include explicit columns. #105
• Columns on the dashboard page now respond to media queries, collapsing to a single column on narrow or mobile browser windows. #106
• Fixed hard-coded /dashboard/ URL, thanks Flávio Juvenal da Silva Junior. #99
• Fixed bug where ?_save- parameters could be accidentally reflected in the query string. #104
• Explicitly require at least Django 3.0. #101
• Fixed a warning about AutoField migrations with Django 3.2. #103
• Fixed a bug where users lacking permissions could end up in an infinite redirect. #30
• Configuration and security documentation now recommends using a read-only database replica as the most robust option. #95
• Added screenshots and demo links for all of the included widgets. #96

• New word cloud widget displayed when queries return wordcount_word and wordcount_count columns. #91
• All pages are now served with cache-control: private header if the user is logged in. #92
• Much improved README, including a detailed list of features. #40

• Fixed bug where saved dashboards relating to groups could be displayed multiple times. #90
• Removed duplicate "Run queries" button. #89
• HTML page titles now include named parameter values, if available. #88

• Documentation is now hosted at https://django-sql-dashboard.datasette.io/ #86
• Dashboard index link is no longer shown on saved dashboards to to users without permission to view it. #87

• Improved and updated documentation for saved dashboards. #84
• Added "Remove all queries" link for resetting interactive dashboard. #85

First non-alpha release! Django SQL Dashboard is now ready for people to use against their production Django applications.

• Saved dashboards can now be created from the interactive dashboard page. #44
• New progress bar widget for queries that return numeric columns total_count and completed_count. #77
• The list of available tables now better reflects your current permissions, and shows the columns for each listed table. #79, #80
• The dashboard index page now lists saved dashboards that the user is able to view or edit. #81
• "Edit" link for dashboards links to the Django Admin, which now respects the edit policy set for a dashboard. #44
• New documentation section covering security. #6
• Show row count on non-truncated results. #76
• More robust extraction of named parameteters from queries, fixing some errer-cases. #75
• Custom widgets can now extend a django_sql_dashboard/widgets/_base_widget.html base template. #78
• Fixed a bug caused by errors on saved dashboard pages being displayed as editable text. #74
• Unlisted public dashboards now include a robots tag to avoid being indexed by search engines. #42

• "Copy to clipboard" button on pretty-printed JSON results. #72
• Saved dashboards no longer display broken export buttons. #71

• Fixed bug where large SQL queries submitted by POST did not correctly evaluate any named parameters. #58

• Handle SQL queries too long for a GET. #58
• Export all results as CSV/TSV feature. #50

• Use separate styles for pre.sql and pre.json

• Do not show "[count]" links for unambiguous columns. #69
• Better HTML titles for pages, for better browser history. #70
• Pretty-print cells containing JSON. #62

• Correctly sign ?sql= on redirects created using DASHBOARD_UPGRADE_OLD_BASE64_LINKS. #68

• Fixed bug with DASHBOARD_UPGRADE_OLD_BASE64_LINKS setting. #67

• Make it easy to provide a custom base template. #7
• Content-Security-Policy: frame-ancestors header. #64
• Signing no longer uses base64/json. #45
• DASHBOARD_UPGRADE_OLD_BASE64_LINKS mechanism. #65

• SQL textarea now always has at least 5 rows
• Always strip trailing ; from SQL, fixing bug with "count" links. #56
• URLize links in results. #61

• Fixed bug with new --http-read-timeout feature

• Fix for bug with columns with identical names. #52
• DASHBOARD_ROW_LIMIT setting. #53
• Size SQL textarea to fit SQL. #51

• New --http-read-timeout option, thanks Eli Block! #14

• New --user-agent= option for passing a custom user-agent to Airtable. #12

Documentation improvements, bug fixes and support for SpatiaLite 5.

• The SQL editor can now be resized by dragging a handle. (#1236)
• Fixed a bug with JSON faceting and the __arraycontains filter caused by tables with spaces in their names. (#1239)
• Upgraded httpx dependency. (#1005)
• JSON faceting is now suggested even if a column contains blank strings. (#1246)
• New datasette.add_memory_database() method. (#1247)
• The Response.asgi_send() method is now documented. (#1266)
• The official Datasette Docker image now bundles SpatiaLite version 5. (#1278)
• Fixed a no such table: pragma_database_list bug when running Datasette against SQLite versions prior to SQLite 3.16.0. (#1276)
• HTML lists displayed in table cells are now styled correctly. Thanks, Bob Whitelock. (#1141, #1252)
• Configuration directory mode now correctly serves immutable databases that are listed in inspect-data.json. Thanks Campbell Allen and Frankie Robertson. (#1031, #1229)

• Better error message for --project that contains uppercase characters. #37
• Documentation on usage with GitHub Actions. #36

• --dayfirst and --yearfirst options for parsedate and parsedatetime commands. #12

• Link to dashboard from admin edit page. #48
• Better error message for queries with % that needs to be escaped. #41

• Pin underlying version of TableauScraper library to avoid problems caused by breaking changes. #4

• Removed unnecessary migration dependency, so this should now install on versions of Django prior to 3.1. Thanks, @jefftriplett. #46

• Serve cache-control: private HTTP header on private dashboards. #43

• Upgraded to latest @vercel/python builder, fixing a number of bugs involving incorrectly handled querystring characters. #28

• Optional support for HTTP Basic authentication. #15

• Documentation now lives at https://django-sql-dashboard.readthedocs.io/ (#36)
• Ability to copy and paste TSV from the default table display. (#29)
• Fixed two bugs with the way count links in column headers work. (#31, #32)
• New permissions system: a saved dashboard can now be made public, private, unlisted, group-only, staff-only or superuser-only. (#27)

• Changed default permission policy: saved dashboards are now inaccessible to the public by default (#37). This will change when permissions are implemented fully in #27.

• Added basic bar chart widget. #10
• Added markdown widget. #12
• Added html widget. #12

• Bundle widget templates in release.

• Fix for bug where templatetags were not bundled in the release package. #22

• Run tests against read-only 'dashboard' connection, refs #18
• Named SQL parameters are now extracted into form fields. #15
• Configure URLs using include() pattern. #14
• ?sql= params are now signed, must first be created using a POST request. #13
• New custom widgets framework #20
• Big number widget for displaying a big number. #20

Progress display now shows the total as well as the number currently in the queue. % hacker-news-to-sqlite trees hn.db 26440397 done: 1, in queue: 0, total: 1 done: 2, in queue: 28, total: 30 done: 3, in queue: 27, total: 30

• Include templates and migrations in the release. #4

• Workaround for psycopg2 dependency bug on macOS. #3

• Initial working alpha.

• Use white-space: pre-wrap when displaying lines, so lines now wrap. #21
• Long lines in results are now truncated, with a [...] link to expand them to full length. #22

• Support importing data from a full URL to a Tableau dashboard hosted somewhere other than public.tableau.com. #2

• No longer produces tables with single quotes in the name, which can cause bugs when viewed in Datasette. #1

First working version.

• Deployments that are configured to check organization membership now use read:org scope instead of the more permissive user scope. #73

• Fix ASGI lifespan bug. #1

• First working release

• Tool now also works against inputs that are JSON files consisting of a list of objects with identical keys, using --format=json. #12

• New sqlite-transform jsonsplit for converting foo,bar into ["foo", "bar"]. #10
• Added --dry-run option to sqlite-tranfsorm lambda for previewing the conversion that is about to be applied. #8

• New --sqlite database.db export option. #10

This release adds the ability to execute queries joining data from more than one database file - similar to the cross database querying feature introduced in Datasette 0.55.

• The db.attach(alias, filepath) Python method can be used to attach extra databases to the same connection, see db.attach() in the Python API documentation. (#113)
• The --attach option attaches extra aliased databases to run SQL queries against directly on the command-line, see attaching additional databases in the CLI documentation. (#236)

Support for cross-database SQL queries and built-in support for serving via HTTPS.

• The new --crossdb command-line option causes Datasette to attach up to ten database files to the same /_memory database connection. This enables cross-database SQL queries, including the ability to use joins and unions to combine data from tables that exist in different database files. See Cross-database queries for details. (#283)
• --ssl-keyfile and --ssl-certfile options can be used to specify a TLS certificate, allowing Datasette to serve traffic over https:// without needing to run it behind a separate proxy. (#1221)
• The /:memory: page has been renamed (and redirected) to /_memory for consistency with the new /_internal database introduced in Datasette 0.54. (#1205)
• Added plugin testing documentation on Using pdb for errors thrown inside Datasette. (#1207)
• The official Datasette Docker image now uses Python 3.7.10, applying the latest security fix for that Python version. (#1235)

• Fix broken tests against Datasette 0.54. #71
• Bundle JavaScript and CSS assets for GraphiQL, instead of loading them from a CDN. #73
• Pin to graphene dependency < 3.0. #72

• Fixed bug in released version caused by unnecessary attr import. #6

• sqlite-utils insert --sniff option for detecting the delimiter and quote character used by a CSV file, see Alternative delimiters and quote characters. (#230)
• The table.rows_where(), table.search() and table.search_sql() methods all now take optional offset= and limit= arguments. (#231)
• New --no-headers option for sqlite-utils insert --csv to handle CSV files that are missing the header row, see CSV files without a header row. (#228)
• Fixed bug where inserting data with extra columns in subsequent chunks would throw an error. Thanks @nieuwenhoven for the fix. (#234)
• Fixed bug importing CSV files with columns containing more than 128KB of data. (#229)
• Test suite now runs in CI against Ubuntu, macOS and Windows. Thanks @nieuwenhoven for the Windows test fixes. (#232)

• Explicitly open file using utf-8 encoding - thanks, RiverZhou. #10
• Fix import error if note has no "updated" element - thanks, Matej Korosec. #8

• Correctly handle entities in XHTML notes. #9
• Fixed a bug importing ENEX files on Windows. Thanks, dskrad. #11

• Fixed bug introduced in 0.6 which caused the non-JavaScript tile explorer to display incorrectly. #22

This release breaks backwards compatibility if you are using this as a tile server. Map tiles served by /-/tiles/... now use the OpenStreetMap coordinate system, where y=0 references the upper-most tile on the map. If you wish to continue using the TMS coordinate system you should switch to /-/tiles-tms/... instead. See Tile coordinate systems for more details.

• Support both OSM and TMS coordinate systems, use OSM by default. #15
• Hide non-JavaScript map as soon as the JavaScript loads in the tile explorer. #21

• Updated README examples to reflect function name changes in version 1.0. #3

• Fixed a code import bug that slipped in to 3.4. (#226)

Renamed two functions for compatibility with the latest Jellyfish - see their documentation for details.

• jaro_distance(a, b) is now jaro_similarity(a, b)
• jaro_winkler(a, b) is now jaro_winkler_similarity(a, b)

• sqlite-utils insert --csv now accepts optional --delimiter and --quotechar options. See Alternative delimiters and quote characters. (#223)

• Tile explorer interface now works without JavaScript! Thanks, Matthew Somerville. #14

• New /-/tiles-stack mechanism for serving tiles from multiple databases. #8
• "tiles-stack-order" plugin setting, for controlling the order in which tiles are served from the stack.
• Tile explorer page now also displays metadata for that tile database. #7
• Tile explorer uses name from metadata if it is available, and provides links to the database and table pages.
• Tile 404s are now served as blank PNGs. #9
• Renamed templates from mbtiles_*.html to tiles_*.html. #10

• --name= option for setting the name to be written into the metadata table. If you use the --city or --country options without passing the --name the city or country name will be used instead. #5
• --attribution=osm shortcut for setting the attribution to © OpenStreetMap contributors without having to type that out in full. #4

• New --attribution option for writing an attribution record to the metadata table. #3

• Attribution is now shown on the tile explorer page if it is included in the metadata table. #6

• Database now includes attribution in the metadata table. #3

• Fixed a bug where ?_search= and ?_sort= parameters were incorrectly duplicated when the filter form on the table page was re-submitted. (#1214)

• Table and database action menu items linking to the tile explorer. #4

• --city and --country options as an alternative to --bbox. #1

Initial working version.

• First non-alpha release.

• Improved README and live demo. #2