dblab

# dblab

__Interactive client for PostgreSQL, MySQL, SQLite3, Oracle and SQL Server.__ --- **Documentation**: https://dblab.app --- ## Table of contents - [Overview](#overview) - [Features](#features) - [Installation](#installation) - [Homebrew](#homebrew) - [Binary Release](#binary-release-linuxmacoswindows) - [Automated installation/update](#automated-installationupdate) - [Help Command](#help) - [Usage](#usage) - [SSH Tunnel](#ssh-tunnel) - [Configuration](#configuration) - [Key bindings configuration](#key-bindings-configuration) - [Connection Profiles](#connection-profiles) - [Navigation](#navigation) - [Panels and the sidebar tree](#panels-and-the-sidebar-tree) - [Result sets](#result-sets) - [Active schema](#active-schema) - [Query editor](#query-editor) - [Modes](#modes) - [Editing and motions](#editing-and-motions) - [Executing queries](#executing-queries) - [Query history](#query-history) - [Help modal](#help-modal) - [Key bindings](#key-bindings) - [Contribute](#contribute) - [License](#license) ## Overview dblab is a fast and lightweight interactive terminal-based UI application for PostgreSQL, MySQL, and SQLite3, written in Go and works on macOS, Linux, and Windows machines. The main idea behind using Go for backend development is to utilize the ability of the compiler to produce zero-dependency binaries for multiple platforms. dblab was created as an attempt to build a very simple and portable application to work with local or remote PostgreSQL/MySQL/SQLite3/Oracle/SQL Server databases. ## Features - Cross-platform support for macOS/Linux/Windows (32/64-bit) - Simple installation (distributed as a single binary) - Zero dependencies. - Vim-style query editor (normal and insert modes, line-oriented editing commands). - Multi-query execution: write multiple SQL statements separated by `;` and run them concurrently with results displayed in separate tabs. - Single-query execution: press ctrl+r to execute only the query on the current cursor line, without running other statements in the editor. - Connection profiles with secure credential storage in the OS keyring. - Query history: executed queries are persisted across sessions and can be browsed/re-used via a filterable list. - Read-only mode: use `--readonly` to prevent accidental writes by forcing the database session into read-only mode (supported for PostgreSQL, MySQL, SQLite, Oracle, and SQL Server). - Schema switching: press ctrl+s to pick the active schema from a filterable list without restarting the app (PostgreSQL and Oracle, when no schema was pinned at startup); the active schema is always shown in the status bar. - Built-in help modal: press ? to display a help overlay showing all available key bindings; press Esc to dismiss it. - Full-screen mode: press alt+f to expand the focused query editor or result set panel to fill the terminal; press Esc to exit. - Per-panel key bindings: the query editor, the sidebar tree, the result set panel and the panel navigation each have their own section in `.dblab.yaml`, so every panel can be rebound independently. ## Installation ### Homebrew It works with Linux, too. ``` brew install --cask danvergara/tools/dblab ``` Or ``` brew tap danvergara/tools brew install --cask dblab ``` ### Binary Release (Linux/macOS/Windows) You can manually download a binary release from [the release page](https://github.com/danvergara/dblab/releases). ### Automated installation/update > Don't forget to always verify what you're piping into bash Install the binary using our bash script: ```sh curl https://raw.githubusercontent.com/danvergara/dblab/master/scripts/install_update_linux.sh | bash ``` ## Help ``` … ``` ## Usage You can start the app without passing flags or parameters; you'll be asked for connection data instead. ```sh $ dblab --host localhost --user myuser --db users --pass password --ssl disable --port 5432 --driver postgres --limit 50 $ dblab --db path/to/file.sqlite3 --driver sqlite $ dblab --host localhost --user system --db FREEPDB1 --pass password --port 1521 --driver oracle --limit 50 $ dblab --host localhost --user SA --db msdb --pass '5@klkbN#ABC' --port 1433 --driver sqlserver --limit 50 ``` Connection URL scheme is also supported: ```sh $ dblab --url 'postgres://user:password@host:port/database?sslmode=[mode]' $ dblab --url 'mysql://user:password@tcp(host:port)/db' $ dblab --url 'file:test.db?_pragma=foreign_keys(1)&_time_format=sqlite' $ dblab --url 'oracle://user:password@localhost:1521/db' $ dblab --url 'sqlserver://SA:myStrong(!)Password@localhost:1433?database=tempdb&encrypt=true&trustservercertificate=false&connection+timeout=30' ``` If you're using PostgreSQL or Oracle, you have the option to define the schema you want to work with. The `--schema` flag is optional: if omitted, dblab will display all schemas the connected user has access to in the sidebar tree. If provided, only that specific schema will be shown. ```sh # Postgres $ dblab --host localhost --user myuser --db users --pass password --schema myschema --ssl disable --port 5432 --driver postgres --limit 50 $ dblab --url postgres://user:password@host:port/database?sslmode=[mode] --schema myschema # Oracle $ dblab --host localhost --user user2 --db FREEPDB1 --pass password --port 1521 --driver oracle --limit 50 --schema user1 $ dblab --url 'oracle://user2:password@localhost:1521/FREEPDB1' --schema user1 ``` For PostgreSQL, the schema can also be set directly in the connection URL with the `search_path` query parameter, instead of passing `--schema` alongside it. Both forms are equivalent: ```sh $ dblab --url 'postgres://user:password@localhost:5432/database?sslmode=disable&search_path=myschema' ``` The schema the session is currently using is shown in the status bar. If you don't pin a schema — no `--schema` flag, no `schema` field in the config file and no `search_path` in the URL — you can also change it at any time with ctrl+s; see [Active schema](#active-schema). You can use the `--readonly` flag to open a connection in read-only mode. This prevents any write operations (INSERT, UPDATE, DELETE, etc.) from being executed, which is useful when you want to safely browse a production database. The same can be achieved via the configuration file by setting `readonly: true` on a database profile (see [Configuration](#configuration)). ``` … ``` As requested in [#125](https://github.com/danvergara/dblab/issues/125), support for MySQL/MariaDB sockets was integrated. ```sh $ dblab --url "mysql://user:password@unix(/path/to/socket/mysql.sock)/dbname?charset=utf8" $ dblab --socket /path/to/socket/mysql.sock --user user --db dbname --pass password --ssl disable --port 5432 --driver mysql --limit 50 ``` Postgres connection through Unix sockets: ```sh $ dblab --url "postgres://user:password@/dbname?host=/path/to/socket" $ dblab --socket /path/to/socket --user user --db dbname --pass password --ssl disable --port 5432 --driver postgres --limit 50 ``` Now, it is possible to ensure SSL connections with `PostgreSQL` databases. SSL-related parameters have been added, such as `--sslcert`, `--sslkey`, `--sslpassword`, and `--sslrootcert`. More information on how to use such connection flags can be found [here](https://www.postgresql.org/docs/current/libpq-connect.html). ```{ .sh .copy } dblab --host db-postgresql-nyc3-56456-do-user-foo-0.fake.db.ondigitalocean.com --user myuser --db users --pass password --schema myschema --port 5432 --driver postgres --limit 50 --ssl require --sslrootcert ~/Downloads/foo.crt ``` ### SSH Tunnel Now, it's possible to connect to Postgres or MySQL (more to come later) databases on a server via SSH using a password or SSH key files. To do so, 6 new flags have been added to the dblab command: | Flag | Description | |----------------------|-------------------------------------------------------------------| | --ssh-host | SSH Server Hostname/IP | | --ssh-port | SSH Port | | --ssh-user | SSH User | | --ssh-pass | SSH Password (Empty string for no password) | | --ssh-key | File with private key for SSH authentication | | --ssh-key-pass | Passphrase for protected private key files | #### Examples Postgres connection via SSH tunnel using a password: ```{ .sh .copy } dblab --host localhost --user postgres --pass password --schema public --ssl disable --port 5432 --driver postgres --limit 50 --ssh-host example.com --ssh-port 22 --ssh-user root --ssh-pass root ``` Postgres connection via SSH tunnel using an SSH private key file: ```{ .sh .copy } dblab --host localhost --user postgres --pass password --schema public --ssl disable --port 5432 --driver postgres --limit 50 --ssh-host example.com --ssh-port 22 --ssh-user root --ssh-key my_ssh_key --ssh-key-pass password ``` Postgres connection using the url parameter via SSH tunnel using a password: ```{ .sh .copy } dblab --url postgres://postgres:password@localhost:5432/users?sslmode=disable --schema public --ssh-host example.com --ssh-port 22 --ssh-user root --ssh-pass root ``` MySQL connection via SSH tunnel using a password: ```{ .sh .copy } dblab --host localhost --user myuser --db mydb --pass 5@klkbN#ABC --ssl enable --port 3306 --driver mysql --limit 50 --ssh-host example.com --ssh-port 22 --ssh-user root --ssh-pass root ``` MySQL connection via SSH tunnel using an SSH private key file: ```{ .sh .copy } dblab --host localhost --user postgres --pass password --ssl enable --port 3306 --driver mysql --limit 50 --ssh-host example.com --ssh-port 22 --ssh-user root --ssh-key my_ssh_key --ssh-key-pass passphrase ``` MySQL connection using the url parameter via SSH tunnel using a password: ```{ .sh .copy } dblab --url "mysql://myuser:5@klkbN#ABC@mysql+tcp(localhost:3306)/mydb" --driver mysql --ssh-host example.com --ssh-port 22 --ssh-user root --ssh-pass root ``` ### Configuration Entering these flags every time is tedious, so `dblab` provides a couple of flags to help with it: `--config` and `--cfg-name`. `dblab` is going to look for a file called `.dblab.yaml`. Currently, there are three places where you can drop a config file: - $XDG_CONFIG_HOME ($XDG_CONFIG_HOME/.dblab.yaml) - $HOME ($HOME/.dblab.yaml) - . (the current directory where you run the command line tool) If you want to use this feature, `--config` is mandatory and `--cfg-name` may be omitted. The config file can store one or multiple database connection sections under the `database` field. `database` is an array; previously it was an object only able to store a single connection section at a time. We strongly encourage you to adopt the new format as of `v0.18.0`. `--cfg-name` takes the name of the desired database section to connect with. It can be omitted and its default value will be the first item in the array. As of `v0.21.0`, SSL connection options are supported in the config file. ```sh # default: test $ dblab --config $ dblab --config --cfg-name "prod" ``` #### Key bindings configuration Key bindings can be configured through the `.dblab.yaml` file. There is a field called `keybindings` where key bindings can be modified. By default, the keybindings are not loaded, so you need to use the `--keybindings` or `-k` flag to load them. Bindings are grouped by the part of the UI they belong to, so every panel can be rebound independently of the others: | Section | What it controls | | --------- | ------------------ | | `keybindings` (top level) | `help`, `qu