Online Tool Store
· 5 min read
How to Draft a Table Schema From JSON
Manesh Jayawardhana
CIO & Co-founder
A JSON sample with twenty fields becomes a CREATE TABLE in seconds. Three of those columns are wrong in ways that will not surface until production.
Generation handles the tedious part. The review is where the schema is actually designed.
Inferred types are as good as the sample
A generator reads values and picks types. That works for the obvious cases and produces predictable errors:
Integers sized to the sample. An id that happens to be 4,821 in the sample becomes a small integer type. It works until the sequence passes the limit, at which point inserts fail.
Numbers that should be strings. Phone numbers, postcodes and account references frequently look numeric and must not be — leading zeros disappear and arithmetic becomes possible where it should not be.
Strings sized to the sample. A varchar(40) derived from the longest value present will reject a longer one later.
Everything nullable, or nothing. The sample says which fields had values, not which fields are guaranteed.
Dates as text. An ISO date string is a string to a parser and should be a date column.
None of these are failures of the generator. It cannot know what the sample does not contain, and reviewing the output against the API’s documentation is the step that makes it correct.
| Inferred | Frequently should be |
|---|---|
int from a small id | bigint |
int from a phone number | varchar |
varchar(40) from longest sample | Larger, or text |
varchar from an ISO date | date / timestamp |
Nested objects are a modelling decision
The generator can put a nested object in a JSON column or split it into its own table. Neither is automatically right.
A JSON column is quick, keeps the shape flexible, and gives up most of what a relational database provides — indexing on inner fields is limited, constraints do not apply, and joins are awkward.
A separate table requires more setup and gives you indexes, foreign keys, constraints and normal query performance.
The test that decides it: will you ever filter, join or aggregate on anything inside this object?
If yes, it needs its own table. If it is genuinely opaque — a blob of settings, a third-party payload stored for reference — a JSON column is appropriate and simpler.
Arrays are the same question with an extra step. An array of scalars can be a JSON column or a join table; an array of objects almost always wants a table.
Dialects differ more than they look
The generated SQL is dialect-specific, and the differences matter:
Type names. serial against auto_increment against identity.
JSON support. PostgreSQL’s jsonb with indexing, MySQL’s json, SQLite’s text-with-functions. These are not equivalent.
String types. text behaves differently between PostgreSQL and MySQL in performance and in limits.
Case sensitivity of identifiers, which catches people migrating between engines.
Generating for the wrong dialect produces SQL that either fails immediately, which is fine, or runs with different semantics, which is not.
Indexes and constraints come next
A generated CREATE TABLE gives you columns. A working schema needs more.
Primary key. Frequently inferrable from an id field and worth stating deliberately, including whether it is natural or surrogate.
Indexes on anything you will filter, join or sort by. A generated schema has none, and the first slow query is usually a missing index.
Not null constraints on fields the application genuinely requires, which is a stricter set than the fields that happened to be populated in the sample.
Unique constraints where the data model requires uniqueness — an email, an external reference.
Foreign keys where nested objects became separate tables.
None of those can be inferred from a JSON sample, and all of them are what makes the schema a schema rather than a set of columns.
Common mistakes to avoid
- Accepting inferred integer sizes for identifiers.
- Letting a numeric-looking string become a numeric column.
- Putting a queryable nested object in a JSON column for convenience.
- Generating for one dialect and running against another.
- Treating the generated schema as final rather than as a first draft.
How to do it with JSON to SQL Schema Generator
The JSON to SQL Schema Generator drafts the table in your browser.
- Paste a representative sample — the most complete one available.
- Choose the dialect, since type names and JSON support differ substantially.
- Decide how nested structures are handled based on whether you will query inside them.
- Review every inferred type against the API’s documentation before running it.
Other developer tools are in the tools directory.
Frequently asked questions
How are column types inferred?
From the sample values, so they are only as good as the sample. Small integers get narrow types, numeric-looking strings become numbers, and nothing indicates which fields are genuinely optional.
Should nested objects become JSON columns?
Only if you will never query inside them. A JSON column gives up indexing, constraints and joins, which is fine for an opaque payload and wrong for anything you will filter on.
Does this handle arrays?
They are flagged rather than converted, because an array is a modelling decision — a join table, a JSON column, or a delimited string, and only the first is normalised.
Final thought
Review the integer widths and the nullable columns before running the statement. Those two are where a generated schema fails, and both fail months later.