This presentation by Michael Sinclair details the development of Queryesk, a database access management tool built entirely in Elixir/Phoenix LiveView. Sinclair, with extensive Elixir and DevOps experience (including scaling Websockets to 4 million connections at PDQ), focuses on solving the common problem of providing developers safe, granular access to production databases while maintaining compliance.
The core technical discussion revolves around overcoming LiveView's limitations when handling large, dynamic datasets in the browser, specifically within Queryesk's data table view. Sinclair chronicles the failed attempts using pure LiveView rendering (too slow when editing fields) and then LiveView streams (memory exhaustion at scale). The ultimate solution involves encapsulating the entire data table, responsible for potentially tens or hundreds of thousands of rows, within a custom Lit Web Component utilizing lit-virtualizer. This delegates rendering and scrolling entirely to the browser, achieving instant navigation through massive result sets without server-side memory pressure.
Furthermore, the talk covers effective JavaScript interoperability via Hooks (used for features like SQL formatting, clipboard copying, and component communication) and the adoption of Brotli compression (Base64 encoded, as LiveView only handles JSON) for efficient transfer of large query results. Finally, Sinclair introduces LiveSync, an open-source library leveraging PostgreSQL replication to automatically update LiveView state based on database changes, simplifying real-time features like query approval workflows.
Queryesk Development in LiveView: Scaling Data Grids and Real-time Updates
0:00 Introduction & Background: Speaker Michael Sinclair introduces his project, Queryesk, a database access management tool built in Elixir/LiveView. He notes his background scaling Elixir systems (e.g., 4 million concurrent WebSockets at PDQ).
0:02 Queryesk Purpose: The tool provides granular, browser-based database access with security features like PII redaction, avoiding the need to distribute production database passwords. It supports query review/approval flows.
0:05 LiveView Interop: Sinclair emphasizes the importance of LiveView's JS interoperability, particularly using Hooks and Web Components (built with Lit) to extend functionality where server-side logic struggles.
0:05:33 Web Component Example (format-date): Demonstrates using a web component to handle timezone/date formatting client-side, as browser-local information is unavailable to the server.
0:07:22 Hooks for Interactivity: Hooks enable integration with external JS libraries (e.g., Chart.js) and managing browser APIs that require direct user interaction (e.g., clipboard).
0:10:10 Data Table Challenge (The Bottleneck): Initially, the data table rendered rows directly in LiveView. Editing a single field forced a full list re-render, making the UI sluggish, even with only 100 rows.
0:11:58 Scaling Issues with Streams: Using Streams helped memory for small sets, but rendering 10,000+ DOM elements still froze the browser, and streams don't support arbitrary query pagination well.
0:14:24 The Web Component Solution: The entire data table was converted into a complex Web Component using Lit Virtualizer. This renders only visible rows (lit-virtualizer), allowing fast, instant scrolling through hundreds of thousands of records without keeping everything in server memory or relying on server-side pagination.
0:16:53 Query Lifecycle & Cancellation: Running queries initiates an Elixir Task with Repo.stream. Results are compressed using Brotli (Base64 encoded because LiveView only supports JSON messages) and streamed to the data table component. The Task PID is kept in state, allowing users to cancel long-running queries.
0:19:10 Brotli Benchmarks: Shows Brotli significantly outperforms JSON/Base64 overhead on large payloads, reducing user wait time.
0:20:31 LiveSync Library: An open-source library utilizing PostgreSQL replication to automatically update LiveView assigns and Ecto schemas based on DB changes, crucial for real-time features like query approval notifications.
0:21:40 LiveSync Demo: Shows one user submitting a query, which immediately appears as "pending" for a second logged-in user via the automatic DB sync.
0:23:42 Future of LiveSync: Goals include using PostgreSQL's binary decoding instead of string protocol for efficiency and implementing bulk processing to reduce redundant updates for multiple connected users on the same data view (e.g., the pending queries page).
0:25:26 Q&A on JS Bundling: In response to a question, the speaker notes his current JavaScript bundle size is 424KB and admits he hasn't implemented smart code splitting yet, though it is a recognized optimization target.
0:26:59 Q&A on Live Components vs. Rows: The speaker confirms that moving individual rows to live_componentdid initially help latency by avoiding full list re-renders, but the extreme scale of Queryesk's data requirements ultimately mandated the move to a browser-side Virtualized Web Component.