Workaround for SELECT * (SELECT all) in sfdx force:data:tree:export SOQL

Question

In certain parts of our existing Apex code base, we've used dynamic soql in combination with object describe methods as a workaround to achieve the equivalent of SELECT * that's normally available in sql.

// This is the object for which we required data.
Map<String, Schema.SObjectField> fieldMap = 
Opportunity.sObjectType.getDescribe().fields.getMap();

// Get all of the fields on the object
Set<String> fieldNames = fieldMap.keySet();

// Build a Dynamic Query String.
List<Opportunity> opps = Database.query('select ' + string.join(fieldNames, ',') + ' from Opportunity');

I'm aware that querying all fields off a record isn't recommended within Salesforce, and should be used carefully/sparingly. However, we have a couple objects with 20 fields or fewer where we really do need every single field on the object in order to support the page and user processes it is involved in, so setting up a scratch org with data would mean exporting all the field values from a set of existing records and importing them into the org.

Unfortunately sfdx force:data:tree:export doesn't allow us to use Apex in conjunction with the SOQL statement to allow us to use the workaround.

Any ideas for how to work around this?

Answer 1

You could write some scripts to retrieve, parse, and generate queries from the CustomObject definition files, but there's no built-in way to do this directly in sfdx. I realize that you're doing dynamic query generation, but you should be updating your unit tests and/or data files as fields are removed/added/etc anyways, so I would think that trying to automate this is likely to do more harm than good.

Answer 2

One technique to relieve the tedium of typing in all those field names is to make a "poor man's data factory," which is simply a spreadsheet workbook with one sheet per Salesforce object. You could use MDAPI to extract the metadata, but I simply use my mouse in to select the Fields table and paste it Text-Only into the spreadsheet.

Specifically, with a column of field names in column A, then a formula like =A1&","would generate another column of strings like CustomFieldName__c, which would save a bunch of time in Select statements.

Taking the time to keep up this spreadsheet also reinforces the point made above where you should be deliberate and careful about what fields you include in any SOQL statement.