# 🗄️ FX ORM Plugin - Complete Cheat Sheet

## 🎯 **Plugin Overview**

**FX ORM** provides zero-latency database operations with CSS-style queries and reactive models. It uses SharedArrayBuffer + Worker bridge on client and direct database access on server for a completely synchronous surface.

### **Core Features**
- ✅ **Zero Latency** - Synchronous surface with background processing
- ✅ **CSS-Style Queries** - `[age>=21][status="active"]` syntax
- ✅ **Reactive Models** - Direct mutation triggers auto-save
- ✅ **Intent-First Results** - IDs only, lazy data hydration
- ✅ **Fuzzy Table Matching** - `$db.user` → `$db.users`
- ✅ **Cross-Realm Compatible** - Same API client and server

---

## 📦 **Installation & Setup**

### **Auto-Loading (Recommended)**

```javascript
// Just use $db - auto-loads fx-orm plugin
const users = $db.users.all();
await $db.connect('sqlite://./app.db');
```

### **Manual Loading**

```html
<script type="module">
import('./fx.js').then(({ fx, $$ }) => {
    import('./plugins/fx-orm.ts').then(plugin => {
        fx.pluginManager.register('orm', plugin.default, {
            global: '$db',
            defaultConnectionString: 'sqlite://./app.db'
        });
    });
});
</script>
```

---

## 🚀 **Core API Reference**

### **Connection Management**

| Method | Description | Example |
|--------|-------------|---------|
| `$db.connect(connectionString)` | Connect to database | `await $db.connect('postgresql://...')` |
| `$db.disconnect()` | Close connection | `await $db.disconnect()` |

### **Query Methods**

| Method | Description | Example |
|--------|-------------|---------|
| `.all()` | Get all records | `$db.users.all()` |
| `.first()` | Get first record | `$db.users.first()` |
| `.find(selector)` | Find by ID/selector | `$db.users.find('#42')` |
| `.where(conditions)` | Filter with CSS conditions | `$db.users.where('[age>=21]')` |
| `.create(data)` | Create new record | `$db.users.create({name: 'John'})` |

---

## 🎪 **CSS-Style Query Syntax**

### **Basic Selectors**

```javascript
// ID selector
const user = $db.users.find('#42');

// Attribute conditions
const adults = $db.users.where('[age>=21]');
const active = $db.users.where('[status="active"]');
const admins = $db.users.where('[role="admin"]');

// Multiple conditions
const activeAdults = $db.users.where('[age>=21][status="active"]');

// String matching
const johns = $db.users.where('[name^="John"]');        // Starts with
const gmails = $db.users.where('[email$="@gmail.com"]'); // Ends with
const managers = $db.users.where('[title*="Manager"]');  // Contains
```

### **Advanced Queries**

```javascript
// Ordering
const newest = $db.users.where('[status="active"]:orderBy(created,desc)');
const oldest = $db.users.where('[age>=18]:orderBy(age,asc)');

// Pagination
const page1 = $db.users.where('[active=true]:limit(10)');
const page2 = $db.users.where('[active=true]:offset(10):limit(10)');

// Complex queries
const complexQuery = $db.users.where(
  '[age>=21][status="active"]:orderBy(lastLogin,desc):limit(5)'
);

// First/last shortcuts
const newestUser = $db.users.where('[status="active"]:first');
const oldestUser = $db.users.where('[status="active"]:last');
```

---

## 🔄 **Reactive Models**

### **Direct Mutation (Auto-Save)**

```javascript
// Get reactive model
const user = $db.users.find('#42');

// Direct mutation triggers auto-save
user.name = 'Updated Name';           // Auto-saves after debounce
user.email = 'new@example.com';       // Batched with above
user.lastActive = Date.now();         // Batched with above

// All changes saved automatically with 600ms debounce
```

### **Reactive Queries**

```javascript
// Query results are reactive
const activeUsers = $db.users.where('[status="active"]');

// Watch for changes
activeUsers.watch(users => {
  console.log('Active users changed:', users.length);
  $('#user-count').text(users.length);
});

// When users change status, the query result updates automatically
const user = $db.users.find('#42');
user.status = 'inactive'; // Triggers activeUsers watcher
```

### **Intent-First Results**

```javascript
// Query returns immediately with IDs (fast)
const users = $db.users.where('[age>=25]'); // ~1ms
console.log('Count:', users.length);        // Immediate

// Data loads lazily on access
const firstName = users[0].name;             // Triggers data load
const secondName = users[1].name;           // Uses cached connection
```

