Repository: sqlfluff/sqlfluff
Pull request: Read config from pyproject.toml
This PR adds support for `pyproject.toml` as a configuration source.

It would help keeping all configuration for a Python project in a single file, since this file is used by other major Python projects like [black] and [pytest].

Some implementation details:

- Takes precedence over `.sqlfluff`.

- `core` section lives in `[tool.sqlfluff.core]`.

    ```toml
    [tool.sqlfluff.core]
    templater = "jinja"
    sql_file_exts = [
        ".sql",
        ".sql.j2",
        ".dml",
        ".ddl",
    ]

    [tool.sqlfluff.indentation]
    indented_joins = false
    template_blocks_indent = false

    [tool.sqlfluff.templater]
    unwrap_wrapped_queries = true

    [tool.sqlfluff.templater.jinja]
    apply_dbt_builtins = true
    ```

Closes #890

[black]: https://black.readthedocs.io/en/stable/pyproject_toml.html
[pytest]: https://docs.pytest.org/en/6.2.x/customize.html#pyproject-toml


Problem statement:
Look for project configuration in pyproject.toml.
A growing number of Python tools look for project-specific configuration in the `[tool.{app}]` section of the `pyproject.toml` file. It would be convenient if `sqlfluff` also inspected this section, especially for Python projects that use `poetry` as a build backend and thus do not rely on `setup.cfg` or potentially any of the other files currently inspected.

