Skip to main content

Program your Database

ScramDB is a UTAP (Unified Transactional Analytical Processing) database: transactions, analytics, and AI/search all run on one live copy of your data. Programmability extends that same engine with functions you write yourself, callable straight from SQL and running right next to your data, with no export and no second system to keep in sync.

A ScramDB function runs inside afterburner, a deterministic, polyglot WebAssembly runtime built into the engine. Functions are:

  • Sealed by default. A function cannot reach the network, the filesystem, or environment variables. It only ever sees the arguments you pass it, and returns a result. Nothing else. A function called from SQL runs fully sealed regardless of what its package's own capability grant declares, so this is not a promise you have to trust the package author to keep. Daemon packages, which are servers and not functions, are the one exception: they get exactly the capabilities their grant declares.
  • Deterministic. The same inputs always produce the same outputs. No clocks, no randomness, no hidden state.
  • Governed. JavaScript functions run inside a sandbox with fuel, memory, timeout, and output-size limits, so a heavy or misbehaving function can never destabilize the database.
  • Callable from SQL. Once registered, a function is just a function: SELECT my_function(col) FROM my_table.

Because ScramDB is UTAP, these functions see your transactional writes the instant they land. There is no pipeline to build and no copy to keep current.

Two ways to add a function​

There are two ways to get your own code running inside ScramDB, and which one you reach for depends on how much code you have:

  1. Inline source. Paste the function body straight into CREATE FUNCTION ... AS $$...$$. No build step, no artifact, no registry. This works for LANGUAGE js, LANGUAGE ts, LANGUAGE python, and LANGUAGE ruby, and CREATE PROCEDURE takes the same four languages (see Procedures).
  2. Packages. Author, build, and publish a self-contained artifact (a .afb file) with the burn toolchain, install it by name with scram.install, which registers the SQL function or functions the package declares, and call it. To bind an installed package under another name, use CREATE OR REPLACE FUNCTION ... AS 'namespace/pkg', in the language the package is written in (js for JavaScript, TypeScript and compiled packages, python or ruby for a source package in those languages). This is the path for multi-file projects, for anything you want to share or version, and it is the only way to run code written in a compiled language such as Rust, Go, C, or C++.

ScramDB also ships 26 functions in 16 package families as preinstalled packages you can call as they are, without writing, building or binding anything. See What you can build below.

Write in any language​

CREATE FUNCTION ... LANGUAGE <word> recognizes four language keywords:

LANGUAGE keywordAvailability todayInline source (AS $$...$$)Installed package (AS 'namespace/pkg')
jsWorksYesYes
tsWorksYesYes
pythonWorksYesYes
rubyWorksYesYes

ts strips the types of the body with afterburner's TypeScript transpiler and runs the JavaScript that is left, the way js runs a body: the two share the same runtime and calling convention.

The quote style of the AS clause means nothing: AS 'return 1' and AS $$return 1$$ are the same inline source, and AS 'namespace/pkg' and AS $$namespace/pkg$$ are the same package reference. A text of the form namespace/pkg (two names around one slash) is read as the name of an installed package; every other text is the function's own source.

Rust, Go, C, and C++ are package-authoring languages, not LANGUAGE keywords. You cannot write LANGUAGE rust AS $$...$$. Instead, you author a package in one of these languages, build it to a .afb artifact with burn, install the artifact with scram.install, and bind it with CREATE FUNCTION ... LANGUAGE js AS 'namespace/pkg' (the LANGUAGE js here names the binding form, not the language the package's source was written in). See Writing a package for the calling convention a compiled package must follow, and Install and use a package for the install-then-bind flow.

Separately from all of this, ScramDB also has a pre-existing, ordinary SQL routine system: LANGUAGE sql and LANGUAGE plpgsql define plain functions directly in SQL. That system is not part of afterburner, has no sandboxing or packages, and is documented in the SQL reference rather than here. Keep the two mechanisms distinct in your head: sql/plpgsql are ordinary SQL routines; js/ts/python/ruby (and the packages bound through them) are afterburner-executed functions with the sealing and governance described above.

What you can build​

ScramDB ships 16 package families, covering search, AI, geospatial, clustering, statistics, and privacy. They are installed at startup (or by one scram.install(...) call), and each function is then callable under its own name (see Install and use a package). They also serve as worked examples for packages of your own:

FamilyWhat it does
embeddingsVector similarity for AI and semantic search (cosine similarity, dot product)
bm25Okapi BM25 relevance scoring for ranking search results
ftsFull-text search helpers such as term frequency
geoGeospatial math: great-circle (haversine) distance and geohash encoding
h3Hexagonal grid spatial indexing
kmeansK-means cluster assignment
dbscanDensity-based clustering (neighbor counts)
regressionOrdinary least squares linear regression
anomalyAnomaly detection (z-score and median-absolute-deviation score)
sketchHyperLogLog approximate distinct counts
thetaTheta sketches for set cardinality and overlap
topkTop-k most frequent items
quantileStreaming quantile and percentile estimates
anonymizePseudonymization: salted SHA-256 hashing and masking
jsonschemaJSON Schema validation
rustrust_sha256, a salted SHA-256 compiled ahead of time from Rust

ScramDB has native VECTOR, HALFVEC, and SPARSEVEC column types with their own indexes and distance operators (see Data Types and Vector Search), and native ARRAY columns, for the common case. The embeddings package's similarity functions (embeddings_cosine_similarity, embeddings_dot) take a vector as a JSON array of numbers in a TEXT argument instead, so they also work over data kept as a plain TEXT or JSONB column rather than a native vector column; a package you write reads and returns the same plain SQL argument and column types (see Writing a package).

rust_sha256 is a package compiled ahead of time from Rust rather than interpreted from JavaScript source. It is the reference example for the compiled-language calling convention described in Writing a package. The h3 functions are a hexagonal grid index of their own, with cell ids that are not those of the H3 index format.

How it fits together​

For a package (the path for multi-file projects and compiled languages):

  1. Write a package: a manifest that declares its functions, a capability grant, and your source. See Writing a package.
  2. Build it into a single, content-addressed .afb artifact with burn.
  3. Install the artifact with SELECT scram.install(...).
  4. Call the function the install registered, like any other function. See Install and use a package.

For inline source, skip straight to CREATE FUNCTION my_fn(...) RETURNS ... LANGUAGE js AS $$ ... $$; (or ts / python / ruby) with no build or install step at all.