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 Responses to “Efficient SQL console”

  1. Jon Wood says:

    That looks really powerful – especially the diagramming from foreign keys. Any idea of when there’s going to be a release of this?

  2. vivien says:

    This will be part of the next Libgda’s V4 beta (in a few days probably).

  3. SF says:

    Try .tables for sqlite3 table list ;-)

  4. […] Vote Efficient SQL concole […]

  5. Damien says:

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

  6. alex says:

    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…

  7. vivien says:

    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…

  8. vivien says:

    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 ;)

  9. leifer says:

    very nice!

  10. Nemesis says:

    Are you going to write a php frontend next? Maybe you can call it phpDbAdmin.

  11. vivien says:

    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…