Add data automatically to a table B when you add data to table A

Question

Can I update a table in Keystone when I add data to another table?

For example: I have a table named Property where I add details of the property. As soon as I enter the data into this Property table, another table, named NewTable, should automatically get populated with the contents.

Is there a way to achieve this?

Answer 1

There are two ways I can see to approach this:

• The afterOperation hook, which lets you configure an async function that runs after the main operation has finished
• A database trigger that runs on UPDATE and INSERT

afterOperation Hook

See the docs here. There's also a hooks guide with some context on how the hooks system works.

In your case, you'll be adding a function to your Property list config.

The operation argument will tell you what type of operation just occurred ('create', 'update', or 'delete') which may be handy if you also want to reflect changes to Property items or clean up records in NewTable when a Property item is deleted.

Depending on the type of operation, the data you're interested in will be available in either the originalItem, item or resolvedData arguments:

• For create operations, resolvedData will contain the values supplied but you'll probably want to reference item, it'll also contain generated and defaulted values that were applied, such as the new item's id. In this case originalItem will be null.
• For update operations, resolvedData will be just the data that changed, which should have everything you need to keep the copy in sync. If you want a move compete picture originalItem and item will be the entire item before and after the update is applied.
• For delete operations originalItem will be the last version of the item before it was removed from the DB. resolvedData and item will both be null.

The context argument is a reference to the Keystone context object which includes all the APIs you'll need to write to your NewTable list. You probably want the Query API, eg. context.query.NewTable.createOne(), context.query.NewTable.updateOne(), etc.

The benefits to using a Keystone hook are:

• The logic is handled within the Keystone app code which may make it easier to maintain if your devs are mostly focused on JavaScript and TypeScript (and maybe not so comfortable with database functionality).
• It's database-independent. That is, the code will be the same regardless of which database platform your project uses.

Database Triggers

Alternatively, I'm pretty sure it's possible to solve this problem at the database level using UPDATE and INSERT triggers.

This solution is, in a sense, "outside" of Keystone and is database specific. The exact syntax you'll need depends on the DB platform (and version) your project is built on:

• PostgreSQL
• MySQL
• SQLite

You'll need to manually add a migration that creates the relevant database structure and add it to your Keystone migrations dir. Once created, Prisma (the DB tooling Keystone uses internally) will ignore the trigger when it's performing its schema comparisons, allowing you to continue using the automatic migrations functionality.

Note that, due to how Prisma works, the table with the copy of the data (NewTable in your example) will need to either be:

• Defined as another Keystone list so Prisma can create and maintain the table, or..
• Manually created in different database schema, so Prisma ignores it. (I believe this isn't possible on SQLite as it lacks the concepts of multiple schemas within a single DB)

If you try to manually create and manage a table within the default database schema, Prisma will get confused (producing a Drift detected: Your database schema is not in sync with your migration history error) and prompt you to reset your DB.