State of the art Database schema design using Prisma

You should not only create simple tables when you are designing your database with Prisma. A decent schema manages connection between your data (such as how users are linked to their posts), sets up regulations to ensure your data is always clean and ensures your queries are efficient. You will have various kinds of relationships – a user may have numerous posts (one-to-many), posts may have numerous tags and tags may belong to numerous posts (many-to-many), and in some cases even data itself may be related to itself (such as users may follow one another). The trick is that you should label these relationships explicitly and have foreign keys so that your database will not permit you to inadvertently erase data which other tables rely on.

Proper schema performance is all about adding the appropriate indexes and constraints at appropriate locations. consider indexes such as the index to a book – they allow your database to find out data at high speed when you are searching a particular thing. When you are frequently searching users by email you should have an index on that email field. It is also possible to add your own constraints to implement business policies (such as ensuring two users do not share the same email), and composite indices when you tend to search across more than one field at once. Occasionally you will be required to replicate data across tables (denormalization) to read it faster particularly in case you are creating some feature which receives high traffic. Don’t forget to add time stamps such as created at and updated at they are very useful in keeping track of the changes.

You require a good migration plan when it comes to modifying your schema in production. Prisma maintains a history of all your schema changes across migration files that can be committed to gits like your code. It is best to test any migration in a staging environment before doing any migration in the production environment. Looking at the prisma migrate diff will show you the precise SQL that will be executed before you do so. In hard-to-change scenarios, including renaming a column upon which your application is relying, you may have to do this in steps, i.e. add the new column, change the application to use both, deploy that, and then drop the old column. This is how your application never fails. To do very complicated data transformations you may find it useful to write some custom SQL scripts which run as part of your migrations, which allows you to have complete control over how your data is updated.