---

## 🎯 **CRUD Operations**

### **Create Records**

```javascript
// Simple create
const userId = $db.users.create({
  name: 'John Doe',
  email: 'john@example.com',
  age: 30,
  status: 'active'
});

// Bulk create
const userIds = $db.users.createMany([
  { name: 'User 1', email: 'user1@example.com' },
  { name: 'User 2', email: 'user2@example.com' },
  { name: 'User 3', email: 'user3@example.com' }
]);

// Create with relationships
const postId = $db.posts.create({
  title: 'My Post',
  content: 'Post content',
  authorId: userId,
  tags: ['javascript', 'fx', 'database']
});
```

### **Read Operations**

```javascript
// Various read patterns
const allUsers = $db.users.all();
const firstUser = $db.users.first();
const specificUser = $db.users.find('#123');

// Filtered reads
const recentPosts = $db.posts.where('[created>=1704067200000]'); // Since timestamp
const publishedPosts = $db.posts.where('[published=true]:orderBy(created,desc)');

// Joins (if supported by adapter)
const postsWithAuthors = $db.posts
  .where('[published=true]')
  .include(['author', 'comments']);
```

### **Update Operations**

```javascript
// Direct mutation (reactive)
const user = $db.users.find('#123');
user.lastLogin = Date.now();     // Auto-saves
user.loginCount += 1;            // Auto-saves

// Bulk updates
$db.users.where('[status="pending"]').updateAll({
  status: 'approved',
  approvedAt: Date.now()
});

// Conditional updates
const post = $db.posts.find('#456');
if (post.status === 'draft') {
  post.status = 'published';
  post.publishedAt = Date.now();
}
```

### **Delete Operations**

```javascript
// Delete single record
const user = $db.users.find('#123');
user.delete(); // Or $db.users.delete('#123')

// Bulk delete
$db.users.where('[status="inactive"][lastLogin<86400000]').deleteAll();

// Soft delete (if configured)
user.status = 'deleted';
user.deletedAt = Date.now();
```

---

## 🔧 **Database Configuration**

### **Connection Strings**

```javascript
// SQLite
await $db.connect('sqlite://./app.db');
await $db.connect('sqlite://./data/production.db');

// PostgreSQL
await $db.connect('postgresql://user:pass@localhost:5432/myapp');
await $db.connect('postgresql://user:pass@prod-db.com:5432/prod_db');

// MySQL
await $db.connect('mysql://user:pass@localhost:3306/myapp');

// MongoDB
await $db.connect('mongodb://localhost:27017/myapp');

// Multiple connections
await $db.connect('postgresql://...', { name: 'primary' });
await $db.connect('redis://...', { name: 'cache' });

// Use specific connection
const primaryUsers = $db('users', 'primary');
const cacheData = $db('sessions', 'cache');
```

### **Schema Definition**

```javascript
// Define table schemas (optional but recommended)
$db.defineSchema('users', {
  id: { type: 'integer', primary: true, autoIncrement: true },
  name: { type: 'string', required: true, maxLength: 100 },
  email: { type: 'string', required: true, unique: true },
  age: { type: 'integer', min: 0, max: 150 },
  status: { type: 'enum', values: ['active', 'inactive', 'banned'] },
  created: { type: 'timestamp', default: 'now' },
  updated: { type: 'timestamp', default: 'now', onUpdate: 'now' }
});

$db.defineSchema('posts', {
  id: { type: 'integer', primary: true, autoIncrement: true },
  title: { type: 'string', required: true },
  content: { type: 'text' },
  authorId: { type: 'integer', references: 'users.id' },
  published: { type: 'boolean', default: false },
  tags: { type: 'json' }
});
```

---

## 🎪 **Advanced Patterns**

### **Reactive Aggregations**

```javascript
// Real-time statistics
const userStats = {
  total: $db.users.all().length,
  active: $db.users.where('[status="active"]').length,
  newToday: $db.users.where('[created>=86400000]').length
};

// Watch aggregations
$db.users.all().watch(users => {
  $$('stats.totalUsers').set(users.length);
  $$('stats.lastUpdate').set(Date.now());
});

$db.users.where('[status="active"]').watch(activeUsers => {
  $$('stats.activeUsers').set(activeUsers.length);
  updateDashboard();
});
```

### **Data Synchronization**

