Streaming to Neon Postgres

​

What’s Neon?

Neon is a fully managed Postgres service that makes it easy to deploy and scale Postgres databases in the cloud. With Neon, you get all the features and power of Postgres without the operational overhead.

Neon is a great choice for storing and querying API data. With Sequin, you can stream data from APIs like Stripe, Salesforce, and GitHub directly into Neon Postgres tables.

To stream data to Neon Postgres via Sequin, you will:

• Create a Neon Postgres database
• Connect Sequin to an API source
• Create a Sequin Postgres consumer that streams to Neon
• Query your API data in Neon Postgres
• Create views to make querying easier

Sequin will first copy all historical data from the API into Postgres, then will keep your Postgres tables up-to-date by inserting, updating, or deleting rows as changes occur in the API. This means your Postgres database will always be in sync with your API data.

​

Create a Neon Postgres database

Sign up for a Neon account at https://console.neon.tech/signup.

Once logged in, complete the setup of your first project. Neon will provision a Postgres instance for you and create a default database, neondb. You can use neondb for this guide.

​

Create a schema and table for Sequin

Next, you’ll want to create a table in your Neon database for Sequin to sync API data to. We also recommend creating a dedicated Postgres schema for Sequin tables, but that’s not required.

You can run SQL in Neon’s console by clicking “SQL editor” in the sidenav.

To create a schema for Sequin:

create schema sequin;

Then, create a table for Sequin to sync API data to:

create table sequin.records (
  sequin_id uuid not null,
  sync_id uuid not null,
  collection_id text not null,
  upstream_id text not null,
  payload jsonb not null,
  upstream_updated_at timestamp with time zone not null,
  upstream_created_at timestamp with time zone,
  inserted_at timestamp with time zone not null,
  updated_at timestamp with time zone not null,
  primary key (sequin_id)
);

You can use the this table to store data from any API. API data is stored denormalized in the payload column. You’ll see later how to create views on top of this table.

​

Create a Postgres user for Sequin

Create a user for Sequin to use when connecting to your Neon database. This user will need insert, update, and delete privileges on the sequin.records table.

In the Neon SQL editor, run:

create user sequin with password 'generate-a-strong-password-here';

Then, grant the sequin user the necessary privileges:

grant usage on schema sequin to sequin;
grant select, insert, update, delete on all tables in schema sequin to sequin;
alter default privileges in schema sequin grant select, insert, update, delete on tables to sequin;

These statements do the following:

1. Grants the sequin user usage permissions on the sequin schema.
2. Grants the sequin user select, insert, update, and delete permissions on all existing tables in the sequin schema.
3. Sets default privileges so that the sequin user will have select, insert, update, and delete permissions on any new tables created in the sequin schema.

With this user, Sequin will only have access to the tables it needs, and won’t be able to modify any other data in your database.

​

Connect Sequin to an API source

You can connect Sequin to an upstream API using either the Sequin console or API.

Here’s a curl request that connects Sequin to the GitHub API:

curl --request POST \
  --url https://api.sequin.io/v1/syncs \
  --header 'Authorization: Bearer {your-api-token}' \
  --header 'Content-Type: application/json' \
  --data '{
    "provider": "github",
    "collection_ids": ["github:pull_request", "github:repository"],
    "credential": {      
      "kind": "jwt_token",
      "token": "ghp_abc123..."
    }
  }'

This request instructs Sequin to sync GitHub pull requests and repositories using the provided API token.

​

Create a Sequin Postgres consumer

Next, you’ll create a Postgres consumer to stream data from GitHub to your Neon Postgres database. Consumers are how you stream data from Sequin’s syncs to destinations.

You can create consumers in Sequin’s console or API. Here’s an example curl request to create a Postgres consumer:

curl --request POST \
  --url https://api.sequin.io/v1/postgres_consumers \
  --header 'Authorization: Bearer {your-api-token}' \
  --header 'Content-Type: application/json' \
  --data '{  
    "name": "Neon - GitHub",
    "schema": "sequin",
    "table": "records",
    "postgres": {
      "name": "github_prod", 
      "connection": {
        "host": "ep-lingering-sky-123456.us-east-2.aws.neon.tech",
        "port": 5432,
        "database": "main", 
        "username": "sequin",
        "password": "the-password-you-created-for-the-sequin-user"  
      }
    },
    "sync_id": "8ff58ef0-d376-4ae8-b2e2-9f0206aa65b8"
  }'

Use the “Connection Details” from your Neon database for the host, port, and database. Use the sequin Postgres you created earlier for the username, and password fields.

The sync_id field specifies which Sequin sync to consume data from. Omit this field to consume data from all syncs.

At this point, your GitHub data should be flowing into Neon Postgres! Let’s verify by querying the database.

​

Query your API data in Neon

In the Neon console, open the “SQL editor.”

Here’s the updated last quarter of the guide, taking into account your edits:

​

Query your API data in Neon Postgres

In the Neon console, open the “SQL editor.”

Verify data is being synced by running this query:

select count(*) from sequin.records;

You should see a non-zero count, indicating that Sequin has begun streaming data from GitHub into your Neon database.

To see an example record:

select * from sequin.records limit 1;

Now, any time a pull request or repository is created, updated, or deleted on GitHub, Sequin will upsert a row in the sequin.records table in real-time!

​

Create views to make querying easier

While you can query the sequin.records table directly, you’ll usually want to create views that flatten the nested JSONB payload.

For example, here’s how you could create a view for GitHub pull requests:

create view sequin.github_pull_requests as
select
  payload->>'id' as github_id,
  payload->>'url' as url,
  payload->>'title' as title,
  payload->>'user'->>'login' as user_login,
  payload->>'body' as body,
  (payload->>'created_at')::timestamp as created_at,
  (payload->>'updated_at')::timestamp as updated_at,
  (payload->>'closed_at')::timestamp as closed_at,
  (payload->>'merged_at')::timestamp as merged_at,
  payload->>'merge_commit_sha' as merge_commit_sha,
  payload->>'requested_reviewers' as requested_reviewers,
  payload->>'head'->>'label' as head_label,
  payload->>'head'->>'ref' as head_ref,
  payload->>'head'->>'sha' as head_sha,
  payload->>'base'->>'label' as base_label,
  payload->>'base'->>'ref' as base_ref,
  payload->>'base'->>'sha' as base_sha
from sequin.records
where collection_id = 'github:pull_request';

The payload->> syntax extracts a value from the JSONB payload. The where collection_id clause is used to filter the view to only pull requests.

With this approach, you can create a view for each collection you’re syncing from GitHub. This gives you full control of the schemas for your API data. Because Sequin is syncing to the core sequin.records table, it will be unaffected by your schema changes.

You can query views like any other table:

select title, state, created_at 
from sequin.github_pull_requests
order by created_at desc
limit 10;

To generate these views, you can use this handy tool.

​

Where to next?

By streaming your API data to Neon Postgres, you have a complete picture of your API data at rest. This means you can query your API data using the full power of SQL, without rate limits, and join it with other data in your Neon database.

If you weren’t following along, head over to Neon to sign up for an account and start streaming.

Check out the Neon docs to learn more about their platform, and the Sequin docs for more on streaming API data.