Writing data
Sequin syncs data from the API to your Postgres database. In order to write data back to the API, you'll connect to your database via Sequin's Postgres Proxy.
When you make inserts, updates, and deletes to your Sequin-synced tables, Sequin's Postgres Proxy applies those changes to the API first. If the API accepts those changes, they are applied to your database.
This doc is about our new Postgres Proxy. If you're an early Sequin customer, you can find the documentation for our HTTP Proxy here.
About the proxy
A database proxy is a service that sits between your database client and your database. Sequin's Postgres Proxy captures mutation queries you run on your database, or queries that perform a create, update, or delete.
When you run a mutation in your database via Sequin's Proxy, Sequin writes the mutation to the upstream API first. If the mutation is accepted by the API, Sequin will apply the mutation to your database:
If the mutation is rejected by the API, Sequin won't commit the write to your database. Instead, Sequin will return a Postgres error to the client:
Why the Proxy?
With the Postgres Proxy, you can mutate your API data right in your database. This means you don't need to think about the API's HTTP interface at all. Sequin takes care of everything from refresh tokens to rate limit management.
When you write through the Proxy, changes are always applied to the upstream API first. Only after the API successfully validates and commits those changes are they applied to your database.
Therefore, your code will receive a Postgres error in response to a failed mutation. This design allows your code to handle any issues associated with your mutation in-line. This design also ensures you avoid the issues associated with a "multi-master" or "multi-writer" architecture. You don't need to worry about the database drifting from the API or have to deal with conflict resolution.
Configuration
You'll connect to the Sequin Postgres Proxy like any other Postgres instance. The Proxy is compatible with all Postgres clients.
There are two steps for configuring your Postgres client to work with the Proxy:
Find your Proxy URL
When you setup a sync on Sequin, we provision a set of Proxy credentials you'll use to connect to your database. You'll use the Proxy credentials we give you to interface with your synced data. The URL will look something like this:
psql://my_sequin_user.sync_j8sual:secret-password@us-west-2.aws.sequindb.com:5432/my_database
You can find the connection URL for each sync on the sync's page, under "Connection Instructions."
The proxy is available in multiple regions to keep reads and writes fast. You can select a new region under "Connection Instructions" to get your sync's connection URL for that region. If you use IP whitelisting, make sure you've added Sequin's inbound IP addresses.
We're considering adding support for using any Postgres user with the Proxy. If using your existing Postgres user(s) is preferable for your use case, let us know.
insert
To create a new record in the API, you'll use the insert command. Here's how to insert a Salesforce contact:
insert into salesforce.contacts (first_name, last_name, email)
values ('Paul', 'Atreides', 'paul.atreides@caladan.io')
returning id;
id
--------------------
0038b00002gszToAAI
(1 row)
An insert command must translate to a single API query. That means there is a maximum number of records that you can insert per query:
| Platform | Max inserts per query |
|---|---|
| Airtable | 10 |
| HubSpot | 1 |
| Salesforce | 200 |
update
To update a record in the API, you'll use the update command. Here's how to update a Salesforce contact:
update salesforce.contacts
set email = `paul@dune.co`, note = 'Moved to new planet called Dune'
where id = '0038b00002gszToAAI';
UPDATE 0 1
(1 row)
An update command must translate to a single API query. That means there is a maximum number of records that you can update per query:
| Platform | Max updates per query |
|---|---|
| Airtable | 10 |
| HubSpot | 100 |
| Salesforce | 200 |
delete
To delete a record in the API, you'll use the delete command. Here's how to delete a Salesforce contact:
delete from salesforce.contacts
where email = 'paul@dune.co';
DELETE 0 1
(1 row)
A delete command must translate to a single API query. That means there is a maximum number of records that you can delete per query:
| Platform | Max deletes per query |
|---|---|
| Airtable | 10 |
| HubSpot | 100 |
| Salesforce | 200 |
Errors
Sequin validates each mutation with the source API before committing it to your database. If the mutation fails the source API's validation, Sequin will return a standard Postgres error:
ERROR: salesforce validation error
DETAIL: Key (email)=(pauldune.co) is not a valid email address.
You can handle the error in your application code as you would any other Postgres error.
We're consolidating the possible error types that you will receive. When we have the complete list, we'll update this part of the docs so that you know what to expect.
Considerations
Sequin's Postgres Proxy provides a radically new way to build on top of APIs. While it's designed to work with all your Postgres queries and workflows, there are two principles behind the Proxy's design that you should keep in mind while building on it:
Mutations are sent to the API synchronously
The Proxy will apply mutations first to the API, before committing the mutation to your database. This is an important part of the Proxy's design.
Therefore:
Network errors can happen
Mutations to Sequin tables via the Proxy trigger a network request to the API under the hood. The mutation will block until both the API request and the database mutation succeed.
This means your mutations are subject to network errors. For example, if the API you're writing to is down, your mutations will fail.
Fortunately, these network errors are translated into Postgres errors so that your application code can easily parse and handle them.
Mutations will take longer to execute
Mutations will take longer to execute than reads, as they need to be sent to the API.
Transactions are ignored
Of note, this design means transactions do not behave normally through the proxy. See Transactions.
Mutations map to one API request
At the moment, Sequin has to be able to map each mutation command to a single API request. This is to prevent you from getting into an inconsistent state.
For example, consider the situation where your insert mutation command needed five API requests to complete but failed on the third request. At this point, you've partially inserted the batch into the API. But it's not clear what mutation is safe to run next. If you re-ran the original mutation, you'd end up re-inserting records into the API.
Therefore:
There is a limit on the number of records that can be mutated at a time
See above the maximum number of records that can be mutated per insert, update, or delete command. For larger mutations, batch your mutations in your code and apply the smaller batches to your database.
Upserts are not supported
Upserts are currently not supported (i.e. insert into ... on conflict do update). That's because they can trigger more than one API request per mutation (e.g. one insert request and one update request). Instead, we recommend doing an insert with on conflict do nothing followed by an update.
Transactions
Mutations through the Proxy are applied to both the API and your database right away, even if you are currently in a transaction.
For example, consider this transaction block:
begin;
insert into salesforce.contacts (email) values ('paul.atreides@caladan.io');
rollback;
When the insert command runs, the insert will be applied to the API and the database outside the transaction. This means the rollback has no effect, as the insert has already been committed.
In other words, mutations to Sequin-synced tables "escape" the transaction.
Ignoring Postgres transactions is unusual, and may require you to rethink how you architect certain workflows. But APIs don't typically support transactions, so Postgres' transaction model does not fit neatly into Sequin's mutation paradigm. (See Considerations for more details on the Proxy's design principles.)
If this behavior causes issues for your application, we'd love to hear from you.
Region
Sequin's Postgres Proxy is currently hosted in the AWS us-west-2 region in Oregon. We'll be adding additional regions in the near future.
If you're far away from the us-west-2 region, this will add a little additional latency to each of your queries.
Databases in a private VPC
Sequin's Postgres Proxy is globally accessible. This means that if your database is in a private VPC, it will be reachable from outside that VPC via the Proxy. Naturally, your database is still protected by your database's security rules. And the Proxy only opens a tunnel to your database's host and port.
In the near future, we'll support IP whitelisting, so you can specify which IP addresses are allowed to connect to your database.
FAQ
What happens if a mutation is successfully applied to the API but not my database?
Before writing to the API, a transaction is opened with your database. Therefore, the chances of an API write succeeding but the corresponding database write failing is low.
Even so, the API is the primary source of truth. All changes are applied to the API first, then flow down to your database.
In the unlikely situation where a database write fails, we'll return a Postgres error indicating the mutation may not be represented in the database yet. Reading from the database after the failed write will not be safe, as there is not a guarantee that the mutation has been applied yet.
However, Sequin's sync process will eventually capture the API change and propagate it to the database. So, this failure mode means your write becomes eventually consistent vs strongly consistent.
We're completing our exhaustive list of possible Postgres errors returned by our Proxy. We'll update this section when the list is completed.
What happens if I mutate my data outside of the proxy?
Mutations made to Sequin-synced tables outside of the Proxy will fail. All inserts, updates, and deletes must be made through the Proxy.
Should I use the Proxy for all my queries?
If you are using a Sequin-hosted database, you'll always use the Proxy for all your queries.
If you are using a self-hosted database, the answer is more nuanced:
All mutations (insert, update, and delete) to Sequin-synced tables should always flow through the Proxy.
For reads (select), we recommend starting out using the Proxy to read from your Sequin-synced tables. Then, as you build your integration, you may consider moving certain read queries or read workflows over to a direct connection to your database.
That's because for reads, Sequin's Proxy provides no benefit at the moment. The Proxy's purpose is to handle mutations. Therefore, reads will be faster on a direct connection, as they'll avoid the extra hop.
We do not recommend using the Proxy as your primary method to query tables that are not synced by Sequin. These tables are safe to query through the Proxy, but the Proxy will be slower than a direct connection and not provide any benefit for queries to non-Sequin tables.
