How to build a CMS with Supabase and 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:

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:

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.

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:

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:

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:

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:

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

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:

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

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:

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.