Starting a new project means designing a database schema. You think about entities, relationships, indexes, and constraints. It takes hours. What if you could describe your app in English and get a complete schema?
In this tutorial, you’ll build a schema generator that takes natural language descriptions and produces SQL schemas. No API keys, no cloud calls, everything runs on your machine.
How It Works
- You describe your app: “An e-commerce platform with users, products, orders”
- AI generates tables, columns, relationships, and indexes
- You get production-ready SQL you can run immediately
Prerequisites
- Ollama installed
- Python 3.10+
- SQLite or PostgreSQL for testing
Step 1: Pull the model
ollama pull qwen2.5-coder:7bStep 2: Create the schema generator
#!/usr/bin/env python3
"""AI database schema generator using Ollama."""
import subprocess
import sys
import json
import urllib.request
OLLAMA_URL = "http://localhost:11434/api/generate"
MODEL = "qwen2.5-coder:7b"
PROMPT_TEMPLATE = """Generate a complete SQL database schema based on this description.
Description: {description}
Database: {db_type}
Requirements:
1. Use appropriate data types (INTEGER, TEXT, TIMESTAMP, etc.)
2. Add PRIMARY KEY and FOREIGN KEY constraints
3. Add indexes for frequently queried columns
4. Include created_at and updated_at timestamps where appropriate
5. Add CHECK constraints for data validation
6. Use proper naming conventions (snake_case, plural table names)
7. Include comments explaining design decisions
8. Output ONLY valid SQL, no explanations
Schema:"""
def generate_schema(description, db_type="postgresql"):
"""Generate a database schema from natural language."""
payload = json.dumps({
"model": MODEL,
"prompt": PROMPT_TEMPLATE.format(
description=description,
db_type=db_type
),
"stream": False,
"options": {"temperature": 0.3, "num_predict": 3000}
}).encode()
req = urllib.request.Request(
OLLAMA_URL,
data=payload,
headers={"Content-Type": "application/json"}
)
with urllib.request.urlopen(req, timeout=120) as resp:
response = json.loads(resp.read())["response"].strip()
# Remove markdown code blocks
if response.startswith("```sql"):
response = response[7:]
if response.startswith("```"):
response = response[3:]
if response.endswith("```"):
response = response[:-3]
return response.strip()
def generate_entity_diagram(schema_sql):
"""Generate a simple text-based entity diagram."""
prompt = f"""Generate a simple text-based entity-relationship diagram from this SQL schema.
Schema:
{schema_sql}
Format:
- List each entity with its attributes
- Show relationships between entities
- Use ASCII art style
Output ONLY the diagram, no explanations:"""
payload = json.dumps({
"model": MODEL,
"prompt": prompt,
"stream": False,
"options": {"temperature": 0.3, "num_predict": 1000}
}).encode()
req = urllib.request.Request(
OLLAMA_URL,
data=payload,
headers={"Content-Type": "application/json"}
)
with urllib.request.urlopen(req, timeout=60) as resp:
return json.loads(resp.read())["response"].strip()
if __name__ == "__main__":
description = input("Describe your application: ")
db_type = input("Database type (postgresql/sqlite/mysql) [postgresql]: ") or "postgresql"
print("\nGenerating schema...")
schema = generate_schema(description, db_type)
print("\n" + "=" * 60)
print("GENERATED SCHEMA")
print("=" * 60)
print(schema)
# Save to file
filename = "schema.sql"
with open(filename, "w") as f:
f.write(schema)
print(f"\nSaved to {filename}")
# Generate diagram
print("\nGenerating entity diagram...")
diagram = generate_entity_diagram(schema)
print("\n" + "=" * 60)
print("ENTITY DIAGRAM")
print("=" * 60)
print(diagram)Step 3: Run it
python schema_generator.pyExample Session
Describe your application: A blog platform with users, posts, comments, and tags
Database type (postgresql/sqlite/mysql) [postgresql]: postgresql
Generating schema...
============================================================
GENERATED SCHEMA
============================================================
-- Users table
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
password_hash VARCHAR(255) NOT NULL,
display_name VARCHAR(100),
bio TEXT,
avatar_url VARCHAR(500),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Posts table
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
title VARCHAR(255) NOT NULL,
slug VARCHAR(255) UNIQUE NOT NULL,
content TEXT NOT NULL,
status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
published_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Comments table
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
content TEXT NOT NULL,
parent_id INTEGER REFERENCES comments(id) ON DELETE CASCADE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Tags table
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
name VARCHAR(50) UNIQUE NOT NULL,
slug VARCHAR(50) UNIQUE NOT NULL
);
-- Post-Tags junction
CREATE TABLE post_tags (
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
-- Indexes
CREATE INDEX idx_posts_user_id ON posts(user_id);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_slug ON posts(slug);
CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_user_id ON comments(user_id);What Makes This Different
The AI understands domain concepts, not just SQL syntax:
- User authentication: Includes password_hash, not password
- Soft deletes: Adds deleted_at where appropriate
- Audit trails: Includes created_at/updated_at timestamps
- Proper constraints: CHECK constraints for status fields
- Junction tables: Automatically creates many-to-many relationships
Variations
- Schema comparison: Compare generated schema with existing database
- Migration generation: Generate ALTER TABLE statements for updates
- Documentation: Generate schema documentation automatically
- Test data: Generate realistic test data for the schema
My Take
This tool eliminates the blank-page problem of database design. Instead of spending hours thinking about tables and relationships, you describe your app and get a complete schema in seconds. The AI captures most of what you need, and you refine the details.
Rating: 8.5/10 — Saves hours on new projects. The generated schema is production-ready for most use cases.
Related Articles
- Ollama: How to Install and Run Local AI Models
- Best AI Models for Coding Locally
- How to Run Qwen 3.6 Locally
- Aider Setup Guide
- Best AI Coding Tools in 2026
FAQ
How accurate is the generated schema?
Very accurate for standard applications (blogs, e-commerce, SaaS). The AI understands common patterns and generates appropriate tables, relationships, and constraints. For specialized domains, review and adjust.
Can I generate schemas for existing databases?
Yes. Provide the existing schema as context, and the AI will generate new tables that fit the existing structure.
Does it handle database-specific features?
Yes. Specify the database type (PostgreSQL, MySQL, SQLite) and the AI uses appropriate syntax, data types, and features.
Can I customize the naming conventions?
Yes. Add naming requirements to the description: “Use camelCase table names” or “Prefix all tables with app_”.
Related: PostgreSQL Cheat Sheet · Database Design Best Practices · Ollama Complete Guide