```javascript
// Sync with external APIs
const syncUsers = async () => {
  const apiUsers = $api.get('/external/users');

  apiUsers.watch(externalUsers => {
    externalUsers.forEach(apiUser => {
      const localUser = $db.users.find(`#${apiUser.id}`);

      if (localUser) {
        // Update existing
        localUser.name = apiUser.name;
        localUser.email = apiUser.email;
        localUser.lastSync = Date.now();
      } else {
        // Create new
        $db.users.create({
          ...apiUser,
          synced: true,
          syncedAt: Date.now()
        });
      }
    });
  });
};

// Auto-sync every 5 minutes
setInterval(syncUsers, 5 * 60 * 1000);
```

### **Complex Relationships**

```javascript
// User with posts and comments
const userWithPosts = $db.users.find('#42');
const userPosts = $db.posts.where(`[authorId=${userWithPosts.id}]`);
const userComments = $db.comments.where(`[userId=${userWithPosts.id}]`);

// Reactive user profile
const createUserProfile = (userId) => {
  const profile = {
    user: $db.users.find(`#${userId}`),
    posts: $db.posts.where(`[authorId=${userId}]`),
    comments: $db.comments.where(`[userId=${userId}]`),
    followers: $db.follows.where(`[followingId=${userId}]`)
  };

  // Watch for changes
  profile.user.watch(() => updateProfileUI());
  profile.posts.watch(() => updatePostsList());

  return profile;
};
```

---

## 🚨 **Best Practices**

### ✅ **DO: Efficient Queries**

```javascript
// ✅ Use specific conditions
const recentActiveUsers = $db.users.where('[status="active"][lastLogin>=86400000]');

// ✅ Use pagination for large datasets
const usersPage = $db.users.where('[status="active"]:orderBy(created,desc):limit(20):offset(40)');

// ✅ Use reactive models for frequent updates
const user = $db.users.find('#42');
user.loginCount += 1; // Auto-saves efficiently

// ✅ Batch related updates
const updateUserProfile = (userId, data) => {
  const user = $db.users.find(`#${userId}`);
  Object.assign(user, data); // All changes batched and saved together
};
```

### ❌ **DON'T: Anti-patterns**

```javascript
// ❌ Don't query in loops
users.forEach(user => {
  const posts = $db.posts.where(`[authorId=${user.id}]`); // Inefficient!
});

// ✅ DO: Use joins or aggregate queries
const usersWithPostCounts = $db.users.all().map(user => ({
  ...user,
  postCount: $db.posts.where(`[authorId=${user.id}]`).length
}));

// ❌ Don't ignore connection management
$db.users.all(); // Without connecting first

// ✅ DO: Always connect first
await $db.connect('sqlite://./app.db');
const users = $db.users.all();
```

---

## 🎊 **Integration with FX Ecosystem**

### **With fx-flow (Business Logic)**

```javascript
const userRegistrationFlow = $flow('userRegistration')
  .node('validateData', {
    runsOn: 'client',
    effect: (ctx) => {
      if (!ctx.in.email.includes('@')) {
        ctx.error('Invalid email');
        return;
      }
      ctx.next('checkDuplicate', ctx.in);
    }
  })
  .node('checkDuplicate', {
    runsOn: 'server',
    effect: (ctx) => {
      const existing = $db.users.where(`[email="${ctx.in.email}"]`).first();
      if (existing) {
        ctx.error('Email already exists');
        return;
      }
      ctx.next('createUser', ctx.in);
    }
  })
  .node('createUser', {
    runsOn: 'server',
    effect: (ctx) => {
      const userId = $db.users.create(ctx.in);
      ctx.set({ userId, created: true });
    }
  });
```

### **With fx-safe (Resilience)**

```javascript
// Resilient database operations
const safeUserFetch = (userId) => {
  return $safe.circuit('database').execute(() => {
    return $db.users.find(`#${userId}`);
  });
};

// Retry failed operations
const retryableCreate = (userData) => {
  return $safe.retry('user-creation', () => {
    return $db.users.create(userData);
  }, { maxAttempts: 3, backoffMs: 1000 });
};
```

### **With fx-cache (Performance)**

```javascript
// Cache expensive queries
const getCachedUserStats = () => {
  return $cache.getOrSet('user-stats', () => {
    return {
      total: $db.users.all().length,
      active: $db.users.where('[status="active"]').length,
      premium: $db.users.where('[plan="premium"]').length
    };
  }, { ttl: 300000 }); // 5 minutes
};

// Cache individual records
const getCachedUser = (userId) => {
  return $cache.getOrSet(`user:${userId}`, () => {
    return $db.users.find(`#${userId}`);
  }, { ttl: 600000 }); // 10 minutes
};
```

---

## 🎯 **Real-World Examples**

### **User Management System**

```javascript
class UserService {
  static async initialize() {
    await $db.connect(Deno.env.get('DATABASE_URL') || 'sqlite://./users.db');
    console.log('📊 Database connected');
  }

