Interactive terminal user interface and CLI for database connections. MySQL, PostgreSQL. More to come.

🗄 dbui

dbui is the terminal user interface and CLI for database connections.

dbui screenshot

It provides features like,

  • Connect to multiple data sources and instances.
  • List all schemas in a selected data source.
  • List all tables in a selected schema.
  • Preview a selected table.
  • Execute custom SQL queries on a selected table or schema.
  • User-friendly UI features like,
    • query execution status,
    • warning and error messages,
    • full-screen and focus modes,
    • mouse support.

Runs on Mac, Linux, and Windows.

Linux

dbui is available as downloadable binaries from the releases page. Download the .deb or .rpm from the releases page and install with sudo dpkg -i and sudo rpm -i respectively.

Currently supported databases

  • MySQL
  • PostgreSQL
  • SQLite (soon)

What's next?

  • Auto-generate SQL Queries for Insert, Update, Delete.
  • Save frequently used SQL Queries.
  • Configurable keyboard layout.
  • Autocomplete for SQL Queries.

Current status

  • 🚧 Under development, expect bugs, errors, and unexpected crashes.
  • 🐞 Bug and error report very well appreciated.
  • 📣 I highly recommend to check for periodic updates.
Known Issues
  • ❗️ Critically low amount of tests.
  • ⚠️ Having multiple connections and many queries triggers a high CPU load. Now when this PR merged, as a next step, I will work on profiling the application.

Demo

Step 1. Install

brew tap kenanbek/dbui https://github.com/kenanbek/dbui
brew install dbui

Other installation options. If you have installed the first versions of dbui, you might need to un-tap the old cask URL. Check this for more information.

Step 2. Run demo databases

docker run -d --name dbui-mysql-demo -p 3316:3306 -e MYSQL_ROOT_PASSWORD=demo genschsa/mysql-employees
docker run -d --name dbui-postgresql-demo -p 5432:5432 ghusta/postgres-world-db:2.4-alpine

Step 3. Create dbui.yml configuration file

Create dbui.yml file in the current folder with the following content:

dataSources:
  - alias: employees
    type: mysql
    dsn: "root:demo@(localhost:3316)/employees"
  - alias: world-db
    type: postgresql
    dsn: "user=world password=world123 host=localhost port=5432 dbname=world-db sslmode=disable"
defaut: employees

More about configuration files.

Step 4. Start dbui

dbui

Table of Contents

Usage

Install

Option 1: Brew

brew tap kenanbek/dbui https://github.com/kenanbek/dbui
brew install dbui

If you have installed the first versions of dbui, you might need to un-tap the old cask URL. Check this for more information.

Option 2: Go Get

go get github.com/kenanbek/dbui

Option 3: Source Code (+demo databases)

git clone https://github.com/KenanBek/dbui.git
cd dbui
make demodbs # runs docker containers with mysql and postgresql demo dbs

# you can destroy them by executing
make demodbs/destroy

Copy configuration and run:

touch dbui.yml # check below for the content
make run

Copy/paste the configuration provided below for demo databases.

Configuration

By default dbui uses configuration file (dbui.yml).

dataSources:
  - alias: employees
    type: mysql
    dsn: "root:demo@(localhost:3316)/employees"
  - alias: world-db
    type: postgresql
    dsn: "user=world password=world123 host=localhost port=5432 dbname=world-db sslmode=disable"
defaut: employees

First, it checks in the current directory, then in the user's home directory.

All provided database connections will be available in the application, and you can switch among them without restarting the application.

Alternatively, it is possible to start dbui for a single database connection using a DSN (data source name) and type arguments.

$ dbui -dsn <connection string> -type <data source type>

# example for a mysql connection
$ dbui -dsn "codekn:codekn@(localhost:3306)/codekn_omni" -type mysql

Configuration Order

# when
$ dbui
# read from `./dbui.yml` if not then from `~/dbui.yml`

# when
$ dbui -f /my/custom/dir/mydbui.yml
# read from custom directory `/my/custom/dir/mydbui.yml`

# when
$ dbui -dsn "<connection-string>" -type mysql
# init single connection mode

Default Keyboard Layout

dbui keyboard hot keys

Focus Hot Keys

  • Ctrl-A - sources
  • Ctrl-S - schemas
  • Ctrl-D - tables
  • Ctrl-E - preview
  • Ctrl-Q - query

Special

  • Tab - navigate to the next element
  • Shift-Tab - navigate to the prev element
  • Ctrl-F - toggle focus-mode
  • Ctrl-C - exit

Table Specific

Use these keys when the tables panel is active:

  • e - describe selected table
  • p - preview selected table (works as ENTER but does not change focus)

Preview Specific

Use these keys when the data preview panel is active:

  • y - copy a selected row into the clipboard (coming soon).

Contribution

The code and its sub-packages include various form of documentation: code comments or README files. Make sure to get familiar with them to know more about internal code structure. This section includes references to additional READMEs.

DBUI is a work in progress, and any feedback/contributions are more than welcome.

The backlog can be viewed on GitHub Issues page.

References

This project inspired by k9s , lazydocker, and htop.

