Don't be afraid of using your chosen database's command-line client.

I might as well say this up front: I don't like using GUI (aka non-command-line or graphical) tools with my databases. This is likely because when I first learned it was with command-line tools, but even so, I think command-line database tools often are the best way to interact with a database manually.

Two of the most popular databases in use on Linux are MySQL and PostgreSQL. Each of them have very useful, if slightly different, command-line clients. If you ever need to move between these two databases, or if you're new to databases in general, a comparison of the two is helpful.

But, because a two-horse race isn't as thrilling as a three-horse one, I wanted to include a third database command-line client in my comparison. I chose SQLite, because it is arguably the most popular database in the world. You probably have several SQLite databases on your local computer right now. The command-line client is nice too.

Also, I use MariaDB instead of MySQL in my examples, because that's what I have installed, and because I like the improvements MariaDB includes in both the command-line client and in the database server. MariaDB and MySQL are very compatible, and my examples are simple, so whenever I mention MariaDB, you can assume it applies to MySQL as well.

To Serve...or Not

PostgreSQL and MariaDB have what is known as a client/server architecture. Clients connect to the server, and although client and server often are installed together and you may think of them as a single entity, they actually are not. The client does not need to be run on the same machine as the server. The MariaDB server is called mysqld, and it always is running while the server is up. Likewise, the PostgreSQL server is called postgres.

SQLite does not have a client/server architecture. There is just the database you are using, which is a local file, and client programs, which can interact with it.

Installation

I won't go into how to install MariaDB, MySQL, PostgreSQL or SQLite3 here. Most distributions have packages for them, and in the case of MariaDB, there are packages for Debian, Ubuntu, Red Hat and a generic Linux binary available from its download page. See the documentation for each and your distribution's documentation for instructions.

On Ubuntu, you can install all three with the following:

sudo apt-get install mariadb-server postgresql sqlite3

Other Linux distributions are just as easy for the most part. (You need to have added the appropriate MariaDB Ubuntu repository for the above to work. Instructions are on the MariaDB downloads page.)

Figure 1. The MariaDB, PostgreSQL and SQLite3 Clients in Action

Basic Client Commands

The client programs for MariaDB, PostgreSQL and SQLite3 are mysql, psql and sqlite3, respectively. I've listed several useful commands for each client in Table 1. The first entry shows the basic command used to connect to a database; however, each client has several options. These include (in the case of MariaDB and PostgreSQL) options for specifying the user, password and database host server. You will need these often, so refer to the man pages for the clients for what they are and how to use them. Some of the commands listed in Table 1 have extended options; refer to the documentation for details.

Table 1. MariaDB/MySQL, PostgreSQL and SQLite Client Cheat Sheet

Task	MariaDB/MySQL	PostgreSQL	SQLite
Connect to a database	mysql <dbname>	psql <dbname>	sqlite3 <filename>
Client help	help contents	\?	.help
SQL help	help contents	\h	n/a
List databases	SHOW DATABASES;	\l	.databases
Change database	USE <dbname>	\c <dbname>	n/a
List tables	SHOW TABLES;	\dt	.tables
Show table info	DESCRIBE <tablename>;	\d <tablename>	.schema <tablename>
Load data	LOAD DATA INFILE '<file>'	\i <file>	.import <file> <table>
Export data	SELECT ... INTO OUTFILE '<file>'	\o <file>	.dump <table>
Exit the client	quit (or exit)	\q	.exit

The first time you connect to a newly installed MariaDB or PostgreSQL database, you need to connect as the database superuser because you likely have not set up any other users.

To launch a freshly installed MariaDB mysql client, do the following:

mysql -u root -p

You will be prompted for the password you entered during the package install process.

To launch a freshly installed PostgreSQL psql client, do the following:

sudo su - postgres
psql

Creating and Deleting a Database

Just installing database clients and/or servers does not automatically give you a database to work with. For MariaDB and PostgreSQL, a database can be created either with the client or with an external utility.

In MariaDB and PostgreSQL, to create a database called library, the command is:

