Salesforce to SQLite CLI Tool

A Node.js CLI tool that extracts data from Salesforce and loads it into a SQLite database based on a load plan configuration.

Features

• Extracts data from Salesforce using SF CLI
• Creates SQLite tables automatically based on query results
• Handles Salesforce relationship fields (e.g., Parent.Name becomes Parent_Name in SQLite)
• Automatic data type inference
• Progress logging with colored output
• Verbose mode for debugging

Prerequisites

• Node.js (v14 or higher)
• Salesforce CLI (sf) installed and authenticated
• Active Salesforce org connection

Sample Load Plans

• Load Plans

SQLite Explorer Pro

Use this explorer to explore the create database:

• SQLite Explorer Pro

Installation

npm install -g salesforce-to-sqlite

Usage

Basic Usage

sf2sqlite  -o <salesforce-username> -l load-plan.json

With Custom Database Name

sf2sqlite  -o [email protected] -l load-plan.json -d my-data.db

Verbose Mode

sf2sqlite  -o [email protected] -l load-plan.json -v

Options

• -o, --org <username> - Salesforce org username or alias (required)
• -l, --load-plan <file> - Path to load plan JSON file (required)
• -d, --database <file> - SQLite database file path (default: salesforce.db)
• -v, --verbose - Enable verbose logging
• -h, --help - Display help information
• -V, --version - Display version number

Load Plan Format

The load plan is a JSON array of object configurations. Each configuration includes:

{
  "object": "ObjectName",
  "compositeKeys": ["Field1", "Field2"],
  "query": "SELECT Field1, Field2, Related.Field FROM ObjectName",
  "fieldMappings": {
    "Field1": "Field1",
    "Field2": {
      "lookup": {
        "object": "RelatedObject",
        "key": "KeyField",
        "field": "Related.Field"
      }
    }
  }
}

How It Works

1. Read Load Plan: Parses the JSON configuration file
2. Query Salesforce: Executes each SOQL query using sf data query
3. Field Name Sanitization: Converts relationship fields (e.g., Account.Name) to SQLite-compatible names (Account_Name)
4. Table Creation: Creates SQLite tables with inferred data types
5. Data Insertion: Inserts queried data into SQLite tables
6. Type Conversion: Automatically converts Salesforce data types to SQLite equivalents

Field Name Handling

Salesforce relationship fields like Parent.Name or Account.Owner.Email are automatically converted to SQLite-compatible field names:

• Parent.Name → Parent_Name
• Account.Owner.Email → Account_Owner_Email
• Product2.StockKeepingUnit → Product2_StockKeepingUnit

Data Type Mapping

| Salesforce Type | SQLite Type | Notes | |----------------|-------------|-------| | String/Text | TEXT | Default for most fields | | Boolean | INTEGER | true=1, false=0 | | Number (Integer) | INTEGER | Whole numbers | | Number (Decimal) | REAL | Decimal numbers | | Date/DateTime | TEXT | ISO format strings | | ID | TEXT | Salesforce IDs |

Examples

Extract Product Catalog Data

sf2sqlite  -o production-org -l load-plan.json -d catalog.db

Debug Query Issues

sf2sqlite  -o sandbox -l load-plan.json -v

Output

The tool provides colored console output:

• 🔵 Info messages
• ✅ Success messages
• ⚠️ Warning messages
• ❌ Error messages

At the end, a summary shows:

• Number of successfully processed objects
• Number of failed objects
• Location of the SQLite database file

Error Handling

• Invalid load plan JSON: Exits with error message
• Failed Salesforce queries: Logs warning and continues with next object
• Table creation errors: Logs error and skips object
• Record insertion errors: Logs error but continues with other records

Performance Notes

• Large queries use a 50MB buffer for CSV data
• SQLite WAL mode is enabled for better performance
• Batch inserts using transactions for speed
• Progress is logged for each object

Troubleshooting

"Command not found: sf"

Install Salesforce CLI: https://developer.salesforce.com/tools/salesforcecli

"No auth found for org"

Authenticate with: sf org login web -a <alias>

"Query timeout"

Try splitting large objects into smaller queries or increase the buffer size in the code

"Failed to parse CSV"

Check query syntax and ensure fields exist in your org

License

MIT (c) Mohan Chinnappan