Chat with your data 💬

Build LLM-powered apps with shinychat, querychat and ggsql

Put a language model inside your Shiny apps: streaming chat UIs with shinychat, natural-language data filtering with querychat, and SQL that draws its own charts with ggsql.

“The most profound technologies are those that disappear.”
–Mark Weiser

In session 7 you built Shiny apps where users interact through sliders and dropdowns. In session 9 you learned to call an LLM from code. This session welds the two: apps where the interface is a conversation, and where the model translates human questions into the filters, queries and charts you used to build by hand.

Everything runs on the same ellmer chat objects from session 9, so your API key setup carries over unchanged.

PART I: A chat UI in one line

The shinychat package provides the chat interface –the streaming text, the message bubbles, the input box– so you never build them from scratch. The quickest possible start is genuinely one line:

library(shinychat)

shinychat::chat_app(ellmer::chat_google_gemini())

1

chat_app() takes any ellmer chat object and wraps it in a complete running Shiny app. Try it now –it is the fastest way to poke at a system prompt interactively.

For a real app you compose the pieces yourself: chat_ui() in the interface, chat_append() in the server. Here is a complete teaching assistant –note how the system prompt does all the personality work, exactly as in session 9:

library(shiny)
library(bslib)
library(shinychat)

ui <- bslib::page_fillable(
  theme = bslib::bs_theme(bootswatch = "journal"),
  h4("R4DEV teaching assistant 🤖"),
  shinychat::chat_ui("chat", fill = TRUE)
)

server <- function(input, output, session) {

  chat <- ellmer::chat_google_gemini(
    system_prompt = paste(
      "You are the teaching assistant of R4DEV, a tidyverse-first R workshop.",
      "Answer questions about R with short runnable examples.",
      "Always use the native pipe |> and never suggest base R plotting."
    )
  )

  observeEvent(input$chat_user_input, {
    stream <- chat$stream_async(input$chat_user_input)
    shinychat::chat_append("chat", stream)
  })
}

shinyApp(ui, server)

2

chat_ui() drops the whole chat interface into your layout, like any other Shiny widget. It fills the page like the dashboards from session 7.

3

The chat object is created once per user session –it carries the conversation memory, so follow-up questions work.

4

Whatever the user types arrives as input$chat_user_input –a plain Shiny input, nothing exotic.

5

$stream_async() returns the answer as it is generated, and chat_append() streams it into the UI word by word. Async matters: while one user waits for an answer, the app stays responsive for everyone else.

Try it live below, download it to run yourself:

Note

The live demo above runs on Claude (Anthropic) rather than Gemini, using credentials already provisioned for this deployment –the code you download and the code in this session both use chat_google_gemini(), matching what you set up with your own free-tier key.

PART II: querychat, or the end of the filter sidebar

Now the genuinely new idea. In session 7’s COVID app, filtering data meant building a sidebar of inputs –one widget per variable, wired to filter() calls. querychat replaces that whole apparatus: the user asks for the subset they want, the LLM writes SQL, and your app reacts to the result.

The safety design deserves attention before the code: the model never sees your data and never executes anything. It only reads the table’s schema –column names and types– and proposes a read-only SELECT statement, which querychat runs locally against a DuckDB copy of your data frame. Hallucinated SQL fails loudly; it cannot delete, modify or leak a single row.

We’ll point it at an old friend –the Spotify archive from session 2:

library(querychat)

songs <- readr::read_csv("spotify_favorites_archive.csv")

querychat::querychat_app(
  songs,
  table_name = "songs",
  client = ellmer::chat_google_gemini(),
  greeting = "Ask me anything about these 1,178 songs — in plain language.",
  data_description = paste(
    "One row per song from the discographies of 8 artists,",
    "with Spotify audio features saved before the API closed in 2024.",
    "valence and energy run from 0 to 1.",
    "cover_url holds the album cover image address."
  )
)

6

