JSON to SQL Types: MySQL and PostgreSQL

1. Problem: a sample is not a complete schema

12 may be a quantity or a large identifier, and 2026-07-29 may be a date or an order code. An inference tool can propose a schema but cannot decide precision, indexes, or constraints. A field containing only null has no evidence for its non-null type.

2. Minimal reproduction: dialects produce different candidates

Objects and arrays can stay as native JSON, while scalar choices need dialect and contract context. These are candidates, not production DDL.

const sample = { count: 12, label: "2026-07-29", enabled: true, meta: { source: "import" }, values: [1, 2], unknown: null };
// MySQL: DOUBLE, TEXT, TINYINT(1) convention, JSON, JSON, nullable unknown
// PostgreSQL: double precision, text, boolean, jsonb, jsonb, nullable unknown

3. Root cause: MySQL and PostgreSQL have different type semantics

MySQL has JSON and commonly uses TINYINT(1) as a boolean convention. PostgreSQL distinguishes json and jsonb and has a native boolean. Numeric types, indexes, and operators differ. A string's date-like shape or length cannot reliably establish its meaning.

4. Recommendation: emit candidates and require contract review

Return dialect, candidate type, nullability, and an uncertainty reason. Be conservative with numbers and strings, and preserve arrays and objects as JSON. Final DDL should come from an explicit contract and migration review, not one sample.

5. Complete code: conservative dialect candidates

This TypeScript proposes candidates only. null becomes unknown plus nullable; dates are not guessed, and numeric samples are not declared integer by default.

type Dialect = "mysql" | "postgresql";
type Candidate = { sqlType: string; nullable: boolean; reason: string };
function inferCandidate(value: unknown, dialect: Dialect): Candidate {
  if (value === null) return { sqlType: "unknown", nullable: true, reason: "null carries no non-null type" };
  if (typeof value === "boolean") return { sqlType: dialect === "mysql" ? "TINYINT(1) convention" : "boolean", nullable: false, reason: "boolean observed" };
  if (typeof value === "number") return { sqlType: dialect === "mysql" ? "DOUBLE (confirm precision)" : "double precision (confirm precision)", nullable: false, reason: "range and scale are unknown" };
  if (typeof value === "string") return { sqlType: "TEXT", nullable: false, reason: "string meaning is not inferable" };
  return { sqlType: dialect === "mysql" ? "JSON" : "jsonb", nullable: false, reason: Array.isArray(value) ? "array preserved as JSON" : "object preserved as JSON" };
}
console.log(inferCandidate(12, "mysql"));
console.log(inferCandidate({ source: "import" }, "postgresql"));

6. Incorrect approaches

Generating INT for every number can overflow IDs or lose decimals. Turning date-shaped strings into DATE can corrupt ordinary text. Making every column TEXT avoids some failures but loses constraints and query semantics. Treating MySQL TINYINT(1) as a universal boolean also breaks portability.

7. Boundaries: NULL, arrays, precision, and time

All-null fields need a human-supplied type. Mixed null and numeric values express nullability, not a new scalar type. Mixed arrays need an element policy. Money needs scale and rounding, large IDs need an identifier contract, and dates need a timezone-aware contract rather than a pattern match.

8. Verification

Prepare null, negative, fractional, large-integer, empty-string, Unicode, object, and empty-array samples. Generate each dialect's candidates, review them, execute the resulting DDL in a temporary database, insert boundary values, and inspect read-back types and precision.

9. FAQ

Why not classify every number as INT or FLOAT? The sample lacks complete range, scale, and unit information.

Should an object become columns or a JSON column? That depends on query and update needs; inference can only propose.

Can null reveal a type? No; it reveals only an empty value and possible nullability.

10. Summary

JSON-to-SQL inference generates candidates, not schema truth. Handle MySQL and PostgreSQL separately, and require contracts for dates, money, and identifiers. Mark uncertainty, then validate DDL with boundary data in the declared database.