CREATE DATABASE library;

To connect to this newly created database in MariaDB, do:

USE library

In PostgreSQL, do:

\c library

To delete the newly created library database, drop it with:

DROP DATABASE library;

I shouldn't have to say this, but be careful with the above command. If you just dropped the library database, create it again. You'll need it later to follow along with the examples in this article.

In SQLite3, there is no database server, and databases are just regular files, often with a .db extension. To create a database, name it on the command line when you launch the client, and if it doesn't exist, the client will create it, like so:

sqlite3 library.db

To remove an SQLite3 database, just remove it like you would any other file (with rm or via your file manager).

Managing Users and Permissions

There isn't space to go into the details of how to create and manage the permissions of database users here. Refer to the MariaDB and PostgreSQL documentation for details. I will continue to use the default superuser accounts for the examples here.

There is no internal database user or user permissions management with SQLite3. If local users have write access to the database file, they can do anything they want.

Common SQL Operations

This article is about the command-line clients for MariaDB, PostgreSQL and SQLite, but one of the main things you do when using such clients is write SQL statements. So let's look at some of the basic SQL-related similarities and differences between the three.

The most common SQL statements are selects, inserts, updates and deletes. As a computer language, SQL is one of the more popular ones, and there is an official standard, ANSI SQL, which has gone through various revisions through the years. Most relational database management systems (RDBMSes) use SQL as their query language, but they differ in how closely they adhere to ANSI SQL. Of the three I'm exploring here, PostgreSQL sticks closest to the standard. MariaDB drifts from the standard in places to make it easier to use. SQLite3 doesn't pretend to support every feature of ANSI SQL. Instead, it supports only a subset. After all, it's supposed to be "Lite".

Some people would like to see SQL die and never be used again. I am not one of those people. SQL has issues, but so do most computer languages. I find SQL easy to read, flexible and well worth the time it takes to learn it. The examples below are simple, and I gloss over a lot of the complexity of SQL. I also don't explain every part of every statement. My goal here is to give you a taste of what SQL looks like in practice and to point out some of the similarities and differences between the three databases. The on-line documentation for each of these databases (and the in-client help for MariaDB and PostgreSQL) includes extensive information on SQL syntax. I found the SQLite syntax diagrams to be especially helpful for that database.

SQL statements can be written on a single line, or they can be broken up across many lines to make it easier to read. In the examples below, I do the latter. SQL statements usually end with a semicolon (;).

The CREATE TABLE Statement

You won't get very far in your database adventures without some tables. If you're not familiar with databases, think of database tables as spreadsheet sheets, without all the fonts and border styles.

Returning to our library example, the most common things in a library are books, so let's create a books table:

CREATE TABLE books (
  bookid serial PRIMARY KEY,
  title varchar(100) NOT NULL,
  seriesid integer,
  authorid integer
);

The above works for both MariaDB and PostgreSQL, but it doesn't work for SQLite3, because of the use of the SERIAL datatype, which often is used as the datatype for a PRIMARY KEY. See the "The SERIAL Datatype" sidebar for more information.

The SERIAL Datatype

A datatype is how you tell the database what type of data is in a column. Common datatypes include integer, text, varchar and date. The SERIAL datatype is a special one. In MariaDB, the SERIAL datatype is an alias for the following: Garrick, one line below.

BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

That's quite a mouthful, but it does the job of creating a column suitable for use as a PRIMARY KEY. BIGINT is a large integer; UNSIGNED means no negative values; NOT NULL means it can't be empty; AUTO_INCREMENT means that if a specific value is not specified when a row is inserted, the value should be "the current highest value + 1"; and UNIQUE means that no other row in that table is allowed to have the same value in that column.

In PostgreSQL, the SERIAL datatype is an alias for this:

INTEGER NOT NULL DEFAULT nextval('tablename_colname_seq')

The strange nextval('tablename_colname_seq') bit is referring to an "ALTER SEQUENCE", specifically:

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

