Stop using one table for your client's

Do you have a saas? And you have a lot of clients? I know you hate managing them, don't you?
Follow my way to manage it easily, no need to use difficult logic and make you frustrated.

All you need is nestjs, postgres. Yes, that's enough.
We will use multi tenancy method with postgres multi schema. Maybe if it is described it will be like this

Let's say you have 100 clients, and each of them has 100 data items. If you use the old method of 1 table for all clients then your table contains 10,000 jumbled data. You need to think about database performance, api performance, think about the logic to filter, add indexes to certain fields.

But if you use this method, you don't need to think about all that.

"Hey, if only talking is easy", oh yes take it easy, I have prepared an example of a repo, you just need to clone it and modify it. repo here.
"Hey then how do I use it?", well I will explain it from the beginning here.

Before that, I assume you are already proficient in using typescript.

1. You must clone the repo to your local.

This repo example uses the nestjs monorepo method, for those who don't know, maybe you can read the nestjs documentation.
In this example repo has 2 services, namely identity and inventory

• Identity Service is very important, to store your client's identity data (Identity Service only has crud Users).
• In addition, it is a service that will become multi tenancy in this example I created an Inventory Service (Inventory Service only has crud Items)

1. Install depedency using npm yes npm install

2. Create .env file using .env.example and fill it according to your database

3. Run the npm run migrate command, this function is for migrating the User table in the public schema

4. Run the 2 services using the commands npm run start:dev identity and npm run start:dev inventory

5. Create one user using the endpoint [POST]http://localhost:3000/users, save the created id for later.

6. To create a new schema automatically you can simply hit the [GET]http://localhost:3000/users/migrate endpoint.

7. Now you check, there will definitely be a new schema, right?

8. To use the endpoint on the Inventory service, just use the [GET]http://localhost:3001/items endpoint, but don't forget to use x-client=id user in the headers with the value of the id created earlier.

9. You can repeat step 6 onwards to create a new client schema.

There may be a few things to note to extend or modify this example repo.

1. If you want to add new migrations you can simply run the command npx mikro-orm migration:create --config ./apps/identity/mikro-orm.config.ts for service Identity and npx mikro-orm migration:create --config ./apps/inventory/mikro-orm.config.ts for service Inventory

2. Especially Inventory migrations you need to modify by following the first migration in the folder. Because the migration process on Inventory uses dyanmic schema and Mikroorm does not support it yet. So I modified it the way it is now.

I think that's enough. I think the rest you understand better. Maybe if there are questions and collaboration, you can contact me.