Electron Archives - Spinning Code

Getting Started with Salesforce2Sql

Salesforce2Sql is a tool dedicated to doing one thing very well: mirroring Salesforce schema.

A little over a year ago I started work on Salesforce2Sql to help support people doing data work with Salesforce. Salesforce2Sql is a simple Electron app that allows you to clone the schema of a Salesforce org into an SQL database (currently supports MySql/MariaDB and Postgres). These mirrors are useful when you want to stage data during migrations in or out of Salesforce.

When is Salesforce2Sql useful

Salesforce2Sql is a tool dedicated to doing one thing very well: mirroring Salesforce schema. It does not attempt to extract data or convert data in any way.

Salesforce provides excellent APIs for data import and export, but they work best with some prep work first. Salesforce2Sql gives you a staging database that mimics your Salesforce schema. In these schema mirrors you can prepare all your data for high speed processing off-platform. Anyone who is looking to move data into or out of Salesforce at high speeds and large volumes benefits from this setup.

I have seen people write large complex ETL jobs meant to go right from one source system into Salesforce. These jobs can be hard to test and they are slow to run. They generally don’t give you an easy way to review the data before you push to Salesforce. By landing the data in a database clone you can break the jobs into stages, review transformations before they are loaded, and run thousands of iterations for testing instead of dozens.

Setup

Salesforce2Sql is built and released for MacOS, Windows, and Linux (most testing is on Mac and Windows). You can download the installers from the current release and follow the standard patterns for your OS. From there start the application to get to work.

You will also need API access to a Salesforce org and a database to create your schema within.

Basic Salesforce2Sql Use

As of this writing Salesforce2Sql uses the old security token connection method. I would like to add OAuth2 support as well but haven’t gotten that done; contributions are welcome. So with the application running, and your security token in hand, click the big “Create New Connection” button on the left side of the main interface.

Step 1: Fetch all objects

Once connected click “Fetch Objects”, and the tool will download a list of every object in your org. There will be several hundred. So the next step is to select which you want to mirror. You will notice Salesforce2Sql selected defaults for you (well I did). It will select all custom objects and based on your org’s structure Salesforce2Sql guess which standard objects to select. There is a search box at the top right to help you find any others you’d like to add but simply checking the box.

Step 2: Fetch all fields

Click the next button to move to the Proposed Schema tab, and then the “Fetch Details” button. Now Salesforce2Sql will query every field on every object you just selected (this may take a moment but honestly I find it much faster than I expected when I first started this project). Once that is complete you can either save that schema to JSON for later re-use, or click Next to move to the “Generated Database” tab.

Step 3: Generate tables

This is the last step. Click “Create Tables” and Salesforce2Sql will ask you for your database credentials. Once you click okay on this final screen the tool will attempt to create all those tables for you. Again this will take a couple minutes if you have a large schema. Once the process is complete you can also save the SQL statements for editing and/or later re-use.

That’s it, you now have a database with a schema that matches your org’s structure.

Preferences

There are a few preferences you might want to experiment with (although I tried to pick smart defaults) when building mirrors. For me the right choices depend on my use case.

Picklists

Salesforce Picklists are a bit of a special beast. The obvious choice is to make a picklist into a SQL Enum to support validation of data. But not all picklists are restricted in Salesforce and aren’t always required. By default the tool will use enum for restricted picklists, varchar for unrestricted picklists, and add blank values to all picklists (since it can’t easily determine if a given picklist is required or not across all page layouts).

If the picklist values in your org are pretty much set, the default settings make a lot of sense. If the picklist values in your org are likely to change you might want to make them all into regular varchar columns.

Auto Indexing

The next important section contains the index settings. While it is possible to over-index a database I think the three sets of default indexes are pretty good guesses: Id columns, external Ids, and picklists. The one you are most likely not to care about are the enums, and therefore the one you might consider disabling if you aren’t processing on those fields at all. Id columns are now case-sensitive even in MySQL by default as of version 0.7.0 since Salesforce Ids are case sensitive.

Additional Settings

The other defaults section let’s you pick a few other system behaviors. The most common to fuss with are first and last in the box.

By default it expects Lookup fields to be 18 character Salesforce Ids – cause that’s what they will be in Salesforce. But during a data migration some people like to put legacy Ids into these fields (I recommend a proper legacy Id field marked as an external Id) so I give the option to use 255 characters instead of 18.

Salesforce also has two categories of fields that are common to ignore in a migration and you may wish to keep out of your database just for ease of use: the audit fields (createdBy and the like) and read-only fields (like formulas). The final two checkboxes in that other defaults section lets you keep those fields out of your clone schema.

The middle two fields control the behavior of field defaults – generally I like these two settings as is in just about every use case, but you may feel differently.

Salesforce2Sql uses Bootswatch themes for design elements. The preference pane also lets you pick a different look-and-feel from their theme list.

Final Notes

There are a couple other details worth knowing.

First, if the process runs into a problem where the SQL engine complains about row size limits Salesforce2Sql will automatically switch all varchar fields to TEXT fields in an attempt to reduce the row size. That will override all preference settings for these fields.

Second, Knex.js – which Salesforce2Sql uses to handle the actual SQL writing – adds indexes as a table alter even when they could be part of the create statement. This makes the process a bit slow and it means that if there are errors during the creation of indexes you may see some errors in the interface but leave you with a pretty-good schema clone.

Finally, yes there is lots of room for improvement. I work on this project when I can, or when I need a bug fixed for my own work. I am excited to get suggestions, ideas, feedback, documentation edits, and code submissions.