  static getAllUsers(filters = {}) {
    let query = '';

    if (filters.status) query += `[status="${filters.status}"]`;
    if (filters.minAge) query += `[age>=${filters.minAge}]`;
    if (filters.search) query += `[name*="${filters.search}"]`;

    query += ':orderBy(created,desc)';

    if (filters.limit) query += `:limit(${filters.limit})`;

    return $db.users.where(query);
  }

  static createUser(userData) {
    // Validate
    if (!userData.email?.includes('@')) {
      throw new Error('Invalid email');
    }

    // Check duplicates
    const existing = $db.users.where(`[email="${userData.email}"]`).first();
    if (existing) {
      throw new Error('Email already exists');
    }

    // Create user
    const userId = $db.users.create({
      ...userData,
      status: 'active',
      created: Date.now(),
      lastLogin: null
    });

    // Update stats
    $$('stats.totalUsers').set($db.users.all().length);

    return $db.users.find(`#${userId}`);
  }

  static updateUserProfile(userId, updates) {
    const user = $db.users.find(`#${userId}`);

    if (!user) {
      throw new Error('User not found');
    }

    // Direct mutation - auto-saves
    Object.assign(user, {
      ...updates,
      updated: Date.now()
    });

    return user;
  }

  static getUserActivity(userId) {
    const user = $db.users.find(`#${userId}`);
    const posts = $db.posts.where(`[authorId=${userId}]`);
    const comments = $db.comments.where(`[userId=${userId}]`);

    return {
      user,
      activity: {
        posts: posts.length,
        comments: comments.length,
        lastPost: posts.where(':orderBy(created,desc):first'),
        lastComment: comments.where(':orderBy(created,desc):first')
      }
    };
  }
}

// Usage
await UserService.initialize();

const users = UserService.getAllUsers({ status: 'active', minAge: 18 });
const newUser = UserService.createUser({
  name: 'Jane Doe',
  email: 'jane@example.com',
  age: 25
});

const activity = UserService.getUserActivity(newUser.id);
```

### **E-commerce Product Catalog**

```javascript
class ProductCatalog {
  static getFeaturedProducts() {
    return $db.products.where('[featured=true][available=true]:orderBy(priority,desc):limit(10)');
  }

  static searchProducts(query, filters = {}) {
    let whereClause = `[name*="${query}"]`;

    if (filters.category) whereClause += `[category="${filters.category}"]`;
    if (filters.minPrice) whereClause += `[price>=${filters.minPrice}]`;
    if (filters.maxPrice) whereClause += `[price<=${filters.maxPrice}]`;
    if (filters.inStock) whereClause += '[stock>0]';

    whereClause += ':orderBy(relevance,desc)';
    if (filters.limit) whereClause += `:limit(${filters.limit})`;

    return $db.products.where(whereClause);
  }

  static getProductWithReviews(productId) {
    const product = $db.products.find(`#${productId}`);
    const reviews = $db.reviews.where(`[productId=${productId}][approved=true]:orderBy(created,desc)`);

    // Reactive average rating
    const avgRating = reviews.length > 0 ?
      reviews.reduce((sum, review) => sum + review.rating, 0) / reviews.length :
      0;

    // Update product rating reactively
    product.averageRating = avgRating;
    product.reviewCount = reviews.length;

    return { product, reviews, avgRating };
  }

  static updateInventory(productId, quantity) {
    const product = $db.products.find(`#${productId}`);

    if (product) {
      product.stock = quantity;
      product.lastUpdated = Date.now();

      // Trigger reactive updates
      if (quantity === 0) {
        product.available = false;
        $$('inventory.outOfStock').set([
          ...$$('inventory.outOfStock').get(),
          productId
        ]);
      }
    }

    return product;
  }
}

// Reactive inventory monitoring
$db.products.where('[stock<=reorderLevel]').watch(lowStockProducts => {
  $$('inventory.lowStock').set(lowStockProducts);

  if (lowStockProducts.length > 0) {
    $$('notifications.inventory').set({
      type: 'warning',
      message: `${lowStockProducts.length} products need reordering`,
      timestamp: Date.now()
    });
  }
});
```

This comprehensive cheat sheet covers FX ORM's powerful database capabilities, from basic CRUD operations to advanced reactive patterns and real-world integration examples.