How to Query a Railway SQLite Database from GitHub Actions

#devops #githubactions #automation

I have an app on Railway that uses SQLite, and for a while I was manually SSHing into my project to query data whenever I needed it locally. What started as occasional checks became a daily interruption.

When I decided to automate this with GitHub Actions, I expected a quick win, but I discovered that what worked on my local machine failed in CI. Even my favorite AI tools, goose and Claude Code, couldn't quite figure it out. They claimed the issues I encountered were underdocumented. After more time than I'd like to admit, here's what I learned.

Why SSH?

I chose SQLite because it was lightweight and I only needed it temporarily, but Railway doesn't expose SQLite databases to the internet (which is good). Unlike Postgres or MySQL, there's no connection string or public endpoint, so the database file lives on disk inside the deployed container and SSH is the only way in.

This worked fine locally:

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();\""

In GitHub Actions, it failed with authentication errors, quote escaping problems, and silent failures.

Problem 1: Getting the Right Token

I needed a RAILWAY_TOKEN for authentication, so I found a token in my Railway account settings and assumed that was it, but I kept getting "Project Token not found."

That was an account token, and you actually need a project token:

Go to your Railway project dashboard
Navigate to Settings
Scroll to Tokens and click Generate Token
Add it as a GitHub Actions secret: RAILWAY_TOKEN

Problem 2: Interactive Login Doesn't Work in CI

Because I was struggling with auth errors, I thought maybe I needed to explicitly log in:

railway login --browserless

This failed with "Cannot login in non-interactive mode" because the --browserless flag still requires manually pasting a token, which isn't possible in CI.

The fix is to remove any login commands entirely since the Railway CLI automatically uses the RAILWAY_TOKEN environment variable when set.

Problem 3: Service Name vs. Service ID

Even with the correct token, I was getting auth errors, and I found examples using --service and --project together, passing a service ID.

This was wrong for my use case. I'm not entirely sure why, but when you specify --project or use a service ID in CI, the CLI seems to ignore RAILWAY_TOKEN and falls back to expecting interactive login.

The solution is to use just the service name (the friendly name on your service card in the dashboard) with --service, and omit --project so the project token handles scoping.

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

After fixing auth, I hit "Syntax error: '(' unexpected" because the command reached the container but the remote shell choked on the quotes.

The culprits are the shell, GitHub Actions, and YAML, which all have their own quoting rules, so by the time the command reached the container, the quotes had been stripped or misinterpreted.

Solution: Move Logic to a Separate Script

Instead of fighting four layers of quote interpretation, I moved everything to a TypeScript file:

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`);

Now my workflow step is clean:

- 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

Building the command in TypeScript means one layer of escaping instead of four, plus proper error handling and local testability.

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/my-script.ts