DB Environments That Don’t Suck: A Dev’s Guide to Not Breaking Prod

In JS land, there are a billion different ways to persist data, and most of them suck. If you’re not careful, you can end up mangling your production DB. Data is the core of any web app, so if you mess up your production DB, you’ve just nuked your app.

Here’s how you can set up different environments in your database so the changes you make to the DB’s schema in dev isn’t reflected in prod, unless you want it to be.

You are probably expecting SQL code snippets and a technical walk through of how to get Gadget to have two different DB environments. It’s way simpler than that. Every app you make with the Gadget framework has a separate development and production DB. Just create an app, and everything is set up for you.

Here’s how it works:

Step 1: Create an app. Sign up for Gadget, then create an app:

Go through the UI flow to create a new web app with no user auth.

Gadget will automatically generate an API that can read and write to our database. We are prompted to choose what language that API should be in. I’ll choose TypeScript. Gadget also creates a frontend for you. I won’t use it in this tutorial:

Gadget will generate a blank project where we can add our tables. All the code that has to do with the database is in the /api folder:

The models folder holds all the tables for our app. Let’s create a new product table.

Now edit the schema to include a name column.

Now we’ve created a product table on the app’s development branch.

The product table won’t show up in production until we hit the deploy button. That’s how simple it is to keep the two databases separate.

Now, how do we write to the product database? We need to hit the API endpoint that runs the api/models/product/actions/create.ts action. You can use the API tab to make API calls in the Gadget UI:

If you want to make requests from your frontend, we need to install the Gadget library, then use the Gadget api to create a new record:

npm install @gadgetinc/react @gadget-client/product-tagger # <---- Replace with your Gadget project name

import { ProductTaggerClient } from "@gadget-client/product-tagger";

export const api = new ProductTaggerClient({
  authenticationMode: { browserSession: true },
});

const productRecord = await api.product.create({
  name: "example value for name",
});

To learn more about the API Gadget generated, go to the “Docs” page, and read through the really nice auto-generated docs specific to your database:

Solution 2: Supabase
Supabase is much more flexible than Gadget. This is both a pro and a con. You can run custom PostgreSQL queries in Supabase and optimize it for your specific use case. That’s awesome. It took me 2 hours to fully understand and implement environment management in Supabase. That’s not so awesome.

First, you need a Supabase database. Follow Supabase’s excellent docs to get your first database up and running: supabase.com/docs/guides/database/overview. You can copy my Supabase table if you want to follow along:

Now that your database is created,you need a project that uses Supabase as the datastore. I set up this Node.js project that just reads and writes to a dummy database. Here’s the GitHub link if you want to follow along.

Start the Supabase CLI by running

supabase init

Now you need to tell Supabase which database you want to connect to. You need to login, then give the CLI the project ID:

supabase login
supabase link --project-ref $PROJECT_ID

You can get your $PROJECT_ID from your project's dashboard URL:

https://supabase.com/dashboard/project/<project-id>

Now, let’s sync the schema of the production DB with your local instance:

supabase db pull

This works just like a git pull:

Now let’s change our local Supabase instance and add a personal_record column. To keep each change atomic, we create migrations. Migrations are little pieces of SQL that change the schema of a database when you run them.

supabase migration new add_personal_record_col

Now we edit the migration in supabase/migrations/<timestamp>_add_personal_record.sql

ALTER TABLE public.workouts
ADD COLUMN personal_record integer;

We apply the migration by running

supabase db reset

Let’s say you’re super happy with your new personal_record column in your database and you want to add it to the production database. We can push the change to the production schema like so:

supabase db push

Pushing the schema directly to the production database is not the best idea. Supabase recommends you set up a staging environment and run a GitHub action to run the migration when changes are merged to main. The Supabase docs walk you through how to do that here.

If you want to keep your development data separate from your production data, you need branches.

Unfortunately, this is a paid feature, so you have to part with $25 a month to see how this feature works. Supabase does a great job of describing the feature in their docs.

Conclusion

Supabase is getting a lot of hype in the dev community, but the DX of setting up separate environments was pretty mid. It took me almost 2 hours to have an environment that worked well. With Gadget, it was literally as easy as creating a new app via the GUI.

Granted, Supabase is more flexible, but most of the time I don’t care about flexibility. I want my project set up the right way with zero hassle.