Select all the files with a spefic extension Github actions workflow

Question

Hi I am trying to run a pre-commit hook on all the files with .sql extension. I was wondering how can I do that. All those files with .sql are in the models/ directory, but I have sub-directories within that directory too (to group those sql files) so what is the best way to get all the files with .sql ending? . This is my .pre-commit-config.yml file:

repos:
  - repo: https://github.com/sqlfluff/sqlfluff
    rev: 0.9.1
    hooks:
      - id: sqlfluff-fix
        args:
          [
            --exclude-rules,
            'L011,L031',
            --dialect,
            'clickhouse',
            --templater,
            'dbt',
            --fix_even_unparsable,
            'True',
          ]
    files: '*.sql'

What should I assign to files: '*.sql': ^ instead of this

In the pre-commit documentation, I couldn't find an answer or in the sqlfluff documentation

Did you try [filtering with file types](https://pre-commit.com/#filtering-files-with-types) e..g `types: [file, sql]`? The extension `.sql` is supported with the `types` filter (https://github.com/pre-commit/identify/blob/fdab9f7afac7b70dd7eb30dd2eea7f7b688b0201/identify/extensions.py#L211). — Azeem, Mar 08 '23 at 08:48
@Azeem yeah thanks that works, how should I set it up so it will take all the sql files in the PR rather than entire branch — Dude, Mar 08 '23 at 09:35
IIUC, you want to run it only on the files in the PR, right? — Azeem, Mar 08 '23 at 10:36
Right. Are you running `pre-commit` via its action (https://github.com/pre-commit/action)? — Azeem, Mar 08 '23 at 10:47
Should I be setting it up as: pre-commit run --hook-stage commit sqlfluff-fix --all-files ? — Dude, Mar 08 '23 at 10:51
You can use `--from-ref` and `--to-ref` for this. See https://github.com/pre-commit/action/issues/7#issuecomment-1251300704 and https://pre-commit.com/#pre-commit-run. — Azeem, Mar 08 '23 at 10:55
@Azeem thank you ser you are a true gentelman. Lastly It does run the linter and shows me the fixed version of the sql file but then the workflow fails. is there a way to automatically apply those changes into that file? — Dude, Mar 08 '23 at 11:22
You're welcome! :) For that, you'll have to use https://docs.sqlfluff.com/en/stable/cli.html#sqlfluff-fix, commit and push the updated files to PR. — Azeem, Mar 08 '23 at 11:27
Aah I see so I have manually commit and push, it cannot be done automatically in the Github actions — Dude, Mar 08 '23 at 11:30
You can use https://github.com/stefanzweifel/git-auto-commit-action for this. — Azeem, Mar 08 '23 at 11:32

Azeem · Accepted Answer · 2023-03-08T14:25:33.910

2

You can filter files by file types i.e. types: [file, sql] as SQL (.sql) filtering is supported by pre-commit.

To keep this restricted to a PR, you might want to use --from-ref and --to-ref as part of your workflow. Refer to this comment for an example:

--from-ref ${{ github.event.pull_request.base.sha }}
--to-ref   ${{ github.event.pull_request.head.sha }}

By default, sqlfluff will fail in case of any violations i.e. it'll return non-zero exit status resulting in step failure. This can be suppressed by using --nofail flag if required.

And, to apply the changes automatically, you'll have to update workflow as if you're running:

sqlfluff fix --force ...

--force is to skip the confirmation during fixing SQL issues.

After that, you can commit these changes via https://github.com/stefanzweifel/git-auto-commit-action or manually.

UPDATE

As pointed out in anthony sottile's answer, the filter is redundant as sqlfluff already takes care of it.

edited Mar 08 '23 at 14:25

answered Mar 08 '23 at 11:58

Azeem

11,148
4
27
40

Thank you so much for you help! There aren't many resources available on sqlfluff with pre-commit hooks but you provided top notch solutions! I am truly GRATEFUL – Dude Mar 08 '23 at 12:01
I dodged the fails with continue-on-error: true step – Dude Mar 08 '23 at 12:03
1

@Dude: Glad I could be of help! :) Regarding `continue-on-error: true`, the step fails as soon as it encounters an error and doesn't execute the rest of the step. For your use case, it might be enough so it's all good. However, you can simply skip linting and directly run the fix command if that's what you want to do eventually. – Azeem Mar 08 '23 at 12:06
`types: [file, sql]` is redundant -- `sql` is subset of `file` -- but also that doesn't need to be set at all (see my answer below) – anthony sottile Mar 08 '23 at 14:16
@anthonysottile: Thank you for pointing that out! I'll update accordingly. – Azeem Mar 08 '23 at 14:22

score 2 · Answer 2 · answered Mar 08 '23 at 14:16

2

files is a regex and not a glob so *.sql is not the correct pattern for it -- the correct regex for that would be files: \.sql$

but, you shouldn't need to set files (or types) at all -- the point of including a pre-written remote repository is it is already set up out of the box for you! for example, the remote sqlfluff repo already sets types: [sql]

disclaimer: I wrote pre-commit

answered Mar 08 '23 at 14:16

anthony sottile

61,815
15
148
207

Haha I actually watched one of your videos on pre-commit in anthony explains just before I started working on this, cant believe you are here lol. Would love to see more episodes on these guys ^^ – Dude Mar 09 '23 at 09:14

Select all the files with a spefic extension Github actions workflow

2 Answers2