Database Interaction

Efficient interaction, efficient storage, and efficient processing are the three key properties of a successful database platform. In this article, we explore the first: efficient interaction.

Interaction Category 1: Command Line Clients

Many database platforms are shipped with a simple command line utility that allows the user to interact with the database. PostgreSQL ships with psql, which gives the user extensive control over the operation of the database and over the tables and schema in the database. Oracle’s SQLPlus and MySQL’s MySQL are similar utilities. Collectively, these are also called SQL shells.

Interaction Category 2: GUI Clients

Another popular way to interact directly with a database is by using a graphical user interface (GUI) that connects to the database server. Oracle’s proprietary SQL Developer software is one of these, although for every database on the market, there are probably at least two or three good, free GUI packages available. Figure 2 shows the “object browser” in pgAdmin III, a free administration tool for PostgreSQL databases.

Interaction Category 3: Application Development

The final method for interacting with a database is through an application. This indirect interaction might occur, for example, when a bank customer is withdrawing money from an ATM. The customer only presses a few buttons and walks away with cash, but the software running on the ATM is communicating with the bank’s database to execute the customer’s transaction. Applications that need to interact with databases can be written in nearly all programming languages, and almost all database platforms support this form of interaction.

Command Line Clients

A command line client usually provides the most robust functionality for interacting with a database. And, because they are usually developed by the same people who developed the database platform, command line clients are typically also the most reliable. On the other hand, effectively using a command line client to its full extent requires expert database skill. The “help” features of command line clients are often not comprehensive, so figuring out how to perform a complex operation may require extensive study and reference on the part of the user. Some basic usage of the PostgreSQL command line client is shown in Figure 1.

All command line clients operate in a similar manner to that shown in Figure 1. For users with extensive knowledge of SQL, these clients are used frequently.

One typically accesses an SQL command line client by logging into the database server and running them from the shell prompt of a UNIX-like operating system. Logging into the server may be achieved via telnet or, preferably, SSH. In a large company, the Information Technology department may have a preferred application for these purposes.

Next Page: GUI Clients