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

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.