SQL writes are in private alpha.
For our publicly-available write strategy, see our doc on mutations.
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.
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:
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.
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:
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:
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.
To create a new record in the API, you’ll use the
insert command. Here’s how to insert a Salesforce
insert into salesforce.contacts (first_name, last_name, email)
values ('Paul', 'Atreides', 'firstname.lastname@example.org')
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:
|Max inserts per query
To update a record in the API, you’ll use the
update command. Here’s how to update a Salesforce
set email = `email@example.com`, note = 'Moved to new planet called Dune'
where id = '0038b00002gszToAAI';
UPDATE 0 1
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:
|Max updates per query
To delete a record in the API, you’ll use the
delete command. Here’s how to delete a Salesforce
delete from salesforce.contacts
where email = 'firstname.lastname@example.org';
DELETE 0 1
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:
|Max deletes per query
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.
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:
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.
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 than reads, as they need to be sent to the API.
Of note, this design means transactions do not behave normally through the proxy. See Transactions.
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.
See above the maximum number of records that can be mutated per
delete command. For larger mutations, batch your mutations in your code and apply the smaller batches to your database.
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
on conflict do nothing followed by an
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:
insert into salesforce.contacts (email) values ('email@example.com');
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.)
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.
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.
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.
Mutations made to Sequin-synced tables outside of the Proxy will fail. All
deletes must be made through the Proxy.
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 (
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.
Was this page helpful?