Navigator

Features

• Shows databases of configured connections
• Shows tables of databases
• Shows columns of tables for restricting rows
• Shows rows of tables with multiple restrictions (operators: =, !=, >, <, >=, <=, like, in)
• Shows detailed row information
• Shows info of foreign table row (based on the foreign key)
• Switch to the foreign table row (forward references)
• Shows foreign keys that point to the current table row (back references)
• Configuration of what is shown based on table comments (currently PostgreSQL only)

Table of Contents

• Features
• Usage
• Filtering
• Examples
  • Show Available Connections
  • Show Databases of Connection
  • Show Tables of Database
  • Show Columns of Table
  • Show Rows where Column equals Value
  • Show Rows where multiple Columns equals Value
  • Show Rows where Column matches Pattern
  • Show Rows where Column is in List
  • Show Rows where any (Search) Column matches Pattern
  • Show Values of selected Row

Usage

usage: dbnav [-h] [--version] [--trace] [--debug] [--info] [--warning]
             [--error] [--critical] [-L LOGFILE] [--daemon]
             [--daemon-host HOST] [--daemon-port PORT] [-T] [-D] [-S] [-J]
             [-A] [-s] [-N] [-m LIMIT]
             [uri]

A database navigation tool that shows database structure and content

positional arguments:
  uri                   the URI to parse (format for PostgreSQL/MySQL:
                        user@host/database/table?filter; for SQLite:
                        databasefile.db/table?filter)

optional arguments:
  -h, --help            show this help message and exit
  --version             show program's version number and exit
  -s, --simplify        simplify the output
  -N, --no-simplify     don't simplify the output
  -m LIMIT, --limit LIMIT
                        limit the results of the main query to this amount of
                        rows (default: 50)

logging:
  --trace               set loglevel to trace
  --debug               set loglevel to debug
  --info                set loglevel to info
  --warning             set loglevel to warning
  --error               set loglevel to error
  --critical            set loglevel to critical
  -L LOGFILE, --logfile LOGFILE
                        the file to log to

daemon:
  --daemon              use a background process to speed up requests
  --daemon-host HOST    the host of the daemon
  --daemon-port PORT    the port of the daemon

formatters:
  -T, --test            output format: test specific
  -D, --default         output format: default
  -S, --simple          output format: simple
  -J, --json            output format: JSON
  -A, --autocomplete    output format: autocomplete

Contact:
  If you experience bugs or want to request new features please visit
  <https://github.com/resamsel/dbnavigator/issues>

In Alfred the keyword is dbnav. The query after the keyword is the URI to your data. No options may be given.

Filtering

The returned results can be restricted by using filters, which are mostly statements going into the SQL where clause. It is described in Filtering.

Examples

Show Available Connections

dbnav

Show Databases of Connection

dbnav myuser@myhost/

Show Tables of Database

dbnav dbnav.sqlite/

Title	Subtitle
_comment	Table
address	Table
article	Table
blog	Table
blog_user	Table
sqlite_sequence	Table
user	Table
user2	Table
user_address	Table

Show Columns of Table

dbnav dbnav.sqlite/user?

Title	Subtitle
company	user
email	user
first_name	user
gender	user
id	user
last_name	user
phone	user
url	user
username	user

Show Rows where Column equals Value

dbnav dbnav.sqlite/user?first_name=Joshua

Title	Subtitle
Joshua	jburtonv (id=32)
Joshua	jalexander80 (id=289)
Joshua	jpalmer8u (id=319)
Joshua	jfernandezc8 (id=441)

Show Rows where multiple Columns equals Value

When using the ampersand (&) in a shell make sure to escape it (prepend it with a backslash () in Bash), since it has a special meaning there.

dbnav dbnav.sqlite/user?first_name=Joshua&last_name=Alexander

Title	Subtitle
Joshua	jalexander80 (id=289)

Show Rows where Column matches Pattern

The tilde (~) will be translated to the like operator in SQL. Use the percent wildcard (%) to match arbitrary strings.

dbnav dbnav.sqlite/user?first_name~%osh%

Title	Subtitle
Joshua	jburtonv (id=32)
Joshua	jalexander80 (id=289)
Joshua	jpalmer8u (id=319)
Joshua	jfernandezc8 (id=441)

Show Rows where Column is in List

The colon (:) will be translated to the in operator in SQL.

dbnav dbnav.sqlite/user?first_name:Herbert,Josh,Martin

Title	Subtitle
Martin	mrichardsonp (id=26)
Martin	mdiaze1 (id=506)

Show Rows where any (Search) Column matches Pattern

dbnav myuser@myhost/mydatabase/mytable?~%erber%

Warning: this is a potentially slow query! See configuration for options to resolve this problem.

Show Values of selected Row

dbnav dbnav.sqlite/user/?id=2

Title	Subtitle
2	user.id
Evelyn	user.first_name
Gardner	user.last_name
user.company
egardner1	user.username
user.email
8-(549)755-1011	user.phone
Female	user.gender
user.url
← article.user_id	article.user_id -> user.id
← blog_user.user_id	blog_user.user_id -> user.id
← user_address.user_id	user_address.user_id -> user.id