Type Inference Rules
- Numbers: INT for integers, DECIMAL for floats, BIGINT for large numbers
- Strings: VARCHAR(n) based on length, TEXT for long strings
- Dates: DATE, DATETIME, or TIMESTAMP based on format
- Objects/Arrays: JSON type for PostgreSQL, TEXT for others
- UUIDs: CHAR(36) or UUID type
What is SQL Schema Generator?
A SQL Schema Generator automatically creates database table definitions (CREATE TABLE statements) from JSON data. It analyzes the structure and values of your JSON to infer appropriate column types, constraints, and indexes — for example, detecting that an email field should be VARCHAR(255) with a UNIQUE constraint, or that a timestamp string maps to a TIMESTAMP column. This eliminates the manual process of designing schemas from scratch and ensures type consistency between your application data and database. The generator supports PostgreSQL, MySQL, and SQLite with dialect-specific type mappings and auto-increment strategies.
How to Use
- Paste your JSON data (object or array of objects) into the input field on the left — the first object in an array is used as the sample for type inference.
- Set the table name and choose your SQL dialect (PostgreSQL, MySQL, or SQLite) in the settings panel.
- Toggle options for auto-increment ID column and timestamp columns (created_at, updated_at) as needed.
- Click 'Generate Schema' to analyze the JSON and produce the CREATE TABLE statement with inferred types and constraints.
- Review and fine-tune the inferred column types, constraints, and defaults in the column editor table, then copy the final SQL.
Why Use This Tool?
Tips & Best Practices
- Provide a representative sample of your data for the most accurate type inference — include fields with the longest expected values
- Review and adjust the inferred types before running the SQL in production — the generator uses heuristics that may not match your exact requirements
- Use the JSON type for nested objects in PostgreSQL for flexible schema design, but consider normalization for query performance
- Add CHECK constraints manually for data validation beyond what type inference provides — for example, ensuring ages are positive
Frequently Asked Questions
How does the type inference work?
The generator examines each value in your JSON data and maps it to the most appropriate SQL type. Integers become INT or BIGINT, decimals become DECIMAL, strings become VARCHAR with length based on the sample, dates are detected by format pattern, and nested objects or arrays become JSON type. You can always override the inferred types in the column editor.
Can I generate schemas for existing databases?
This tool generates CREATE TABLE statements for new tables from JSON data. If you have existing data in JSON format that you want to store in a database, paste the data and the tool will create the appropriate schema. For reverse engineering existing databases, you would need database-specific introspection tools.
What SQL dialects are supported?
The generator supports PostgreSQL, MySQL, and SQLite. Each dialect has specific type mappings — for example, auto-increment uses SERIAL in PostgreSQL, AUTO_INCREMENT in MySQL, and INTEGER PRIMARY KEY in SQLite. The generated SQL follows the syntax conventions of the selected dialect.
When should I NOT use this generator?
Avoid this generator when you need complex schemas with many-to-many relationships, partial indexes, or database-specific features like PostgreSQL's exclusion constraints. It's also not suitable for migrating existing production schemas — use a dedicated migration tool like Flyway, Liquibase, or Prisma Migrate for version-controlled schema changes.
Is my JSON data sent to any server?
No. All type inference and SQL generation happens entirely in your browser using JavaScript. Your JSON data is never sent to any server, stored, or transmitted. You can safely use this tool with sensitive or proprietary data.
Real-world Examples
Generating a Users Table from API Response
A REST API returns user objects with mixed types. The generator infers appropriate SQL types and adds a UNIQUE constraint on the email field automatically.
{"id":1,"name":"John Doe","email":"[email protected]","age":28,"isActive":true,"createdAt":"2024-01-15T10:30:00Z"}CREATE TABLE users ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL UNIQUE, age INT NOT NULL, isactive BOOLEAN NOT NULL, createdat TIMESTAMP NOT NULL );
Inferring JSON Columns for Nested Data
When JSON data contains nested objects, the generator suggests the JSON column type in PostgreSQL for flexible storage without flattening.
{"id":1,"name":"Widget","price":9.99,"specs":{"weight":"200g","color":"blue"}}CREATE TABLE products ( id SERIAL PRIMARY KEY, name VARCHAR(255) NOT NULL, price DECIMAL(10,4) NOT NULL, specs JSON NOT NULL );