Like chat_app(), querychat_app() is the batteries-included version: data table, chat sidebar, live SQL display. For custom layouts there are modular pieces (querychat_ui(), querychat_server()) that plug into any Shiny app.

7

Any ellmer chat works. Translating questions to SQL is easy work –a small, cheap model does it fine.

8

The data_description is your prompt-craft moment: tell the model what a row means and what units the columns use. Good descriptions are the difference between “it just works” and confused SQL.

Once it runs, try these –and watch the SQL it writes (querychat shows it; always read it, that’s your audit trail):

• “Only songs by Oasis, most energetic first”
• “Songs sadder than 0.2 valence but with energy above 0.8” –angry music, in other words
• “Which artist has the happiest average discography?”

Get the app:

PART III: SQL that draws: ggsql

You just watched an LLM write SQL for you –which makes this the right moment to meet ggsql, a brand-new tool from Posit (alpha, April 2026) that extends SQL with a VISUALISE clause borrowed straight from the grammar of graphics you learned in session 2. One language selects the data and draws it.

These chunks run live in this page through ggsql’s knitr engine –the r: prefix lets SQL read data frames straight from the R session:

library(ggsql)
library(tidyverse)

songs <- readr::read_csv(paste0(getwd(), "/spotify_favorites_archive.csv"))

SELECT artist, AVG(valence) AS mean_valence
FROM r:songs
GROUP BY artist
VISUALISE mean_valence AS x, artist AS y
DRAW bar

Aggregation in SQL, mapping and geometry in the VISUALISE clause: mean_valence AS x is exactly aes(x = mean_valence) wearing a SQL costume. One more, with color as a third channel:

SELECT valence, energy, artist
FROM r:songs
VISUALISE valence AS x, energy AS y, artist AS color
DRAW point

WarningAlpha software

ggsql is an alpha release –the syntax may still change, and you will hit rough edges. That is not a reason to avoid it; it is a reason to pin the version in your projects and read the release notes when you update. Watching a tool this young is also watching how the R ecosystem evolves in real time.

Why does ggsql belong in this session? Because of what you saw in Part II: LLMs are remarkably good at writing SQL. A model that can answer “show me energy against valence, colored by artist” with a complete ggsql query –data selection and chart in one statement– is a very short step from the querychat app you just ran. The pieces on this page are the ingredients of the next generation of data tools; now you know how each one works alone.

Capstone: Ask R4DEV

One last app, and it puts together nearly everything the workshop has taught: a chatbot that answers questions about R4DEV itself, grounded only in the actual content of these 13 sessions.

The pipeline: every session’s prose was chunked by heading (session 6’s scraping instincts, applied to our own .qmd files instead of a website), indexed into DuckDB’s full-text search (a keyword/BM25 ranker –no embedding model needed, which matters: it means retrieval runs with zero external API calls, identically on your laptop and once deployed), and wired into a shinychat interface. Every question first retrieves the most relevant excerpts, then the model is instructed to answer only from what was retrieved –the same grounding fix that would have saved the mood-scoring experiment back in session 9.

TipAsk it something

Try “How do I make an interactive map with leaflet?” or “What packages does R4DEV use for text analysis?” –then ask it something the workshop never covers, and watch it say so instead of guessing.

🏗 Practice 10: Ship it

NoteEasy

• Change the chat app’s system prompt so it refuses to answer anything not related to R, politely
• Run the querychat app and find the five angriest songs (low valence, high energy). Read the SQL it generated –would you have written it differently?

ImportantIntermediate

• Point querychat_app() at the Maddison GDP data from session 2 and ask it three questions you answered with dplyr back then. Compare the SQL to your pipes
• Rewrite the ggsql scatter above to show only songs with valence below 0.5, faceted by artist –check the ggsql docs for the FACET syntax

CautionAdvanced

• Combine sessions 9 and 10: build a Shiny app with a chat_ui() where the model has a tool that runs ggsql_execute() on the songs table and returns the chart. You will have built a tiny data analyst