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

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

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Very useful article

marksen's picture

That was exactly what I needed to clear my ideas about these 3 db sw.

small error

Anonymous's picture

Hi,

small error in your delete and explanation. According to the text, you want to delete book with bookid = 8, but the code says bookid = 7.

Nice comparisation of the command line of some of the biggest/most used open source and/or free DBs.

SQLite3 allows multi insert

A.Bouchez's picture

As of version 3.7.11 (the latest one) SQLite does support multi-row-insert.
The new multi-valued insert is merely syntactic suger (sic) for the compound insert.
There is no performance advantage one way or the other: an explicit transaction is needed to make insertion fast (due to the ACID feature of SQLite, outside a transaction each INSERT will wait for the data to be flushed on disk).

NICE !!

biohazd's picture

Very nice article - its exactly what I needed....

Good Article!

Anonymous's picture

Good article. I've only used MySQL before, so I find this to be refreshing about other database management systems.

A bit of clarity

Anonymous's picture

Please note that PostgreSQL is not "pickier" about the use of single/double quotes. The ANSI SQL standard is. PostgreSQL is merely following that standard, perhaps closer than the other DBs you reviewed. They're quite open about this decision. Good on them for that, it has helped produce a powerful tool.

Regards,

Just A. User

Thanks for the comparison

Anonymous's picture

Thanks for the article, quite useful as a quick comparison between those three databases command-line interfaces.

Thanks for the comparison

Anonymous's picture

Thanks for the article, quite useful as a quick comparison between those three databases command-line interfaces.

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix