Neon•16mo ago

genetic-orange

[Branching Strategy] Do we need to delete the unused branches?

Hi, Just experimenting with the branching feature and wondering if I can keep the branch and reset if when needed or should I delete them when not in use? Also, what's the best strategy for merging/deploying to main. Currently, my migration to prod is a bit tricky. I have two databases: one for prod and one for staging. staging has dummy data. 1. prisma migrate dev on staging dev branch. 2. prisma migrate deploy on staging main 3. prisma migrate deploy on prod preview branch 4. prisma migrate deploy on prod main branch Here the preview branch is used to test if everything is okay in PROD with actual data with the migration before applying to PROD main, which has live users. I used to use PlanetScale before and it was so much easier on the deployment process! 1. yarn db:push to dev/staging branch 2. Deploy branch to main branch from console. I have watched all the CI/CD references in the NEON videos and wondering if there is a better approach to do this. Honestly, given I was using PS, I would honestly appreciate something like PS itself on Neon.

46 Replies

secure-lavender•16mo ago

wondering if I can keep the branch and reset if when needed or should I delete them when not in use?

Yes, we recommend doing this for development branches.

I have two databases: one for prod and one for staging. staging has dummy data.

so in your case, you can set up a staging branch that will have production data. If I understood your setup correctly, what you can do is: 1. Create a Neon development branch from the Neon main branch and code against it. Generate database migrations with Prisma migrate dev and track them in version control 2. Create a Neon staging branch from the Neon main branch. This way it won't have dummy data. Use the prisma migrate deploy command against this Neon staging branch 3. if everything works well, you reapply the migrations with prisma migrate deploy against the main branch You don't really need to have a separate database Neon currently doesn't support merging branches, but it's something we're thinking about cc @bryan

prisma migrate dev on staging dev branch. prisma migrate deploy on staging main prisma migrate deploy on prod preview branch prisma migrate deploy on prod main branch

what's the difference here between staging and preview

secure-lavender•16mo ago

Also, not sure if you've seen this video or not. I talk about the recommended flow for Prisma https://www.youtube.com/watch?v=PX881bVAPxM

Neon

YouTube

