Generate database schemas and Pydantic/SQLAlchemy models with AI-powered natural language prompts. Keep your code and database in sync with automated model generation and schema management for seamless Python integration.

How it works

When you describe your database needs in natural language, GibsonAI generates both the MySQL database schema and corresponding Pydantic schemas and SQLAlchemy models. Your code and database stay synchronized through automatic model generation and schema management.

Key Features

Natural Language Schema Generation

  • Plain English Prompts: Describe your database needs in simple language
  • Context Understanding: AI understands relationships and constraints
  • Optimization Suggestions: Get suggestions for better performance
  • Iterative Refinement: Refine schemas through conversation

Automatic Python Model Generation

  • Pydantic Schemas: Type-safe validation schemas for all tables
  • SQLAlchemy Models: Complete ORM models with relationships
  • Code Synchronization: Models automatically updated with schema changes
  • Python Integration: Ready-to-use code for immediate integration

Text-to-SQL Analysis

  • Natural Language Queries: Ask questions about your data
  • Gibson Studio: Run generated SQL queries in the intuitive data management UI
  • Schema Analysis: Analyze table relationships and data patterns
  • Performance Insights: Get recommendations for optimization

Step-by-step guide

1. Generate schema with natural language

# Create a comprehensive schema
gibson modify "Create an e-commerce system with users, products, orders, and payments. Users can have multiple orders, each order contains multiple products, and each order has one payment record"

2. Generate Python models

# Generate Pydantic schemas for validation
gibson code schemas

# Generate SQLAlchemy models for ORM
gibson code models

# Generate all Python code
gibson code base

3. Explore with text-to-SQL

Use Gibson Studio to analyze your generated schema:

  • "Show me the relationship between users and orders"
  • "Which tables have foreign key constraints?"
  • "What's the structure of the products table?"
  • "Find any tables without primary keys"

4. Access your data

Integration options:

  • RESTful APIs: Base URL https://api.gibsonai.com
    • SQL queries: /v1/-/query
    • Table operations: /v1/-/[table-name-in-kebab-case]
  • OpenAPI Spec: Available in your project settings
  • Direct Connection: Connection string available in the UI
  • API Documentation: Available in the data API section

Example schema generation

Natural language prompt

"Create a blog system with users, posts, and comments. Users can write multiple posts, and each post can have multiple comments. Include timestamps, user roles, and post categories."

Generated MySQL schema

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role ENUM('user', 'admin') DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE categories (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    user_id INT NOT NULL,
    category_id INT NOT NULL,
    published BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (category_id) REFERENCES categories(id)
);

CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    content TEXT NOT NULL,
    user_id INT NOT NULL,
    post_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id),
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE
);

Generated Pydantic schemas

from pydantic import BaseModel, EmailStr
from datetime import datetime
from typing import Optional
from enum import Enum

class UserRole(str, Enum):
    USER = "user"
    ADMIN = "admin"

class User(BaseModel):
    id: Optional[int] = None
    username: str
    email: EmailStr
    password_hash: str
    role: UserRole = UserRole.USER
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None

class Category(BaseModel):
    id: Optional[int] = None
    name: str
    description: Optional[str] = None
    created_at: Optional[datetime] = None

class Post(BaseModel):
    id: Optional[int] = None
    title: str
    content: str
    user_id: int
    category_id: int
    published: bool = False
    created_at: Optional[datetime] = None
    updated_at: Optional[datetime] = None

class Comment(BaseModel):
    id: Optional[int] = None
    content: str
    user_id: int
    post_id: int
    created_at: Optional[datetime] = None

Generated SQLAlchemy models

from sqlalchemy import Column, Integer, String, Text, Boolean, DateTime, ForeignKey, Enum
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship
from datetime import datetime
import enum

Base = declarative_base()

class UserRole(enum.Enum):
    USER = "user"
    ADMIN = "admin"

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True, autoincrement=True)
    username = Column(String(50), unique=True, nullable=False)
    email = Column(String(255), unique=True, nullable=False)
    password_hash = Column(String(255), nullable=False)
    role = Column(Enum(UserRole), default=UserRole.USER)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    posts = relationship("Post", back_populates="user")
    comments = relationship("Comment", back_populates="user")

class Category(Base):
    __tablename__ = 'categories'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(100), unique=True, nullable=False)
    description = Column(Text)
    created_at = Column(DateTime, default=datetime.utcnow)

    posts = relationship("Post", back_populates="category")

class Post(Base):
    __tablename__ = 'posts'

    id = Column(Integer, primary_key=True, autoincrement=True)
    title = Column(String(255), nullable=False)
    content = Column(Text, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    category_id = Column(Integer, ForeignKey('categories.id'), nullable=False)
    published = Column(Boolean, default=False)
    created_at = Column(DateTime, default=datetime.utcnow)
    updated_at = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    user = relationship("User", back_populates="posts")
    category = relationship("Category", back_populates="posts")
    comments = relationship("Comment", back_populates="post", cascade="all, delete-orphan")

class Comment(Base):
    __tablename__ = 'comments'

    id = Column(Integer, primary_key=True, autoincrement=True)
    content = Column(Text, nullable=False)
    user_id = Column(Integer, ForeignKey('users.id'), nullable=False)
    post_id = Column(Integer, ForeignKey('posts.id'), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

    user = relationship("User", back_populates="comments")
    post = relationship("Post", back_populates="comments")

Integration examples

Using Pydantic for validation

from pydantic import ValidationError

# Validate user input
try:
    user_data = {
        "username": "john_doe",
        "email": "john@example.com",
        "password_hash": "hashed_password",
        "role": "admin"
    }
    user = User(**user_data)
    print(f"Valid user: {user.username}")
except ValidationError as e:
    print(f"Validation error: {e}")

Using SQLAlchemy for database operations

from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Use connection string from GibsonAI UI
engine = create_engine("your-connection-string-from-ui")
Session = sessionmaker(bind=engine)
session = Session()

# Create new user
new_user = User(
    username="jane_doe",
    email="jane@example.com",
    password_hash="hashed_password",
    role=UserRole.USER
)
session.add(new_user)
session.commit()

# Query with relationships
posts_with_authors = session.query(Post).join(User).filter(
    User.role == UserRole.ADMIN
).all()

Using RESTful APIs

import requests

# Create a new post
post_data = {
    "title": "My First Post",
    "content": "This is the content of my first post.",
    "user_id": 1,
    "category_id": 1,
    "published": True
}

response = requests.post("https://api.gibsonai.com/v1/-/posts", json=post_data)
new_post = response.json()

# Get posts with filtering
response = requests.get("https://api.gibsonai.com/v1/-/posts?published=true&limit=10")
posts = response.json()

Use cases

What's next?

Try it on GibsonAI!

GibsonAI is an AI-powered developer platform that lets you design, build, and deploy production-grade serverless databases in minutes — using just natural language prompts.

Sign Up