Being working with many DB back-ends I gradually learn pros/cons of every single DB interactive interface, including both CLI and GUI ones. I was always wondering, it would be cool if I’ll place limit as MySQL does in ORACLE, or have nice table layout of MySQL or simple switch to various outputs as SQLite does. Be honest I never considered GUI as best interface to perform simple tedious tasks, such as select statements. At the same time being UNIX user I always admit that UNIX K.I.S.S. principle is a way to go almost everywhere. So, those two ideas are met in my head and db-shell project was born.

Make it simple, intuitive, easy to discovery (not remember) and work smoothly with various DB back-ends. DB shell should be transparent to DB back-ends, its location (local or remote) and user friendly. By that, I mean, help users to see data rather then remember how to get this data. Simple things, such as, aliases, multiple connections, migration, history, etc. must be present in system design and supported from the beginning.

Among all programming languages python strength is simplicity and flexibility. As a first prototype for dbsh it fits really well. The two additional tools, IPython (interactive python shell) and SQLAlchemy (a SQL toolkit and ORM tool) provide necessary components (layers) to make dbsh usable and concentrate on feature development and usability. Below you can see an actual implementation of dbsh.

As any user who are familiar with shell environment would expect that dbsh would provide an friendly environment to access your favorite DB back-end and make it simple to manipulate data over there. Here we outline a few (common) use cases.

* alias function, e.g. alias for select statements

* implement pipe and redirect, e.g.

   select * from table | less

   or

   select * from table > file.sql

* simple pagination (page, next, prev)

* DB statistics (show how many connections, which DBs)

* plot query (it can be handy for some simple data visualization)

* integrated development environment, allow users to write python functions and execute them from the db shell

* redirection input/output

* schema analyzer

* query builder

* query history

* Formatted output:

  - stdout (txt, csv, one-line output, tabs)

  - XML  (for interprocess communications)

  - HTML (for web extensions)

* schema converter and migration tool (from one DB back-end to another)

* check schema normalization

A connection to DB back-end is provided via standard python DB API interface which is implemented via concrete DB python module, e.g. pysqlite for SQLite DB. The SQLAlchemy toolkit provides all necessary tools for mapping DB tables onto python objects and simplify their usage. The IPython framework at the same time provides robust framework for dbsh via it’s set of magic functions.