This is just PostgreSQL's way of creating an auto-incrementing column. Thankfully, when you create a column with type SERIAL, PostgreSQL creates the ALTER SEQUENCE for you. This column also is suitable for use as a PRIMARY KEY.

A common feature of many database tables is a PRIMARY KEY. This key uniquely refers to a single row of a table. The PRIMARY KEY can be a combination of two or more columns in a row (as long as the combination is guaranteed to be unique in that database table), but most commonly, there is a specific, auto-incrementing column that is used as the PRIMARY KEY.

Every row in an SQLite3 table automatically has a PRIMARY KEY column (SQLite calls it the RowID) created when you create the table. However, it is hidden unless you specify a column with a type of integer PRIMARY KEY. So for SQLite, change the bookid line in the CREATE TABLE statement above to this:

bookid integer PRIMARY KEY,

And, SQLite3 will create a table with equivalent settings to MariaDB and PostgreSQL.

The INSERT Statement

Now that you have a table, it's time to enter (or INSERT) some information. Inserting data between the three databases is very similar, but there is one important difference. Both MariaDB and PostgreSQL allow you to insert multiple rows of information in one statement. SQLite3, on the other hand, lets you insert only a single row at a time.

For example, to insert some data into the books table you created earlier, use this SQL statement for both MariaDB and PostgreSQL:

INSERT INTO books (title, seriesid, authorid) VALUES
  ('The Fellowship of the Ring', 1, 1),
  ('The Two Towers', 1, 1),
  ('The Return of the King', 1, 1),
  ('The Sum of All Men', 2, 2),
  ('Brotherhood of the Wolf', 2, 2),
  ('Wizardborn', 2, 2),
  ('The Hobbbit', NULL, 1);

You may have noticed a typo in the last line. I did it on purpose so you would have something to fix later.

For SQLite3, each row that you are inserting needs to be done separately, like so:

INSERT INTO books (title, seriesid, authorid) VALUES
  ('The Fellowship of the Ring', 1, 1);
INSERT INTO books (title, seriesid, authorid) VALUES
  ('The Two Towers', 1, 1);

...and so on.

In the SQL statements above, I don't specify the bookid in the column names section. I do this because that column is set up as the PRIMARY KEY, and it is filled automatically by the database with the correct value.

The SELECT Statement

SELECT is the most common database operation. The only reason I didn't talk about this first is because until the table was CREATE-ed and had data INSERT-ed into it, as you did in the previous sections, there was nothing to SELECT.

On all three of the databases, SELECT statements work pretty much the same. Basic SELECT statements, such as the following, will work on all three:

SELECT * FROM books;
SELECT title, authorid FROM books WHERE authorid = 1;
SELECT * FROM books ORDER BY authorid;

Joins also work very well across all three. Joins are where you combine information from two or more tables together. For example, here is a join that matches author names to their books based on the authorid number:

SELECT title AS "Book Title", givenname, surname
  FROM books INNER JOIN authors USING (authorid)
ORDER BY surname;

The above SELECT statement presupposes the creation of an authors table and the insertion into it of at least a couple rows of data, like so:

On MariaDB and PostgreSQL:

CREATE TABLE authors (
  authorid serial PRIMARY KEY,
  surname varchar(100),
  givenname varchar(100),
  birthdate date
);

On SQLite3, change the authorid line to the following, and the CREATE TABLE statement will work properly:

  authorid integer PRIMARY KEY,

Here is some data for the table, formatted to work on all three:

INSERT INTO authors (surname, givenname) VALUES
  ('Tolkien', 'J.R.R.');
INSERT INTO authors (surname, givenname) VALUES
  ('Farland', 'David');

Now, you can run the SELECT ... JOIN statement.

The UPDATE Statement

Remember that typo? Well, it's time to fix it. This UPDATE line works for all three:

UPDATE books SET title = 'The Hobbit' WHERE title = 'The Hobbbit';

The DELETE Statement

Deleting rows also is the same across all three:

DELETE FROM books WHERE bookid = 7;

