How to Query a Railway SQLite Database from GitHub Actions

Source: Dev.to

Why SSH?

Railway doesn’t expose SQLite databases to the internet, so the database file lives on disk inside the deployed container. SSH is the only way to access it.

railway ssh "node -e \"const db = require('better-sqlite3')('./data/my-demo.db'); const all = db.prepare('SELECT * FROM signups ORDER BY created_at DESC').all(); console.log(JSON.stringify(all)); db.close();\""

Problem 1: Getting the Right Token

RAILWAY_TOKEN must be a project token, not an account token.

1. Go to your Railway project dashboard.
2. Navigate to Settings → Tokens.
3. Click Generate Token.
4. Add the token as a GitHub Actions secret named RAILWAY_TOKEN.

Problem 2: Interactive Login Doesn’t Work in CI

Running railway login --browserless fails because it still requires manual token pasting.

Fix: Remove any login commands. The Railway CLI automatically uses the RAILWAY_TOKEN environment variable when it’s set.

Problem 3: Service Name vs. Service ID

Using --project or a service ID in CI causes the CLI to ignore RAILWAY_TOKEN and expect interactive login.

Solution: Use only the friendly service name with --service and omit --project.

railway ssh --service your-service-name --environment production \
  "node -e \"const db = require('better-sqlite3')('./path/to/database.db'); const rows = db.prepare('SELECT * FROM your_table').all(); console.log(JSON.stringify(rows)); db.close();\""

Problem 4: Escaping Quotes

Multiple layers of quoting (shell → GitHub Actions → YAML) broke the command.

Solution: Move the logic to a separate TypeScript script and invoke it from the workflow.

// scripts/query-railway.ts
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`);

Workflow step to run the script

- name: Query db
  env:
    RAILWAY_TOKEN: ${{ secrets.RAILWAY_TOKEN }}
    RAILWAY_SERVICE_NAME: my-app-service
    RAILWAY_ENVIRONMENT: production
  run: npx tsx scripts/query-railway.ts

Complete Workflow

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/query-railway.ts