SQL vs NoSQL: which database to choose

"SQL vs NoSQL" sounds like a choice between two products, but it isn't. SQL is a query language and, by extension, the relational model behind it. NoSQL is an umbrella for everything that doesn't follow that model: document, key-value, wide-column and graph databases. Comparing the two means comparing ways of organizing data.

The decision matters because switching databases later is expensive: you rewrite queries, migrate data and redo tests. So choose based on the shape of your data and how you access it, not on hype.

What is a SQL database?#

A SQL (relational) database stores data in tables made of rows and columns. You define the schema up front: column names, types and constraints. Tables link to each other through foreign keys, and a query combines them with JOIN.

Common examples are PostgreSQL, MySQL and SQLite. The core promise is integrity: the database rejects an order that points to a user who doesn't exist, and a transaction guarantees that a set of writes happens completely or not at all.

What is a NoSQL database?#

"NoSQL" doesn't describe a model, it describes an absence. The types most used by people hosting bots, APIs and websites are:

• Document (MongoDB): JSON-like objects grouped into collections. Each document can carry nested lists and objects.
• Key-value and in-memory structures (Redis): access by key, with types such as strings, hashes, lists, sets and sorted sets, according to the Redis documentation.
• Wide-column and graph: for large-scale analytics and networks of relationships. They fall outside the scope of most small projects.

What these databases share is the choice to give up part of the relational model in exchange for another advantage: a flexible shape, direct access by key or easier distribution of data across servers.

What is the difference between SQL and NoSQL, side by side?#

The table is a tendency, not a law. PostgreSQL stores JSON with the jsonb type, and MongoDB has transactions and schema validation. The boundaries blur, so the right criterion is your access pattern, not the label.

How does the same data look in each model?#

Take an order with items. In the relational model, it splits into tables:

CREATE TABLE orders (
  id         serial PRIMARY KEY,
  user_id    integer NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  order_id integer NOT NULL REFERENCES orders (id),
  product  text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product)
);

SELECT o.id, i.product, i.quantity
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.user_id = 42;

In the document model, the same order becomes a single object with the items inside (example in mongosh):

db.orders.insertOne({
  userId: 42,
  createdAt: new Date(),
  items: [
    { product: "T-shirt", quantity: 2 },
    { product: "Mug", quantity: 1 },
  ],
});

db.orders.find({ userId: 42 });

Reading the full order is a single operation, with no join. The cost shows up when the same data is queried from another angle, for example "which orders contain a Mug": in the relational model that's an index and a JOIN, while in the document model you depend on an index on the nested field and on keeping the data consistent in your application.

When do transactions decide the choice?#

Moving a balance between two accounts must happen completely or not at all. In SQL this is natural:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

In PostgreSQL, the default isolation level is Read Committed, and you can raise it to Repeatable Read or Serializable when the logic requires it, according to the documentation.

MongoDB supports multi-document transactions, but its documentation warns that they usually cost more than single-document writes and recommends embedding as the first modeling option.

The rule of thumb: if your domain lives on transactions across many entities (balances, inventory, payments), the relational model is the natural fit. If each operation touches a single object, the document model works well.

Is a flexible schema an advantage or a risk?#

In a document database, a collection accepts documents with different fields. That speeds up the start of a project and helps with heterogeneous data, such as catalogs where each product has its own attributes. The MongoDB documentation describes this flexible model and schema validation for when you want to lock the shape down.

The risk is the implicit schema: without rules, each version of the code writes data its own way and every read has to handle all the variations. In a relational database, migrations take more work, but the database guarantees that every record follows the structure. There is a middle ground: PostgreSQL combines fixed columns with an indexable jsonb field, which covers many "semi-flexible" cases without switching databases.

How do you choose between SQL and NoSQL?#

Ask four questions:

1. Does your data have strong relationships? Users, orders, subscriptions and inventory point to each other. SQL.
2. Do you read and write the whole object at once? Per-server Discord settings, a profile with optional fields. Documents work well, and so does PostgreSQL with jsonb.
3. Is access by key, and is losing the data tolerable? Cache, sessions, cooldowns, rate limits. Redis. It can write to disk (see the persistence documentation), but it was designed for data that fits in memory.
4. Does the shape change every week? In a prototype, flexibility helps. In production, weigh how much enforcing structure is worth.

Without a clear reason to do otherwise, a relational database is the safest starting point. To decide within each family, see which database to choose, PostgreSQL vs MySQL and what MongoDB is. For the role of Redis alongside another database, read Redis as a cache or as a database.

How to do this on Square Cloud#

Square Cloud offers MongoDB, PostgreSQL, MySQL and Redis hosting, starting on the Standard plan. The Hobby plan doesn't include database hosting. So you have both sides of this comparison: SQL with PostgreSQL or MySQL, NoSQL with MongoDB or Redis.

You create the database in the dashboard, and it is ready in seconds in the same datacenter as your applications, with an average latency of about 1 ms and mandatory TLS. Combining two engines in one project, such as PostgreSQL for permanent data and Redis for caching, just means creating both and storing the connection URLs in environment variables. The number of databases is limited only by your plan's RAM: each one uses at least 512 MB (Redis) or 1024 MB (the others).

You take manual snapshots whenever you want, and every plan that includes databases also has a daily automatic snapshot. You can restore a snapshot to the same database, to another one of the same engine or to a new database. Redis has persistence, and PostgreSQL is native PostgreSQL, without extensions. See the options on Databases.