Schema diff tutorial

In this guide we will create an initial schema on a new database called people on our production branch. We'll then create a development branch called feature/address, following one possible convention for naming feature branches. After making schema changes on feature/address, we'll use the Schema Diff tool on the Branches page to get a side-by-side, GitHub-style visual comparison between the feature/address development branch and production.

Before you start

To complete this tutorial, you'll need:

• A Neon account. Sign up here.

• To interact with your Neon database from the command line:

  • Install the Neon CLI
  • Download and install the psql client

1. Create the Initial Schema

   First, create a new database called people on the production branch and add some sample data to it.

   ConsoleCLIAPI

   1. Create the database.

      In the Neon Console, go to Databases → New Database. Make sure your production branch is selected, then create the new database called people.

   2. Add the schema.

      Go to the SQL Editor, enter the following SQL statement and click Run to apply.

      CREATE TABLE person (
          id SERIAL PRIMARY KEY,
          name TEXT NOT NULL,
          email TEXT UNIQUE NOT NULL
      );

2. Create a development branch

   Create a new development branch off of production. This branch will be an exact, isolated copy of production.

   For the purposes of this tutorial, name the branch feature/address, which could work as a good convention for creating isolated branches for working on specific features.

   ConsoleCLIAPI

   1. Create the development branch

      On the Branches page, click Create Branch, making sure of the following:

      • Select production as the default branch.
      • Name the branch feature/address.

   2. Verify the schema on your new branch

      From the SQL Editor, use the meta-command \d person to inspect the schema of the person table. Make sure that the people database on the branch feature/address is selected.

      

3. Update schema on a dev branch

   Let's introduce some differences between the two branches. Add a new table to store addresses on the feature/address branch.

   ConsoleCLIAPI

   In the SQL Editor, make sure you select feature/address as the branch and people as the database.

   Enter this SQL statement to create a new address table.

   CREATE TABLE address (
       id SERIAL PRIMARY KEY,
       person_id INTEGER NOT NULL,
       street TEXT NOT NULL,
       city TEXT NOT NULL,
       state TEXT NOT NULL,
       zip_code TEXT NOT NULL,
       FOREIGN KEY (person_id) REFERENCES person(id)
   );

4. View the schema differences

   Now that you have some differences between your branches, you can view the schema differences.

   ConsoleCLIAPI

   1. Click on feature/address to open the detailed view, then click Schema diff.

      

   2. Make sure you select people as the database and then click Compare.

      

   You will see the schema differences between feature/address and its parent production, including the new address table that we added to the feature/address branch.

   You can also launch Schema Diff from the Restore page, usually as part of verifying schemas before you restore a branch to its own or another branch's history. See Instant restore for more info.