Create and View Databases

In the previous chapter we talked about how schema and data work together to provide us with structured data with which we can interact in various, useful ways. In this section we're going to focus on schema, and how it provides the structure to house our data.

So far we've only very briefly discussed schema, and that's been mostly in terms of tables and table columns. We'll look at tables more closely in the next couple of chapters; before we can work with tables, however, we need somewhere for the tables to exist: we need to create the database itself.

The database is also part of the schema. We can perhaps think of it in terms of the foundations and the 'outer shell' of a building, with the tables in the database being various rooms. The rooms are perhaps different sizes, have different things in them, and some of them might be connected to each other, but they all exist within the same building.

In this chapter, you will create your first database. Recall that there are three SQL sub-languages: Data Definition Language, Data Manipulation Language, and Data Control Language. We will be using Data Definition Language, or DDL, to create our database, since this deals with setting up the structure, or schema, of a database.

As the name implies, Data Definition Language is focused on defining the characteristics of the database and its tables and columns. This includes creating databases and tables, or altering and changing finer details about your database, such as table names and column data types. But it does not deal with the data within a database.

Follow along the below instructions step by step to work through the meta-commands and SQL statements provided.

Create a database

If you're currently in the psql console (e.g. from working through the tutorial in the previous chapter), then type in \q and hit 'enter' to return to the terminal.

Let's create a new database. We'll name it sql_book and use the createdb command to create it:

$ createdb sql_book

Now that our new sql_book database has been created, we should be able to connect to it via the psql console. We'll use the psql command, passing it the -d option with the database name sql_book:

$ psql -d sql_book

This opens the psql console and connects to the database specified by the -d option.

If you see the prompt below (ignoring the version number, which may be different), then everything is working as intended:

psql (9.5.3)
Type "help" for help.

sql_book=#

Notice that the prompt in the psql console is the database that we are connected to.

We can also see that a new database has been added to our current list of databases by using the \list meta-command. Let's try that now.

sql_book=# \list
                                List of databases
Name           |   Owner   | Encoding |   Collate   |    Ctype    |    Access privileges
---------------+-----------+----------+-------------+-------------+-------------------------
postgres       |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
sql_book       |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0      |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/User            +
               |           |          |             |             | User=CTc/User
template1      |   User    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | User=CTc/User      +
               |           |          |             |             | =c/User
(4 rows)

After you install PostgreSQL, you'll start out with four databases: template0, template1, postgres, and a database for the currently logged in user. And the output above shows, whenever you create a new database it will be added to the running list of databases.

Using a SQL Statement

We created our sql_book database from the terminal using the PostgreSQL client application createdb. In an earlier chapter we explained that some PostgreSQL client applications essentially act as 'wrappers' around actual SQL syntax; createdb is one of these. Using createdb can offer us a nice shortcut, but the same operation can be accomplished using the SQL statement CREATE DATABASE. Remember that utility functions are executed from the terminal, while SQL statements are executed from within the psql console.

For example, if we wanted to create another database from the psql console, we could do so with an SQL statement like this:

sql_book=# CREATE DATABASE another_database;
CREATE DATABASE
sql_book=#

On the first line we are using the CREATE DATABASE SQL command with another_database as the 'name' parameter for that command. Note that we terminate our statement with a semi-colon ; in order to tell PostgreSQL that the command ends here and that it should now execute the command.

The CREATE DATABASE (without the prompt) on the second line is the response returned by PostgreSQL to let us know that is has executed the statement successfully. Our prompt is again available on the third line, ready for us to issue another command.

Convention: Uppercase Commands, Lowercase names

Throughout the book, we use the convention of uppercase for SQL statements and lowercase names for tables and databases. This convention is for clarity only and is used by many SQL developers, but SQL itself is not case-sensitive. For example, we will write:

CREATE DATABASE my_database;

instead of:

create database my_database;

even though both options work. This is a pretty common convention, so we suggest you follow along with this guideline.

Use the CREATE DATABASE command to create one more database called yet_another_database in the same way. Once you have done this, use the list meta-command to check that both another_database and yet_another_database have been added to your list of databases.

When creating our another_database and yet_another_database databases, we only passed a single parameter, 'name' to the CREATE DATABASE command. This parameter is mandatory. It is worth noting that there are other, optional, parameters which set things such as the database's encoding, collation, connection limit, and so on. These other parameters are beyond the scope of this book; when ommited, PostgreSQL simply uses the default settings for these parameters.

