LaunchSchool: An online school for Software Engineers

Interacting With a Database

There are many interfaces, or clients, that you can use to interact with a RDBMS such as PostgreSQL. You may access the database from a programming language, through a GUI application that allows access and administration, or through the command-line interface. One thing these interfaces all have in common is the underlying architecture they use to interact with the database: they issue a request, or declaration, and receive a response in return. This is generally referred to as "client-server" architecture. PostgreSQL is a "client-server" database, which is an architecture design used by most relational databases.

If we think about this image of the sushi restaurant a little further, we can imagine that there might be numerous ways of submitting your sushi order, depending on the restaurant. You could for example give your order to a waiter who writes it down and then passes it on to the chef, or you could speak to the chef directly; certain restaurants might have some sort of touchscreen system which lets you select which sushi you want and this information then gets relayed to the chef. These different ordering methods are essentially different layers of abstraction within the same basic model.

Similarly, whichever type of PostgreSQL client you choose to use, that interface is basically an abstraction; ultimately they all work by issuing queries to the database, using SQL syntax. Depending on the client, that SQL syntax may be 'wrapped' in some other commands or even abstracted altogether by some sort of visual interface, but underneath it all the same thing is essentially happening.

In this book, we will use the command-line interface. We think that by using the command line-interface you'll develop a stronger understanding of database fundamentals and SQL. Don't worry if you've never used a command-line interface before -- it's not as scary as you think. In this book we'll walk you through interacting with PostgreSQL using the command-line, though if you're a complete command-line novice you may benefit from first reading our Introduction to the Command Line book.

PostgreSQL Client Applications

PostgreSQL comes packaged with a number of what are called 'client applications'. These client applications are used to interact with PostgreSQL in various ways by issuing a command via the command line. Some commonly used PostgreSQL client applications are createdb, dropdb, pg_dump, pg_restore, and pg_bench. Some of these client applications are essentially wrappers around SQL commands; for example createdb, which can be used to create a new PostgreSQL database, is a wrapper around the SQL command CREATE DATABASE.

One client application that we will be using throughout this book is psql. psql is a PostgreSQL interactive console, or a terminal-based front-end to PostgreSQL. It allows you to write queries in SQL syntax, issue those queries to a PostgreSQL database, and see the results of those queries in the terminal window. In that sense the psql console is essentially a REPL; you may already be familiar with other REPLs, such as IRB.

An important thing to remember about commands such as createdb, dropdb, etc is that you call them from your terminal, and not from within the psql prompt.

The psql console

In your terminal, issue the following command.

$ psql postgres

$ sudo -u postgres psql postgres

This is basically telling PostgreSQL to open the psql console and connect to the database called 'postgres' (note: 'postgres' is a default database that is created when you install PostgreSQL). You should see a response like the below code block. Your postgres version number may be different, but that shouldn't make a difference for this book.

psql (9.5.8)
Type "help" for help.

postgres=#

You are now connected to the database called 'postgres' and can issue commands to it. Notice that the prompt is now the database name followed by = and #, which is distinctly different to the usual command line prompt of $.

One thing to be aware of is that PostgreSQL is more tightly coupled to the UNIX environment than some other database systems. It uses the native user accounts to determine who is connecting to it (by default). Many of the commands that you may need to run are available as executable programs that can be run from anywhere on the machine the database is running on, assuming that the user running them has the correct permissions.

There are two different types of commands you can issue from the psql console prompt:

1. You can issue psql console meta-commands
2. You can run SQL statements using standard SQL syntax

Meta-commands

The syntax for a psql console meta-command is a backslash \ followed by the command and any optional arguments. One example would be \conninfo; try running that command from the psql console prompt. You should receive a response detailing any relevant connection information for your current connection to the database.

Meta-commands can be used for a number of different things, such as connecting to a different database, listing tables, describing the structure of a particular table, setting environment variables, and so on.

We'll look at some examples of meta-commands later in this book. For now, one useful command to remember is \q, which quits the psql console and returns to the normal command line. Make sure to note this down, otherwise you'll be stuck inside the psql console.

SQL Statements

SQL statements are commands issued to the database using SQL syntax. A simple statement might look something like this:

SELECT name FROM people WHERE id = 1;

SQL statements always terminate in a semi-colon. This means that you can actually write statements over multiple lines; PostgreSQL will not execute the statement until it reaches the semi-colon. The above example could have been written in the following way:

SELECT name
       FROM people
       WHERE id = 1;

A SELECT statement is used to retrieve data from a database. Whichever way you write it, the response in the psql console would look something like this:

  name
---------
 Michael
(1 row)

(Note: the above code is for example purposes only. We haven't created a people table, so these queries won't work if you try to run them).

In the next chapter, we'll look a little bit more at what some of the different parts of a SELECT statement like this mean. Before we break-down the syntax of the language in detail, let's briefly outline some higher-level distinctions.

SQL Sub-languages

SQL can be thought of as comprising of three separate sub-languages, each concerned with a specific aspect of manipulating or interacting with a database. The three sub-languages are:

• DDL: Data Definition Language. Used to define the structure of a database and the tables and columns within it.
• DML: Data Manipulation Language. Used to retrieve or modify data stored in a database. SELECT queries are part of DML.
• DCL: Data Control Language. Used to determine what various users are allowed to do when interacting with a database.

This book is concerned with looking at the first two sub-languages in this list, DDL and DML.

Summary

In this chapter we've looked at a number of different ways of interacting with PostgreSQL. We've explained that you can access a database using a programming language, or a GUI application, and then focused specifically on interacting with Postgres via the Command Line in various different ways:

• Using PostgreSQL Client Applications
• Using one of those Client Applications, psql, to:
  • Run psql console meta-commands
  • Issue SQL statements

The remainder of this book will be mainly focused on SQL statements. In the next chapter we'll run through a few simple examples of a sub-set of these statements in the form of SELECT queries.