Any server software that needs to store data needs a database. This blog has one.
There are a few different database systems that we can use. For this post, we will be looking at relational databases only, because I have not worked with document-based databases yet and I do not intend to work with them in the near future.
Since SQL is the language used to interact with relational databases, they are also called SQL databases.
SQL is a rather simple language to learn, with maybe 5 different types of commands. Each database system has its own quirks, so they each publish their own, slightly different, tutorials to learn from.
A relational database has tables to store all the information, with rows being added, read, modified or removed. Columns and tables are generally only added or removed during updates and maintenance.
If your app stores songs in user-defined playlists, would you expect each playlist to have its own table?
The SQL way of doing it is to have one table where each row is a song, another table where each row is a playlist, and a third table where each row is a link between a song and a playlist. Two different rows can put the same song in two different playlists.
SQLite
SQLite is, as the name suggests, a lightweight implementation of a relational database system. It is easy to use, and is a strong contender for being the world's most deployed software.
SQLite is imported as a library in whichever programming language the application is written in. Data is stored in a single file, which makes manual review and backup restoration easy.
SQLite performs best when only a few interactions are made at a time. This is the case for an application on a personal device, but not so for a popular web application which needs to be used by many people at once.
PostgreSQL
PostgreSQL is more of a traditional database system that runs as a server and has some user management features.
Instructions to install and run it on your server are here. Alternatively, you can install it via docker (as I did with the example command in the Docker tutorial).
You can have multiple users and multiple databases in one PostgreSQL instance. With user management features, you can enforce access permissions on each database. Each server software you host can have its own user and database, and it will be unable to see the details of the other databases. This is useful because if an attacker manages to execute code as one of your servers, data related to other servers is not compromised.
Connecting To The Database Server
If you are on a UNIX based system (Linux or MacOS), you can connect to the database as the database admin (called postgres) from the same computer with psql -U postgres -h 127.0.0.1 -p 5432, assuming you exposed it on the default port 5432. Logged in as postgres, you can run the following SQL commands.
Making Schema Details Private-By-Default
By default, PostgreSQL shows all the tables and their columns in a database publicly. You can revoke this with REVOKE ALL ON SCHEMA public FROM public;.
In SQL, the convention is that language-specific words are written in uppercase and variables are written in lowercase. Don't forget the semicolon at the end of the command!
Adding A New User And Their Database
You can create a new user with CREATE ROLE alice LOGIN ENCRYPTED PASSWORD 'alice_password';.
Then create a database owned by that user with CREATE DATABASE alice_db WITH OWNER=alice;.
Finally revoke all public access to this database with REVOKE ALL ON DATABASE alice_db FROM public;.
Do the same with a second user, say bob.
Logging In As A Regular User
Log out from psql with \q, then log in as bob with psql -U bob -h 127.0.0.1 -p 5432 -d bob_db.
Try to connect to alice_db with \c alice_db - this should fail, proving database isolation.
Do this for every server you host, and you'll have completely isolated databases.