PostGraphile: Ignore Table Names Start with ACT

Understanding PostGraphile Table Filtering

PostGraphile automatically scans your PostgreSQL database to generate a comprehensive GraphQL schema. By default, it includes every table it discovers, which can lead to unnecessary exposure of internal or temporary tables. When you have tables with names like ACT_logs, ACT_cache, or ACT_temp, you may want to hide them from the public API entirely. This is particularly useful when these tables are used for internal application state management rather than user-facing data.

The core mechanism for this exclusion is the command-line interface (CLI) argument that defines which tables should be included. While you can explicitly list every table you want to include, it is often more efficient to use regular expressions to include most tables while excluding specific patterns. This approach reduces configuration complexity and makes maintenance easier as your database grows.

Configuring the Ignore Pattern

The primary method to ignore tables is using the --tables option in your PostGraphile startup command. This option accepts a string that follows a specific syntax for inclusion and exclusion. To ignore tables starting with "ACT", you need to construct a regex pattern that matches any table name beginning with those letters.

Here is the standard command structure:

postgraphile \
  -c "postgres://user:password@localhost/dbname" \
  --schema public \
  --tables 'public,!^public\.ACT.*'

In this example, the --tables value contains two parts separated by a comma. The first part, public, tells PostGraphile to include all tables in the public schema. The second part, !^public\.ACT.*, is the exclusion rule. The exclamation mark indicates negation. The caret ^ asserts the start of the string. The literal characters ACT match the prefix. The asterisk * matches any subsequent characters. The backslash before the dot escapes the period, ensuring it matches a literal dot rather than any character in regex terms.

Alternative: Using Environment Variables

If you prefer not to embed the configuration directly in your startup script, you can use environment variables. This is often cleaner for containerized deployments like Docker. Set the PGGRAPHILE_TABLES environment variable to the same regex string.

export PGGRAPHILE_TABLES='public,!^public\.ACT.*'
postgraphile -c "postgres://user:password@localhost/dbname"

This achieves the exact same result as the CLI flag. PostGraphile reads this variable during initialization and applies the filtering logic before generating the schema. This method is recommended for production environments where configuration changes should be separate from code deployment.

Verifying the Configuration

After applying the configuration, it is crucial to verify that the tables are indeed ignored. You can do this by inspecting the generated GraphQL schema. Start PostGraphile and navigate to the GraphiQL interface. Search for types related to the tables you intended to exclude. If the configuration is correct, you will not see any mutation inputs or output types for ACT_logs or similar names.

If you encounter issues, check the console output when PostGraphile starts. It often logs the tables it has identified and filtered. Look for warnings or informational messages about excluded tables. This logging can help confirm that your regex pattern is matching the intended tables. Remember that regex matching is case-sensitive by default. If your tables are named act_logs in lowercase, you must adjust your pattern accordingly, such as !^public\.act.*.

Common Pitfalls and Best Practices

One common mistake is forgetting to escape special characters in regex. In PostgreSQL, table names are often quoted if they contain mixed cases or special characters. However, inside the PostGraphile --tables regex, you are matching against the unquoted identifier. Ensure your pattern reflects the actual name stored in the system catalogs. Another pitfall is overlapping rules. If you specify both an explicit inclusion list and a complex exclusion list, ensure they do not conflict. The exclusion rules generally take precedence in modern versions of PostGraphile, but clarity is key.

It is also advisable to test your regex pattern in isolation before applying it to the full command. Tools like online regex testers or simple SQL queries can help validate that your pattern correctly identifies the target tables. For instance, you can run a query against pg_tables to see which tables match your pattern. This proactive testing prevents runtime errors and unexpected schema generation.

Frequently Asked Questions

Can I ignore multiple prefixes at once?

Yes, you can combine multiple exclusion patterns in the --tables argument. Separate each pattern with a comma. For example, to ignore both ACT_ and TEMP_ prefixed tables, use !^public\.ACT.*,!^public\.TEMP_*. This allows you to manage multiple categories of internal tables efficiently.

Does this affect database performance?

No, ignoring tables in PostGraphile does not impact database performance. The filtering happens during the schema generation phase, which occurs at startup. It does not change how PostgreSQL handles queries or indexes. Your database operations remain unaffected by this configuration change.

What if I need to expose some ACT tables later?

You can modify the --tables regex dynamically. Since PostGraphile regenerates the schema on startup, simply updating the exclusion pattern and restarting the service will expose the previously hidden tables. There is no need to rebuild the entire application or migrate data.

How do I handle schema-specific exclusions?

The regex pattern must account for the schema name. If your tables are in a schema other than public, such as internal, update the pattern to !^internal\.ACT.*. Always prefix the pattern with the schema name to avoid accidental exclusions in other schemas.