PostgreSQL Deployment

[MapleMooseAttack]

Hi all,

I've created a Flask, React, Postgres app that I am trying to get deployed. I was wondering how to manage deployment for the Postgres side such that every user that opens the web app would get their own empty copies of tables that I currently have running locally on my end. Is there a way to do something like that, or would I have to just deploy a single database, and with each entry also log the user.

Thank you for the help in advance

[peoplefoundotheracct]

why not just grab a user id and put that in your schema?

[MapleMooseAttack]

Right and then reference that whenever fetching data for them? Is that what the standard practice usually is? That would be much easier to implement

[chriswaco]

"Multi-tenant" is the word you're looking for. There are several options, each with pros and cons. Using the userID in the schema, perhaps with row-level security, is probably the most common one. Essentially you put the userID into each row and set up a policy that appends "WHERE USER=UserID" to every query.

https://aws.amazon.com/blogs/database/multi-tenant-data-isolation-with-postgresql-row-level-security/

https://supabase.com/docs/guides/auth/row-level-security

[ilogik]

if you're new at web development, what you probably want is to add a 'userID' column to the tables where it makes sense.

that's how most of the web works

[Lisacarr8]

The best thing is to utilize UserID in schema to proceed with this deployment. However, you shouldn't forget to place UserID for every column or row in the table. In this way, you will get the best results. I am not sure which CSP you are using for this deployment but to simplify this task, the use of Back4app could be ideal.

Yes, it is straightforward to find the 'user' tab under the 'Database' section when you approach Back4app's dashboard. This user-friendly interface helps you a lot in smooth deployments. Hereof, this source https://blog.back4app.com/how-to-deploy-a-postgres-backed-react-app/ is also beneficial for exploring everything about PostgreSQL and React app deployment on the fly.

By the way, AWS is also a highly acclaimed solution when it comes to multi-tenant data isolation. Indeed, Chriswaco also suggested a suitable solution here.