Datasette

sqlite-utils is a core building block in the ecosystem that provides a Python library and a command-line utility for managing SQLite databases. It is used to bridge the gap between raw data and a Datasette-ready database.

Key capabilities include:

• Data Ingestion: Inserting data from JSON, CSV, or TSV formats into SQLite. It can automatically create tables with correct schemas or alter existing tables to add missing columns.
• Full-Text Search (FTS) Configuration: Setting up tables for SQLite FTS, including the creation of triggers to maintain search index synchronization.
• Advanced Schema Modification: Performing operations not supported by standard SQLite ALTER TABLE syntax, such as changing column types or redefining primary keys.
• Relationship Management: Adding foreign keys to existing tables and extracting columns into separate lookup tables.

Dogsheep is a collection of tools designed for personal analytics. It enables users to create a 'personal data warehouse' by importing data from various web services into SQLite databases, which can then be explored using Datasette.

Commonly used tools within the Dogsheep project include:

• github-to-sqlite: Imports GitHub data into SQLite.
• twitter-to-sqlite: Imports Twitter data into SQLite.

By using these tools, you can centralize your personal data from different sources into a single SQLite database for analysis.

Datasette uses the pluggy plugin system to allow customization via plugin hooks.

To implement a hook, create a function in your plugin and decorate it with @hookimpl. While each hook has a full documented signature, you only need to accept the specific parameters your implementation requires.

For example, if you only need the value and column from the render_cell hook, you can define it like this:

@hookimpl
def render_cell(value, column):
    if column == "stars":
        return "*" * int(value)

When managing access to stored queries, there is a distinction between viewing a query and executing it:

• view-query: Allows an actor to view a stored query page.
• Executing queries:
  • Untrusted stored queries: Require either execute-sql or the relevant write permissions to run.
  • Trusted stored queries: Can be executed with only the view-query permission.

Datasette provides several endpoints to inspect the current instance's configuration, environment, and state. You can view these endpoints in a browser or append .json to the URL to receive a JSON response.

Most JSON responses include an "ok": true key. Most introspection endpoints are covered by the JSON API stability promise, except for /-/threads and /-/actions, which may change in future releases.

Optimize Datasette's performance and caching behavior.

• default_cache_ttl: Default Cache-Control: max-age=X header in seconds (default 5s). Override with ?_ttl=N.
• cache_size_kb: Amount of memory SQLite uses for its per-connection cache in KB.
• allow_download: Enables/disables downloading the original SQLite database (only works in immutable mode).

datasette mydatabase.db --setting default_cache_ttl 60 --setting cache_size_kb 5000

SQLite FTS supports advanced query syntax like AND, OR, NOT, and NEAR. By default, Datasette escapes these characters to prevent user errors.

To use these operators:

1. Per-request: Append &_searchmode=raw to the query string.
2. Default for a table: Set "searchmode": "raw" in the table's metadata configuration.
3. Override default: If a table is set to raw mode but you want to return to default escaping, append &_searchmode=escaped to the query string.

https://fara.datasettes.com/fara/FARA_All_ShortForms?_search=manafort&_searchmode=raw

When a Datasette page loads, it dispatches a custom datasette_init event on the document object. The event's detail property contains a reference to the datasetteManager object, which allows you to interact with the Datasette frontend environment.

To access the manager, use document.addEventListener() to listen for the event.

document.addEventListener("datasette_init", function (evt) {
    const manager = evt.detail;
    console.log("Datasette version:", manager.VERSION);
});

The Datasette ecosystem is composed of two primary categories of tools designed to facilitate gathering, analyzing, and publishing data:

1. Tools for building SQLite databases: Utilities used to prepare, manipulate, and populate the SQLite databases that Datasette serves.
2. Datasette Plugins: Extensions that add new functionality directly to the Datasette instance (e.g., new authentication methods, UI components, or API endpoints).

You can find curated lists of these resources in the Plugins directory and the Tools directory on the official Datasette website.