Schema diff tutorial

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

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. Step 1: Create the Initial Schema

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

   ConsoleCLI

   1. Create the database.

      In the Neon Console, go to Databases → New Database. Make sure your main 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. Step 2: Create a development branch

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

   For the purposes of this tutorial, name the branch dev/jordan, following our recommended convention of creating a long-lived development branch for each member of your team.

   ConsoleCLI

   1. Create the development branch

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

      • Select main as the default branch.
      • Name the branch dev/jordan.

   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 dev/jordan is selected.

      

3. Step 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 dev/jordan branch.

   ConsoleCLI

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

   Enter this SQL statemenet 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. Step 4: View the schema differences

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

   ConsoleCLI

   1. Click on dev/jordan to open the detailed view, then under Compare to Parent click Open schema diff.

      

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

      

   You will see the schema differences between dev/jordan and its parent main, including the new address table that we added to the dev/jordan 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 Branch restore for more info.