Efficient SQL console

I’ve been using SQL consoles for years. Each database has its own SQL console: psql for PostgreSQL, mysql for MySQL, sqlite3 for SQLite3, sqlplus for Oracle,… When one needs information about database objects such as for example the tables, their columns and relations with other tables, one has to remember the syntax to use depending on th SQL console (for example to list the tables: \dt for PostgreSQL, show tables; for MySQL, select name from sqlite_master where type=’table’; for SQLite3).

This can be quite frustrating when using several databases and consoles, and is the reason I decided to write yet another SQL console, part of Libgda which overcomes all these little difficulties. The syntax (and displayed information) is the same for every database Libgda can connect to.

I’ve recently improved it even more by turning the SQL console into a web server to display meta data in a user friendly way. Here is the use case: the user uses the SQL console normally and then decides to start the web server (through a \http command) and then points his web browser to http://localhost:12345 (the port number can also be specified). Here is below the kind of information displayed:

There is at the moment no security around the web server (anyone can connect) but this will be added.

11 thoughts on “Efficient SQL console”

  1. Is the web “console” able to display table inheritance (for instance using a PostregreSQL backend) ?

  2. Take a look at H2; it brings a web console much like yours which can connect to any DB that has a JDBC driver.

    Might give you some ideas…

  3. It can’t yet display table inheritance because Libgda does not know about it. If it did (which is feasable), then displaying that is just a question of asking graphviz to do it…

  4. I’ve used the H2 console but only for simple statement, I’ll look at it for that aspect. Hopefully for an H2 database, the information displayed in its console and in the Libgda’s console will be the same 😉

  5. I don’t have any plan for a PHP frontend or PHP module, but writing a PHP module for Libgda should not be very complicated…

Comments are closed.

Leave a Reply

Your email address will not be published. Required fields are marked *