Snippets for working with datasette and R

Datasette is a great tool to work and show data in a sqlite database in a browser.

Enable full text search (fts)

• automatically get a search field in datasette
• docs

enable-fts datasette_db.sqlite table_name column_name

A small functions does this

enable_fts <- function(db_path, table, column) {
  statement <- paste("sqlite-utils enable-fts", db_path, table, column)
  cli::cli_inform("Enable full text search: {statement}")
  system(statement)
}

set_index(db_path="my_db.db", table="my_table, column="my_column")

Creating an index

create_index <- function(db_path, table, column) {
  statement <- paste("sqlite-utils create-index", db_path, table, column)
  cli::cli_inform("Set index with: {statement}")
  system(statement)
}

create_index(db_path="my_db.db", table="my_table, column="my_column")

Make links clickable

• use markdown rendering or html rendering to make any url clickable
• add a target=blank to open it in a new tab (currently only works with html rendering)

For example in R:

data <- data  %>% mutate(url = paste0('<a href="https://', url, '" target="_blank">', url, '</a>'))

than add to datasette metadata json file:

{
    "plugins": {
        "datasette-render-html": {
            "columns":["url"]
        }
    }
}

Render timestamps

Installing the rendering timestamps plugin does exactly that: rendering all timestamps in all tables of a database that are plus/minus five years from now. Also, you can define in the metadata file columns that should be rendered.

Beware R users: A dataframe column of a `<dttm>` type needs to be converted to character before writing to the database:

data <- data %>% mutate(timestamp = as.character(timestamp))