Hints from issue discussion:
@shawnohare this is a great idea. Do you know what the best practice precedence is between `pyproject.toml` and other files like `tox.ini` and `setup.cfg`? I'd love to make sure that SQLFluff is consistent with other tools in how it handles that.
This would also be a great first issue for anyone new to the project if people want to have a go! (It might even be a one line change 😄 )
The way to do this for anyone wanting to pick up this issue would be to add the filename in [load_config_at_path](https://github.com/sqlfluff/sqlfluff/blob/f4931633a6e398e198b14008e7092bf63760efee/src/sqlfluff/core/config.py#L239) and probably also extend [_get_config_elems_from_file](https://github.com/sqlfluff/sqlfluff/blob/f4931633a6e398e198b14008e7092bf63760efee/src/sqlfluff/core/config.py#L144) to use a proper toml parser if the extension of the file is `.toml` instead of the current `configparser` which is used for the other files.
> @shawnohare this is a great idea. Do you know what the best practice precedence is between `pyproject.toml` and other files like `tox.ini` and `setup.cfg`? I'd love to make sure that SQLFluff is consistent with other tools in how it handles that.

I honestly do not know if there’s necessarily a best practice around this. Here’s the order that `isort` uses: https://pycqa.github.io/isort/docs/configuration/config_files/ . I find this order to be pretty reasonable.

In my personal experience, many of the tools I’ve used which support `pyproject.toml` use this file exclusively unless they were created before this file became standard, in which case you get something like `isort`s preferences. Personally I’d prefer all tools moves toward deprecating everything but `pyrpoject.toml`, just for consistency, despite the fact that this file originally was for build related metadata.

Hello all,

If nobody is working on this and there's interest in supporting `pyproject.toml` as a config source, I can work on a PR.
Sure, go for it!

Commit message: Test pyproject.toml precedence

Patch to review:
diff --git a/docs/source/configuration.rst b/docs/source/configuration.rst
index 87a42f1c155..ef150033b8e 100644
--- a/docs/source/configuration.rst
+++ b/docs/source/configuration.rst
@@ -19,13 +19,44 @@ any vales read from earlier files.
 - :code:`tox.ini`
 - :code:`pep8.ini`
 - :code:`.sqlfluff`
+- :code:`pyproject.toml`
 
-Within these files, they will be read like an `cfg file`_, and *SQLFluff*
-will look for sections which start with *SQLFluff*, and where subsections
-are delimited by a semicolon. For example the *jinjacontext* section will
-be indicated in the section started with *[sqlfluff:jinjacontext]*.
+Within these files, the first four will be read like an `cfg file`_, and
+*SQLFluff* will look for sections which start with *SQLFluff*, and where
+subsections are delimited by a semicolon. For example the *jinjacontext*
+section will be indicated in the section started with
+*[sqlfluff:jinjacontext]*.
+
+For the `pyproject.toml file`_, all valid sections start with `tool.sqlfluff`
+and subsections are delimited by a dot and suffixed with *__options*.
+For example the *jinjacontext* section will be indicated in the section started
+with *[tools.sqlfluff.jinjacontext]*.
+
+For example
+
+.. code-block:: toml
+
+    [tools.sqlfluff]
+    templater = "jinja"
+    sql_file_exts = [
+        ".sql",
+        ".sql.j2",
+        ".dml",
+        ".ddl",
+    ]
+
+    [sqlfluff.indentation__options]
+    indented_joins = false
+    template_blocks_indent = false
+
+    [sqlfluff.templater__options]
+    unwrap_wrapped_queries = true
+
+    [sqlfluff.templater__options.jinja__options]
+    apply_dbt_builtins = true
 
 .. _`cfg file`: https://docs.python.org/3/library/configparser.html
+.. _`pyproject.toml file`: https://www.python.org/dev/peps/pep-0518/
 
 Nesting
 -------
diff --git a/requirements.txt b/requirements.txt
index ac5de19a1d4..7c3cc318201 100644
--- a/requirements.txt
+++ b/requirements.txt
@@ -14,3 +14,4 @@ pytest
 appdirs
 # dataclasses backport for python 3.6
 dataclasses; python_version < '3.7'
+toml
diff --git a/setup.py b/setup.py
index 3a7840923d3..f5625d6b4fe 100644
--- a/setup.py
+++ b/setup.py
@@ -98,6 +98,8 @@ def read(*names, **kwargs):
         "typing_extensions",
         # We provide a testing library for plugins in sqlfluff.testing
         "pytest",
+        # For parsing pyproject.toml
+        "toml",
     ],
     extras_require={
         "dbt": ["dbt>=0.17"],
diff --git a/src/sqlfluff/core/config.py b/src/sqlfluff/core/config.py
index e3437952e35..77e1e07e200 100644
--- a/src/sqlfluff/core/config.py
+++ b/src/sqlfluff/core/config.py
@@ -144,6 +144,34 @@ def get_global(cls) -> "ConfigLoader":
             global_loader = cls()
         return global_loader
 
+    @staticmethod
+    def _walk_toml(config: Dict[str, Any], base_key=()):
+        """Recursively walk the nested config inside a TOML file."""
+        buff: List[tuple] = []
+        for k, v in config.items():
+            if isinstance(v, dict) and k.endswith("__options"):
+                key = base_key + (k[:-9],)
+                buff.extend(ConfigLoader._walk_toml(v, key))
+            else:
+                key = (base_key or ("core",)) + (k,)
+                buff.append((key, v))
+
+        return buff
+
+    @staticmethod
+    def _get_config_elems_from_toml(fpath: str) -> List[Tuple[tuple, Any]]:
+        """Load a config from a TOML file and return a list of tuples.
+
+        The return value is a list of tuples, were each tuple has two elements,
+        the first is a tuple of paths, the second is the value at that path.
+        """
+        import toml
+
+        config = toml.load(fpath)
+        tool = config.get("tool", {}).get("sqlfluff", {})
+
+        return ConfigLoader._walk_toml(tool)
+
     @staticmethod
     def _get_config_elems_from_file(fpath: str) -> List[Tuple[tuple, Any]]:
         """Load a config from a file and return a list of tuples.
@@ -229,7 +257,10 @@ def _incorporate_vals(ctx: dict, vals: List[Tuple[Tuple[str, ...], Any]]) -> dic
 
     def load_default_config_file(self, file_dir: str, file_name: str) -> dict:
         """Load the default config file."""
-        elems = self._get_config_elems_from_file(os.path.join(file_dir, file_name))
+        if file_name == "pyproject.toml":
+            elems = self._get_config_elems_from_toml(os.path.join(file_dir, file_name))
+        else:
+            elems = self._get_config_elems_from_file(os.path.join(file_dir, file_name))
         return self._incorporate_vals({}, elems)
 
     def load_config_at_path(self, path: str) -> dict:
@@ -240,7 +271,13 @@ def load_config_at_path(self, path: str) -> dict:
 
         # The potential filenames we would look for at this path.
         # NB: later in this list overwrites earlier
-        filename_options = ["setup.cfg", "tox.ini", "pep8.ini", ".sqlfluff"]
+        filename_options = [
+            "setup.cfg",
+            "tox.ini",
+            "pep8.ini",
+            ".sqlfluff",
+            "pyproject.toml",
+        ]
 
         configs: dict = {}
 
@@ -253,7 +290,10 @@ def load_config_at_path(self, path: str) -> dict:
         # iterate this way round to make sure things overwrite is the right direction
         for fname in filename_options:
             if fname in d:
-                elems = self._get_config_elems_from_file(os.path.join(p, fname))
+                if fname == "pyproject.toml":
+                    elems = self._get_config_elems_from_toml(os.path.join(p, fname))
+                else:
+                    elems = self._get_config_elems_from_file(os.path.join(p, fname))
                 configs = self._incorporate_vals(configs, elems)
 
         # Store in the cache
diff --git a/test/core/config_test.py b/test/core/config_test.py
index 634bfed264c..d2cfdacd9b6 100644
--- a/test/core/config_test.py
+++ b/test/core/config_test.py
@@ -63,7 +63,28 @@ def test__config__load_nested():
         )
     )
     assert cfg == {
-        "core": {"testing_val": "foobar", "testing_int": 6, "testing_bar": 7.698},
+        "core": {"testing_val": "foobar", "testing_int": 1, "testing_bar": 7.698},
+        "bar": {"foo": "foobar"},
+        "fnarr": {"fnarr": {"foo": "foobar"}},
+    }
+
+
+def test__config__load_toml():
+    """Test loading config from a pyproject.toml file."""
+    c = ConfigLoader()
+    cfg = c.load_default_config_file(
+        os.path.join("test", "fixtures", "config", "toml"),
+        "pyproject.toml",
+    )
+    assert cfg == {
+        "core": {
+            "testing_int": 5,
+            "testing_bar": 7.698,
+            "testing_bool": False,
+            "testing_arr": ["a", "b", "c"],
+            "testing_inline_table": {"x": 1},
+            "bar": "foo",
+        },
         "bar": {"foo": "foobar"},
         "fnarr": {"fnarr": {"foo": "foobar"}},
     }
diff --git a/test/fixtures/config/inheritance_a/nested/pyproject.toml b/test/fixtures/config/inheritance_a/nested/pyproject.toml
new file mode 100644
index 00000000000..afa0799f5cc
--- /dev/null
+++ b/test/fixtures/config/inheritance_a/nested/pyproject.toml
@@ -0,0 +1,2 @@
+[tool.sqlfluff]
+testing_int = 1
diff --git a/test/fixtures/config/toml/pyproject.toml b/test/fixtures/config/toml/pyproject.toml
new file mode 100644
index 00000000000..60167718b1c
--- /dev/null
+++ b/test/fixtures/config/toml/pyproject.toml
@@ -0,0 +1,13 @@
+[tool.sqlfluff]
+testing_int = 5
+testing_bar = 7.698
+testing_bool = false
+testing_arr = [ "a", "b", "c" ]
+testing_inline_table = { x = 1 }
+bar = "foo"
+
+[tool.sqlfluff.bar__options]
+foo = "foobar"
+
+[tool.sqlfluff.fnarr__options.fnarr__options]
+foo = "foobar"


Code review comments:
diff_hunk: @@ -144,6 +144,34 @@ def get_global(cls) -> "ConfigLoader":
             global_loader = cls()
         return global_loader
 
+    @staticmethod
+    def _walk_toml(config: Dict[str, Any], base_key=()):
+        """Recursively walk the nested config inside a TOML file."""
+        buff: List[tuple] = []
+        for k, v in config.items():
+            if isinstance(v, dict) and k.endswith("__options"):
+                key = base_key + (k[:-9],)
+                buff.extend(ConfigLoader._walk_toml(v, key))
+            else:
+                key = (base_key or ("core",)) + (k,)
+                buff.append((key, v))
+
+        return buff
+
+    @staticmethod
+    def _get_config_elems_from_toml(fpath: str) -> List[Tuple[tuple, Any]]:
+        """Load a config from a TOML file and return a list of tuples.
+
+        The return value is a list of tuples, were each tuple has two elements,
+        the first is a tuple of paths, the second is the value at that path.
+        """
+        import toml
path: src/sqlfluff/core/config.py
text: @user1:
Could this import be moved to the top of the file? That's generally the preferred location for imports.

@author:
Sure. I'll do that.