Tabular API

An API service that provides RESTful access to CSV or tabular data converted by Hydra. This service provides a REST API to access PostgreSQL database tables containing CSV data, offering HTTP querying capabilities, pagination, and data streaming for CSV or tabular resources.

This service is mainly used, developed and maintained by data.gouv.fr - the France Open Data platform. The production API is deployed on data.gouv.fr infrastructure at https://tabular-api.data.gouv.fr/api. See the product documentation (in French) for usage details and the technical documentation for API reference.

🛠️ Installation & Setup

📋 Requirements

• Python >= 3.11, < 3.15
• uv for dependency management
• Docker & Docker Compose

Docker Compose profiles

Run everything locally in Docker: docker compose --profile test up -d → Tabular API at http://localhost:8005, Metrics API at http://localhost:8006, PostgREST at http://localhost:8080.

🧪 Run with a test database

1. Start the stack

   With the test profile, you can either run the full stack in Docker (recommended for a quick local run) or only the infrastructure and run the API with uv (for development with hot reload).

   Option A – All in Docker (easiest for running locally):

   docker compose --profile test up -d
   

   This starts the test PostgreSQL, PostgREST, Tabular API (port 8005), and Metrics API (port 8006). You can use the APIs at http://localhost:8005 and http://localhost:8006.

   Option B – Only infrastructure in Docker, API with uv (for development):

   docker compose --profile test up -d postgres-test postgrest-test
   

   PostgREST is available at http://localhost:8080. Then start the API proxy on the host:

   uv sync
   uv run adev runserver -p8005 api_tabular/tabular/app.py    # Tabular API (dev server)
   uv run adev runserver -p8006 api_tabular/metrics/app.py   # Metrics API (dev server)
   

   Note: For production (on the host), use gunicorn with aiohttp worker:

   # Tabular API (port 8005)
   uv run gunicorn api_tabular.tabular.app:app_factory \
     --bind 0.0.0.0:8005 \
     --worker-class aiohttp.GunicornWebWorker \
     --workers 4 \
     --access-logfile -
   
   # Metrics API (port 8006)
   uv run gunicorn api_tabular.metrics.app:app_factory \
     --bind 0.0.0.0:8006 \
     --worker-class aiohttp.GunicornWebWorker \
     --workers 4 \
     --access-logfile -
   

   The API provides a controlled layer over PostgREST (security and access control); exposing PostgREST directly would be too permissive.

2. Test the API

   Query the API using a resource_id. Several test resources are available in the fake database:

   • aaaaaaaa-1111-bbbb-2222-cccccccccccc - Main test resource with 1000 rows
   • aaaaaaaa-5555-bbbb-6666-cccccccccccc - Resource with database indexes
   • dddddddd-7777-eeee-8888-ffffffffffff - Resource allowed for aggregation
   • aaaaaaaa-9999-bbbb-1010-cccccccccccc - Resource with indexes and aggregation allowed

🏭 Run with a real Hydra database

To use the API with a real database served by Hydra instead of the fake test database:

1. Start the real Hydra CSV database locally:

   First, you need to have Hydra CSV database running locally. See the Hydra repository for instructions on how to set it up. Make sure the Hydra CSV database is accessible on localhost:5434.

2. Start PostgREST pointing to your local Hydra database:

   docker compose --profile hydra up -d
   

   The --profile hydra flag tells Docker Compose to start the PostgREST service configured for a local real Hydra CSV database (instead of the test one provided by the docker compose in this repo). By default, this starts PostgREST on port 8080. You can customize the port using the PGREST_PORT environment variable:

   # Use default port 8080
   docker compose --profile hydra up -d
   
   # Use custom port (e.g., 8081)
   PGREST_PORT=8081 docker compose --profile hydra up -d
   

3. Configure the API to use it:

   # If using default port 8080
   export PGREST_ENDPOINT="http://localhost:8080"
   
   # If using custom port (e.g., 8081)
   export PGREST_ENDPOINT="http://localhost:8081"
   

