Your valuable company data is in Salesforce and you’ve been tasked with getting it outta there and safe and sound into your own MySQL or MS SQL databases. After all, nobody wants their precious data fragmented across third party services.

Sadly, Salesforce doesn’t currently have the option to connect directly to an external database ☹️

In this scenario you’re left with a number of questions.

  1. How can you export your Salesforce data with minimal hassle?
  2. Is there some way you can have the data pre-defined into SQL syntax including TABLE and INSERT statements so you don’t need to manually do it?
  3. Can you somehow set up an automated pipeline for all future data exports?

The answer to those questions lies with APIs.

​

Part one: salesforce APIs

Those lovely people over at Salesforce have a bunch of APIs for developers to do all sorts of wizardry with. Just take a look at this handy list.

​

salesforce APIs

​

We’ve chosen the Bulk API, which will export your Salesforce data to one of CSV, JSON, or XML.

You can use whichever API you prefer. Salesforce has good documentation on choosing APIs so it’s a good idea to take a look at that if you don’t want to use Bulk.

We’ll be focusing on exporting to CSV. John Miller very kindly shared his method and code, which you can see on Pastebin. His original instructions are on Stack Exchange.

For JSON and XML, Salesforce has some more documentation that outlines exactly how to export using Bulk API. And for a general overview of the Bulk API, check out this doc.

If you want to skip using a Salesforce API for some reason, you can use the Data Loader interface.

​

Part two: SQLizer API

Once you’ve navigated through the Salesforce export process, the hard part’s over. Now you need to convert CSV to SQL. That’s where the SQLizer API comes in.

The Sqlizer API is a simple REST interface that gives you programmatic access to our file conversion algorithm. The processes of converting a file with the API follows four steps:

  1. Initiate the file conversion
  2. Upload your file
  3. Monitor the conversion progress
  4. Download your converted file

See full docs for more detail.

​

Step 1: Initiate the file conversion

A JSON to SQL file conversion can be initiated with a POST request to http://sqlizer.io/api/files/ and later accessed and updated at the endpoint http://sqlizer.io/api/files/{ID}/.

If the request is successful the server will respond with a HTTP 200 code and a JSON representation of the file conversion. The ID field is especially important as it is used in the url of all future requests for this file conversion.

​

Step 2: Upload your file

Once you have a file ID you can begin uploading your file data by sending POST requests to http://sqlizer.io/api/files/{ID}/data/.

The Sqlizer API allows you to upload your data in a single request or multiple requests. The latter is achieved by splitting the file into parts and sending each individually. Most files can be uploaded in a single request but if your file is larger than 100Mb or you have an unreliable connection an upload in multiple parts is recommended.

Check out the full API documentation for in-depth details on single and multiple requests.

​

Step 3: Monitor the conversion progress

Once your file upload is finalised it will be placed in a queue for processing. You can check on its progress with a GET request to http://sqlizer.io/api/files/{ID}/.

curl http://sqlizer.io/api/files/7EVHQlVpq6YrRGLxNyjJdZ-b7DN3hcKpbqwK215IyPpE8ZeddSK4GVe_q0LNdCZnNCwOi1ewyTSEVMy6rkpi8g==/ \ -H "Authorization: Bearer {API-KEY}"

You can see sample responses in full API documentation.

​

Step 4: Download your converted file

Once the file conversion process is complete you can download the converted file from the url specified in the returned ResultUrl parameter.

🎊 Success! 🎉

​

Part three: SQL file into SQL database

Now all that’s left to do is simply import/upload your new SQL file into your database. Because SQLizer has pre-defined TABLE and INSERT statements this step is simply waiting for your file to import/upload.

​

Automate and be done with it

With this setup you could potentially automate the entire process of exporting Salesforce data and getting it into your own database. You can even set up a large-scale pipeline with the SQLizer API, convert files en-masse.

Happy automating!

SQLizer converts files into SQL databases. With an API to help automate conversion tasks, it’s one of the best ways to transfer data between databases. Convert a file now.