The above will delete the row in the books table that has a bookid of 8. If you've been following along, there should not be an entry with that bookid, so nothing will happen.

The ALTER Statement

Suppose I decide to remove the seriesid column from the books table. In MariaDB and PostgreSQL, the following statement will do it:

ALTER TABLE books DROP seriesid;

SQLite3, on the other hand, does not support the removal of columns from tables. You can add columns to a table, or modify columns, but the only way to remove a column is to create a new table without a seriesid column, transfer the data from the old table to the new table, drop the old table, and then rename the new table to the original name. It's not as annoying as you might think, thanks to some SQL INSERT trickery (well, I thought it was tricky the first time I saw it in action). The basic idea is to use the output of a SELECT statement as the input to an INSERT statement, like so:

CREATE TABLE books2 (
  bookid integer PRIMARY KEY NOT NULL,
  title varchar(100) NOT NULL,
  authorid integer
);
INSERT INTO books2 (bookid, title, authorid)
  SELECT bookid, title, authorid FROM books;
DROP TABLE books;
ALTER TABLE books2 RENAME TO books;

The above trick also works as written in MariaDB and PostgreSQL as long as you change the bookid line of the CREATE TABLE statement to the following:

bookid serial PRIMARY KEY,

But, that's an awful lot of work if you just want to drop a column from a table.

These examples should be enough SQL to give you a picture of how the three compare to each other.

SQLite Output

When trying the SQL examples, you will notice the SQLite output is not nearly as pretty as the output from MariaDB/MySQL or PostgreSQL. By default, SQLite doesn't print column names or try to pad columns so that they line up nice and fancy like the others do. To make SQLite do so for the SELECT ... JOIN statement, enter the the following commands before the statement:

.explain ON
.mode column
.width 30 10 10

The .explain command instructs SQLite to display column headers; .mode sets the output to display in columns, and the .width command sets the width of the columns. The only issue with doing this is that it will mess up the output of future queries (unless they happen to look fine with the .width values you specified). To reset things back to the default, set the output mode back to the default "list" with .mode list. Doing this also turns off explain and resets the column widths back to their defaults.

Single vs. Double Quotes

In the SQL examples I use single quotes (') for most things and double quotes (") sparingly. MariaDB and SQLite allow you to use single or double quotes interchangeably for most quoted text in queries. PostgreSQL is pickier, because it tries to stay closer to the ANSI SQL standard, which says single quotes should be used for values (for example: title = 'The Hobbbit'), and double quotes should be used for system identifiers (field names, table names and so on—for example: SELECT title AS "Book Title"...). You can force MariaDB to obey the standard—and reject double-quoted values—with the command SET sql_mode='ANSI_QUOTES'.

Conclusion

It is not hard to interact with databases on the command line. In my opinion, doing the tasks listed above is much easier on the command line than through a graphical database program.

Of course, manipulating your database by hand, whether on the command line or with a graphical program, probably should be avoided in many cases in favor of using an automated front end—for example, a PHP content management front end for the database that contains the content for your company Web site. However, for those times when you do need to dive in and tweak something manually, or for small projects that don't justify the time or expense of a custom front end, there is no need to be afraid of using the command-line client of your chosen database.

Resources

MariaDB Web Site: http://mariadb.org

MariaDB Documentation: http://kb.askmonty.org

MariaDB Downloads: http://downloads.askmonty.org

PostgreSQL Web Site: http://www.postgresql.org

PostgreSQL Documentation: http://www.postgresql.org/docs

PostgreSQL Downloads: http://www.postgresql.org/download

SQLite Web Site: http://www.sqlite.org

SQLite Documentation: http://www.sqlite.org/docs.html

SQLite Downloads: http://www.sqlite.org/download.html

SQLite SQL Syntax Diagrams: http://www.sqlite.org/syntaxdiagrams.html

Wikipedia Article on SQL: http://en.wikipedia.org/wiki/SQL

Wikibooks Article on Moving between MySQL and PostgreSQL: http://en.wikibooks.org/wiki/Converting_MySQL_to_PostgreSQL