4. Start the API services:

   uv sync
   uv run adev runserver -p8005 api_tabular/tabular/app.py     # Dev server
   uv run adev runserver -p8006 api_tabular/metrics/app.py     # Dev server
   

   Note: For production, use gunicorn with aiohttp worker:

   # Tabular API (port 8005)
   uv run gunicorn api_tabular.tabular.app:app_factory \
     --bind 0.0.0.0:8005 \
     --worker-class aiohttp.GunicornWebWorker \
     --workers 4 \
     --access-logfile -
   
   # Metrics API (port 8006)
   uv run gunicorn api_tabular.metrics.app:app_factory \
     --bind 0.0.0.0:8006 \
     --worker-class aiohttp.GunicornWebWorker \
     --workers 4 \
     --access-logfile -
   

5. Use real resource IDs from your Hydra database instead of the test IDs.

Note: Make sure your Hydra CSV database is accessible and the database schema matches the expected structure. The test database uses the csvapi schema, while real Hydra databases typically use the public schema.

📚 API Documentation

Resource Endpoints

Get Resource Metadata

GET /api/resources/{resource_id}/

Returns basic information about the resource including creation date, URL, and available endpoints.

Example:

curl http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/

Response:

{
  "created_at": "2023-04-21T22:54:22.043492+00:00",
  "url": "https://data.gouv.fr/datasets/example/resources/fake.csv",
  "links": [
    {
      "href": "/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/profile/",
      "type": "GET",
      "rel": "profile"
    },
    {
      "href": "/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/data/",
      "type": "GET",
      "rel": "data"
    },
    {
      "href": "/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/swagger/",
      "type": "GET",
      "rel": "swagger"
    }
  ]
}

Get Resource Profile

GET /api/resources/{resource_id}/profile/

Returns the CSV profile information (column types, headers, etc.) generated by csv-detective.

Example:

curl http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/profile/

Response:

{
  "profile": {
    "header": [
        "id",
        "score",
        "decompte",
        "is_true",
        "birth",
        "liste"
    ]
  },
  "...": "..."
}

Get Resource Data

GET /api/resources/{resource_id}/data/

Returns the actual data with support for filtering, sorting, and pagination.

Example:

curl http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/data/

Response:

{
  "data": [
    {
        "__id": 1,
        "id": " 8c7a6452-9295-4db2-b692-34104574fded",
        "score": 0.708,
        "decompte": 90,
        "is_true": false,
        "birth": "1949-07-16",
        "liste": "[0]"
    },
    ...
  ],
  "links": {
      "profile": "http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/profile/",
      "swagger": "http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/swagger/",
      "next": "http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/data/?page=2&page_size=20",
      "prev": null
  },
  "meta": {
      "page": 1,
      "page_size": 20,
      "total": 1000
  }
}

Get Resource Data as CSV

GET /api/resources/{resource_id}/data/csv/

Streams the data directly as a CSV file for download.

Get Resource Data as JSON

GET /api/resources/{resource_id}/data/json/

Streams the data directly as a JSON file for download.

Get Swagger Documentation

GET /api/resources/{resource_id}/swagger/

Returns OpenAPI/Swagger documentation specific to this resource.

Query Operators

