This GitHub Action lints SQLite schema files to enforce various constraints. It is designed to ensure that your SQLite schemas adhere to best practices.

You can also install it to validate schemas locally.

Inputs

schema-file: (Required) The SQL schema file to lint.

Usage

To use this action in your workflow, include a step in your job that uses playfulpachyderm/sqlite-lint. Below is an example of how to set it up in a GitHub Actions workflow file:

jobs:
  lint:
    runs-on: ubuntu-latest

    steps:
      - name: Checkout code
        uses: actions/checkout@v4

      - name: Validate SQL schema
        uses: playfulpachyderm/sqlite-lint@v1.0.0
        with:
          schema-file: pkg/db/schema.sql

Installing

CGo is required. You will need a C compiler (and if it isn't gcc you might have to specify what it is as CC=whatever before installing).

CGO_ENABLED=1 go install github.com/playfulpachyderm/sqlite-lint/cmd/sqlite_lint@v1.0.2

sqlite_lint <path/to/schema.sql>

Available Checks

All checks are enabled by default. To turn one off, you can use:

    with:
        - schema-file: [...]
        - require_not_null: false

This will disable the require_not_null check.

When running locally, you can use:

INPUT_REQUIRE_NOT_NULL=false sqlite_lint <path/to/schema.sql>  # `require_not_null` check will be skipped

`require_not_null`

Enforce that all columns should be marked as not null unless they are foreign keys.

Explanation: Nulls are a common source of unexpected bugs, because they're usually an invalid state but often get created by mistake (e.g., you forgot to set a value). Explicitly disabling nulls prevents such mistakes.

If you need to track "unset" values, add an is_X_valid column to make it explicit. Note that in many cases, 0 or "" (empty string) are sufficient null values, and an is_X_valid flag might not even be required.

Foreign keys are exempt in this check because null is the only value the integrity checker will accept to represent "this row has no related item".

`require_strict`

Enforce that all tables should be marked as strict.

Explanation: By default, SQLite is very loose with what it accepts, and basically doesn't enforce any type checking. "Strict" tables disable this "looseness" and enforce that inserted values match the stated type of the column.

"Strict" tables also limit to a small number of column types: int, integer, real, text, blob or any. To represent dates / times, use Unix epoch times in milliseconds, and convert to formatted dates (and timezones) only when displaying the value to a user. This is the most portable and least bug-prone method to handle dates.

See more about "strict" tables in SQLite's documentation: https://sqlite.org/stricttables.html

`forbid_int_type`

Enforce that all columns should use integer type instead of int.

Explanation: This is an extension of "strict" tables, which allow two redundant integer types, integer and int. This check standardizes the types further, permitting only integer.

`require_explicit_primary_key`

Enforce that all tables must have a primary key. If the primary key is rowid, it must be declared explicitly.

Explanation: All tables need to have a primary key, and it should usually be rowid. Declaring it explicitly improves the readability of the schema.

`require_indexes_for_foreign_keys`

Enforce that columns referenced by foreign keys must have indexes.

Explanation: Foreign keys are usually used for joins. Joining on un-indexed columns is very slow. Ensuring that all foreign-key-referenced columns have indexes will greatly improve the performance of database operations.