Prisma essentials: from development to production (Prisma Migrate w...

Learn about database migrations using Prisma Migrate and how to go from Development to Preview to Production. - Code example: https://github.com/neondatabase/preview-branches-with-fly/tree/with-prisma - Try Neon at https://neon.tech/youtube → Neon Discord: https://neon.tech/discord → Follow us on Twitter: https://twitter.com/neondatabase
→ S...

genetic-orangeOP•16mo ago

Hi @Mahmoud thank you! 🙌

wondering if I can keep the branch and reset if when needed or should I delete them when not in use? Yes, we recommend doing this for development branches.

Sorry, can you be explicit? I assume you meant first option?

You don't really need to have a separate database

Actually, in my case, this is to avoid sharing PROD access to everyone working on the project. Especially, freelancers. When you branch off from main, you are copying data as well, which is not something we want in this case. Plus, we have a separate login for staging users and we tend to test the staging db with test data as well.

Also, not sure if you've seen this video or not. I talk about the recommended flow for Prisma

Yes, I did. I think I am following the same pattern here but like I said, I need the staging env as well in this case. So instead of dev -> preview -> main on prod, I do dev -> main on staging and preview -> main on prod.

Neon currently doesn't support merging branches, but it's something we're thinking about

Thanks. Would appreciate if that would be mostly console driven or at least as an option like PS. Plus, they have good practices built in to make sure the data is not lost at the same time the database is not down. I look forward to see Neon's version of how we merge branches but if you ever need ideas, just copy PS please :D.

secure-lavender•16mo ago

Actually, in my case, this is to avoid sharing PROD access to everyone working on the project. Especially, freelancers.

Would creating a specific role not work?

Sorry, can you be explicit? I assume you meant first option?

You can use a Neon branch for your development environment. This branch will have the data of its parent. You can think of it as a replacement to having a local postgres instance running with Docker for example

genetic-orangeOP•16mo ago

Would creating a specific role not work?

I doubt it or may be I am not sure if that helps. Even if I create a specific role (I assume you are talking about RLS) with limited permissions, it will be a hassle to manage. For example, they will need full access to all tables. For me, staging db implies, we are okay with giving full Read/Write access but don't touch PROD. May be a precautionary measure but copying data is not expected when branching. Perhaps that could be an option to allow only schema to be copied and not data? Interestingly, I did not have a second db when using PS. Beacuse they don't copy PROD data when branching. Those are two different env.

secure-lavender•16mo ago

Perhaps that could be an option to allow only schema to be copied and not data?

This is also something we're thinking about

secure-lavender•16mo ago

(I assume you are talking about RLS

We have a Manage Database access guide https://neon.tech/docs/manage/database-access

Neon

Manage database access - Neon Docs

Each Neon project is created with a default Postgres role that takes its name from your Neon account (the account you registered with). For example, if a user named "Alex" signs up for Neon, the proje...

genetic-orangeOP•16mo ago

Cheers! Also would be cool if the console can create Roles easily. I am sure it will help a lot of new users by reducing security risks and following best practises. Just tried creating a new rw role and looks like it is not working as intended.

CREATE ROLE readwrite PASSWORD '<strong password>';
GRANT CONNECT ON DATABASE "app-staging" TO readwrite;
GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO readwrite;
-- User creation
CREATE USER devrw WITH PASSWORD '<strong password>';
-- Grant privileges to user
GRANT readwrite TO devrw;

CREATE ROLE readwrite PASSWORD '<strong password>';
GRANT CONNECT ON DATABASE "app-staging" TO readwrite;
GRANT USAGE, CREATE ON SCHEMA public TO readwrite;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO readwrite;
-- User creation
CREATE USER devrw WITH PASSWORD '<strong password>';
-- Grant privileges to user
GRANT readwrite TO devrw;

1. This user has access to both the DBs! Let's say I have "app" and "app-staging". While I specifically added "app-staging" only, this role can still connect to "app". Also, I can see they are using same hosts? 2. This user cannot see all the tables. I have postgis enabled and I can only see those Tables/Views/Routines 3. Also, noticed that the preview branch is also introduced in the staging DB! Isn't branches database specific? I think this is what I am trying to do: 1. I want to have two DBs: staging-db and prod-db 2. I want to have two branches: dev-branch in staging-db and preview-branch in prod-db 3. I want to create a developer-role which have readwrite access to only staging-db on all branches. This is what I could do: 1. Yes, easy 2. Branches seem to share between databases! I want branches to be database specific. Is that possible? Otherwise, I am not sure what's the difference between a db and a branch 3. This wasn't successful either. Was able to create the role with readwrite access but existing tables won't load. I haven't tried new tables but existing should work in the first place. Secondly, all roles have access to all DBs! I specifically added staging-db with GRANT CONNECT ON DATABASE "app-staging" TO readwrite; but again, the roles and users seem to be shared betwen DBs as well, which is not ideal.

secure-lavender•16mo ago

In Neon, a project is a postgres cluster. Within a cluster, you can have many databases. The default database that gets created along with a project is called neondb (Of course, you can change this or create more databases if you like) Now when you create a branch, you're copying the entire cluster (that's why if you have two databases on the main branch, you're going to see them copied in the child branch)

1. I want to have two DBs: staging-db and prod-db 2. I want to have two branches: dev-branch in staging-db and preview-branch in prod-db 3. I want to create a developer-role which have readwrite access to only staging-db on all branches.

Do you care about copying data between different environments? If not, you can set up two projects, one for staging and one for production the staging project can be shared with anyone on your team And since you're using Prisma, you can have the database migrations tracked in code so you can easily reapply migrations between projects

secure-lavender•16mo ago

btw I think you'll find this doc guide/diagram helpful https://neon.tech/docs/manage/overview

Neon

Overview of the Neon object hierarchy - Neon Docs

Managing your Neon project requires an understanding of the Neon object hierarchy. The following diagram shows how objects in Neon are related. See below for a description of each object. Neon account...

genetic-orangeOP•16mo ago

Thanks @Mahmoud . That is good to know.

Now when you create a branch, you're copying the entire cluster (that's why if you have two databases on the main branch, you're going to see them copied in the child branch)

That's fine. Aligns with what I wanted. Instead of dev on app-db-staging, I got preview. So there are 4 variations 1. app-db-staging on main 2. app-db-staging on preview (clone of main) 3. app-db on main 4. app-db on preview (clone of main)

Do you care about copying data between different environments?

That would be ideal because sometimes there are general tables with shareable data and might be easier to copy? I mean you can do even if they are different projects but that's not a concern for now. I have looked at the diagram. Thank you for that. So basically, I have to switch the notion of db with branches. I was in the notion that branches are from DB but it is the otherway around. Let me give it a try and see how that goes. However, I am not sure why the above sql script did not work. I mean I could not see all the tables for both staging and prod for the main branch. I have tried all combinations here (also wondering why it let me do that) but all of them are not showing existing tables.

secure-lavender•16mo ago

Each branch is isolated from the other (differnet hosts and connection strings). Any changes made to the child branch don't affect the parent. This includes changes to the different roles If you want a role to persisit across branches then you need to run the sql query against the main branch. This way when you branch off of main, you will have that role

genetic-orangeOP•16mo ago

@Mahmoud is this normal? DB is down! Just deleted a role from the console and it went into a Pending mode. Cannot login of course! Should I try the Restore feature?

genetic-orangeOP•16mo ago

secure-lavender•16mo ago

Hmm, is it still stuck in pending?

genetic-orangeOP•16mo ago

yes, it's been at least 5mins

secure-lavender•16mo ago

Can you share your project ID

genetic-orangeOP•16mo ago

cold-wave-05752888

secure-lavender•16mo ago

I'll ask internally

genetic-orangeOP•16mo ago

Thanks mate

secure-lavender•16mo ago

Sorry for the inconvenience 🙏

genetic-orangeOP•16mo ago

On a side note, should I try Restoring?

secure-lavender•16mo ago

No it won't make a difference

genetic-orangeOP•16mo ago

Thanks. I will have to just wait then. 😅 I don't if this exactly caused the issue but if you wanna try: All you have to do is to create a readonly role using a super user and just delete it from the console. Both role and the user.

-- readonly role
CREATE ROLE readonly PASSWORD '<password>';
GRANT CONNECT ON DATABASE <database> TO readonly;
GRANT USAGE ON SCHEMA <schema> TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO readonly;
-- User creation
CREATE USER readonly_user1 WITH PASSWORD '<password>';
-- Grant privileges to user
GRANT readonly TO readonly_user1;

-- readonly role
CREATE ROLE readonly PASSWORD '<password>';
GRANT CONNECT ON DATABASE <database> TO readonly;
GRANT USAGE ON SCHEMA <schema> TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema> GRANT SELECT ON TABLES TO readonly;
-- User creation
CREATE USER readonly_user1 WITH PASSWORD '<password>';
-- Grant privileges to user
GRANT readonly TO readonly_user1;

secure-lavender•16mo ago

Our engineering team is taking a look

genetic-orangeOP•16mo ago

Thank you. I am glad you saw this message.

secure-lavender•16mo ago

And I'm glad you reached out 😄

genetic-orangeOP•16mo ago

Hopefully it is just me and not an outage!

secure-lavender•16mo ago

Yea it's something related to your project when you tried to delete the role

genetic-orangeOP•16mo ago

I deleted another role before with

-- Revoke all privileges from the devrw user
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM devrw;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM devrw;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM devrw;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM devrw;

-- Revoke the readwrite role from devrw
REVOKE readwrite FROM devrw;

-- Revoke all privileges from the readwrite role
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM readwrite;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM readwrite;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM readwrite;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM readwrite;

-- Drop the devrw user
DROP USER IF EXISTS devrw;

-- Drop the readwrite role
DROP ROLE IF EXISTS readwrite;

-- Revoke all privileges from the devrw user
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM devrw;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM devrw;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM devrw;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM devrw;

-- Revoke the readwrite role from devrw
REVOKE readwrite FROM devrw;

-- Revoke all privileges from the readwrite role
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM readwrite;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM readwrite;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM readwrite;
REVOKE ALL PRIVILEGES ON SCHEMA public FROM readwrite;

-- Drop the devrw user
DROP USER IF EXISTS devrw;

-- Drop the readwrite role
DROP ROLE IF EXISTS readwrite;

And it worked fine, But this time, I just deleted through console hoping these will be done in the background. @Mahmoud is it too much to ask if they have a magic switch to restore the db for now and work on the fix later? I am happy to re-create this on a different branch or a new project!

secure-lavender•16mo ago

Are you able to create branches?

genetic-orangeOP•16mo ago

Unfortunately no. That's what I tried first!

genetic-orangeOP•16mo ago

On a side note, I would love to see a Export DB button on the Restore page. Just in case this happens again, I have an option to export the latest data and serve from another host until this gets resolved. Let's say Server2 for DB.

genetic-orangeOP•16mo ago

Interestingly someone already thought about this! Could be improved but better than nothing for sure!

secure-lavender•16mo ago

I would love to see a Export DB button on the Restore page.

I'll share this feedback with the team yea this just exports the query results

genetic-orangeOP•16mo ago

Hopefully, I get to connect again in the morning (in about 6 hours). Can't we just restart the cluster? Is there an option to do? It is concerning that it is still showing "Pending" 😓

secure-lavender•16mo ago

Hopefully, I get to connect again in the morning (in about 6 hours).

Should be fixed by then hopefully

Can't we just restart the cluster?

It's a bit complicated 😅 Really sorry about that

genetic-orangeOP•16mo ago

Thank you. Appreciate all your help! @Mahmoud if this will take longer time, can I request to download the DB to email or a temp project? It feels super concerning when there is nothing I could do to help!

secure-lavender•16mo ago

I'm AFK for a few hours, so I'll be slow to respond. @ShinyPokemon , do you mind following up here once the issue is resolved?

sensitive-blue•16mo ago

@zilla I see that our team has taken action to help remedy the issue. How are things looking now? Edit: sorry, they're still looking into it. I got threads crossed 🙂

genetic-orangeOP•16mo ago

@ShinyPokemon @Mahmoud Thank you. It looks like it is fixed! Would be great if I can know if it is safe to try creating a role again? Don;t want to get locked out again!

secure-lavender•16mo ago

It's safe to manage roles using SQL, not the console. This should be fixed soon and it should be possible to do both

genetic-orangeOP•16mo ago

Thank you. Won’t touch console again. 🙌 Hello again 👋 @Mahmoud Based on the branching convo we had, I came up with a solution that meets my needs. 3 Branches: 1. DEV_BRANCH 2. PREVIEW_BRANCH 3. MAIN_BRANCH 2 DBs: 1. PROD_DB 2. DEV_DB I am intending to share the DEV_BRANCH credentials to developers. However, they can access both PROD_DB and DEV_DB. Since I don't want to share the data of PROD_DB, my option is to nuke it and use only DEV_DB for all the testing/development from the DEV_BRANCH. My question is I am still not sure why the databases are not unique to the branches. I mean I can access the both DBs from all branches, which does not fit with the arch diagram. In the diagram, if I am not mistaken, databases and roles are unique to the branches?

secure-lavender•16mo ago

After you create a branch you need to reset the credentials Since changes made to the child branch don't affect the parent

genetic-orangeOP•16mo ago

Cheers! Thanks for clearing that up.

secure-lavender•16mo ago

No problem! Glad you found a setup that works for you

Gaming

Programming

[Branching Strategy] Do we need to delete the unused branches?

Did you find this page helpful?