sqlquery

SQL query editor

Overview

Htmlwidget/Shiny gadget to write SQL queries with autocompletion for SQL keywords and for table names and fields. There are two addins in the package, in both you can write queries, but :

• Non-interactive mode: you cannot run queries but you keep control over your R session;
• Interactive mode: you can run queries and see first rows of result.

Installation

You can install from Github:

source("https://install-github.me/dreamRs/sqlquery")

Htmlwidget

Basic usage:

library(sqlquery)
sql_query(value = "SELECT * FROM mtcars")

Get autocompletion for tables and fields in a Database:

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(conn = con, name = "mtcars", value = mtcars)

sql_query(conn = con)

Shiny app

Basic usage:

library(sqlquery)
sql_query_app(value = "SELECT * FROM mtcars")

With a connection :

library(DBI)
con <- dbConnect(RSQLite::SQLite(), ":memory:")
dbWriteTable(conn = con, name = "mtcars", value = mtcars)

sql_query_app(conn = con)

Click button see results to display first rows with RStudio's View (if app runs in pane viewer) or in a modal (if app runs in dialog viewer or browser).

To change display mode, use:

options("sqlquery.display.mode" = "dialog")

Addins

You can launch addins via RStudio addins menu. If you want to use a connection with addins, you can set option sqlquery.connection in your .Rprofile :

options("sqlquery.connection" = function() {
  con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
  DBI::dbWriteTable(conn = con, name = "mtcars", value = mtcars)
  return(con)
})

This has to be a function returning a connection.

If you want to use a default schema, use:

options("sqlquery.schema" = "myschema")