I use tview terminal UI for the interactive user interface:

For test purposes, I use these Docker-based demo databases:

Comments
  • Linter

    Linter

    Try to fix linter errors and introduce the lint step in the build action. Make it a mandatory step for each new PR.

    I mainly focused on Interface definitions. For the repeating parts like MySQL and PostgreSQL implementations of DataSource interface, added Abc exported comments to make the linter happy.

    Once merged, direct commit's to the main repo can be restricted. TBD.

  • Can't go get it.

    Can't go get it.

    Hi there, I've got a small issue when I try to install it.

    ❯ go get github.com/KenanBek/dbui
    go: downloading github.com/KenanBek/dbui v0.1.2
    go get: github.com/KenanBek/dbui@none updating to
            github.com/KenanBek/[email protected]: parsing go.mod:
            module declares its path as: dbui
                    but was required as: github.com/KenanBek/dbui
    
  • conf layer (yaml and single conn mode)

    conf layer (yaml and single conn mode)

    usages:

    # when
    $ dbui
    # read from ~/dbui.conf (yaml conf)
    
    # when
    $ dbui -f ./mydbui.conf
    # read from mydbui.conf in the cd (yaml conf)
    
    # when
    $ dbui -dsn "<connection-string>"
    # init single connection mode
    

    nice example for yaml: https://github.com/satellity/satellity/blob/master/internal/configs/config.go

  • create, recreate, destroy demo databases with makefile target

    create, recreate, destroy demo databases with makefile target

    For the demo and testing purposes add makefile target to create a few MySQL and PostgreSQL databases running in docker with exposed ports (different port numbers)

    Examples:

    alias: tiger
    type: MySQL
    
    alias: lion
    type: postgresql
    
    alias: wolf
    type: postgresql
    
  • fix[homebrew-formulae]: error on install:

    fix[homebrew-formulae]: error on install: "formulae require at least a URL"

    Describe the bug The formulae doesn't install. The full error is:

    Error: formulae require at least a URL 
    /opt/homebrew/Library/Homebrew/formula.rb:283:in `determine_active_spec'
    /opt/homebrew/Library/Homebrew/formula.rb:214:in `initialize'
    /opt/homebrew/Library/Homebrew/formulary.rb:308:in `new'
    /opt/homebrew/Library/Homebrew/formulary.rb:308:in `get_formula'
    /opt/homebrew/Library/Homebrew/formulary.rb:561:in `factory'
    /opt/homebrew/Library/Homebrew/cli/parser.rb:634:in `block in formulae'
    /opt/homebrew/Library/Homebrew/cli/parser.rb:630:in `map'
    /opt/homebrew/Library/Homebrew/cli/parser.rb:630:in `formulae'
    /opt/homebrew/Library/Homebrew/cli/parser.rb:307:in `parse'
    /opt/homebrew/Library/Homebrew/cmd/install.rb:147:in `install'
    /opt/homebrew/Library/Homebrew/brew.rb:97:in `<main>'
    

    To Reproduce

    1. Open any terminal on macOS
    2. Run brew tap kenanbek/dbui && brew install dbui

    Expected behavior The formulae should install dbui.

    Screenshots If applicable, add screenshots to help explain your problem.

    Environment (please complete the following information):

    • OS Darwin/macOS
    • Terminal n/a
    • Version 13.1

    Additional context Add any other context about the problem here.

  • db#systemlist crashes with Argument of filter() must be a List, Dictionary or Blob

    db#systemlist crashes with Argument of filter() must be a List, Dictionary or Blob

    Describe the bug

    When trying to connect to a DB by hitting enter on the list item, I get this error:

    Error detected while processing function <SNR>115_method[2]..47[30]..50[10]..51[5]..55[6]..db_ui#schemas#query:
    line    1:
    E118: Too many arguments for function: db#systemlist
    Error detected while processing function <SNR>115_method[2]..47[30]..50[10]..51[5]..55[6]..<lambda>35:
    line    1:
    E896: Argument of filter() must be a List, Dictionary or Blob
    Error detected while processing function <SNR>115_method[2]..47[30]..50[10]..51[5]..55[6]..<lambda>35[1]..<SNR>113_results_parser:
    line    2:
    E896: Argument of filter() must be a List, Dictionary or Blob
    Press ENTER or type command to continue
    

    The list item does not expand. But if I close DBUI and open it again, the list item shows up as opened and I see + New Query and the other menu items.

    To Reproduce

    • Run :DBUI
    • Select a database
    • Hit enter

    Expected behavior

    • Error does not show up
    • Database menu opens up immediately without me having to close and reopen :DBUI

    Screenshots

    image

    Environment (please complete the following information):

    • OS: Linux, Debian 11.5, on kernel 5.10.0-19-amd64
    • Terminal: Alacritty 0.12.0-dev
    • DBUI Version: 0.7.0
    • Neovim Version: 0.8.1
  • Auto select schema

    Auto select schema

    Describe the bug When choosing a different database, schema should be automatically selected

    To Reproduce Steps to reproduce the behavior: image

    Expected behavior Should select the schema and show the tables for that selected database.

  • Timeout for long running queries

    Timeout for long running queries

    Is your feature request related to a problem? Please describe.

    Queries on big tables without LIMIT statement takes a long time and it looks like the application is hanging.

    Describe the solution you'd like

    Introduce a timeout parameter. If the execution takes longer than the timeout app raises an error.

WAL-G is an archival restoration tool for PostgreSQL, MySQL/MariaDB, and MS SQL Server (beta for MongoDB and Redis).

WAL-G is an archival restoration tool for PostgreSQL, MySQL/MariaDB, and MS SQL Server (beta for MongoDB and Redis).

Jan 1, 2023
Go-Postgresql-Query-Builder - A query builder for Postgresql in Go

Postgresql Query Builder for Go This query builder aims to make complex queries

Nov 17, 2022
Database wrapper that manage read write connections

rwdb Database wrapper that manage read write connections Install go get github.com/andizzle/rwdb Create connections package main import "github.com/

Dec 10, 2022
mysql to mysql 轻量级多线程的库表数据同步

goMysqlSync golang mysql to mysql 轻量级多线程库表级数据同步 测试运行 设置当前binlog位置并且开始运行 go run main.go -position mysql-bin.000001 1 1619431429 查询当前binlog位置,参数n为秒数,查询结

Nov 15, 2022
A Go rest API project that is following solid and common principles and is connected to local MySQL database.
A Go rest API project that is following solid and common principles and is connected to local MySQL database.

This is an intermediate-level go project that running with a project structure optimized RESTful API service in Go. API's of that project is designed based on solid and common principles and connected to the local MySQL database.

Dec 25, 2022
A simple Golang-based application that queries a PostgreSQL database

Qwik-E-Mart Demo App A simple Golang-based application that queries a PostgreSQL database named qwikemart to read and return customer data stored in t

Nov 6, 2021
📊 parallel black box PostgreSQL unit tests run against a real database.
📊   parallel black box PostgreSQL unit tests run against a real database.

?? psql-docker-tests-example Parallel black box PostgreSQL unit tests run against a real database. Consider reading the Medium Story first. This packa

Sep 15, 2022
CRUD API example is written in Go using net/http package and MySQL database.
CRUD API example is written in Go using net/http package and MySQL database.

GoCrudBook CRUD API example is written in Go using net/http package and MySQL database. Requirements Go MySQL Code Editor Project Structure GoCrudBook

Dec 10, 2022
A proxy is database proxy that de-identifies PII for PostgresDB and MySQL

Surf Surf is a database proxy that is capable of de-identifying PII and anonymizing sentive data fields. Supported databases include Postgres, MySQL,

Dec 14, 2021
Library for scanning data from a database into Go structs and more

scany Overview Go favors simplicity, and it's pretty common to work with a database via driver directly without any ORM. It provides great control and

Jan 9, 2023
Vitess is a database clustering system for horizontal scaling of MySQL.

Vitess Vitess is a database clustering system for horizontal scaling of MySQL through generalized sharding. By encapsulating shard-routing logic, Vite

Jan 3, 2023
Dumpling is a fast, easy-to-use tool written by Go for dumping data from the database(MySQL, TiDB...) to local/cloud(S3, GCP...) in multifarious formats(SQL, CSV...).

?? Dumpling Dumpling is a tool and a Go library for creating SQL dump from a MySQL-compatible database. It is intended to replace mysqldump and mydump

Nov 9, 2022
Vitess is a database clustering system for horizontal scaling of MySQL.

Vitess Vitess is a database clustering system for horizontal scaling of MySQL through generalized sharding. By encapsulating shard-routing logic, Vite

Jan 4, 2023
A MySQL-compatible relational database with a storage agnostic query engine. Implemented in pure Go.

go-mysql-server go-mysql-server is a SQL engine which parses standard SQL (based on MySQL syntax) and executes queries on data sources of your choice.

Jan 2, 2023
Golang restAPI crud project with mySql database.
 Golang restAPI crud project with mySql database.

Golang RestAPI using gorilla/mux Golang restAPI crud project with mySql database. Test Api with Thunder Client vs code beautiful Extension. and use Be

Mar 26, 2022
Go-postgres - go-postgres library provide NoSQL functionality which can execute queries with pool of connections

GO Postgres go-postgres library provide NoSQL functionality which can execute queries with pool of connections. What is this repository for? Establish

Dec 31, 2021
Use SQL to instantly query users, groups, applications and more from Okta. Open source CLI. No DB required.
Use SQL to instantly query users, groups, applications and more from Okta. Open source CLI. No DB required.

Okta Plugin for Steampipe Use SQL to query infrastructure including users, groups, applications and more from Okta. Get started → Documentation: Table

Nov 10, 2022
Use SQL to instantly query instances, networks, databases, and more from Scaleway. Open source CLI. No DB required.
Use SQL to instantly query instances, networks, databases, and more from Scaleway. Open source CLI. No DB required.

Scaleway Plugin for Steampipe Use SQL to query infrastructure servers, networks, databases and more from your Scaleway project. Get started → Document

Nov 16, 2022