SQL writes

With Sequin, you create, update, and delete objects in Salesforce through your database using SQL. You don't need to interface with the Salesforce API in any way.

Synchronous mutations

A query that creates, updates, or deletes records in your database is called a mutation.

When you run a mutation in your database via Sequin, Sequin validates the mutation with Salesforce's API. If the mutation is accepted by Salesforce, Sequin will apply the mutation to your database:

If the mutation is rejected by Salesforce, Sequin won't commit the write to your database. Instead, Sequin will return a Postgres error:

Postgres Proxy

Synchronous writes are a feature of Sequin's Postgres Proxy. The Proxy intercepts your SQL queries and executes them against the Salesforce API before committing the transaction to your database:

This pattern means you can easily handle Salesforce validation errors in your code. Furthermore, it means your database and Salesforce will never get out of sync.

Connecting

You'll connect to the Sequin Postgres Proxy like any other Postgres instance. The Proxy is compatible with any Postgres client.

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:secret-password@proxy.sequindb.com:5432/my_database

At the moment, all requests through the Proxy route through Sequin's servers on the west coast of the U.S. (see limitations). Depending on where your servers are located, this could introduce extra latency to your database queries.

As such, you may consider running reads to your synced data directly against your database and reserve the Proxy for writes. We encourage you to start with using the Proxy for all reads and writes to your Sequin data, then make tweaks from there if your application calls for it.

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 Salesforce object, you'll use the INSERT statement:

insert into salesforce.contacts (first_name, last_name, email)
values ('Paul', 'Atreides', 'paul.atreides@caladan.io');

The query will return the Salesforce id for the newly created record(s) as changed_ids:

changed_ids
0038b00002gszToAAI

You can insert up to 200 records per query at a time.

We don't support complex returning statements yet. See limitations for more info about returning.

UPDATE

To update an existing Salesforce object, you'll use the UPDATE statement:

update salesforce.contacts
set email = `paul@dune.co`, note = 'Moved to new planet called Dune'
where id = '0038b00002gszToAAI';

As with inserts, the query will return the Salesforce id for the updated record(s) as changed_ids:

changed_ids
0038b00002gszToAAI

You can update up to 200 records per query at a time.

DELETE

To delete an existing Salesforce object, you'll use the DELETE statement:

delete from salesforce.contacts
where email = 'paul@dune.co';

The query will return the Salesforce id for the deleted record(s) as changed_ids:

changed_ids
0038b00002gszToAAI

You can delete up to 200 records per query at a time.

Errors

Sequin validates each mutation with the Salesforce API before committing it to your database. If the mutation fails Salesforce'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.

Limitations

Returning statements: Returning statements are not currently supported. By default, Sequin will return the id of the object that was created, updated, or deleted as changed_ids.

We'll support returning * soon. If your use case requires a more complicated returning statement, let us know.

Max mutation size: Sequin validates each mutation with the Salesforce API upon execution. As a result, mutations are constrained by Salesforce's max API batch size of 200 objects per query.

Upserts: Upserts are currently not supported (i.e. insert into ... on conflict do update). Instead, we recommend doing an insert with on conflict do nothing followed by an update.

Globally accessible proxy host: 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.

Proxy regions: The Sequin 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.