Output migration SQL

[alper]

Feature Description

I was kinda disappointed to see that migration:show does not output the SQL of the migration.

This is a relatively essential feature for many applications for instance if you want to lint the SQL through a tool like: https://github.com/sbdchd/squawk

The Solution

I would like a command similar to django-admin sqlmigrate where you can pass a migration by name (as output by migration:show) and then get the SQL output to stdout.

It would be nice if it were possible to do this without a connection to the database. I think it's possible here because all the SQL is already materialized whereas in Django it isn't.

Considered Alternatives

I checked but could not find any workaround other than patching the migration files (or what we are doing is to rip the SQL code from the migration files using the typescript APIs).

Additional Context

No response

Relevant Database Driver(s)

• [ ] aurora-mysql
• [ ] aurora-postgres
• [ ] better-sqlite3
• [ ] cockroachdb
• [ ] cordova
• [ ] expo
• [ ] mongodb
• [ ] mysql
• [ ] nativescript
• [ ] oracle
• [ ] postgres
• [ ] react-native
• [ ] sap
• [ ] spanner
• [ ] sqlite
• [ ] sqlite-abstract
• [ ] sqljs
• [ ] sqlserver

Are you willing to resolve this issue by submitting a Pull Request?

Yes, I have the time, and I know how to start.

[bmbferreira]

Just wanted to share how I ended up doing because of this limitation: basically I run the migrations on a docker container, extract the SQL statements executed and then run squawk on the sql file. Here's an example using github actions:

  lint-db-migrations:
    name: Lint DB Migrations
    permissions:
      contents: read
      pull-requests: write
    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_USER: postgres
          POSTGRES_PASSWORD: postgrespassword
          POSTGRES_DB: mydb
        ports:
          - 54320:5432
        # Set health checks to wait until postgres has started
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
    runs-on: ubuntu-latest
    steps:
      - name: checkout target branch
        uses: actions/checkout@v4
        with:
          ref: ${{ github.event.pull_request.base.ref }}

      - name: Run migrations from the target branch
        run: yarn typeorm migration:run

      - name: Enable logs for the postgres user
        run: PGPASSWORD=postgrespassword psql -h localhost -p 54320 -U postgres -d mydb -c "ALTER ROLE postgres SET log_statement TO 'all';"

      - name: checkout source branch
        uses: actions/checkout@v4
        with:
          ref: ${{ github.event.pull_request.head.ref }}

 
      - name: Run migrations from the source branch
        run: |
          yarn typeorm migration:run

      - name: Get the docker logs from the postgres container and save it into a file
        run: docker logs "${{ job.services.postgres.id }}" >&  logs

      - name: Extract the queries from the logs
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
        run: node scripts/gh-actions/extract_sql_statements.js

      - name: View the queries
        run: cat logs

      - name: View the extracted queries
        run: cat extracted_statements.sql

      - uses: sbdchd/squawk-action@v2
        env:
          GITHUB_TOKEN: ${{ secrets.GITHUB_TOKEN }}
        with:
          files: 'extracted_statements.sql'
          fail-on-violations: true

and the js script to extract the logs:

const fs = require('fs')
const readline = require('readline')

const inputFilePath = 'logs'
const outputFilePath = 'extracted_statements.sql'

async function extractSQLStatements() {
  const fileStream = fs.createReadStream(inputFilePath)
  const rl = readline.createInterface({
    input: fileStream,
    crlfDelay: Infinity,
  })

  const sqlStatements = []
  // extract only logs from lines that start with "2024-09-16 08:40:30.307 UTC [348] LOG:  statement:"
  const statementRegex =
    /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3} UTC \[\d+\] LOG:  statement: (.*)$/
  const logRegex = /^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}\.\d{3} UTC \[\d+\](.*)$/

  for await (const line of rl) {
    const match = statementRegex.exec(line)
    if (match) {
      let statement = match[1].trim()
      if (!statement) {
        // Handle multi-line SQL statements
        statement = ''
        let nextLine = (await rl[Symbol.asyncIterator]().next()).value
        while (nextLine && !logRegex.exec(nextLine)) {
          statement += nextLine + '\n'
          nextLine = (await rl[Symbol.asyncIterator]().next()).value
        }
      }
      if (!statement.endsWith(';')) {
        statement += ';'
      }
      sqlStatements.push(statement.trim())
    }
  }

  fs.writeFileSync(outputFilePath, sqlStatements.join('\n\n'))
  console.log(`Extracted SQL statements have been written to ${outputFilePath}`)
}

extractSQLStatements().catch((err) => console.error(err))

[vovados1]

Hey everyone! Had this issue as well, so I created a cli script that allows to generate .sql files from typeorm migration files, and revert them as typeorm migration:revert does it.

https://www.npmjs.com/package/typeorm-raw-migrations