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.
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.
|Connect to a database||
|Show table info||
|Exit the client||
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:
In PostgreSQL, do:
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:
To remove an SQLite3 database, just remove it like you would any other file
rm or via your file manager).
Until recently, IBM’s Power Platform was looked upon as being the system that hosted IBM’s flavor of UNIX and proprietary operating system called IBM i. These servers often are found in medium-size businesses running ERP, CRM and financials for on-premise customers. By enabling the Power platform to run the Linux OS, IBM now has positioned Power to be the platform of choice for those already running Linux that are facing scalability issues, especially customers looking at analytics, big data or cloud computing.
￼Running Linux on IBM’s Power hardware offers some obvious benefits, including improved processing speed and memory bandwidth, inherent security, and simpler deployment and management. But if you look beyond the impressive architecture, you’ll also find an open ecosystem that has given rise to a strong, innovative community, as well as an inventory of system and network management applications that really help leverage the benefits offered by running Linux on Power.Get the Guide
|Fancy Tricks for Changing Numeric Base||May 29, 2016|
|Working with Command Arguments||May 28, 2016|
|Secure Desktops with Qubes: Installation||May 28, 2016|
|CentOS 6.8 Released||May 27, 2016|
|Secure Desktops with Qubes: Introduction||May 27, 2016|
|Chris Birchall's Re-Engineering Legacy Software (Manning Publications)||May 26, 2016|
- Tips for Optimizing Linux Memory Usage
- Secure Desktops with Qubes: Introduction
- Working with Command Arguments
- Download "Linux Management with Red Hat Satellite: Measuring Business Impact and ROI"
- Secure Desktops with Qubes: Installation
- Fancy Tricks for Changing Numeric Base
- CentOS 6.8 Released
- Linux Mint 18
- The Italian Army Switches to LibreOffice
- Petros Koutoupis' RapidDisk