MariaDB/MySQL, PostgreSQL and SQLite3—Comparing Command-Line Interfaces

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.

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

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.

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

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

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.

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

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.

BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE

INTEGER NOT NULL DEFAULT nextval('tablename_colname_seq')

ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

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.

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.

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.

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

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.

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.

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.