> ## Documentation Index > Fetch the complete documentation index at: https://docs.chameleondb.dev/llms.txt > Use this file to discover all available pages before exploring further. # chameleon introspect > Generate ChameleonDB schema from existing database ## Synopsis ```bash theme={null} chameleon introspect [flags] ``` Introspect an existing database and generate a ChameleonDB schema. ## Description The `introspect` command: 1. **Connects to existing database** - Uses provided connection string 2. **Scans all tables** - Detects user tables, columns, types 3. **Generates schema** - Creates ChameleonDB `.cham` schema file 4. **Respects paranoid mode** - Blocked in readonly mode 5. **Handles overwrites safely** - Prompts before replacing existing schemas **Supported databases:** * ✅ PostgreSQL * 🚧 MySQL (coming soon) * 🚧 SQLite (coming soon) ## Arguments Database connection string or environment variable reference **Formats:** * Direct URL: `postgresql://user:pass@localhost/db` * Env variable: `$DATABASE_URL` * Env variable: `${DATABASE_URL}` * Env variable: `env:DATABASE_URL` ## Flags Output file path (auto-prefixed with `schemas/`) ```bash theme={null} chameleon introspect postgresql://... -o myschema.cham ``` Result: `schemas/myschema.cham` Force overwrite without confirmation ```bash theme={null} chameleon introspect postgresql://... --force ``` **Warning:** Use with caution. Overwrites existing schema without backup. ## Examples ### Basic Introspection ```bash theme={null} chameleon introspect postgresql://user:password@localhost/mydb ``` **Output:** ``` ℹ Paranoid Mode active: standard ℹ Introspecting database... ✓ Database detected ℹ Scanning tables... ✓ Found 3 table(s) ℹ Generating schema... ✓ Schema written to schemas/schema.cham ℹ Next steps: 1. Review schema and adjust relations manually 2. Run: chameleon validate 3. Use with your application ``` ### Custom Output File ```bash theme={null} chameleon introspect postgresql://localhost/blog -o blog.cham ``` **Output:** ``` ✓ Schema written to schemas/blog.cham ``` ### Using Environment Variable ```bash theme={null} export DATABASE_URL="postgresql://user:password@localhost/mydb" chameleon introspect $DATABASE_URL ``` Or: ```bash theme={null} chameleon introspect '${DATABASE_URL}' chameleon introspect env:DATABASE_URL ``` ### Force Overwrite ```bash theme={null} chameleon introspect postgresql://localhost/mydb --force ``` **Output:** ``` ✓ Schema written to schemas/schema.cham (overwritten) ``` ## Before Introspection ### Existing Database **PostgreSQL tables:** ```sql theme={null} CREATE TABLE users ( id UUID PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, name VARCHAR(255), created_at TIMESTAMP DEFAULT NOW() ); CREATE TABLE posts ( id UUID PRIMARY KEY, title VARCHAR(255) NOT NULL, content TEXT, author_id UUID REFERENCES users(id), published BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT NOW() ); ``` ## After Introspection ### Generated Schema **schemas/schema.cham:** ```chameleon theme={null} entity User { id: uuid primary, email: string unique, name: string, created_at: timestamp default now(), } entity Post { id: uuid primary, title: string, content: string, author_id: uuid, published: bool default false, created_at: timestamp default now(), } ``` ### Manual Adjustments You may need to add relationships manually: ```chameleon theme={null} entity User { id: uuid primary, email: string unique, name: string, posts: [Post] via author_id, // Added manually created_at: timestamp default now(), } entity Post { id: uuid primary, title: string, content: string, author_id: uuid, author: User, // Added manually published: bool default false, created_at: timestamp default now(), } ``` ## Paranoid Mode Behavior ### Readonly Mode (Default) ```bash theme={null} chameleon introspect postgresql://localhost/mydb ``` **Error:** ``` ❌ Read Only Paranoid Mode is active - introspection is blocked ℹ Mode upgrade available: 1. Run: chameleon config auth set-password 2. Run: chameleon config set mode=standard 3. Retry: chameleon introspect ❌ readonly mode: introspect is blocked ``` ### Standard Mode ```bash theme={null} # Upgrade mode first chameleon config set mode=standard # Now introspection works chameleon introspect postgresql://localhost/mydb ✓ Schema written to schemas/schema.cham ``` ## Overwrite Protection ### Default Schema Detected If `schemas/schema.cham` contains the default schema from `chameleon init`: ```bash theme={null} chameleon introspect postgresql://localhost/mydb ``` **Output:** ``` ⚠️ Found default schema.cham from 'chameleon init' ℹ File exists: schemas/schema.cham Overwrite? (yes/no): yes ✓ Schema written to schemas/schema.cham ``` ### Modified Schema Detected If `schemas/schema.cham` has custom entities: ```bash theme={null} chameleon introspect postgresql://localhost/mydb ``` **Output:** ``` ❌ Found modified schema.cham with custom entities! ⚠️ This appears to be a working schema file ℹ Existing schema detected: schemas/schema.cham Options: 1. Create backup and overwrite (recommended) 2. Use different output file 3. Cancel Choose option (1-3): 1 ✓ Backup created: schemas/schema.cham.backup ✓ Schema written to schemas/schema.cham ``` ### Using Different Output File ``` Choose option (1-3): 2 Enter new output file path: introspected.cham ℹ Auto-added .cham extension: introspected.cham ℹ Auto-prefixed with schemas/: schemas/introspected.cham ✓ Schema written to schemas/introspected.cham ``` ## Connection String Formats ### PostgreSQL ```bash theme={null} # Full URL chameleon introspect "postgresql://user:password@localhost:5432/dbname" # Without password (prompts) chameleon introspect "postgresql://user@localhost/dbname" # Unix socket chameleon introspect "postgresql:///dbname?host=/var/run/postgresql" # SSL mode chameleon introspect "postgresql://user:pass@host/db?sslmode=require" ``` ### Environment Variable References ```bash theme={null} # Using $ chameleon introspect $DATABASE_URL # Using ${} chameleon introspect '${DATABASE_URL}' # Using env: prefix chameleon introspect env:DATABASE_URL ``` ## Error Handling ### Connection Failed ```bash theme={null} chameleon introspect postgresql://localhost/mydb ``` **Error:** ``` ❌ failed to connect to database: connection refused ℹ Check: - Database is running - Connection string is correct - Network connectivity ``` ### Database Not Detected ```bash theme={null} chameleon introspect mysql://localhost/mydb ``` **Error:** ``` ❌ failed to detect database type ℹ Supported: - PostgreSQL (postgresql://) - MySQL (coming soon) - SQLite (coming soon) ``` ### No Tables Found ```bash theme={null} chameleon introspect postgresql://localhost/emptydb ``` **Output:** ``` ✓ Database detected ℹ Scanning tables... ✓ Found 0 table(s) ℹ Generating schema... ✓ Schema written to schemas/schema.cham ⚠️ Warning: No tables found in database ``` **Generated schema:** ```chameleon theme={null} // No tables found in database ``` ### Permission Denied ```bash theme={null} chameleon introspect postgresql://user@localhost/mydb ``` **Error:** ``` ❌ failed to scan tables: permission denied ℹ Ensure database user has SELECT permissions: GRANT SELECT ON ALL TABLES IN SCHEMA public TO user; ``` ## Type Mapping ### PostgreSQL to ChameleonDB | PostgreSQL | ChameleonDB | | -------------------------- | ----------- | | `UUID` | `uuid` | | `VARCHAR`, `TEXT` | `string` | | `INTEGER`, `BIGINT` | `int` | | `BOOLEAN` | `bool` | | `TIMESTAMP`, `TIMESTAMPTZ` | `timestamp` | | `NUMERIC`, `DECIMAL` | `decimal` | | `JSONB`, `JSON` | `json` | ## Limitations ### Not Detected * **Relationships** - Foreign keys detected but not converted to relations * **Indexes** - Not included in schema * **Triggers** - Not supported * **Views** - Only tables are introspected * **Constraints** - Only PRIMARY and UNIQUE detected ### Manual Post-Processing After introspection, you may need to: 1. **Add relationships:** ```chameleon theme={null} posts: [Post] via author_id, author: User, ``` 2. **Add indexes:** ```chameleon theme={null} @index([email]) ``` 3. **Refine types:** ```chameleon theme={null} // Change generic 'string' to more specific types email: string, // Could be: email: string @format(email) ``` ## Best Practices ### 1. Introspect to New File ```bash theme={null} chameleon introspect postgresql://... -o introspected.cham ``` Preserves existing schema for comparison. ### 2. Review Before Committing ```bash theme={null} # Generate schema chameleon introspect postgresql://localhost/mydb # Review changes cat schemas/schema.cham # Validate chameleon validate # Commit git add schemas/schema.cham git commit -m "Add introspected schema" ``` ### 3. Use in CI/CD ```yaml theme={null} # .github/workflows/introspect.yml name: Introspect Database on: schedule: - cron: '0 0 * * 0' # Weekly jobs: introspect: runs-on: ubuntu-latest steps: - uses: actions/checkout@v3 - name: Introspect run: | chameleon introspect ${{ secrets.DATABASE_URL }} -o weekly.cham - name: Create PR if changes run: | git diff --exit-code schemas/weekly.cham || \ gh pr create --title "Schema drift detected" ``` ## See Also * [`chameleon init`](/cli/init) - Initialize new project * [`chameleon validate`](/cli/validate) - Validate generated schema * [`chameleon migrate`](/cli/migrate) - Apply schema to database * [Schema Reference](/schema/entities) - Learn schema syntax * [Integrity Modes](/concepts/integrity-modes) - Understand paranoid modes