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.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState