Building a Postgres TUI App in Rust Because Opening pgAdmin is a Hassle

Do you find yourself opening pgAdmin or DbVisualizer every time you need to browse your database?
It's fine for deep dives, but waiting for an app to launch when you just want a quick peek is a hassle (maybe it's just me...).

As the title suggests, the TUI app I'm currently developing is a tool that allows you to view the state of your Postgres database directly from your local terminal.
To make it useful for quick table checks when launching pgAdmin feels like overkill, I chose Rust, using ratatui for rendering and sqlx for data retrieval.
In this article, I'll look back at how I built it, featuring excerpts from the code currently under development.

By the way, I named this TUI app "dbtui."
(Searching for it brought up some obscure tools, but I'm sticking with this name for now.)

What I Built

It launches from the terminal.

It displays a list of tables from the specified database and allows you to view the details of a table from there.
It's a simple tool where pressing the right arrow key just moves you to the right.

Overview of dbtui

Under construction & these are only code excerpts to keep it brief. I might post the full code if there's interest.

The app follows a very simple flow: "Load configuration → Connect to DB → Switch terminal to TUI mode → Run event loop."
First, here is an excerpt from main.rs.

// src/main.rs
#[tokio::main]
async fn main() -> Result<()> {
    dotenvy::dotenv().ok();
    let config = Config::from_env()?;
    let db = Database::connect(&config.database_url).await?;

    let mut terminal = setup_terminal()?;
    let mut app = App::new(config, db);

    let result = run_app(&mut terminal, &mut app).await;
    restore_terminal(&mut terminal)?;
    result
}

It reads .env, initializes Config and Database, and enters the crossterm alternate screen via setup_terminal.

TUI apps can sometimes leave the terminal screen in a broken state if they crash.
Therefore, the design ensures that restore_terminal is always called at the very end after receiving the result.

Event Loop Connecting tokio and ratatui

In run_app, we handle ratatui rendering and tokio asynchronous events simultaneously.

• Key input → EventStream
• Periodic updates → tokio::time::interval(200ms)

async fn run_app(terminal: &mut Term, app: &mut App) -> Result<()> {
    let mut events = EventStream::new();
    let mut ticker = tokio::time::interval(Duration::from_millis(200));

    loop {
        terminal.draw(|frame| ui::draw(frame, app))?;

        tokio::select! {
            _ = ticker.tick() => {
                if app.should_refresh() {
                    if let Err(err) = app.refresh().await {
                        app.set_error(err);
                    }
                }
            }
            maybe_event = events.next() => {
                // Determine key input or resizing
            }
        }
    }
}

terminal.draw is ratatui's rendering closure, and the actual processing is delegated to ui::draw.
On the other hand, inside tokio::select!, we handle key input and apply configuration changes.

To prevent interference between rendering and asynchronous processing, the roles are divided as follows:

• Rendering is synchronous
• I/O is encapsulated within the App side

Role of the Config Module

src/config.rs is responsible for reading and normalizing .env and environment variables.
Reasonable default values are defined for queries and refresh intervals, and corrections such as forcing zero or empty strings to max(1) are also handled here.

// src/config.rs
pub fn from_env() -> Result<Self> {
    let database_url = env::var("DATABASE_URL")
        .or_else(|_| env::var("DBTUI_DATABASE_URL"))
        .context("Environment variable DATABASE_URL is not set")?;

    let query = env::var("DBTUI_QUERY").unwrap_or_else(|_| {
        "SELECT table_name AS name FROM information_schema.tables WHERE table_schema = 'public' ORDER BY table_name"
            .to_string()
    });

    let refresh_secs = env::var("DBTUI_REFRESH_SECS")
        .ok()
        .and_then(|value| value.parse::<u64>().ok())
        .unwrap_or(5);

    let detail_limit = env::var("DBTUI_DETAIL_LIMIT")
        .ok()
        .and_then(|value| value.parse::<usize>().ok())
        .unwrap_or(100)
        .max(1);

    Ok(Self::new(database_url, query, refresh_secs, detail_limit))
}

Even if settings are overwritten via a form, Config::new reapplies max to the intervals and limits. This allows the app to operate with the assurance that these values will "always be 1 or greater."

Fetching Data with sqlx

In src/data.rs, I use sqlx's PgPool to execute arbitrary queries and format the results into a TableSnapshot for screen display.

// src/data.rs
pub async fn fetch(&self, query: &str) -> Result<TableSnapshot> {
    let rows = sqlx::query(query)
        .fetch_all(&self.pool)
        .await
        .with_context(|| format!("Failed to execute query: {query}"))?;

    let columns: Vec<String> = match rows.first() {
        Some(row) => row.columns().iter().map(|c| c.name().to_string()).collect(),
        None => Vec::new(),
    };

    let formatted_rows: Vec<Vec<String>> = rows
        .iter()
        .map(|row| format_row(row, columns.len()))
        .collect::<Result<_>>()?;

    Ok(TableSnapshot {
        columns,
        rows: formatted_rows,
        fetched_at: Local::now(),
    })
}

It extracts column names from the sqlx Row and converts each cell into a string via format_cell before passing them to ratatui. This should simplify the rendering logic in ratatui.

Type Conversion

Postgres has a rich set of types, so format_cell formats each one accordingly.
Key points include:

• NULL detection
• Individual processing for integers, floats, strings, UUIDs, etc.

fn format_cell(row: &PgRow, idx: usize) -> Result<String> {
    if row.try_get_raw(idx)?.is_null() {
        return Ok("NULL".to_string());
    }

    let column = row.column(idx);
    let type_name = column.type_info().name().to_ascii_uppercase();

    let value = match type_name.as_str() {
        "BOOL" => row.try_get::<bool, _>(idx)?.to_string(),
        "INT2" => row.try_get::<i16, _>(idx)?.to_string(),
        "INT4" | "OID" => row.try_get::<i32, _>(idx)?.to_string(),
        "INT8" => row.try_get::<i64, _>(idx)?.to_string(),
        "FLOAT4" => row.try_get::<f32, _>(idx)?.to_string(),
        "FLOAT8" => row.try_get::<f64, _>(idx)?.to_string(),
        "NUMERIC" | "MONEY" => row.try_get::<String, _>(idx)?,
        "UUID" | "TEXT" | "VARCHAR" | "CHAR" | "BPCHAR" | "NAME" => row.try_get::<String, _>(idx)?,
        "DATE" => row.try_get::<NaiveDate, _>(idx)?.to_string(),
        "TIME" => row.try_get::<NaiveTime, _>(idx)?.format("%H:%M:%S%.f").to_string(),
        "TIMESTAMP" => row.try_get::<NaiveDateTime, _>(idx)?.format("%Y-%m-%d %H:%M:%S").to_string(),
        "TIMESTAMPTZ" => row.try_get::<DateTime<Local>, _>(idx)?.format("%Y-%m-%d %H:%M:%S%:z").to_string(),
        "JSON" | "JSONB" => {
            let value: Value = row.try_get(idx)?;
            value.to_string()
        }
        "BYTEA" => {
            let bytes = row.try_get::<Vec<u8>, _>(idx)?;
            format!("0x{}", hex::encode(bytes))
        }
        _ => row.try_get::<String, _>(idx).unwrap_or_else(|_| format!("<{type_name}>")),
    };

    Ok(value)
}

By handling this thoroughly here, the UI side can focus exclusively on calculating text widths and adjusting layouts. By converting types like JSON and BYTEA into strings, you don't have to worry about the app crashing due to type errors during monitoring.

Responsibilities of the App Struct

The App struct in src/app.rs is responsible for state management.
It bundles everything together, including the mode, selected row, column scrolling, snapshots, configuration forms, and status.
Methods like refresh and refresh_detail_if_needed fetch data via sqlx and reset flags as necessary.

pub async fn refresh(&mut self) -> Result<()> {
    let snapshot = self.db.fetch(&self.config.query).await?;
    let row_count = snapshot.rows.len();
    self.list_snapshot = Some(snapshot);
    self.detail_snapshot = None;
    self.force_refresh = false;
    self.last_refresh = Some(Instant::now());
    if row_count == 0 {
        self.selected_row = 0;
    } else {
        self.selected_row = self.selected_row.min(row_count.saturating_sub(1));
    }
    self.detail_dirty = true;
    self.refresh_detail_if_needed().await?;
    Ok(())
}

When AppMode is ConfigEditor, the handling of key inputs is toggled. While browsing, it handles row navigation using g or arrow keys, and reloading with r. I ensured that schema and table names are extracted via selected_table_target to construct safe SQL.
By using MAX and MIN to keep the selection position and column offset within valid ranges, I have prevented panics caused by referencing data outside the viewport.

Rendering with ratatui

src/ui.rs is the part responsible for rendering the screen.
The draw function creates a layout split vertically into three parts, with the top section subdivided to display the "Table List" and "Table Details."

pub fn draw(f: &mut Frame<'_>, app: &App) {
    let layout = Layout::default()
        .direction(Direction::Vertical)
        .margin(1)
        .constraints([
            Constraint::Min(12),
            Constraint::Length(3),
            Constraint::Length(2),
        ])
        .split(f.size());

    let table_chunks = Layout::default()
        .direction(Direction::Vertical)
        .constraints([Constraint::Percentage(45), Constraint::Percentage(55)].as_ref())
        .split(layout[0]);

    draw_table_list(f, table_chunks[0], app);
    draw_table_detail(f, table_chunks[1], app);
    draw_status(f, layout[1], app);
    draw_help(f, layout[2]);

    if matches!(app.mode(), AppMode::ConfigEditor) {
        draw_config_editor(f, app);
    }
}

By simply utilizing ratatui's Layout and Block, the rendering functions only read the App reference without modifying its state. Since the UI is decoupled from the event handlers, I believe the configuration is easier to follow just by reading the rendering logic.

Implementation of Table Display

The render_snapshot function unifies the display for both the list and details.
By combining the Table widget and TableState, vertical scrolling is handled by ratatui, while horizontal scrolling is managed manually using column_offset.

fn render_snapshot(
    f: &mut Frame<'_>,
    area: Rect,
    snapshot: Option<&TableSnapshot>,
    title: &str,
    empty_message: &str,
    highlight_row: Option<usize>,
    column_offset: usize,
) {
    let block = Block::default().title(title).borders(Borders::ALL);

    match snapshot {
        Some(snapshot) if snapshot.columns.is_empty() => {
            let paragraph = Paragraph::new("No columns in the result set")
                .block(block)
                .alignment(Alignment::Center);
            f.render_widget(paragraph, area);
        }
        Some(snapshot) => {
            // Omitted: Calculate visible_columns and display_lengths to draw the Table
        }
        None => {
            let paragraph = Paragraph::new(empty_message)
                .block(block)
                .alignment(Alignment::Center);
            f.render_widget(paragraph, area);
        }
    }
}

Since the display width is measured for each column using text_width and allocated with Constraint::Ratio, the table layout does not break even with full-width characters.

Execution Steps and Daily Usage

How to run and use:

1. Write your DATABASE_URL in .env.
2. Just run cargo run (I hope to make it a command eventually).
3. The list displays within a few hundred milliseconds.

Main Controls:

• ↑↓: Move between rows
• ←→: Horizontal scrolling
• r: Refresh
• c: Configuration panel (allows switching the connected database, etc.)

Tailing stderr with tail -f in tmux makes it easier to track errors.

Performance and Stability

• Does not break even with a 200ms interval × sqlx.
• Lightweight UI as it only handles strings.
• Re-generates PgPool when settings are changed.

Towards Future Extensions

I am already personally satisfied with the current state, but I am nurturing several ideas for improvement:

• Using sqlx's query_as! to insert type-safe predefined queries.
• Graphing refresh times using ratatui's Sparkline.
• Extending TableTarget to allow detailed retrieval even for tables with composite keys.
• Making the App multi-view compatible to monitor multiple queries in parallel within the same process.

In any case, I intend to keep growing the tool without changing the basic policy of reliably retrieving values with sqlx and rendering them clearly with ratatui.

Summary

dbtui (I low-key like the name) is a tool built with Rust × sqlx × ratatui to fulfill my need for a "quick to open, quick to close DB browsing tool."

• Type-aware formatting
• Precise state management of the App
• Easy-to-read UI with ratatui

Combining these elements, it has become a comfortable tool for checking Postgres contents directly within the terminal environment.

I hope this serves as a reference for anyone looking to build a similar tool!