How to build a CMS with React-Admin

In this post, I will explain how to build a CMS proof of concept using React-Admin and Supabase. You can check out the result in the video below:

I've been working with react-admin on various projects, some of which required basic CMS features in addition to the core application. We've used headless CMS like Strapi, Directus, or Prismic for these features. However, react-admin is so powerful that it can be used to build the CMS part, too. That's why I worked on a CMS proof-of-concept using react-admin for the admin UI and Supabase (which provides a REST API) for the backend.

Defining The Data Model

A CMS requires a data model that is both flexible and dynamic, allowing the addition of new entities and new fields to existing entities. The following diagram represents the data model I came up with:

Entities like posts and pages are stored in an entities table, while fields such as title or content are stored in a fields table. Since an entity can have multiple fields and fields can be shared by multiple entities, it is a many-to-many relationship materialized by the entities_fields table. A separate field_types table is needed to store the different PostgreSQL types of fields (text, integer, boolean, etc.).

If we follow this structure for posts and pages entities, it results in the following data being stored in the configuration tables:

field_types

fields

entities

entities_fields

Using SQL Triggers For Dynamic Tables

The data model described above is the configuration model. I'll need a second model to store data for posts and pages. For instance, when a 'posts' record is added to the entities table, I want to create a posts table. The actual posts will be stored in this posts table. Similarly, when a new post field is added to the entities_fields table, I want to add a column to the posts table.

So I need to implement logic to dynamically create, update, or delete entities table based on the data in the entities, fields, and field_types tables. I’ll use SQL triggers to achieve this. They can be configured directly within the Supabase Studio interface.

The first trigger handles entity tables. It automatically creates, updates, and deletes the table associated with an entity whenever a row is inserted, updated, or removed from the entities table:

DECLARE
    old_table_name TEXT;
    new_table_name TEXT;
BEGIN
    -- Handle DELETE case
    IF TG_OP = 'DELETE' THEN
        -- Set the table name to be deleted (based on the old entity name)
        old_table_name := OLD.name;

        -- Drop the table
        EXECUTE format('DROP TABLE IF EXISTS %I', old_table_name);
        RETURN OLD;
    END IF;

    -- Handle UPDATE case
    IF TG_OP = 'UPDATE' THEN
        -- If the name has changed, rename the table
        IF OLD.name IS DISTINCT FROM NEW.name THEN
            old_table_name := OLD.name;
            new_table_name := NEW.name;

            -- Rename the table
            EXECUTE format('ALTER TABLE %I RENAME TO %I', old_table_name, new_table_name);
        END IF;
        RETURN NEW;
    END IF;

    -- Handle INSERT case (create new table)
    IF TG_OP = 'INSERT' THEN
        -- Set the new table name
        new_table_name := NEW.name;

        -- Create the table
        EXECUTE format('CREATE TABLE %I ("id" bigint generated by default as identity not null, "created_at" timestamp with time zone not null default now())', new_table_name);

        RETURN NEW;
    END IF;
END;

With the triggers to manage the tables in place, I now need another trigger to handle the columns of these tables. This trigger will execute after each insert or delete operation on the entities_fields join table.

DECLARE
    entity_table_name TEXT;
    field_name TEXT;
    field_type TEXT;
BEGIN
    -- Handle DELETE: Remove field from the entity's table
    IF TG_OP = 'DELETE' THEN
        -- Set the table name based on the entity
        SELECT name INTO entity_table_name FROM entities WHERE id = OLD.entity_id;
        -- Get field name and remove it from the entity table
        SELECT f.name INTO field_name FROM fields f WHERE f.id = OLD.field_id;
        IF entity_table_name IS NOT NULL AND field_name IS NOT NULL THEN
            EXECUTE format('ALTER TABLE %I DROP COLUMN IF EXISTS %I', entity_table_name, field_name);
        END IF;
        RETURN OLD;
    END IF;

    -- Handle INSERT: Add new field to the entity's table
    IF TG_OP = 'INSERT' THEN
        -- Set the table name based on the entity
        SELECT name INTO entity_table_name FROM entities WHERE id = NEW.entity_id;
        -- Get the field name and type
        SELECT f.name, ft.type INTO field_name, field_type
        FROM fields f
        JOIN field_types ft ON f.field_type_id = ft.id
        WHERE f.id = NEW.field_id;

        -- Add the field to the entity's table
        EXECUTE format('ALTER TABLE %I ADD COLUMN %I %s', entity_table_name, field_name, field_type);
        RETURN NEW;
    END IF;

    -- Safety return in case no match
    RETURN NULL;
