How to choose a database for your project

Choosing a database is one of the hardest decisions to undo in a project. Switching later means rewriting queries, migrating data and retesting everything. That is why half an hour of thinking before the first CREATE TABLE pays off.

The good news is that the choice is rarely dramatic. Five databases cover almost everything bots, APIs and websites need: PostgreSQL, MySQL, MongoDB, Redis and SQLite.

What criteria decide the choice?#

Four questions settle most cases:

1. What shape is the data? Records with fixed columns and relationships (users, orders, products) call for a relational database. Documents whose fields vary from item to item call for a document database.
2. Do you need consistency across several writes? Moving a balance between two accounts must happen completely or not at all. That is a transaction, and relational databases are built around it.
3. How will you read the data? Very fast exact-key lookups (sessions, counters, cooldowns) favor Redis. Reports that combine tables favor SQL.
4. How many processes access the database? A single process on the same machine can use a file (SQLite). Several servers or applications call for a database server reached over the network.

Answer these four and the list of candidates shrinks to one or two.

When should you choose PostgreSQL or MySQL?#

These are two widely used relational databases. They store data in tables, link tables with foreign keys and query everything with SQL. Both have full transactions: in MySQL, the default InnoDB engine follows the ACID model and supports foreign keys.

Choose one of them when the data has a clear structure and relationships: user accounts, an online store, a subscription system, a bot economy with inventory and currency. It is the safe bet when you are unsure.

PostgreSQL offers extensions, advanced types and the jsonb type, which stores JSON with index support. That lets you mix fixed columns with a flexible field in the same table:

CREATE TABLE products (
  id      serial PRIMARY KEY,
  name    text NOT NULL,
  price   numeric(10, 2) NOT NULL,
  extras  jsonb NOT NULL DEFAULT '{}'
);

CREATE INDEX products_extras_idx ON products USING GIN (extras);

INSERT INTO products (name, price, extras)
VALUES ('T-shirt', 19.90, '{"sizes": ["S", "M", "L"], "color": "black"}');

SELECT name FROM products WHERE extras @> '{"color": "black"}';

MySQL is very popular on hosting providers and in the PHP ecosystem, with plenty of tools and tutorials. For a new project with no constraints, the practical difference between the two is usually small. See the detailed comparison in PostgreSQL vs MySQL: which to choose.

When should you choose MongoDB?#

MongoDB stores JSON-like documents in collections, with no mandatory schema. Each document can have different fields and carry nested lists and objects inside it.

It works well when you read and write the whole object at once: per-server settings for a Discord bot, profiles with optional fields, catalogs where each item has its own attributes. The flexibility speeds up the start of a project, because changing the shape does not require a migration.

Two cautions. First, without a schema, validation becomes your job (in code or in database validation rules). Second, MongoDB supports multi-document transactions, but its own documentation says that in most cases they cost more than single-document writes. They are also no substitute for good schema design, since embedded documents already reduce the need for transactions. If you are already writing many relationships and transactions, you probably want a relational database. See what MongoDB is and what it is for.

When should you choose Redis?#

Redis keeps everything in memory, accessed by key, and is very fast. Its classic role is the second database: response cache, sessions, queues, counters, rate limits and bot command cooldowns.

It can write to disk. The official documentation describes two ways: RDB (point-in-time snapshots of the dataset at intervals) and AOF (a log of every write). With RDB alone, a crash can cost you the last few minutes of data. With AOF and the default of syncing every second, the risk drops to about one second of writes. Even so, Redis was designed for data that fits in memory, so do not use it as the main store for data you cannot lose or that grows without bound.

Rule of thumb: if losing the data is an annoyance (the cache warms up again), Redis is great. If losing it is a loss (orders, payments), the data lives in a relational or document database. More in what Redis is and what it is for.

What about SQLite?#

SQLite is a whole database inside a single file, with no server. The project's documentation positions it as a replacement for opening files with fopen(), not as a competitor to client/server databases. It is excellent for prototypes, command-line tools, desktop apps and small single-process bots.

The limit is concurrency: SQLite allows many readers but only one writer at a time, and its documentation advises against having many computers access the same file directly over a network. When your project grows to more than one server or many simultaneous writes, move to PostgreSQL or MySQL. Starting on SQLite and migrating later is a legitimate path, as long as you stick to standard SQL and avoid SQLite-only features.

Which database for each type of project?#

When in doubt, choose PostgreSQL. It covers the relational case and a good part of the flexible case, and people rarely regret it.

How to do this on Square Cloud#

The client-server databases in the table (MongoDB, PostgreSQL, MySQL and Redis) have hosting on Square Cloud from the Standard plan; Hobby does not include databases. You create the database in the dashboard (name, memory and engine), and it is ready in seconds in the same datacenter as your applications, with an average latency of about 1 ms and mandatory TLS. If you follow the "when in doubt, PostgreSQL" advice, note that it is native PostgreSQL, without extensions.

The pattern from the FAQ, permanent data in a relational database and Redis as a cache or queue, fits on one plan. Different engines live side by side, limited only by the plan's RAM: at least 1024 MB for PostgreSQL, MySQL and MongoDB and 512 MB for Redis. A PostgreSQL and a Redis together take 1.5 GB of the 4 GB entry Standard plan. Each one has its own connection URL, which the dashboard hands you and you store in an application environment variable.

SQLite has a place too: each application's disk persists across restarts and deploys, so a single-process bot can keep its .db file there. Leave that file out of the deploy ZIP, because a commit overwrites the files that are in the package. Criterion 4 still applies: applications behind a load balancer do not share a disk, so once you run two processes, the answer is a database server.

For data you cannot lose, you take manual snapshots whenever you want, and every plan that includes databases also takes an automatic daily snapshot. See the databases available today on Databases.