Pardon the mess

You are viewing docs related to an older version of Sequin. We’re in the process of updating our provider-specific guides and will be done in a few weeks (February 2024). Please click here to view the latest version of the docs.

If you’re interested in GitHub and want to learn more about what’s changing, send us a note.

This guide walks you through setting up a real-time sync between GitHub and your database using Sequin.

In just a couple of minutes, you’ll configure and start your sync, read your GitHub data, and write an update back to GitHub.

Create a GitHub sync

Connect GitHub and your database to Sequin to start your sync:

Step 1: Create a new Sequin account at https://app.sequin.io/signup.

Step 2: Select GitHub as the platform you want to sync.

Step 3: Now create the connection to GitHub by selecting Select an account, then Install in another account, then Install the Sequin GitHub app. You’ll be redirected to GitHub:

Install GitHub App

Step 4: You can connect Sequin to either an individual user (i.e. your GitHub user) or an organization. If you want to connect Sequin to multiple users or organizations, you’ll create separate syncs for each user or organization.

Step 5: After selecting a user or organization, you can choose which repositories to allow Sequin access to:

Authenticate with GitHub

Sequin will sync all repositories it has access to as well as each repository’s associated objects. You can change these settings at any time by editing the Sequin GitHub app’s permissions. You can find GitHub app permissions on the Installations page for your user or organization.

Step 6: For each GitHub object you’re syncing, you can choose which properties will sync to your database.

Step 7: Connect to the destination database you want to sync your GitHub data to. Sync to a database you host by choosing the Connect option and following the steps to connect to your database. Or, if you want to get up and running quickly, click the Launch option to spin up a demo database we host for you (on Amazon RDS).

Step 8: Click Create.

After you click Create, Sequin will begin backfilling all your GitHub data. We’ll show you the progress we’re making in the console. Within a minute or two, you can begin exploring your schema and querying your data in SQL. We’ll send you an email when your backfill is complete.

info

For more information on the GitHub app setup and install process, see Setup & Installation in our GitHub reference.

Query your GitHub data using SQL

Connect to your database via Sequin’s Postgres Proxy, which lets you write to your synced tables as well as read them. We’ll use psql as an example in this setup guide, but you can use any SQL client that works with Postgres.

Step 1: Go to Connection Instructions on the Sequin console. Copy the psql command to connect to your database via the Proxy.

Step 2: Open your terminal, and paste the psql command. Press Enter. This will open a connection to your database so you can write your first query:

psql -d {your_connection_string_here}
psql (14.2, server 12.11)
Type "help" for help.

db7hea89oah3tas=>

Step 3: To get a sense of your schema, list all the tables you’re syncing to your database by typing the command \dt:

db7hea89oah3tas=> \dt

                   List of relations
 Schema |           Name            | Type  |  Owner
--------+---------------------------+-------+----------
 public | _sync_cdc                 | table | postgres
 public | repository                | table | postgres
 public | pull_request              | table | postgres
 public | issue                     | table | postgres
 public | commit                    | table | postgres
(5 rows)

You’ll see all the GitHub objects you selected when setting up your sync are now tables in your database. You’ll also see a _sync_cdc table which Sequin uses to manage your sync.

Step 4: Now, write your first query. You can start with a simple select to get your bearings:

db7hea89oah3tas=> select count(*) from repository;

 count
-------
    15
(1 row)

Now, select the id, title, state, and repository_id for a specific pull request:

db7hea89oah3tas=> select id, title, state, repository_id from github_sequin.pull_request where state = 'OPEN';

| id         | title                                           | state          | repository_id |
|------------|-------------------------------------------------|----------------|---------------|
| 1550230553 | Fix logger config in test environment           | OPEN           |     918381649 |
| 1457048141 | Add GitHub sync support to Sequin               | OPEN           |     918381649 |
(2 rows)

Next, grab the most recent issue in one of your repositories:

db7hea89oah3tas=> select id, title, body, repository_id from github_sequin.issue order by created_at desc limit 1;

| id         | title                                         | body                                               | repository_id |
|------------|-----------------------------------------------|----------------------------------------------------|---------------|
| 1508595082 | Send back an ID for the proxy request handler | We need to send back an X-Instance-ID header [...] |     406885628 |
(1 row)

You can use the id for this issue in the last step of this guide.

Write back to GitHub

To create, update, or delete objects in GitHub, you can insert, update, or delete rows in your Postgres database.

When you’re connected to your database through the Sequin Proxy, the Proxy listens for changes. When you make a mutation, the Proxy applies the mutation to GitHub’s API first. If the mutation succeeds, your database is updated as well. If it fails, your database mutation will be rolled back, and you’ll receive a Postgres error.

In this example, you can update the issue in the prior step by giving it a new state:

update issue
set state = 'closed'
where id = '{{issue_id}}'

This call will update the status of the issue. If the Postgres write returned without an error, that means the API write succeeded as well!

You can see over on GitHub that the record has updated. Naturally, the change will be reflected in your database as well:

db7hea89oah3tas=> select state, closed_at from issue where id='{{issue_id}}';

 state     | closed_at
-----------+--------------------
 closed    | 2023-10-18 22:53:32
(1 row)

Note that your database record was immediately updated with changes that happened on GitHub when you closed the issue, namely closed_at.

Next steps

Your GitHub tables are now available as fully readable and writeable tables in your database. You can query for all your data using SQL, and mutate data thanks to Sequin’s Postgres Proxy. To build on this foundation, here are some next steps:

  • Setup your ORM to work with your synced tables.
  • Edit the GitHub objects and properties you’re syncing at any time.
  • Create views on your GitHub data to tailor your schema to your needs.
  • Invite your team to your Sequin account and start building!

If you need assistance setting up your sync, just send us a note. We’re around and eager to help.

Was this page helpful?