JSON columns

There's an endless discussion happening between developers whether to use a SQL or NoSQL database. Overall it's a controversial topic whether the database should have a schema and changing it can be more complex, or whether the application should handle the schema. But by utilizing JSON columns, you can merge the NoSQL benefits of schemaless data for some data with schema for the most of the data.

Usage

MySQL

CREATE TABLE products (
  -- use normal columns for common attributes
  id bigint PRIMARY KEY auto_increment,
  author_id bigint NOT NULL,
  category_id bigint NOT NULL,
  name text NOT NULL,
  price numeric(15,2) NOT NULL,

  -- use a json attribute for seldom used attributes
  attributes json NOT NULL DEFAULT ('{}')
);

PostgreSQL

CREATE TABLE products (
  -- use normal columns for common attributes
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id bigint NOT NULL,
  category_id bigint NOT NULL,
  name text NOT NULL,
  price numeric(15,2) NOT NULL,

  -- use a json attribute for seldom used attributes
  attributes jsonb NOT NULL DEFAULT '{}'
);

Detailed Explanation

The best solution for an application is most often a relational database like MySQL or PostgreSQL because of their great flexibility. Having to define and change a database schema got developers interested in schema-free NoSQL databases to simplify their development. However, the schema in NoSQL databases will just be distributed across the application's code with having to support multiple versions. Nevertheless, you can combine the power of both worlds by using a traditional schema with every column defined and (schema-free) JSON columns in your application.

Combining structured data and semi-structured data can simplify the database's schema. The former example can replace the Entity–Attribute–Value model by storing different attributes for each type of item in JSON column. You can also store JSON api responses exactly the same as you received them or store data in JSON columns to avoid transforming data before returning it via your api. When softening your strict schema requirements, you will find many opportunities in extending tables with JSON columns.

The possibilities of JSON columns are endless, but you should follow these rules to not fall into schema-less database traps:

• Store most values in normal columns
• Use json for values which would be empty most of the time
• Don't store references to other tables
• Avoid deeply nesting

Additional Resources

• MySQL Documentation: The JSON Data Type and it's usage.
• PostgreSQL documentation: The differences and purposes of the JSON Types.

Tobias Petry

SQL for Devs Founder