{sqlviewer}: Make SQL Queries Viewable and Pipeable
Authors: Grzegorz Smoliński
Abstract: {sqlviewer} is an Shiny app to make running SQL queries for those working with R easier. It provides queries results previewer (by displaying first 1000 rows) as well as piping-like possibility to construct complex queries. The main three goals are: (1) make working with SQL using R more comfortable; (2) bring better debug experience by displaying results from temporary queries; (3) help construct complex queries. To run query (and see results), simply copy SQL query to clipboard when app is running and name of the query will be inserted into app. Then click on the name to run query and see result. SQL query has to be named (see details below). Example query could look like this:
-- #versicolor_virginica
SELECT DISTINCT i.Species
FROM iris i
WHERE i.Species IN ('versicolor', 'virginica');
-- #filtered_data
SELECT *
FROM iris i
WHERE i.Species IN (
-- |> versicolor_virginica
);
Full Description:
Deployment
Unfortunately, {sqlviewer} needs functionality to read from clipboard which is (if I'm right) not allowed on posit.cloud or shinyapps.io, so I was not able to deploy the app. The app can be, however, easile accessible by installing it from the github and run locally:
# install.packages("pak")
pak::pak("gsmolinski/sqlviewer")
Example of usage
To start using {sqlviewer}, it is necessary to have sample database:
# install.packages("RSQLite") # run if package not installed already
# install.packages("fs") # run if package not installed already
temp_db <- fs::file_temp("sqlviewerDB_example", ext = ".db")
conn <- DBI::dbConnect(RSQLite::SQLite(), dbname = temp_db)
DBI::dbWriteTable(conn, "iris", iris)
DBI::dbDisconnect(conn)
After the database with iris dataset was saved in temporary location (path is stored in temp_db variable), we need SQL queries to run. {sqlviewer} expects that each SQL query will be named (have label), names won’t be duplicated and will consist only of letters, numbers and underscores. Additionally, each name (label) must be in its own line:
-- #versicolor_virginica
SELECT DISTINCT i.Species
FROM iris i
WHERE i.Species IN ('versicolor', 'virginica');
-- #filtered_data
SELECT *
FROM iris i
WHERE i.Species IN (
-- |> versicolor_virginica
);
Above we shown not just named queries, but also how to use piping. Pipe operator (|>) has to be in its own line and can be read as “here put this query”.
Coming back to R, what’s left now is to run {sqlviewer} app, but because the app was developed to enable parallelization (each query can be run in separate process), before running app, we can run {future} plan.
future::plan("multisession") # enable parallel mode
sqlviewer::open(RSQLite::SQLite(), dbname = temp_db) # run 'sqlviewer' app against temporary database
App should be running now on the default host and port and in the console full web address should be printed already - it can be copied to web browser to see the app.
How to run SQL queries? They have to be copy to clipboard and if in the app switch input is set to TRUE (i.e. is blue, not grey), queries’ names will be inserted. To run a query, click on its name. Other possibilities are: copy the full query (i.e. with all resolved piped queries) or remove the query (which also copies full query to clipboard before removing). Near switch button there is also button to re-run the same query as before. Please note that currently background processes can’t be stopped, so if one ran long-running query and want to stop it, it will be necessary to restart main R session.
To add more queries, simply copy them to clipboard. If the copied name already exists, will be replaced by new query.
Running Locally or in the Web Browser
By default, sqlviewer do not open itself in IDE viewer or web browser. To open web browser with the running app after calling sqlviewer::open() , use parameter launch_browser set to TRUE:
sqlviewer::open(RSQLite::SQLite(), dbname = temp_db, launch_browser = TRUE)
What is important here is to note that in sqlviewer two modes to read from or write back (queries with resolved piped queries) to clipboard are possible, using clipr::write_clip() and clipr::read_clip() or JavaScript functions. JavaScript should be used in any web-based environment (like opening app in the web browser), while clipr functions should be used where JavaScript do not work. One example of not working JavaScript function is opening app in the local (desktop) RStudio Viewer, so to use sqlviewer in the viewer locally, we need to change two parameters:
sqlviewer::open(RSQLite::SQLite(), dbname = temp_db, clipboard_mode = "local", launch_browser = .rs.invokeShinyPaneViewer)
Shiny app: https:://app_is_not_deployed_see_description
Repo: GitHub - gsmolinski/sqlviewer: Make SQL Queries Viewable and Pipeable
Thumbnail:
Full image:
