[ ← Back Home ]

Last modified on January 1, 2023

Table of Contents

SQL is a standardized language for database management. This article is a “cheat sheet” of sorts, helping you with the semantics of database engines such as MySQL and PostgreSQL.

Note on Encoding

While a manual server setup will normally ensure a proper locale is installed, some VPS providers and default server ISOs may need further configuration to make sure all data is encoded correctly on your server. Before installing any database engine, ensure your server is using the proper encoding locale.

Simply run the command dpkg-reconfigure locales and select the desired locale from the text menu. en_US.UTF-8 is the default for the english-speaking US.

Reboot for these changes to take effect.


MySQL is one of the most popular and oldest database software still around today in modern hosting. MariaDB is a community-developed fork of MySQL which works almost exactly the same, but with additional security and stability improvements.

Basic operation

To enter the MySQL prompt, run mysql.

Creating a user

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

Creating a database

CREATE DATABASE database_name;

Granting permissions

use database_name;
GRANT ALL ON database_name.* TO 'username'@'localhost';

Listing existing databases


Deleting a database

DROP DATABASE database_name;


PostgreSQL is a more extensible SQL database, common in use with lots of server software like PeerTube and Matrix Synapse.

Basic operation

The psql prompt is only accessible through the postgres user, so firstly switch to using it:

sudo su postgres

Creating a user

User creation in PostgreSQL is done outside of the psql prompt. The createuser command is used, by the postgres user:

createuser --pwprompt username

Deleting a user

dropuser username

Dumping a database

pg_dump -U username database_name > file.sql

Restoring from a file

psql -U username database_name < file.sql

Some PostgreSQL operations are performed by the postgres user, such as:

Some operations are done from the prompt. Run this command to enter the PostgreSQL prompt:


Listing existing databases

Simply run \l in the psql prompt to list existing databases.


Creating a database

Just like in regular SQL, the CREATE DATABASE command is used, but the syntax for ownership is different:

CREATE DATABASE database_name
OWNER username;

Deleting a database

Just like regular SQL, run DROP DATABASE:

DROP DATABASE database_name