SC DUG April 2021 – Getting Started with Electron

This month I gave a talk at South Carolina Drupal User Group on Getting Started with Electron. Electron allows you to use your web developer skills to create desktop applications. I based this talk on some of my recent side projects and the Electron Project Starter I posted the end of last year.

If you would like to join us please check out our up coming events on MeetUp for meeting times, locations, and remote connection information.

We frequently use these presentations to practice new presentations, try out heavily revised versions, and test out new ideas with a friendly audience. So if some of the content of these videos seems a bit rough please understand we are all learning all the time and we are open to constructive feedback. If you want to see a polished version checkout our group members’ talks at camps and cons.

If you are interested in giving a practice talk, leave me a comment here, contact me through Drupal.org, or find me on Drupal Slack. We’re excited to hear new voices and ideas. We want to support the community, and that means you.

Salesforce Electron Starter

Back in August I created an Electron project starter that provides a template to use for electron projects with the goal of outlining how to follow the current best practices for writing secure electron apps. I had extracted that from a couple of personal projects I work on from time to time, one of those projects is ElectronForce – a tool to explore Salesforce orgs. Because I get ideas of things I want to try out from time to time as Salesforce APIs applications I have now created a derivative project that is setup to create apps that leverage JSForce to interact with Salesforce orgs.

Thus I would like to introduce Electron Salesforce Base.

Like my generic project starter it is intended to be a jumping off point that handles some of the basic elements of a project. It’s a bit more opinionated because comes with a little more plumbing in place – there is a simple interface and it’ll actually log into orgs (assuming you have your security token).

The interface is built using a Bootstrap dark theme from Bootswatch, and is set up to follow the Airbnb ESLint standards (with a couple small tweaks). The interface generates two windows, one that is meant to be the main interface and includes the controls to log into your org, and a second that is meant to keep a running log of events.

The main thread is fully isolated from the render threads, with all requests and data being passed back and forth using the current inter process communication methodology from Electron leveraging the IPCmain object in the main thread and the contextBridge in the render thread – there is no access to remote in the render thread (actually remote is fully disabled as it should be), and the preload.js file largely serves to filter IPC requests to maintain thread isolation. Currently the main thread of this project isn’t what I would call graceful, and I’m actually working on a refactor for ElectronForce to improve the IPC listener definitions (readers with examples of good design patterns are more than welcome to offer suggestions, whatever I settle on will likely get folded back into this project eventually).

To help understand the general pattern that’s emerging as people get better at sandboxing in Electron (and Electron gets better at demanded it) I find it helpful to think of Electron apps in a client-server model with two largely separate applications and a well defined API for communication between them. You’ll see that reflected here, and in my other recent Electron apps. You can implement whatever you’d like in each layer and just pass messages back and forth. This also means you can totally refactor one part of your application without worrying about the other. So if you hate my proto-interface dump it and build something better.

If you look at the code for this base project, then look at ElectronForce, you will see the render thread provides all the details of the interface – including use of render-friendly libraries like jQuery and a collection of helper functions to make life a little easier – makes an API call (with a filter list provided in preload.js), and then handles responses from the main thread. In main.js you see all the IPC listeners defined, which then pass the needed data to JSForce to make the API calls, before handing back structured data for the interface to render.

As you dig through the code you may notice various @TODO statements that are notes to myself about places with obvious room for improvement. I’m always happy to get suggestions, as comments here, issues there, or as pull requests to help resolve those notes with better solutions.

Simple Electron Starter

Earlier today I push my Electron Simple Starter to Github. It has dependencies only to Electron, Electron Debug, and ESlint (but no specific settings, you can add those yourself). All the basic pieces are in place to encourage good security practices. It will run without warnings or errors, and puts in place all plumbing you need for their current inter-process communication, default overrides, and process sandboxing to help you write a secure app.

Off and on I’ve been playing at writing simple programs in JavaScript using Electron. As a long-time web developer the idea of writing a web app that can be compiled to an native application across a web swath of major operating systems has massive appeal.

But when I started to write Electron apps to scratch various itches, I was quickly annoyed at the number of security warnings I got when following the project tutorials. The PHP community used to ignore bad security in tutorials to the detriment of web, so it bugs me to see that behavior crop up in other places. With my most recent side project – a Salesforce API exploration tool – I finally decided I was overdue in figuring out how to resolve all the warnings the basic quick start from the main project triggers. Using a combination of this secure electron project template and the main project’s security tutorial I finally got there.

Then I wanted to scratch a different itch, which hasn’t really gone anywhere, cause all the work to get started in a secure way felt like a mountain to climb again. The secure electron template is too opinionated for me to use directly for a small toy project, and ElectronForce has all kinds of other code already in place, so I spun my wheels for awhile. Then I finally bit the bullet and extracted the bits I needed for the next project. Once I realized I had a fairly clean baseline, I figured I would probably want it again soon (I create projects frequently to explore an idea or scratch an itch) so I created a new project template that sets the baseline and is fairly unopinionated. My goal is to have something I can grab to start writing a simple application quickly.

While I’ve made some effort to secure this project baseline, security is always the project developer’s responsibility – you are still responsible for your project’s security. Please feel free to use my template, but understand that you still have to follow best practices to keep your app secure and those will change over time. The Electron project will inevitably evolve and change their security system again, and I will not promise to keep up. Also this is a template, not a library, when some future Electron adds features I didn’t use, you’ll need to update your project.

If some specific piece of this template confuses you, please feel free to ask either here or on Github. I can try to explain as best I am able, and maybe you’ll inspire another post sometime in the future to cover it in depth.