Map JSON Booleans to SQL Boolean and Bit Columns

Boolean Type Mapping

JSON has a native boolean type with values true and false. SQL support for booleans varies significantly across database engines.

Example JSON

{
  "id": 1,
  "username": "alice",
  "is_active": true,
  "is_verified": false,
  "accepts_marketing": true
}

Generated SQL (PostgreSQL)

CREATE TABLE users (
  id INTEGER NOT NULL,
  username VARCHAR(255) NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  is_verified BOOLEAN NOT NULL DEFAULT FALSE,
  accepts_marketing BOOLEAN NOT NULL DEFAULT TRUE
);

Database-Specific Types

Database	Type	Values
PostgreSQL	BOOLEAN	TRUE / FALSE
MySQL	BOOLEAN (alias for TINYINT(1))	1 / 0
SQL Server	BIT	1 / 0
SQLite	INTEGER	1 / 0
Oracle	NUMBER(1)	1 / 0

The converter selects the appropriate type based on your chosen target database. When no target is specified, it defaults to the ANSI BOOLEAN type.

Default Values

The converter uses the sample JSON value to set the DEFAULT clause. If the sample shows "is_active": true, the column gets DEFAULT TRUE. This is a starting point — review and adjust defaults based on your business logic.

Naming Convention

Boolean columns conventionally start with is_, has_, can_, or accepts_. The converter detects these prefixes and applies BOOLEAN even if the sample value is 0 or 1 (which might otherwise map to INTEGER).

NOT NULL Recommendation

Boolean columns should almost always be NOT NULL with a default. A three-valued boolean (TRUE, FALSE, NULL) is a common source of bugs. If you genuinely need three states, consider an enum column ('yes', 'no', 'unknown') instead.

Indexing Booleans

Indexing a boolean column alone is usually not helpful because the cardinality is only 2. However, a composite index like (is_active, created_at) can be highly selective when filtering active records by date.