Flexible schema using Drizzle and Zod

In many applications, data structures can evolve rapidly or vary significantly between entries. Relational database schemas can be limiting in these scenarios, often requiring migrations for even minor changes.

With some experimenting, I found an approach that combines the structure of SQL with the flexibility of document databases, using SQLite's JSON capabilities and Drizzle ORM.

The Concept

The core idea is to create a table with two main columns:

This structure allows for:

Implementation

Let's implement this concept using SQLite and Drizzle ORM. We'll create a generic items table that could store various types of data.

import { z } from "zod";
import { sqliteTable, json, text, index } from "drizzle-orm/sqlite-core";

// Define possible item types
export const ItemTypeEnum = z.enum(["user", "product", "order"]);
export type ItemType = z.infer<typeof ItemTypeEnum>;

// Define schemas for each item type
export const UserSchema = z.object({
  type: z.literal(ItemTypeEnum.enum.user),
  name: z.string(),
  email: z.string().email(),
  age: z.number().optional(),
});

export const ProductSchema = z.object({
  type: z.literal(ItemTypeEnum.enum.product),
  name: z.string(),
  price: z.number(),
  categories: z.array(z.string()),
});

export const OrderSchema = z.object({
  type: z.literal(ItemTypeEnum.enum.order),
  user_id: z.number(),
  products: z.array(z.number()),
  total: z.number(),
  status: z.enum(["pending", "shipped", "delivered"]),
});

// Combine all item types into a discriminated union
export const ItemSchema = z.discriminatedUnion("type", [
  UserSchema,
  ProductSchema,
  OrderSchema,
]);
export type ItemSchemaType = z.infer<typeof ItemSchema>;

// Define the SQLite table
export const Items = sqliteTable("items", {
  type: text("type").generatedAs(`data ->> '$.type'`).$type<ItemType>(),
  data: json("data").$type<ItemSchemaType>().notNull(),
}, (table) => ({
  type_index: index("idx_type").on(table.type),
}));

Code Explanation

Practical Examples

Inserting Data

Here's how you might insert different types of items into the database:

import { db } from './db'; // Assume this is your Drizzle database instance
import { Items, ItemSchema } from './schema'; // From the previous example

async function insertItem(item: z.infer<typeof ItemSchema>) {
  try {
    const validatedItem = ItemSchema.parse(item);
    await db.insert(Items).values({
      data: validatedItem,
    });
    console.log('Item inserted successfully');
  } catch (error) {
    console.error('Error inserting item:', error);
  }
}

// Example usage:
insertItem({
  type: 'user',
  name: 'John Doe',
  email: 'john@example.com',
  age: 30,
});

insertItem({
  type: 'product',
  name: 'Widget',
  price: 19.99,
  categories: ['electronics', 'gadgets'],
});

Reading and Rendering Data in React

Now let's create a React component that fetches and renders a list of items, using the type field to determine how to display each item:

import React, { useState, useEffect } from 'react';
import { db } from './db';
import { Items, ItemSchemaType } from './schema';

const ItemList: React.FC = () => {
  const [items, setItems] = useState<ItemSchemaType[]>([]);

  useEffect(() => {
    const fetchItems = async () => {
      const fetchedItems = await db.select().from(Items);
      setItems(fetchedItems.map(item => item.data as ItemSchemaType));
    };
    fetchItems();
  }, []);

  const renderItem = (item: ItemSchemaType) => {
    switch (item.type) {
      case 'user':
        return (
          <div className="user-item">
            <h3>{item.name}</h3>
            <p>Email: {item.email}</p>
            {item.age && <p>Age: {item.age}</p>}
          </div>
        );
      case 'product':
        return (
          <div className="product-item">
            <h3>{item.name}</h3>
            <p>Price: ${item.price.toFixed(2)}</p>
            <p>Categories: {item.categories.join(', ')}</p>
          </div>
        );
      case 'order':
        return (
          <div className="order-item">
            <h3>Order #{item.user_id}</h3>
            <p>Total: ${item.total.toFixed(2)}</p>
            <p>Status: {item.status}</p>
          </div>
        );
      default:
        return <div>Unknown item type</div>;
    }
  };

  return (
    <div className="item-list">
      {items.map((item, index) => (
        <div key={index} className="item">
          {renderItem(item)}
        </div>
      ))}
    </div>
  );
};

export default ItemList;

In this example:

Conclusion

This flexible schema approach allows us to store diverse data structures in a single table while maintaining type safety and the ability to query efficiently. By using Zod for runtime validation and TypeScript for static typing, we can ensure data integrity while keeping our database schema flexible.

The React example demonstrates how this approach can be particularly powerful in front-end applications, allowing for dynamic rendering based on the data type without needing separate API endpoints or tables for each data type.

Remember to consider performance implications when querying large datasets, and always validate data on insertion to maintain data integrity.

Benefits and Use Cases

This approach is particularly useful for:

It allows you to add new item types or fields without altering the table structure, while still maintaining some level of structure and queryability.