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:

1. A type column to categorize the data
2. A data column to store the actual content as JSON

This structure allows for:

• Storing varied data structures in a single table
• Adding new data types without altering the table schema
• Maintaining queryability on common fields

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

1. We define an enum ItemTypeEnum for possible item types. This can be easily extended in the future.
2. For each item type, we create a Zod schema (e.g., UserSchema, ProductSchema). These schemas define the structure and validation rules for each type of item.
3. The ItemSchema combines all item types into a discriminated union, allowing for type-safe handling of different item structures.
4. The SQLite table Items is defined with two main columns:
   • type: A generated column that extracts the type from the JSON data
   • data: The JSON column that stores the actual item data
5. We use Drizzle's $type method to ensure type safety between TypeScript and the database.
6. An index is created on the type column for efficient querying.

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:

1. We use a useEffect hook to fetch items from the database when the component mounts.
2. The renderItem function uses a switch statement on the item.type to determine how to render each item. This demonstrates how we can have different UI representations for each item type, all stored in the same table.
3. TypeScript's discriminated union feature ensures that we have access to the correct properties for each item type within the respective case blocks.
4. We're treating all items as ItemSchemaType, which is the union type of all possible item schemas. This allows for type-safe access to properties based on the type field.

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:

• Applications with evolving data models
• Systems that need to store varied, user-defined structures
• Prototyping and rapid development where the final data model is not yet established

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