SQL vs NoSQL: how to choose a database for your project

“Should we use SQL or NoSQL?” is one of the first questions on a new project, and it’s often answered by fashion rather than by the data. The honest short answer for most business apps is: start with a SQL database such as PostgreSQL, unless you can name the specific reason you need something else. This post explains why, and what those reasons look like.

The difference in one example#

Take a blog post with its comments.

A SQL (relational) database stores them in separate tables with fixed columns, linked by keys:

CREATE TABLE posts (
  id    INTEGER PRIMARY KEY,
  title TEXT NOT NULL
);

CREATE TABLE comments (
  id      INTEGER PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id),
  author  TEXT NOT NULL,
  body    TEXT NOT NULL
);

A document database (the most common kind of NoSQL, such as MongoDB or Firestore) can store the post and its comments together as one JSON-like document:

{
  "_id": "post-17",
  "title": "Database design basics",
  "comments": [
    { "author": "Amina", "body": "Very clear, thanks." },
    { "author": "Rafi", "body": "What about indexes?" }
  ]
}

Neither is “better”. They’re optimised for different questions:

• Showing one post with its comments is a single read in the document model; SQL needs a join (which is fast with the right index).
• “Every comment Amina has written, across all posts” is a simple query in SQL. In the document model, the comments are scattered inside many posts.

What SQL databases give you#

PostgreSQL, MySQL, SQL Server and SQLite are all relational. They share a few strengths:

• A schema. Columns have types and rules (NOT NULL, UNIQUE, foreign keys), so bad data is rejected at the door.
• Joins. You can combine data in ways you didn’t plan for when you designed the tables. New report next quarter? Usually just a new query.
• Transactions. Several changes succeed or fail together, which matters for orders, payments and stock. See transactions explained.
• SQL itself. A standard language that most developers, analysts and reporting tools already speak.

The cost: you design the tables up front, and changing them later means a migration.

The kinds of NoSQL#

“NoSQL” isn’t one thing. It’s a label for several different designs:

Their shared trade: you design around the questions you’ll ask, and in return get flexibility in shape, or very high scale for those particular questions. Questions you didn’t plan for can be awkward or slow.

Myths worth dropping#

• “NoSQL scales and SQL doesn’t.” A single well-indexed PostgreSQL server comfortably handles the traffic of the vast majority of businesses, and read replicas add more. You hit SQL’s limits much later than most people expect.
• “NoSQL has no schema.” The data always has a shape. Without a database schema, that shape is enforced (or not) by your code, which is harder to see and easier to break.
• “NoSQL can’t do transactions.” Many can now: MongoDB has supported multi-document transactions since version 4.0. Check the specific database instead of the category.
• “SQL can’t store flexible data.” PostgreSQL’s jsonb columns store and index JSON, so you can keep a fixed core plus a flexible part in the same table.

How to choose#

Ask these in order:

1. Is the data related, and does it need to stay consistent? Customers, orders, invoices, bookings, stock: choose SQL. This covers most business apps.
2. Is it read and written as whole, self-contained units whose shape varies? Product catalogues with very different attributes, content blocks, user settings: a document database fits, or a jsonb column in PostgreSQL.
3. Do you need very fast lookups by key, and can you lose or rebuild the data? Caches, sessions, rate-limit counters: a key-value store such as Redis, usually alongside your main database.
4. Are you writing huge volumes that you only query in a few known ways? Analytics events, logs, IoT readings: wide-column or a time-series database.
5. Are the connections the main thing you query? Graph.

Many real systems use two: PostgreSQL as the source of truth, plus Redis for caching and rate limits. That’s normal, not a failure to choose.

A quick way to test a choice: write down the ten questions your app will ask the database most often, then check how each would be answered. If you find yourself copying the same data into many documents to answer them, the data is relational.

If you’re using Wix, its CMS makes much of this choice for you, and database design basics still applies to how you set up collections. For a project that needs its own backend and database, I can help with the design as part of Wix apps with the Wix CLI and API integration work.