MogwaiNerfHerder/sf-schema-snapshot
Dump a Salesforce org's entire data model (objects, fields, relationships, validation rules) to SQLite in minutes, not hours. - MogwaiNerfHerder/sf-schema-snapshot
0 stars · 0 forks · 0 watchers
README
sf-schema-snapshot
Dump a Salesforce org's entire data model: every object, every field (with picklist values), every lookup/master-detail relationship in both directions, record types, and validation-rule formulas, into a local SQLite file. Once it's a database, you stop clicking through Setup > Object Manager and start writing SQL. python schema_snapshot.py build --db sf_schema.db --all Runs in under 3 minutes against a 1,000+ object org (standard objects + custom objects + every managed package you've installed). sqlite> SELECT name, type, reference_to FROM fields WHERE object_name='Opportunity' AND type='reference'; sqlite> SELECT child_object, field FROM child_relationships WHERE parent_object='Account'; sqlite> SELECT name, error_condition_formula FROM validation_rules WHERE object_name='Opportunity' AND active=1;
Table of contents
Why this exists Setup Usage Schema reference Example queries How it's fast Troubleshooting What's not covered Contributing
Why this exists Anyone who's inherited (or grown) a Salesforce org with 50+ custom objects and a handful of managed packages knows the pain: you want to answer a structural question ("what actually points at this object", "which validation rules fire on Closed Won", "what picklist values are actually active on this field"), and the honest options are:
Click through Setup > Object Manager, one object at a time. Go read the raw metadata XML and hope you're looking at the right sandbox/prod version. Ask in Slack and wait.
None of those scale, and none of them are queryable. This tool turns "the entire org's schema" into a single SQLite file, so the question becomes a SELECT. It's read-only against your org, it only ever calls describe and metadata-read SOQL. It cannot modify any data or configuration. Setup Prerequisites:
Salesforce CLI (sf), authenticated against the org you want to snapshot: sf org login web
Python 3.10+
Install: git clone https://github.com/MogwaiNerfHerder/sf-schema-snapshot.git cd sf-schema-snapshot pip install -r requirements.txt Or as an installed command: pipx install git+https://github.com/MogwaiNerfHerder/sf-schema-snapshot.git sf-schema-snapshot build --db sf_schema.db --all No Salesforce Connected App setup needed, the script piggybacks on your existing sf CLI session for auth (see How it's fast). Usage
Everything: standard objects, custom objects, managed packages
python schema_snapshot.py build --db sf_schema.db --allJust your custom objects, seconds instead of minutes
python schema_snapshot.py build --db sf_schema.db --custom-onlyA specific, known list
python schema_snapshot.py build --db sf_schema.db --objects Opportunity,Account,MyObject__cRe-run without validation rules (skips the Tooling API pass)
python schema_snapshot.py build --db sf_schema.db --all --skip-validation-rulesInterrupted a run? Pick up where it left off instead of starting over
python schema_snapshot.py build --db sf_schema.db --all --resumeTune concurrency (default 10; raise it if your org/network can take it,
lower it if you're hitting API rate limits)
python schema_snapshot.py build --db sf_schema.db --all --workers 20Sanity-check what you got
python schema_snapshot.py stats --db sf_schema.db Every build run is idempotent per object: re-running with the same --db replaces that object's rows rather than duplicating them, so you can re-run nightly and diff the file over time if you want to track schema drift. Querying the result Any SQLite client works (DB Browser for SQLite, DataGrip, VS Code SQLite extensions, the sqlite3 CLI if you have it installed). Or just Python's stdlib, no extra dependency: import sqlite3 conn = sqlite3.connect("sf_schema.db") for row in conn.execute("SELECT name, label FROM objects WHERE custom=1 ORDER BY name"): print(row) Schema reference objectsColumn Meaning
name API name (e.g. Opportunity, MyObject__c), primary key
label Display label
custom 1 if a custom object
key_prefix The 3-char Id prefix (e.g. 006 for Opportunity)
queryable / createable / updateable / deletable Org-level CRUD permissions for the running user, as SF reports them
fields
Column Meaning
object_name, name Composite primary key
label, type Display label, field type (string, picklist, reference, currency, etc.)
length, precision, scale Size constraints where applicable
nillable, unique_flag, createable, updateable Booleans (stored as 0/1)
default_value JSON-encoded default, if any
calculated_formula Formula text, for formula fields
picklist_values JSON array of {value, label, active}, includes inactive values, flagged, so you never accidentally map to a value SF no longer accepts
reference_to JSON array of object names this lookup can point to (arrays because polymorphic lookups like WhoId can target several)
relationship_name The relationship name used in SOQL traversal (e.g. Account for AccountId)
child_relationships The reverse of fields.reference_to: for a given parent object, every object that has a lookup pointing at it.
Column Meaning
parent_object The object being pointed at
child_object The object with the lookup field
field The lookup field's API name on the child
relationship_name Name for subquery traversal (e.g. OpportunityLineItems), null if SF doesn't expose one
cascade_delete, restricted_delete Delete-behavior flags
record_types
Column Meaning
object_name, name Which object, which record type
developer_name API name of the record type
active, is_default Booleans
validation_rules Org-wide (not per-object, query by object_name to filter).
Column Meaning
object_name Object the rule fires on
name Rule's developer name
active Boolean
description Admin-facing description, if set
error_message The message shown to users
error_condition_formula The actual formula, this is the part Setup makes you click into each rule individually to see
Example queries Every lookup field pointing at Account, across the whole org: SELECT object_name, name AS field, relationship_name FROM fields WHERE type = 'reference' AND reference_to LIKE '%Account%'; Every object with a validation rule that mentions a given field: SELECT object_name, name, error_condition_formula FROM validation_rules WHERE error_condition_formula LIKE '%StageName%'; All active picklist values for a field (Python, easiest for the nested JSON): import sqlite3, json conn = sqlite3.connect("sf_schema.db") row = conn.execute( "SELECT picklist_values FROM fields WHERE object_name='Opportunity' AND name='StageName'" ).fetchone() for v in json.loads(row[0]): if v["active"]: print(v["value"]) Custom objects with no incoming relationships (candidates for cleanup / orphaned config): SELECT o.name FROM objects o WHERE o.custom = 1 AND o.name NOT IN (SELECT DISTINCT parent_object FROM child_relationships) AND o.name NOT IN (SELECT DISTINCT reference_to FROM fields WHERE reference_to IS NOT NULL); (reference_to is JSON, so this needs a json_each join for exact matching; the query above is a fast approximation, ask in an issue if you want the exact version.) Which objects have the most fields (bloat check): SELECT object_name, count(*) AS field_count FROM fields GROUP BY object_name ORDER BY field_count DESC LIMIT 20; Required (non-nillable, no default) custom fields on an object, useful before writing an integration: SELECT name, label, type FROM fields WHERE object_name = 'Opportunity' AND nillable = 0 AND default_value IS NULL AND name LIKE '%__c'; How it's fast The obvious first implementation loops sf sobject describe -s <Object> once per object through the Salesforce CLI: for obj in $(sf sobject list); do sf sobject describe -s "$obj" --json >> dump.json done Don't do this. Each sf CLI invocation spins up a fresh Node.js process, roughly 5-8 seconds of pure startup overhead before it even makes the API call. Against a 1,000+ object org (which is most real orgs once you count managed packages: Salesforce ships plenty of standard objects, and things like duplicate-rule packages, integration connectors, etc. each add dozens more), that loop takes four-plus hours. This script instead:
Authenticates once, via sf org display --json, to get an access token and instance URL. Hits the REST describe endpoint directly over HTTP for every object, in parallel: GET {instanceUrl}/services/data/v{apiVersion}/sobjects/{ObjectName}/describe Authorization: Bearer {accessToken}
Runs --workers (default 10) of those requests concurrently with a thread pool.
Same data, same source of truth as the CLI, the CLI's sobject describe command is doing exactly this REST call under the hood anyway. The only thing that changes is not re-paying Node.js startup 1,000 times. Net effect: hours down to under 3 minutes. The sf CLI is still used for two things where it's the right tool: the one-time auth handoff, and Tooling API SOQL for validation rules (Tooling API isn't part of the standard describe endpoint). The validation-rule gotcha If you try to bulk-query validation rule formulas via Tooling API SOQL: SELECT ValidationName, Active, Metadata FROM ValidationRule ...Salesforce rejects it once there's more than one row, because Metadata (and any FullName-typed field) is a compound/metadata field that Tooling API only lets you retrieve one row at a time. This script works around it with a two-pass fetch: list Id/ValidationName/Active in one bulk query, then loop a per-Id follow-up query just for Metadata. With most orgs having a few dozen validation rules at most, that's still effectively instant. Troubleshooting ERROR: No authorization information found: run sf org login web first. The script uses whatever org your sf CLI session currently points at (sf org display to check, sf org list to see all logged-in orgs, sf config set target-org=<alias> to switch). UnicodeDecodeError / garbled characters in field labels (Windows): this is why the script forces encoding="utf-8" on the sf CLI subprocess call. If you see mojibake anyway, check you're on a recent sf CLI version. It's slower than 3 minutes for me: that number is for a ~1,175-object org over a decent connection with default --workers 10. Try raising --workers; if you start seeing failures instead, you're hitting API rate limits and should lower it instead. Some objects fail with permission errors: expected and handled gracefully, the script logs the failure and continues rather than aborting the whole run. Check the end-of-run summary for the failed object list. Usually it's an object your authenticated user genuinely can't describe (rare, but some managed-package internal objects restrict this). Unattended / CI use: swap sf org login web for sf org login jwt --client-id ... --jwt-key-file ... (JWT bearer flow); everything downstream is unchanged. What's not covered (yet)
Flows and legacy Workflow Rules: different Tooling API objects with their own metadata shapes, not yet wired up. Page layouts: same story, plus they're verbose (per-object, per-record-type). Apex triggers/classes: arguably out of scope for a "data model" snapshot, but an apex_triggers table mapping trigger to object would be a natural add. Sharing rules / OWD: would need the Metadata API proper (SOAP or the retrieve/deploy REST endpoints), not just describe + Tooling SOQL.
PRs adding any of the above welcome, the describe_many_rest / get_org_session pattern in schema_snapshot.py generalizes cleanly to any other bulk metadata pull. Contributing Issues and PRs welcome, see CONTRIBUTING.md. Changelog See CHANGELOG.md. License MIT, see LICENSE.