Skip to content

SQLite: functions

SQLite has no DATE_TRUNC() and no statistics aggregates. hare registers functions of its own on every SQLite connection it opens for its functions SQLite has no SQL of, so a function of hare.query.functions works. Where SQLite has a function or an operator, hare writes it and its result is SQLite's own — UPPER() folds ASCII letters only, CAST truncates a fraction — see What runs differently. A query reaches the registered functions through the lookups and functions; no code calls them by name.

from hare.query.functions import Lower, Trunc

# hare_trunc_date_part() and LOWER() on SQLite, DATE_TRUNC() and LOWER() on PostgreSQL.
await Book.objects.annotate(month=Trunc("published", "month"), key=Lower("title")).values("month", "key")

What hare registers

Every name starts with hare_, except regexp and match, which SQLite's REGEXP and MATCH operators call.

Area Functions What they give
Dates and times hare_trunc_date_part, hare_extract_date_part, hare_date_timestamp Trunc(), Extract() and the date part lookups; a date compared with a datetime by its first moment.
Dates and times hare_datetime_shift, hare_date_shift, hare_datetime_difference, hare_date_difference Date and datetime arithmetic with a timedelta, exact to the microsecond — see Date/time arithmetic.
Dates and times hare_local_now A Now() default of a DateField or TimeField in a zone with daylight saving rules.
Numbers hare_math_<name>: abs, ceil, floor, sign, mod, power, log, sqrt, exp, ln, sin, cos, tan, cot, asin, acos, atan, atan2, degrees, radians, pi The math functions — see Math functions.
Numbers hare_greatest, hare_least Greatest()/Least() comparing numbers as numbers; like SQLite's max()/min(), they give NULL for any NULL argument.
Dates and times hare_cast Cast() to a date, a datetime or a time of day, read from its stored text in UTC — see Casts.
Numbers hare_stddev_pop, hare_stddev_samp, hare_var_pop, hare_var_samp StdDev() and Variance() as aggregates and window functions.
Text hare_text_<name>: left, right, lpad, rpad, repeat, reverse, md5, sha1, sha224, sha256, sha384, sha512 The text and hash functions SQLite lacks — see Text functions.
JSON hare_json_path, hare_json_datetime A path with a digit segment and a date compared with a JSON value — see JSON.
Full-text search hare_full_text_query The search text as an FTS5 query whose words are never read as FTS5 syntax — see Full-text search.
Regular expressions regexp, match __posix_regex and __iposix_regex, only on a connection with install_regexp_functions=true — see Regular expressions.
Vectors sqlite-vec, hare_vector_negative_inner_product The VectorField distances, only on a connection with load_sqlite_vec=true — see Vector search.

Math functions

SQLite's own math functions are an optional build feature, so hare registers them, and each gives what SQLite's own gives: an integer argument gives an integer (Abs, Sign, Mod of integers), Mod takes the sign of the dividend, an argument outside a function's domain gives NULL (Ln(0), Sqrt(-1)) and a result past a float is an infinity. A division by zero and a Mod by zero give NULL as well; hare checks neither. Round() is SQLite's own rounding: half away from zero for a float.

Casts

Cast() to a number is SQLite's own CAST — to an INTEGER or a REAL:

  • a fraction is cut off when a number becomes an integer, where PostgreSQL rounds;
  • text that isn't a number is 0, a number padded with spaces its number (' 42 ' is 42);
  • an integer is kept whatever the target field's range.

SQLite has no boolean type: a cast to a boolean reads the value as a number (CAST(... AS NUMERIC)), true when it isn't 0 — 'true' is false. SQLite has no date types either: a date or a time is read from its stored text in UTC by hare's hare_cast. A JSON null of a JSONField path (Cast(F("data__n"), IntField())) is NULL.

A cast to text is SQLite's own: a float is 2.0, with 15 significant digits (17 from SQLite 3.53), and a boolean is 1 or 0. A CharField's max_length cuts nothing. Any other cast, such as a boolean to a float, is SQLite's own conversion.

Case mapping