END;

With the example posts and page configuration defined earlier, these triggers will create the following entity tables:

• posts: id, title, content, draft
• pages: id, title, content

The frontend will only need to query the posts and page tables, without having to worry about the underlying structure. This approach offers great flexibility for the configuration and good performance for the data storage.

The SQL triggers do not account for SQL injection vulnerabilities and could likely be improved in terms of security. I will not cover these aspects in this post. Additionally, other triggers may be needed to handle cases such as renaming a column when a field name is updated, but these are not included in this proof of concept.

Using React-Admin Components For CMS Configuration

After bootstrapping a basic React-Admin app with the ra-supabase package, I need to create the list, show, create, and edit pages for the configuration tables: entities, fields, and field_types. For example, in the entities form, I use the following code to manage the fields:

const EntitiesEdit = () => (
  <Edit>
    <SimpleForm>
      <TextInput source="name" validate={required()} />
      <ReferenceManyToManyInput
        reference="fields"
        through="entities_fields"
        using="entity_id,field_id"
      >
        <AutocompleteArrayInput label="Fields" optionText="name" />
      </ReferenceManyToManyInput>
    </SimpleForm>
  </Edit>
);

A key detail here is using the <ReferenceManyToManyInput> component to handle the many-to-many relationship between entities and fields. It enables the selection of multiple fields from the fields table their association with an entity. The through and using props define the join table and the columns that establish the relationship between the two tables.

Similarly, I use the <ReferenceManyToManyField> component to create the entities list, displaying the associated fields as chips:

const EntitiesList = () => (
  <List>
    <Datagrid>
      <TextField source="id" />
      <TextField source="name" />
      <ReferenceManyToManyField
        reference="fields"
        through="entities_fields"
        using="entity_id,field_id"
        label="Fields"
      >
        <SingleFieldList>
          <ChipField source="name" />
        </SingleFieldList>
      </ReferenceManyToManyField>
      <DateField source="created_at" />
    </Datagrid>
  </List>
);

I'll use a similar approach to define the CRUD views for the fields and field_types tables.

Handling Dynamic Resources With React-Admin

Thanks to the SQL triggers I set up earlier, when a user changes the CMS configuration by altering the entities, fields, or field_types tables, the corresponding entity tables are automatically created, updated, or deleted.

Now, how can I define CRUD views for these entities? Since the tables are created dynamically, I cannot explicitly define the resources in the <Admin> component. Instead, I need to generate the resources dynamically based on the data from the entities and fields tables.

To achieve this, I’ll create a query that fetches the entities and fields from the API. I'll use Supabase's ability to join data from several tables with its select function. I'll store the result in a React context. The App component will then use this context to generate the resources dynamically.

Below is the <DynamicResourceProvider> implementation:

export const DynamicResourceProvider = ({
  children,
}: { children: React.ReactNode }) => {
  const [dynamicResources, setDynamicResources] = useState([]);

  const fetchDynamicResources = async () => {
    const { data } = await supabaseClient.from("entities").select(`
        name,
        fields:entities_fields (
          field:field_id (
            name,
            field_type_id (
              type
            )
          )
        )
      `);

    return data;
  };

  const { data: entities } = useQuery(
    {
      queryKey: [DYNAMIC_RESOURCES_QUERY_KEY],
      queryFn: fetchDynamicResources,
    },
    queryClient,
  );

  useEffect(() => {
    const newContextValue = entities?.map((entity) => ({
      name: entity.name,
      fields: entity.fields.map((f) => ({
        name: f.field.name,
        type: f.field.field_type_id.type,
      })),
    }));
    setDynamicResources(newContextValue || []);
  }, [entities]);

  return (
    <DynamicResourceContext.Provider value={dynamicResources || []}>
      {children}
    </DynamicResourceContext.Provider>
  );
};

