---
title: Automated Database Branching with GitHub Actions
subtitle: Learn how to automate database branching for your application using Neon and GitHub Actions
enableTableOfContents: true
author: dhanush-reddy
createdAt: '2024-11-29T00:00:00.000Z'
---
Database changes can be one of the trickiest parts of application development. When multiple developers work on features that require database modifications, they often face challenges like conflicting schema changes, difficulty in testing migrations, and the risk of breaking the production database.
Database branching solves these problems by allowing developers to create isolated database environments for each feature branch, just like they do with code.
This guide demonstrates how to implement automated database branching using Neon and GitHub Actions, where each pull request gets its own database branch, complete with the necessary schema changes. You'll build a Next.js Todo application that showcases this workflow, which automates several critical database operations, including:
- Creating a new database branch when a pull request is opened
- Automatically applying schema migrations to the new branch
- Showing schema diffs directly in your pull request
- Syncing schema changes to production when the PR is merged
By the end of this guide, you'll have a system where database changes are as seamless as code changes, with each feature safely isolated in its own environment until it's ready for production. This approach not only makes database changes safer but also gives developers the confidence to experiment with schema changes without fear of breaking the production environment.
## Prerequisites
- A [Neon account](https://console.neon.tech)
- A [GitHub account](https://github.com/)
- Node.js installed on your machine
- Basic familiarity with Next.js and TypeScript
## Setting Up Your Neon Database
1. Create a new Neon project from the [Neon Console](https://console.neon.tech). For instructions, see [Create a project](/docs/manage/projects#create-a-project).
2. Note your connection string from the connection details page.
Your connection string will look similar to this:
```shell
postgres://[user]:[password]@[neon_hostname]/[dbname]?sslmode=require&channel_binding=require
```
## Set up the project
1. Create a new Next.js project with TypeScript:
```bash shouldWrap
npx create-next-app@14 todo-app --typescript --tailwind --use-npm --eslint --app --no-src-dir --import-alias "@/*"
cd todo-app
```
2. Install the required dependencies:
```bash
npm install drizzle-orm @neondatabase/serverless dotenv
npm install -D drizzle-kit
```
## Configure the database schema
This guide demonstrates database schema definition using Drizzle ORM. The underlying principles can be easily adapted to your preferred ORM, such as Prisma, TypeORM, or Sequelize.
1. Create `app/db/schema.ts`:
The following code defines the database schema for a simple Todo application:
```typescript
import { integer, text, boolean, pgTable } from 'drizzle-orm/pg-core';
export const todo = pgTable('todo', {
id: integer('id').primaryKey(),
text: text('text').notNull(),
done: boolean('done').default(false).notNull(),
});
```
2. Create `drizzle.config.ts` in your project root:
```typescript
import { config } from 'dotenv';
import { defineConfig } from 'drizzle-kit';
config({ path: '.env' });
export default defineConfig({
schema: './app/db/schema.ts',
out: './migrations',
dialect: 'postgresql',
dbCredentials: {
url: process.env.DATABASE_URL!,
},
});
```
3. Add database scripts to your `package.json`:
```json
{
...
"scripts": {
...
"db:generate": "drizzle-kit generate",
"db:migrate": "drizzle-kit migrate"
}
}
```
4. Create a `.env` file in your project root:
```bash shouldWrap
DATABASE_URL=postgres://[user]:[password]@[neon_hostname]/[dbname]?sslmode=require&channel_binding=require
```
5. Push your code to a GitHub repository.
## Set up the Neon GitHub integration
The [Neon GitHub integration](/docs/guides/neon-github-integration) connects your Neon project to your application repository and automatically sets a `NEON_API_KEY` secret and `NEON_PROJECT_ID` variable for you. These variables will support the GitHub Actions workflow we'll create in a later step.
1. In the Neon Console, navigate to the **Integrations** page in your Neon project.
2. Locate the **GitHub** card and click **Add**.
3. On the **GitHub** drawer, click **Install GitHub App**.
4. If you have more than one GitHub account, select the account where you want to install the GitHub app.
5. Select the GitHub repository to connect to your Neon project, and click **Connect**.
The final page of the GitHub integration setup provides a sample GitHub Actions workflow. With this workflow as a example, we'll create a custom GitHub Actions workflow in the next steps.
## Create the GitHub Actions workflow
Create `.github/workflows/neon_workflow.yaml` file and add the following code:
```yaml
name: Create/Delete Branch for Pull Request
on:
pull_request:
types:
- opened
- reopened
- synchronize
- closed
# Ensures only the latest commit runs, preventing race conditions in concurrent PR updates
concurrency:
group: ${{ github.workflow }}-${{ github.ref }}
jobs:
setup:
name: Setup
outputs:
branch: ${{ steps.branch_name.outputs.current_branch }}
runs-on: ubuntu-latest
permissions:
contents: read
pull-requests: write
steps:
- name: Get branch name
id: branch_name
uses: tj-actions/branch-names@v8
create_neon_branch:
name: Create Neon Branch
needs: setup
if: |
github.event_name == 'pull_request' && (
github.event.action == 'synchronize'
|| github.event.action == 'opened'
|| github.event.action == 'reopened')
runs-on: ubuntu-latest
steps:
- name: Create Neon Branch
id: create_neon_branch
uses: neondatabase/create-branch-action@v5
with:
project_id: ${{ vars.NEON_PROJECT_ID }}
branch_name: preview/pr-${{ github.event.number }}-${{ needs.setup.outputs.branch }}
api_key: ${{ secrets.NEON_API_KEY }}
- name: Checkout
uses: actions/checkout@v4
- name: Run Migrations on Preview Branch
run: npm install && npm run db:generate && npm run db:migrate
env:
DATABASE_URL: '${{ steps.create_neon_branch.outputs.db_url }}'
- name: Post Schema Diff Comment to PR
uses: neondatabase/schema-diff-action@v1
with:
project_id: ${{ vars.NEON_PROJECT_ID }}
compare_branch: preview/pr-${{ github.event.number }}-${{ needs.setup.outputs.branch }}
api_key: ${{ secrets.NEON_API_KEY }}
delete_neon_branch:
name: Delete Neon Branch and Apply Migrations on Production Database
needs: setup
if: |
github.event_name == 'pull_request' &&
github.event.action == 'closed'
runs-on: ubuntu-latest
steps:
- name: Delete Neon Branch
uses: neondatabase/delete-branch-action@v3
with:
project_id: ${{ vars.NEON_PROJECT_ID }}
branch: preview/pr-${{ github.event.number }}-${{ needs.setup.outputs.branch }}
api_key: ${{ secrets.NEON_API_KEY }}
- name: Checkout
if: github.event.pull_request.merged == true
uses: actions/checkout@v4
- name: Apply migrations to production
if: github.event.pull_request.merged == true
run: |
npm install
npm run db:generate
npm run db:migrate
env:
DATABASE_URL: '${{ secrets.DATABASE_URL }}'
```
To set up GitHub Actions correctly:
1. **Enable Workflow Permissions**:
Go to your repository's GitHub Actions settings, navigate to **Actions** > **General**, and set **Workflow permissions** to **Read and write permissions**.
2. **Add Database Connection String**:
Add a `DATABASE_URL` secret to your repository under **Settings** > **Secrets and variables** > **Actions**, using the connection string for your production database that you noted earlier. While you're here, you should see the `NEON_API_KEY` secret and `NEON_PROJECT_ID` variable that have already been set by the Neon GitHub integration.
The step outputs from the `create_neon_branch` action will only be available within the same job (`create_neon_branch`). Therefore, write all test code, migrations, and related steps in that job itself. The outputs are marked as secrets. If you need separate jobs, refer to [GitHub's documentation on workflow commands](https://docs.github.com/en/actions/reference/workflows-and-actions/workflow-commands#workflow) for patterns on how to handle this.
It's important to understand the roles of your GitHub secrets. The `NEON_API_KEY` (created by the integration) is used to manage your Neon project, like creating and deleting branches. The `DATABASE_URL` secret you just created points exclusively to your primary production database. The workflow uses this only after a PR is successfully merged to apply migrations, ensuring a safe separation from the ephemeral preview databases used during testing.
## Understanding the workflow
The GitHub Actions workflow automates database branching and schema management for pull requests. Here's a breakdown of the workflow:
### Create Branch Job
This job runs when a pull request is opened, reopened, or synchronized:
1. **Branch Creation**:
- Uses Neon's [`create-branch-action`](https://github.com/marketplace/actions/neon-create-branch-github-action) to create a new database branch
- Names the branch using the pattern `preview/pr-{number}-{branch_name}`
- Inherits the schema and data from the parent branch
2. **Migration Handling**:
- Installs project dependencies
- Generates migration files using Drizzle
- Applies migrations to the newly created branch
- Uses the branch-specific `DATABASE_URL` for migration operations
3. **Schema Diff Generation**:
- Uses Neon's [`schema-diff-action`](https://github.com/marketplace/actions/neon-schema-diff-github-action)
- Compares the schema of the new branch with the parent branch
- Automatically posts the differences as a comment on the pull request
- Helps reviewers understand database changes at a glance
### Delete Branch Job
This job executes when a pull request is closed (either merged or rejected):
1. **Production Migration**:
- If the PR is merged, applies migrations to the production database
- Uses the main `DATABASE_URL` stored in repository secrets
- Ensures production database stays in sync with merged changes
2. **Cleanup**:
- Removes the preview branch using Neon's [`delete-branch-action`](https://github.com/marketplace/actions/neon-database-delete-branch)
## Flow Summary
Here's how the entire process works from start to finish:
1. Developer creates a new feature branch and makes schema changes
2. When they open a pull request:
- A new database branch is automatically created
- Schema migrations are generated and applied
- A schema diff comment is posted on the PR
3. During PR review:
- Reviewers can see exactly what database changes are being made
- The isolated database branch prevents conflicts with other features
- Additional commits trigger automatic migration updates
4. When the PR is approved and merged:
- Migrations are automatically applied to the production database
- The preview branch is deleted
- The schema changes are now live in production
5. If the PR is closed without merging:
- The preview branch is automatically deleted
- No changes are made to the production database
This automated workflow ensures that:
1. Every feature gets its own isolated database environment
2. Schema changes are automatically tracked and documented in the pull request
3. Migrations are consistently applied across environments
4. Production database stays in sync with merged code
5. Database resources are efficiently managed
6. The risk of manual migration errors is minimized
## Test the workflow
To test the workflow, perform the following steps:
1. Create a new feature branch:
```bash
git checkout -b feature/add-todo-created-at
```
2. Modify the schema in `db/schema/todos.ts`:
```typescript
export const todo = pgTable('todo', {
id: integer('id').primaryKey(),
text: text('text').notNull(),
done: boolean('done').default(false).notNull(),
created_at: timestamp('created_at').notNull().defaultNow(),
});
```
3. Commit and push your changes:
```bash
git add .
git commit -m "feat: add created_at field to todo"
git push origin feature/add-todo-created-at
```
4. Open a pull request on GitHub
The workflow will:
- Create a new database branch for your PR
- Apply the schema migration
- Post a schema diff comment on the PR
- After merging, apply the changes to production
## Source code
You can find the complete source code for this example on GitHub.
Get started with automated database branching
## Resources
- [Neon GitHub Integration Documentation](/docs/guides/neon-github-integration)
- [Database Branching Workflows](/branching)
- [GitHub Actions Documentation](https://docs.github.com/en/actions)