Upper(), Lower() and the case-insensitive lookups (iexact, icontains, istartswith, iendswith) are SQLite's own UPPER(), LOWER() and LIKE, which fold the ASCII letters only: é and É, п and П are different letters to them, and Upper("привет") is привет.

Text functions

Left, Right, LPad, RPad, Repeat, Reverse and the digests are hare's functions: a negative Left/Right length drops characters from the other end, and LPad/RPad cut a longer text to the length. A length, a position and a count have no 32-bit limit, and a result longer than SQLite holds in one value (1e9 bytes) is refused before it is built (string or blob too big).

Substr, Chr and Ord are SQLite's own substr(), char() and unicode(): a negative start counts from the end, a negative length takes the characters before the start, and unicode('') is NULL. A text function given an expression of the wrong type is left to SQLite; a literal of the wrong type is refused.

JSON

A JSONField is JSON_TEXT, and SQLite's json_extract(), json_type() and json_object() read it. SQLite's JSON has no containment: contains and contained_by of a JSONField raise UnSupportedError before any SQL is sent. hare adds the meaning of its own paths:

  • a path with a digit segment is an object's key or an array's index, whichever the value at that point is (hare_json_path);
  • a date compared with a JSON value is read as timestamp or timestamptz, fractional seconds cut at microseconds (hare_json_datetime).

The rest is SQLite's own:

  • equality of a path compares the JSON form: true and 1 differ, a string and a number differ; equality of a whole JSONField compares the stored text, so key order and 1 against 1.0 matter, and a container in __filter is compared as minified JSON text;
  • ordering and gt, gte, lt, lte, range of a path, or of a whole JSONField, compare the value json_extract() reads: a string as itself, true and false as 1 and 0, a JSON null as NULL, an object or an array as its minified text; SQLite orders numbers before text;
  • has_key, has_keys and has_any_keys find a key of an object only (json_type());
  • a value read as text is SQLite's stored text, without spaces after , and :, and so is a Cast() to text and a Concat() of it; a text lookup of a path matches the text json_extract() gives;
  • JSONObject() and JSONArray() are written by json_object(): a boolean as 1 or 0, a float with 15 significant digits (17 from SQLite 3.53), a moment or a time as its stored text, bytes as uppercase hex, a JSON value nested;
  • a null byte inside a JSON value is stored as it is.

See JSONField for the lookups themselves.

Regular expressions

With install_regexp_functions=true in the connection URL, hare registers regexp() and match(), which SQLite's REGEXP and MATCH operators call, and Features.supports_posix_regex is on. A pattern is a Python re pattern, with its syntax and semantics: __iposix_regex is re.IGNORECASE, and PostgreSQL's [[:digit:]], \y or (?n) aren't translated. An invalid pattern raises with Python's message. Python's re doesn't guard against catastrophic backtracking: a pattern as short as (a+)+$ can hold the connection, so never pass a pattern from user input unchecked.

Errors

SQLite reports any error a registered function raises as user-defined function raised exception. hare keeps the function's own message and raises OperationalError with it.

Compiled versions

The rust.native extension (see Wheels) holds compiled versions of the functions SQLite calls once per row. Where it is installed, hare registers them in place of the Python ones; the results are the same.

In constraints and indexes

The database checks a constraint and keeps an index up to date without hare, and any other program writing the file has none of these functions. A constraint condition, an index key or a GeneratedField expression whose SQL needs one (Greatest() is hare_greatest) raises UnSupportedError, naming the function, when the model is bound (Hare.init()) — before makemigrations writes a migration no migrate could apply:

from hare.ddl import Index
from hare.query.functions import Trunc


class Book(Model):
    published = fields.DatetimeField()

    class Meta:
        # UnSupportedError on SQLite: Trunc() needs hare_trunc_date_part.
        indexes = (Index(Trunc("published", "month"), name="book_published_month"),)

The same goes for Extract(), Cast(), the JSONField path lookups and the regular expression lookups. Date and time arithmetic there raises ConfigurationError: SQLite has no SQL for it.