Next, I can wrap the entire application within this context provider. It's essential to ensure that <App /> is a child of the <DynamicResourceProvider> since the context will be utilized within the <App /> component:

ReactDOM.createRoot(document.getElementById("root") as HTMLElement).render(
  <React.StrictMode>
    <DynamicResourceProvider>
      <App />
    </DynamicResourceProvider>
  </React.StrictMode>,
);

Finally, I can consume the context to render one Resource for each entity within the <Admin> component:

const App = () => {
  const customResources = useDynamicResources();
  return (
    <Admin
      layout={Layout}
      dataProvider={dataProvider}
      queryClient={queryClient}
    >
      {customResources.map((resource) => (
        <Resource
          key={resource.name}
          name={resource.name}
          list={DynamicResourceList}
          edit={DynamicResourceEdit}
          create={DynamicResourceCreate}
        />
      ))}
      <Resource {...fieldTypes} />
      <Resource {...fields} />
      <Resource {...entities} />
    </Admin>
  );
};

The <Admin /> component now creates one menu entry per entity.

Generating List And Form Components With Dynamic Fields

Next, I need to generate dynamic list, edit, and create components for each resource. That's the purpose of the DynamicResourceList, DynamicResourceEdit, and DynamicResourceCreate components.

In these components, I need to map PostgreSQL types to corresponding React-Admin fields and inputs. For instance, a text field can be represented as a <TextField> in the List component and a <TextInput> in the Form component.

I create utility functions to manage this mapping, such as for the inputs:

export const getInput = (fieldType: string, source: string) => {
  switch (fieldType) {
    case "text":
      return <TextInput key={source} source={source} />;
    case "numeric":
      return <NumberInput key={source} source={source} />;
    case "date":
      return <DateInput key={source} source={source} />;
    case "boolean":
      return <BooleanInput key={source} source={source} />;
    default:
      throw new Error(`Unknown field type: ${fieldType}`);
  }
};

I can then use it to generate the components for the dynamic resources:

export const DynamicResourceEdit = () => {
  const dynamicResources = useDynamicResources();
  const resource = useResourceContext();
  const fields = dynamicResources.find((r) => r.name === resource)?.fields;
  return (
    <Edit>
      <SimpleForm>
        {fields?.map((field) => getInput(field.type, field.name))}
      </SimpleForm>
    </Edit>
  );
};

And that's it! I can now enter data in entity tables using the admin interface.

Limitations

This proof of concept has some limitations that need to be addressed.

One issue I encountered is with React-Admin’s efficient cache management, which helps reduce the number of API calls. However, I need to invalidate the cache for the query fetching the dynamic resources whenever an entity is created, updated, or deleted. To resolve this, I specify an onSuccess callback that triggers after performing any of these actions. For example, in the EntitiesCreate component:

const onSuccess = (data: RaRecord) => {
  queryClient.invalidateQueries({ queryKey: [DYNAMIC_RESOURCES_QUERY_KEY] });
  notify("ra.notification.created", {
    type: "info",
    messageArgs: { smart_count: 1 },
  });
  redirect(`/entities/${data.id}`);
};

Another limitation is the lack of support for relationships between entities. For instance, I might need a comment entity with a post_id foreign key to associate a comment with a post. This can be probably addressed by adding a reference_entities column to the entities table to store foreign key relationships between entities. Implementing this would require updating the triggers to create the necessary foreign keys.

Conclusion

In this post, I showed how to build a CMS proof of concept using React-Admin and Supabase. By combining SQL triggers for managing the database schema, React-Admin for the administration interface, and a custom context provider to handle dynamic resources, I was able to create a flexible and dynamic CMS with minimal code.

This proof of concept can be expanded with additional features not covered here, such as authentication or mandatory fields. It also has some limitations, as noted earlier. Nevertheless, it offers a strong foundation for building a CMS with React-Admin and Supabase. More importantly, it highlights how React-Admin can effectively handle dynamic resources.

The full code for this proof of concept is on GitHub: marmelab/react-admin-cms.