We’re giving CockroachDB’s interactive SQL shell a face lift in v23.1!
The SQL shell is the interactive read-eval-print loop (REPL) bundled inside CockroachDB under the command cockroach sql. It is also available as the standalone and more modestly sized cockroach-sql downloadable program, as well as our development tool cockroach demo.
We are keenly aware of the special role that CockroachDB’s SQL shell plays in our user community. It is the most frequently used exploration and learning tool when experimenting with CockroachDB. It is a mission-critical tool for operators who need to analyze performance and troubleshoot faults. It aids developers to test their queries and compare query plans. It helps advocates demonstrate CockroachDB’s benefits to their peers. By far, it is the most versatile and most exposed of CockroachDB’s native interactive user experiences.
Our shell did not receive much love in the past few years. We neglected it somewhat, partly because our database team is at its strongest building a SQL query engine rather than terminal user interfaces, and partly because we were intimidated by its wide audience and deep impact on our community. It is hard to make changes when any change could be detrimental to the productivity of thousands.
Yet, we had to do something. Our users noticed how recent macOS releases have amplified the risk of crashes on that platform due to a janky handling of unix signals. We also started to receive increasingly frequent complaints of a lack of support for multi-line editing, a side effect of folks experimenting with larger and larger SQL queries. There were display and navigation bugs, and people were noticing. The line editor was based on a 30 year old C library, and that was starting to show its age: the bugs became harder to fix, and no member of our team felt confident to add new features.
And so we did. We made a leap of faith, and partnered with Charm to adopt their event-driven model and ecosystem. This change brought us a solid and extensible foundation, one that we feel empowered to build upon. We worked hard to ensure that workflows from previous CockroachDB versions were not disrupted, while we fixed our bugs and added new features.
The history of CockroachDB’s SQL shell
A native, interactive SQL shell has been bundled with CockroachDB ever since the first release. Then and now, we continue to believe that a REPL is an essential part of a SQL database’s learning and development toolbox.
Like all REPL software, the SQL shell needs logic to read input and print results. (The evaluation, in our case the execution of SQL queries, is handled in the back-end by the database itself.)
To print results, we have the luxury of a simple data model: we only have to print tabular data.
Ever since the first release, CockroachDB has contained a diversity of table formatters: TSV, CSV, HTML, SQL, raw records, plain text / ascii art. While we continuously make small incremental improvements to this area, it remains rather stable: it does its job, it is reasonably simple, and we receive extremely few requests or complaints about it.
There is, comparatively, much more to say about the input side of things.
The “reading input” part is handled by an input line editor. It is an editor, instead of a simple prompt, because the user expects to be able to move their cursor around and modify the input interactively before they send it for execution.
A line editor in a unix terminal is always, perhaps surprisingly, a rather complex piece of software. It has to interact with a diversity of terminal types, handle the intricacies of line discipline, analyze input for regular characters and special keys, produce escape sequences to display the current input on screen, and contain a complex redraw algorithm to handle screen (terminal) resizes. Truth be told, the unix TTY subsystem is an intimidating beast, and we have learned to respect it.
Besides, the line editor is also where users spend the most of their time, and thus where they are the most sensitive to productivity improvements or regressions.
In the first release of CockroachDB in 2017, we used Chzyer’s readline library as a line editor. We were drawn to it as it was, at the time, the most feature complete native Go library in that category. Sadly, despite the best efforts of its authors, we found that it was behaving poorly with input longer than the width of the terminal. It also simply did not handle multi-line input very well.
Faced with its limitations, we seriously considered turning to the “gold standard” of unix line editors: the GNU readline library. GNU readline is the interface that our users have the most experience with, for it powers most unix shells (including GNU Bash), many other SQL shells (including Postgres' own psql shell) and a host of other utilities with a long history. It is also battle-tested, extremely customizable and contains very few bugs. We like it! Sadly for us, GNU readline is licensed solely under the GNU Public License (GPL). This precluded its inclusion inside CockroachDB which, at the time, was released under the Apache Public License.
Instead, we turned towards the next best thing after GNU readline: BSD’s editline library, also known as “libedit”. Editline was developed initially for NetBSD, and has since been adopted by the other BSD operating systems. It is extremely portable, also battle-tested, featureful and customizable, and was supported natively on macOS through its lineage with FreeBSD. We adopted libedit by creating a Cgo wrapper library. This integration was completed in 2018, in time for CockroachDB v2.0.
We liked libedit’s ability to handle all the editor workflows that our users already knew from other programs. It was also much better at handling long input lines than Chzyer’s readline. What we did not fully like about it was its dependency on Linux, on the ncurses library, and on the terminfo database. This made its integration in containers somewhat more complex than we wanted it to be. Still, it carried the day through 10 (!) major releases of CockroachDB, all the way from v2.x in 2018 to v22.2 last year—and it is still included as a fallback, temporarily, in v23.1.
This brings us to today, where we can, at last, admit that our libedit integration outlived its welcome. For one, we would like to simplify our build process and tools, and the presence of an external C library creates friction against this simplification. Then, more importantly, libedit needs to integrate with unix signal handling (to handle Ctrl+C, which is delivered as SIGINT, and terminal resizes delivered as SIGWINCH), and the Go runtime system really, really does not like when another piece of code inside the same process has opinions about signal delivery. This relative animosity between libedit and Go was the source of numerous bugs and instabilities, most notably on macOS. Finally, as we’ll discuss later, we started to feel the need for a richer user interface, and the relatively younger folk on our team found it hard to grok, maintain, and extend libedit’s ancient and venerable C code base.
The history that did not get written
It is difficult to overstate how hard it is to program a unix line editor. The event model is complex, the low-level interface to the OS is complex, and there are very many edge cases. Knowing this, we were, and still are, extremely shy to start re-implementing a line editor from scratch. It would also be a serious opportunity cost, as our time would be best spent enhancing the SQL database itself.
And so given that libedit had shortcomings, that we didn’t know (at the time) of better native approaches in Go, and that we did not wish to start re-implementing our own, we started looking at alternative approaches.
One approach that turned out quite successful is the use of Postgres' own SQL shell, psql, with CockroachDB. Starting in v19.1, pointing psql to a CockroachDB cluster results in a powerful experience for folk already comfortable with psql. Equipped with GNU readline, psql is quite stable and portable. It also contains a diverse feature set that complements well that of cockroach sql and is superbly documented.
Our users knew this, we knew this, and we know of quite a few folk who continue to be successful with psql. Yet, we are not keen to advertise psql as the main interface to CockroachDB. For one, the optics are not great; it feels odd to “require PostgreSQL to use CockroachDB”. The combination of both is also practically harder to deploy and maintain, especially in orchestrated environments. And perhaps more importantly, psql does not handle CockroachDB oddities such as its transaction state, and does not do multi-line input very well.
We also considered other tools. We are very impressed by usql (Go) - . and pgcli (Python). Both work quite well with CockroachDB.
Especially the latter—pgcli—is probably the furthest towards where we’d like to be. Its feature coverage is large, it is quite stable, and it offers seducing eye candy. Still, it is also a relatively large software package of its own, with a sizable chain of Python dependencies. Making pgcli a first-class interface to CockroachDB in our documentation would burden our users with deploying and managing a Python software package, a choice at odds with our desire to keep CockroachDB self-contained. At this point, we are friendly to whomever wishes to use pgcli (or any other tool, really), but we want to ensure that CockroachDB includes its own SQL shell out of the box.
Charm and Bubble Tea
Independent from our struggles, some time in 2019, two special individuals decided to deliver a gift to the Go community: Christian Rocha and Toby Padilla had a vision of an entire ecosystem of frameworks and tools to implement rich and beautiful terminal user interfaces (TUIs) in Go programs, including a vibrant community. By late 2019 Charm was incorporated and backed by investors who shared their vision.
Lo and behold! It happened. Since then, Charm has grown into a federation of numerous projects. Charm itself provides a framework for developers to organize their code (Bubble Tea), pre-defined composable widgets to create rich TUIs (Bubbles), command-line tools to create interactive, beautiful shell scripts (Gum), as well as a miscellanea of components and services to empower developers and system administrators.
We discovered Charm in the summer of 2022. The buzz of their community had started to become hard to avoid. A short investigation on our side revealed the technology was solid, their team committed to open source collaboration, and foremost that Charm was spearheading a cultural movement—a vision of beauty in productivity. That, we saw, was the most likely to create enthusiasm in our team and motivate meaningful investment in the future of our tooling. (More on that below.)
The part of the Charm suite that we like and trust the most is Bubble Tea, which is their “core” library: a framework to create interactive terminal applications in Go. Bubble Tea is inspired by the Elm architecture. Its abstractions are extremely composable, a feature inherited from Elm’s lineage of functional languages. Its event handling is both asynchronous and yet easy to use, a property enabled by Go’s concurrency runtime. On the surface, it borrows concepts from the traditional model-view-controller architecture for application design, which makes it approachable for engineers with only limited experience building user interfaces. All these three aspects—composability, asynchrony, approachability—were exactly what we looked for in a new core for our SQL shell.
Another thing we like about the Charm ecosystem is its modern approach to handling low-level interactions with the terminal, especially the thorny problem of escape sequences. Lip Gloss, Charm’s terminal layout library, is itself built on the shoulders of the fine library Termenv, by Christian Muehlhaeuser. Termenv is a core terminal handling library originally developed for the Markdown renderer Glamour, then later generalized for use by Bubble Tea. Termenv was designed with a clever strategy. It did not stray towards maximum terminal compatibility coverage, in the way that ncurses/terminfo (and termcap before them) had to; that choice would have burdened Termenv with a complex architecture. Neither did its author Christian play dumb and limit compatibility to just xterm and iTerm2; the terminal emulation ecosystem is, as of 2023, also still quite vibrant with a lot of contenders. Instead, Christian thoroughly investigated the most actively used terminal emulators across multiple platforms as of 2020-2023, and identified the broadest set of common supported features, and focused on that. The result is a foundation that is sophisticated enough that it doesn’t misbehave on some of the more “edgy” terminals, yet simple enough that its implementation remains approachable and easy to maintain by the youngest generation of developers.
Enter Bubbline and Catwalk
With our newfound appetite for what Charm had to offer, we needed to find a path towards integration. One obstacle was that Charm’s widget library Bubbles is designed around a box model: its components, including its built-in text editors, keep a fixed rectangular size throughout their lifetime. In contrast, a REPL needs an editor that starts one line high and grows/shrinks dynamically as the user types in their queries.
We achieved this by implementing Bubbline, a new Bubble Tea widget that extends the text editor already included in Charm’s Bubbles. Bubbline handles the dynamic resizing and also complements Bubbles with the numerous features CockroachDB had already gotten used to through their experience with libedit. We also slapped on a couple of extra features that our users found missing in previous versions of our SQL shell, such as the ability to transfer control to an external editor to edit large queries. An overview of Bubbline’s features can be found on its home page.
Overall, the exercise was fruitful. Not only did we build a brand new line editor with all the motivational advantages of a trending community; we also collaborated closely with the Charm team and contributed numerous bug fixes that will also benefit the wider Charm ecosystem.
Two areas of this work also deserve further acknowledgement.
For one, at the time of this writing, the Charm ecosystem gets rave reviews from its tech community but its core libraries do not contain very many unit tests. In fact, we found very few guardrails that prevent inadvertent changes and regressions in the behavior of TUI components. In contrast, CockroachDB’s quality standards pretty much mandate a comprehensive test suite for all user-facing features, as our primary audience (DB administrators) is extremely adverse to unannounced UX changes.
To palliate this shortcoming, we contributed a generic, open source unit test framework for Bubble Tea-based applications, called Catwalk. We use Catwalk ourselves to exercise the full feature surface in Bubbline, but we designed it so it could benefit any project in the Charm community, too.
Separately, we also invested in an implementation of bracketed paste for Bubble Tea and Bubbles. Bracketed paste is the mechanism through which a user can copy an example snippet from a web page or another document and paste it into a terminal application as one unit. Without bracketed paste, each line in the pasted text would be considered as a separate input and executed separately; in our SQL world, it is common to want to execute multiple queries separated by semicolons as a single unit. We had, in fact, introduced support for bracketed paste in go-libedit when we started using it back in 2018; it was just natural we would push for the same in Bubbline. This required us to re-architect the core event loop of Bubble Tea and then also change the way that Bubble widgets interpret input. These changes are already integrated inside CockroachDB and we are currently working with the Charm team to make them available to the wider ecosystem as well.
Towards a more glamorous UX
The story so far was our integration of Charm’s Bubble Tea inside CockroachDB’s interactive SQL shell. Starting in v23.1, the default UX will be powered by Bubble Tea and include numerous quality-of-life improvements compared to previous versions.
That said, our Bubble Tea integration remains limited so far to just the line editor. Our SQL shell only transfers control to Bubble Tea when we need to request input from the user, and control is transferred away when time comes to execute a query.
This approach enabled us to leapfrog our editor UX into a more enlightened future, but it is merely the start of a wider story. Bubble Tea is asynchronous and event-driven, which makes it possible to design terminal UIs with multiple components working together, side by side and concurrently. This is a shift of mindset in our development team and product design; one where we can now transition away from a single linear top-level event loop. With Charm and Bubble Tea, we have adopted a solid foundation to extend the SQL shell with additional database inspection tools. Thanks to Bubble Tea’s compositional semantics, we can enable various engineers or teams to contribute independent components and extend UX bit by bit without tight coupling. Our team is also enthusiastic about the potential of leveraging Charm’s libraries to create beautiful, stylish, and delightful interactions. Some ideas we have already started exploring include live monitoring, inline documentation, result pagination, interactive data editors, and more.
In the next post in this series, we will illustrate our first Charm-enabled major UX development in CockroachDB history, one that our users have been asking since day 1. Stay tuned!
