Metadata-Version: 2.4
Name: django-sqlfun
Version: 0.2.1
Summary: Django app that lets you define custom SQL functions
Keywords: django,database,sql-functions,custom-sql
Author: Radu Suciu
License-Expression: MIT
License-File: LICENSE
Classifier: Framework :: Django
Classifier: Framework :: Django :: 5.2
Classifier: Framework :: Django :: 6.0
Classifier: Framework :: Django :: 6.1
Classifier: Intended Audience :: Developers
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Programming Language :: Python :: 3.14
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Libraries
Classifier: Topic :: Software Development :: Libraries :: Python Modules
Requires-Dist: django>=5.2,<7.0 ; python_full_version < '3.14'
Requires-Dist: django>=5.2.8,<7.0 ; python_full_version >= '3.14'
Requires-Dist: sqlparse>=0.4.3
Requires-Python: >=3.11
Description-Content-Type: text/markdown

[![PyPI pyversions](https://img.shields.io/pypi/pyversions/django-sqlfun.svg)](https://pypi.python.org/pypi/django-sqlfun/)
[![Django versions](https://img.shields.io/pypi/frameworkversions/django/django-sqlfun)](https://pypi.python.org/pypi/django-sqlfun/)
[![PyPI version](https://img.shields.io/pypi/v/django-sqlfun.svg)](https://pypi.python.org/pypi/django-sqlfun/)
[![GitHub release](https://img.shields.io/github/release/radusuciu/django-sqlfun.svg)](https://github.com/radusuciu/django-sqlfun/releases/)

# Django SQL Fun

Django SQLFun allows you to define and manage custom SQL functions in code. When you change the function definitions and call `makemigrations`, it will generate migrations for any functions that have been added, removed, or changed. These function classes can also be used in Django querysets since the `SqlFun` class inherits from [`django.db.models.expressions.Func`](https://docs.djangoproject.com/en/5.0/ref/models/expressions/#func-expressions).

**Note**: I'm still developing this so there may be some rough edges. Breaking changes may happen.

## Installation

1. Install using your favorite python package manager, eg. `pip install django-sqlfun`.
2. Add `sqlfun` to `INSTALLED_APPS` in your django settings
3. Run `manage.py migrate` (on a fresh install this is a no-op for sqlfun; on upgrades from ≤0.1.x it removes sqlfun's old bookkeeping table)

## Use

1. Define a custom function in a module that gets imported on project load (eg. `models.py`). See below for example, or the [`test_project`](tests/test_project).
2. Run `manage.py makemigrations`
3. Run `manage.py migrate`

### Example

Define a custom function in your `models.py`:

```python
# models.py
from sqlfun import SqlFun
from django.db.models import IntegerField

class BadSum(SqlFun):
    """Almost returns the sum of two numbers."""
    
    app_label = 'test_project' # [optional] if omitted, sqlfun will atempt to auto-resolve it
    sql = """
        CREATE OR REPLACE FUNCTION bad_sum(
            first integer,
            second integer
        ) RETURNS integer as $$
        SELECT first + second + 1;
        $$
        LANGUAGE sql
        stable;
    """
    output_field = IntegerField()
```

Then run `manage.py makemigrations` and `manage.py migrate` and you should be good to go. You can use it in SQL: `SELECT bad_sum(2, 2)`, or in a Python queryset like so: `MyModel.objects.annotate(foo=BadSum(Value(2), Value(2)))`.

### Notes

- Function definitions must use `CREATE OR REPLACE FUNCTION` — `makemigrations` rejects plain `CREATE FUNCTION`, since sqlfun re-executes definitions against databases where the function may already exist
- `makemigrations` creates each changed function inside a rolled-back transaction to read its signature, so the argument and return types must already exist in the database. With `AS $$ ... $$` bodies, tables and views the body references do not need to exist yet. SQL-standard bodies (`BEGIN ATOMIC ... END` or a bare `RETURN`) are always checked when the function is created, so if a function must be generated before the migration that creates its tables has been applied, write its body as `AS $$ ... $$`
- SQL functions are normalized before comparison, so whitespace-only changes do not generate migrations
- Change detection works by replaying sqlfun's operations from your existing migration files — there is no state outside your repo, so fresh clones and CI see exactly what you see
- If you squash or delete migrations that contain sqlfun operations, that state is lost: the next `makemigrations` re-emits a baseline migration re-declaring the affected functions (harmless to apply, but noisy)
- the `--dry-run`, `--name`, and `--check` options of `makemigrations` are respected. `--check` exits with a non-zero status if any sqlfun function changes are missing migrations (in addition to Django's own model-change check), writes nothing, and requires a reachable database — it fails rather than silently passing if sqlfun changes cannot be evaluated. Use `makemigrations --database <alias>` to run introspection against a specific database alias.

### Upgrading

**From ≤0.1.x to 0.2.0** (breaking): sqlfun no longer keeps a bookkeeping
table — a function's history now lives in your migration files as
`sqlfun.operations.CreateFunction` / `DropFunction` operations. To upgrade
an existing project:

1. Upgrade the package.
2. Run `manage.py migrate` — this drops sqlfun's old tracking table.
3. Run `manage.py makemigrations` once, **before editing or deleting any
   function definitions**. Your old migrations contain only `RunSQL`
   operations, which the new change detection does not read, so this run
   emits one baseline migration per app re-declaring every registered
   function.
4. Run `manage.py migrate` — the baseline applies as a no-op
   `CREATE OR REPLACE` against your existing functions.

The baseline records the live definition it replaces, so reversing it
restores the function that existed before the baseline. This also avoids
dropping the function when dependent views or other objects exist.

If you deleted a function class before step 3, sqlfun has no record of it:
drop that function manually. If you changed a function's arguments or
return type before step 3, the baseline also drops the old function first.
sqlfun reads that old definition from the database `makemigrations` runs
against, so reversing the baseline restores it.

## Development

These instructions assume a recent Ubuntu/Debian environment.

1. Clone the repository
2. Install [uv](https://docs.astral.sh/uv/getting-started/installation/)
3. Install the `libpq-dev` package since `psycopg2` depends on it.
4. Install dependencies with `uv sync` (this creates `.venv` and installs the dev group)

Testing also requires a recent install of docker which is used to spin up a test postgres instance.

## Credits

This project is inspired by two great projects: [`django-pgtrigger`](https://github.com/Opus10/django-pgtrigger) and [`django-pgviews`](https://github.com/mypebble/django-pgviews).