Database naming

There are some conventions you should be aware of when naming a database. As a guideline, always try to keep database names self-descriptive. A descriptive name is especially helpful if you end up having a lot of databases. A database containing information about Employees could be named 'employees' or 'employee_database'. A less descriptive name might be 'emp' or 'records'. Also, database names should be written in snake_case, that is, lowercase with words separated by underscores.

Connecting to a Database

In order to be able to issue commands to a database we need to be connected to it. At the start of this chapter, we saw that from terminal we can open the psql console and connect to a database using the psql command with the -d option and a database name.

When we are in the psql console itself, we can connect to a different database by using the \c or \connect meta-commands (these both do the same thing). Let's try this out.

We should currently be connected to the sql_book database that we connected to earlier. Let's now use the \c meta-command connect to the another_database database you created:

sql_book=# \c another_database
You are now connected to database "another_database" as user "User".
another_database=#

On the first line we were connected to the sql_book database, as can be seen by the prompt. We used the \c meta command, passing it another_database as the 'dbname' argument.

The output on the second line is simply informing us that we have successfully connected to the database another_database. When we open a connection to a anther database in this way, the previous connection is closed. We are now connected to another_database and not sql_book.

Notice how the prompt on the third line has now changed to the name of the database we are now connected to. Also notice that meta-commands, unlike SQL statements, do not have to be terminated with a semi-colon.

The \c and \connect meta-commands can take arguments other than 'dbname', such as 'username', 'host', 'port', etc. When ommited, the command reuses the values from the previous connection. If connecting to a locally installed database you can generally omit these other arguments entirely.

The pieces of connection information represented by these arguments aren't specific to the \c and \connect meta-commands, or even to PostgreSQL. This type of connection information is generally required whichever interface you are using to connect to a database, particulary when connecting to databases that are hosted remotely, or on a different server to the application or system that is connecting to that database.

Let's now disconnect from another_database and connect to yet_another_database instead. Use the \c or \connect meta-commands to connect to the yet_another_database database.

Your command prompt should now look like this:

yet_another_database=#

Delete the Database

Occasionally you're going to want to delete a database. We can use the SQL command DROP DATABASE to do this. The syntax is pretty much the same as that for CREATE DATABASE; the command, followed by the name of the database we want to delete, followed by a semi-colon. Let's use the DROP DATABASE to delete the another_database database:

yet_another_database=# DROP DATABASE another_database;
DROP DATABASE
yet_another_database=#

The first line above is our SQL statement. The DROP DATABASE (without the prompt) on the second line is the response returned by PostgreSQL to let us know that is has executed the statement successfully. Our prompt is again available on the third line, ready for us to issue another command.

Using dropdb

In the same way that createdb is a PostgreSQL client application that acts as a wrapper for the CREATE DATABASE SQL command, dropdb performs the same role for the PostgreSQL has a special command, dropdb DROP DATABASE SQL command.

To use dropdb, we need to issue the command from terminal rather than the psql console. Use \q to quit the psql console and return to terminal. From terminal, let's use dropdb to delete the yet_another_database database.

$ dropdb yet_another_database

Caution The DROP DATABASE and dropdb commands should be used with extreme care, as their actions are permanent and cannot be reversed. When these commands are issued, all data and schema related to the database is deleted. It's best to take a minute and think carefully before issuing these commands.

Re-open the psql console and connect to the sql_book database by using the psql command:

$ psql -d sql_book

If you use the \list meta-command now, you should see that another_database and yet_another_database are no longer in the list of databases.

Summary

We're just dipping our toes in, but we already have a small handful of commands, so let's quickly recap. First we have some commands that can be used within a psql session:

PSQL Command Notes
\l or \list displays all databases
\c sql_book or \connect sql_book connects to the sql_book database
\q db_name exits the PostgreSQL session and return to the command-line prompt

We also have some commands that are programs installed by PostgreSQL on our system:

Command-line Command Notes
psql -d sql_book starts a psql session and connect to the sql_book database
createdb sql_book creates a new database called sql_book using a psql utility
dropdb my_database permanently deletes the database named my_database and all its data

Remember, that some of the utilities we've shown such as createdb and dropdb are wrapper functions for actual SQL statements:

SQL Statement Notes
CREATE DATABASE sql_book creates a new database called sql_book
DROP DATABASE my_database permanently deletes the database named my_database and all its data

In the next chapter, we will create a table and go into more specific details about DDL and altering our database structure, or database schema.