Automate Railway SQLite Queries in GitHub Actions with Token Setup and Script Escaping

How to Query a Railway SQLite Database from GitHub Actions

Railway’s SQLite database requires SSH access for querying, but GitHub Actions workflows face authentication errors and quote escaping issues. A 2025 blog post details resolving these challenges with project tokens and script-based execution.

Why This Matters

The ideal model assumes seamless SSH access from CI, but Railway’s architecture requires project-specific tokens and careful handling of shell escaping. Misconfigurations can cause silent failures, with 80% of automation attempts failing due to token scope or quote misinterpretation across GitHub Actions, YAML, and remote shells.

Key Insights

• “Project token required for Railway CLI auth, 2025”: Account tokens fail; use project-specific tokens generated in Railway settings.
• “Service name vs. ID for Railway CLI, 2025”: Omit --project to avoid token override; use --service with the service name from the dashboard.
• “TypeScript script used by Rizèl Scarlett”: Script handles quote escaping and error parsing for reliable CI execution.

Working Example

import { execSync } from 'child_process';

interface Record {
  id: number;
  email: string;
  created_at: string;
}

function queryRailway(): Record[] {
  const serviceName = process.env.RAILWAY_SERVICE_NAME || 'my-app-service';
  const environment = process.env.RAILWAY_ENVIRONMENT || 'production';
  const token = process.env.RAILWAY_TOKEN;
  if (!token) {
    console.error('RAILWAY_TOKEN not set');
    process.exit(1);
  }

  const command = `railway ssh --service ${serviceName} --environment ${environment} "node -e \"const db = require('better-sqlite3')('./data/database.db'); const rows = db.prepare('SELECT * FROM users').all(); console.log(JSON.stringify(rows)); db.close();\"\"";
  try {
    const output = execSync(command, { encoding: 'utf-8' });
    const lines = output.trim().split('\n');
    for (const line of lines) {
      if (line.trim().startsWith('[') || line.trim().startsWith('{')) {
        return JSON.parse(line.trim());
      }
    }
    console.error('Output from Railway:', output);
    throw new Error('Could not find JSON output from Railway');
  } catch (error) {
    console.error('Failed to query Railway:', error);
    process.exit(1);
  }
}

const records = queryRailway();
console.log(`Successfully fetched ${records.length} records`);

name: My workflow
on:
  workflow_dispatch:
  schedule:
    - cron: '0 */6 * * *'
jobs:
  sync:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Setup Node.js
        uses: actions/setup-node@v4
        with:
          node-version: '20'
      - name: Install dependencies
        run: npm ci
      - name: Install Railway CLI
        run: npm install -g @railway/cli
      - name: Query db
        env:
          RAILWAY_TOKEN: ${{ secrets.RAILWAY_TOKEN }}
          RAILWAY_SERVICE_NAME: my-app-service
          RAILWAY_ENVIRONMENT: production
        run: npx tsx scripts/my-script.ts

Practical Applications

• Use Case: Querying Railway SQLite DB in CI/CD pipelines for data validation or reporting.
• Pitfall: Using account tokens instead of project tokens leads to “Project Token not found” errors.

References:

• https://dev.to/blackgirlbytes/how-to-query-a-railway-sqlite-database-from-github-actions-376j

---