mrcampbell commented 1 year ago

Feature request

Simple REST Client that calls the Salesforce API

This is something I would like to work on, and am creating this ticket to track it.

Is your feature request related to a problem? Please describe.

Not my current problem, but one I've had in the past many times. Salesforce API connections are necessary to any business, and could be revolutionary in this context for every and any business reason a Salesforce customer could have. I've worked a ton with the Salesforce API, and can make this extensible so that some of their subsidiary API's (i.e. SteelBrick/CPQ) could be plugged in.

Salesforce also has its own Meta API that describes all of its API objects and relationships, making way for reflection in creating staging tables ranging from ETL Staging Tables to joins between the customer table and Salesforce. But that would require a separate schema with separate permissions, but still something to keep in mind.

But there are three problems businesses who use Salesforce usually need to solve:

Snapshots:

These are regularly performed queries used to capture the current state of the Salesforce data. I say "current state" because Salesforce only keeps an audit log if you specify it, and they take maintenance from Salesforce Devs in most cases. But these "Snapshot Queries" are made of a small group of primitive-equivalent types, from PickLists (which could be proxied/equivalent to an Enum/MultiSelect), String, Float, etc, with few exceptions.

Live Updates:

Salesforce provides a long-polling endpoint that uses the Bayeux Protocol to allow consumers to listen to custom "Topics" which are broadcast during CRUD events on Salesforce Objects, and ChangeSets which are broadcasted every time an object or object type experiences a change. This allows consumers to maintain state or at least eventual consistency. I don't think this problem could be solved in this context, as we're not working on a Materialize type of SQL Streaming, but I don't think that's outside the realm of possibilities

Roles/Permissions

Typically, if a Salesforce customer integrates with an app, there is only ever one permission set that gives all the access that integration would ever need. Our case would be unique in that perhaps this is being used for a static query in a deployment that has service_role access, but this would be extremely useful in data exploration, and in one-off queries. I, as a hypothetical admin or CTO, want to know that there are read-only credentials with tight access on schema/table/column/row. This is cleanly managed by just creating Salesforce API Keys for whomever and storing them as Env Vars on their machine, so there is no new level of risk or need for access/role permissions.

Describe the solution you'd like

I would like to be able to perform CRUD statements via the Salesforce API from SQL. I want the SQL client to consume the Meta API of my organization, which shows me all the objects and fields I have access to, so I can see what "tables" and "columns" I have access to at query time.

I would also like to be able to perform SOQL queries via string parameters:

select * from Accounts where soql_condition = "AccountId = ..."

This would allow me to perform functions and joins on my Salesforce Instance as part of my query, as I already have a massive subset of views and functions in my Salesforce's Apex repository, and SOQL exposes the full functionality of Salesforce to me (as in it can all be set up to be called on CRUD Triggers.

Describe alternatives you've considered

// TODO A clear and concise description of any alternative solutions or features you've considered.

Additional context

// TODO: Add any other context or screenshots about the feature request here.

burmecia commented 1 year ago

The Salesforce wrapper sounds cool, thanks @mrcampbell !

elvise commented 1 year ago

@mrcampbell amazing! Do you have a PR ?