The data endpoint can be queried with the following operators as query string (replacing column_name with the name of an actual column), if the column type allows it (see the swagger for each column's allowed parameters):

Filtering Operators

# exact
column_name__exact=value

# differs
column_name__differs=value

# is `null`
column_name__isnull

# is not `null`
column_name__isnotnull

# contains
column_name__contains=value

# does not contain (value does not contain)
column_name__notcontains=value

# in (value in list)
column_name__in=value1,value2,value3

# notin (value not in list)
column_name__notin=value1,value2,value3

# less
column_name__less=value

# greater
column_name__greater=value

# strictly less
column_name__strictly_less=value

# strictly greater
column_name__strictly_greater=value

Sorting

# sort by column
column_name__sort=asc
column_name__sort=desc

Aggregation Operators

⚠️ WARNING: Aggregation requests are disabled by default. You can allow or disallow it for all resources with the ALLOW_AGGREGATION config. If ALLOW_AGGREGATION is set to false, you can specify allow exceptions by listing these in the ALLOW_AGGREGATION_EXCEPTIONS list. You can get the current status and exceptions at /api/aggregation-exceptions/ endpoint.

# group by values
column_name__groupby

# count values
column_name__count

# mean / average
column_name__avg

# minimum
column_name__min

# maximum
column_name__max

# sum
column_name__sum

Note: Passing an aggregation operator (count, avg, min, max, sum) returns a column that is named <column_name>__<operator> (for instance: ?birth__groupby&score__sum will return a list of dicts with the keys birth and score__sum).

⚠️ WARNING: columns that contain JSON objects (see the profile to know which ones do) do not support filtering nor aggregation for now, except isnull and isnotnull.

Pagination

page=1          # Page number (default: 1)
page_size=20    # Items per page (default: 20, max: 50)

Column Selection

columns=col1,col2,col3    # Select specific columns only

Example Queries

Basic Filtering

curl http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/data/?score__greater=0.9&decompte__exact=13

Returns:

{
  "data": [
    {
      "__id": 52,
      "id": " 5174f26d-d62b-4adb-a43a-c3b6288fa2f6",
      "score": 0.985,
      "decompte": 13,
      "is_true": false,
      "birth": "1980-03-23",
      "liste": "[0]"
    },
    {
      "__id": 543,
      "id": " 8705df7c-8a6a-49e2-9514-cf2fb532525e",
      "score": 0.955,
      "decompte": 13,
      "is_true": true,
      "birth": "1965-02-06",
      "liste": "[0, 1, 2]"
    }
  ],
  "links": {
    "profile": "http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/profile/",
    "swagger": "http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/swagger/",
    "next": null,
    "prev": null
  },
  "meta": {
    "page": 1,
    "page_size": 20,
    "total": 2
  }
}

Top level parameters are considered joined with the AND operator. It is possible to add nested conditions with the following syntax:

or=(<column1>__<operator1>.<value1>,<column2>__<operator2>.<value2>,...)

Note: the conditions within an OR have a . instead of a = between the operator and the value.

Note: the syntax for isnull and isnotnull is <column1>__is(not)null (no value is needed, like outisde of OR groups)

This scales as much as needed, but the inner operators should be or(...) and and(...) (without the =), like:

decompte__less=10&or=(is_true__exact.true,birth__greater.1980,and(score__strictly_less.5,liste__isnotnull,or(...)))

Note: if the column name and/or the value of the filter contains special characters (including dots), the item should be wrapped within double quotes:

or=("is true"__exact.true,and(score__strictly_less."0.1","avg.views"__greater."15.5"))

Aggregation with Filtering

With filters and aggregators (filtering is always done before aggregation, no matter the order in the parameters):

curl http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/data/?decompte__groupby&birth__less=1996&score__avg

i.e. decompte and average of score for all rows where birth<="1996", grouped by decompte, returns:

{
    "data": [
        {
            "decompte": 55,
            "score__avg": 0.7123333333333334
        },
        {
            "decompte": 27,
            "score__avg": 0.6068888888888889
        },
        {
            "decompte": 23,
            "score__avg": 0.4603333333333334
        },
        ...
    ]
}

Pagination

curl http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/data/?page=2&page_size=30

Column Selection

curl http://localhost:8005/api/resources/aaaaaaaa-1111-bbbb-2222-cccccccccccc/data/?columns=id,score,birth

Metrics API

The metrics service provides similar functionality for system metrics:

# Get metrics data
curl http://localhost:8006/api/{model}/data/

# Get metrics as CSV
curl http://localhost:8006/api/{model}/data/csv/

Health Check

# Main API health
curl http://localhost:8005/health/

# Metrics API health
curl http://localhost:8006/health/

⚙️ Configuration

Configuration is handled through TOML files and environment variables. The default configuration is in api_tabular/config_default.toml.

Key Configuration Options

Environment Variables

You can override any configuration value using environment variables:

export PGREST_ENDPOINT="http://my-postgrest:8080"
export PAGE_SIZE_DEFAULT=50
export SENTRY_DSN="https://your-sentry-dsn"

Once the containers are up and running, you can directly query PostgREST on: <PGREST_ENDPOINT>/<table_name>?<filters> like for example: http://localhost:8080/eb7a008177131590c2f1a2ca0?decompte=eq.10

Custom Configuration File

Create a config.toml file in the project root or set the CSVAPI_SETTINGS environment variable:

export CSVAPI_SETTINGS="/path/to/your/config.toml"

🧪 Testing

This project uses pytest for testing with async support and mocking capabilities. You must have the two test containers running for the tests to run (see ### 🧪 Run with a test database for setup instructions).

Running Tests

# Run all tests
uv run pytest

# Run specific test file
uv run pytest tests/test_api.py

# Run tests with verbose output
uv run pytest -v

# Run tests and show print statements
uv run pytest -s

Tests Structure

• tests/test_api.py - API endpoint tests (actually pings the running API)
• tests/test_config.py - Configuration loading tests
• tests/test_query.py - Query building and processing tests
• tests/test_swagger.py - Swagger documentation tests (actually pings the running API)
• tests/test_utils.py - Utility function tests
• tests/conftest.py - Test fixtures and configuration

CI/CD Testing

Tests are automatically run in CI/CD. See .circleci/config.yml for the complete CI/CD configuration.

🤝 Contributing

🧹 Code Linting, Formatting and Type Checking

This project follows PEP 8 style guidelines using Ruff for linting and formatting, and ty for type checking. Either running these commands manually or installing the pre-commit hook is required before submitting contributions.

# Lint (including import sorting) and format code
uv run ruff check --fix && uv run ruff format

# Type check (ty)
uv run ty check

By default ty check checks the project root; pass paths to check specific files or directories. See the ty CLI reference for options.

🔗 Pre-commit Hooks

This repository uses a pre-commit hook which lint and format code before each commit. Installing the pre-commit hook is required for contributions.

Install pre-commit hooks:

uv run pre-commit install

The pre-commit hook that automatically:

• Check YAML syntax
• Fix end-of-file issues
• Remove trailing whitespace
• Check for large files
• Run Ruff linting and formatting

🧪 Running Tests

Pull requests cannot be merged unless all CI/CD tests pass. Tests are automatically run on every pull request and push to main branch. See .circleci/config.yml for the complete CI/CD configuration, and the 🧪 Testing section above for detailed testing commands.

🏷️ Releases and versioning

The release process uses the tag_version.sh script to create git tags, GitHub releases and update CHANGELOG.md automatically. Package version numbers are automatically derived from git tags using setuptools_scm, so no manual version updates are needed in pyproject.toml.

Prerequisites: GitHub CLI must be installed and authenticated, and you must be on the main branch with a clean working directory.

# Create a new release
./tag_version.sh <version>

# Example
./tag_version.sh 2.5.0

# Dry run to see what would happen
./tag_version.sh 2.5.0 --dry-run

The script automatically:

• Extracts commits since the last tag and formats them for CHANGELOG.md
• Identifies breaking changes (commits with !: in the subject)
• Creates a git tag and pushes it to the remote repository
• Creates a GitHub release with the changelog content

Publishing to PyPI

uv publish uploads the built package. It authenticates with the UV_PUBLISH_TOKEN environment variable and sends that value as a PyPI API token, with the username __token__. See the uv environment reference.

Set that variable in the environment that runs the publish step:

1. Create an API token on PyPI. Limit its scope to this project when you can. The value starts with pypi-.
2. Store it as a secret named UV_PUBLISH_TOKEN and expose it to the publish step.

📄 License

This project is licensed under the MIT License - see the LICENSE file for details.

🆘 Support

• Issues: GitHub Issues
• Discussion: Use the discussion section at the end of the production API page
• Contact Form: Support form

🌐 Production Resources

• Production API: https://tabular-api.data.gouv.fr/api
• Product Documentation: API tabulaire data.gouv.fr (beta) (in French)
• Technical Documentation: Swagger/OpenAPI docs