Get started

dotnet --version

dotnet new console -n ArxSample -f net10.0
cd ArxSample

dotnet add package ARX.SDK.PostgreSql --version 1.0.0-rc.1

docker run --name arx-samples-postgres -e POSTGRES_PASSWORD=your-password -p 5432:5432 -d postgres:16-alpine
docker exec arx-samples-postgres createdb -U postgres arx_samples

macOS and Linux (bash, zsh)

docker exec -i arx-samples-postgres psql -U postgres -d arx_samples -v ON_ERROR_STOP=1 < sample-schema.sql

Windows PowerShell

Get-Content sample-schema.sql -Raw | docker exec -i arx-samples-postgres psql -U postgres -d arx_samples -v ON_ERROR_STOP=1

The sample schema the samples run against.

-- Schema for the ARX.SDK PostgreSQL documentation samples: a small CRM and product catalogue, and a table of every
-- data type. Run it in a database of its own (arx_samples). Re-runnable: it drops, by name, only the tables, views,
-- types and routines it creates below, and the reporting schema once nothing else is left in it, so running it again
-- starts over and nothing else in the database is touched. Run it with psql or any SQL tool, for example:
--   psql "host=localhost dbname=arx_samples user=postgres" -v ON_ERROR_STOP=1 -f sample-schema.sql
--
-- ARX conventions used here: the table name is the entity name, and a lookup column stores the referenced row's id.
-- Every table's id column is declared in SampleCatalog.cs: most are a generated bigint "id"; country and device show a
-- natural id and a generated uuid; price_list and activity_log have no id column (NoIdColumn). Selection set columns
-- hold the option's code itself.

DROP PROCEDURE IF EXISTS sp_recalculate_order_totals();
DROP FUNCTION IF EXISTS fn_accounts_with_status(text), fn_account_totals(), fn_order_count(bigint), fn_account_rows(), fn_log_action(bigint, text);
DROP MATERIALIZED VIEW IF EXISTS mv_account_sales_summary;
DROP VIEW IF EXISTS reporting.vw_product_catalogue;
-- The reporting schema is the script's own: it goes only when the script's view was the last thing in it.
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'reporting')
        AND NOT EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE n.nspname = 'reporting')
        AND NOT EXISTS (SELECT 1 FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace WHERE n.nspname = 'reporting') THEN
        DROP SCHEMA reporting;
    END IF;
END $$;
DROP TABLE IF EXISTS web_visit;
DROP VIEW IF EXISTS vw_key_accounts;
DROP VIEW IF EXISTS vw_contact_directory;
DROP FUNCTION IF EXISTS fn_contacts_of_account(bigint);
DROP VIEW IF EXISTS vw_account_overview;
DROP TABLE IF EXISTS legacy_customer;
DROP TABLE IF EXISTS activity_log;
DROP TABLE IF EXISTS price_list;
DROP TABLE IF EXISTS device;
DROP TABLE IF EXISTS country;
DROP TABLE IF EXISTS order_line;
DROP TABLE IF EXISTS sales_order;
DROP TABLE IF EXISTS data_type_showcase;
DROP TABLE IF EXISTS product;
DROP TABLE IF EXISTS product_category;
ALTER TABLE IF EXISTS account DROP CONSTRAINT IF EXISTS account_primary_contact_fkey;
DROP TABLE IF EXISTS contact;
DROP TABLE IF EXISTS account;
DROP TYPE IF EXISTS showcase_mood;

-- The routines this script creates, whatever their arguments (so a changed signature needs no new DROP line), and no
-- other: only these names, only in public.
DO $$
DECLARE routine record;
BEGIN
    FOR routine IN
        SELECT p.oid::regprocedure AS signature, p.prokind FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
        WHERE n.nspname = 'public' AND p.proname IN (
            'fn_account_rows', 'fn_account_totals', 'fn_accounts_with_status', 'fn_contact_count', 'fn_contacts_named', 'fn_contacts_of_account',
            'fn_log_action', 'fn_order_count', 'fn_products_like', 'fn_touch_account', 'sp_account_report', 'sp_account_totals',
            'sp_import_progress', 'sp_order_with_lines', 'sp_place_order', 'sp_products', 'sp_products_page', 'sp_recalculate_order_totals',
            'sp_recalculate_totals', 'sp_rename_account', 'sp_reserve_credit', 'sp_slow')
    LOOP
        EXECUTE format('DROP %s IF EXISTS %s', CASE routine.prokind WHEN 'p' THEN 'PROCEDURE' ELSE 'FUNCTION' END, routine.signature);
    END LOOP;
END $$;

-- Customers. industry is an Integer selection set built from a .NET enum; status a String set the CHECK also guards;
-- segments a multiple-choice String set stored as a text[] array.
CREATE TABLE account (
    id                bigserial     PRIMARY KEY,
    guid              uuid          NOT NULL DEFAULT gen_random_uuid(),
    name              text          NOT NULL,
    account_number    varchar(20)   UNIQUE,
    industry          smallint,
    status            text          NOT NULL DEFAULT 'ACTIVE' CHECK (status IN ('ACTIVE', 'ON_HOLD', 'CLOSED')),
    segments          text[],
    website           varchar(200),
    annual_revenue    numeric(18, 2),
    employee_count    int           CHECK (employee_count >= 0),
    credit_limit      numeric(18, 2),
    is_key_account    boolean       NOT NULL DEFAULT false,
    founded_on        date,
    parent_account_id bigint        REFERENCES account (id),
    primary_contact_id bigint,       -- references contact (added after contact below; the preload samples follow it)
    created_on        timestamptz   NOT NULL DEFAULT now(),
    modified_on       timestamptz
);

-- People at a customer. account_id is a lookup (ArxEntityReference); email is unique. preferred_channel, lead_source
-- and rating are selection sets (lead_source and rating come from the catalog's JSON).
CREATE TABLE contact (
    id                bigserial     PRIMARY KEY,
    guid              uuid          NOT NULL DEFAULT gen_random_uuid(),
    account_id        bigint        REFERENCES account (id),
    first_name        text          NOT NULL,
    last_name         text          NOT NULL,
    job_title         varchar(100),
    email             varchar(254)  UNIQUE,
    phone             varchar(30),
    preferred_channel char(1),
    lead_source       text,
    rating            smallint,
    birthday          date,
    do_not_email      boolean       NOT NULL DEFAULT false,
    consent_given_at  timestamptz,
    notes             text,
    created_on        timestamptz   NOT NULL DEFAULT now()
);

-- account.primary_contact_id: a contact of the account, the reference a preload follows to add contact.
ALTER TABLE account ADD CONSTRAINT account_primary_contact_fkey FOREIGN KEY (primary_contact_id) REFERENCES contact (id);

CREATE TABLE product_category (
    id    bigserial PRIMARY KEY,
    name  text      NOT NULL UNIQUE
);

-- The catalogue. attributes is free-form JSON (ArxJsonB); image is binary (byte[] or ArxBytea).
CREATE TABLE product (
    id              bigserial      PRIMARY KEY,
    guid            uuid           NOT NULL DEFAULT gen_random_uuid(),
    sku             varchar(32)    NOT NULL UNIQUE,
    name            text           NOT NULL,
    description     text,
    category_id     bigint         REFERENCES product_category (id),
    unit_price      numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
    cost            numeric(12, 4),
    weight_kg       double precision,
    stock_quantity  int            NOT NULL DEFAULT 0,
    is_active       boolean        NOT NULL DEFAULT true,
    tags            text[],
    attributes      jsonb,
    image           bytea,
    launched_on     date,
    created_on      timestamptz    NOT NULL DEFAULT now()
);

-- An order and its lines, for the "one process, one transaction" samples.
CREATE TABLE sales_order (
    id           bigserial      PRIMARY KEY,
    order_number varchar(20)    NOT NULL UNIQUE,
    account_id   bigint         NOT NULL REFERENCES account (id),
    ordered_on   timestamptz    NOT NULL,
    total        numeric(14, 2) NOT NULL DEFAULT 0
);

CREATE TABLE order_line (
    id          bigserial      PRIMARY KEY,
    order_id    bigint         NOT NULL REFERENCES sales_order (id),
    product_id  bigint         NOT NULL REFERENCES product (id),
    quantity    int            NOT NULL CHECK (quantity > 0),
    unit_price  numeric(12, 2) NOT NULL
);

-- One column for every kind of value the PostgreSQL provider writes (README, "Type mapping").
CREATE TYPE showcase_mood AS ENUM ('happy', 'neutral', 'sad');

CREATE TABLE data_type_showcase (
    id                 bigserial        PRIMARY KEY,
    text_value         text,
    varchar_value      varchar(50),
    char_value         char(3),
    smallint_value     smallint,
    int_value          int,
    bigint_value       bigint,
    numeric_value      numeric(30, 6),
    real_value         real,
    double_value       double precision,
    bool_value         boolean,
    uuid_value         uuid,
    timestamptz_value  timestamptz,
    timestamp_value    timestamp,
    date_value         date,
    time_value         time,
    interval_value     interval,
    bytea_value        bytea,
    jsonb_value        jsonb,
    text_array         text[],
    int_array          int[],
    numeric_array      numeric[],
    uuid_array         uuid[],
    date_array         date[],
    timestamptz_array  timestamptz[],
    mood               showcase_mood,
    account_id         bigint           REFERENCES account (id),
    industry           smallint
);

-- Ids that aren't a generated bigint: a natural text id and a generated uuid. Then two tables with no id column: a
-- price identified by region and SKU together, and a log whose rows may repeat. All declared in SampleCatalog.cs:
-- ARX reads ids and keys from the database only when asked to preload them (the DynamicTableSchemaServiceSetup samples).
CREATE TABLE country (
    code  char(2)  PRIMARY KEY,
    name  text     NOT NULL
);

CREATE TABLE device (
    id             uuid    PRIMARY KEY DEFAULT gen_random_uuid(),
    serial_number  text    NOT NULL UNIQUE,
    account_id     bigint  REFERENCES account (id)
);

CREATE TABLE price_list (
    region      char(2)        NOT NULL REFERENCES country (code),
    sku         varchar(32)    NOT NULL,
    unit_price  numeric(12, 2) NOT NULL CHECK (unit_price >= 0),
    valid_from  date           NOT NULL,
    PRIMARY KEY (region, sku)
);

-- No primary key at all: the same action can be logged twice.
CREATE TABLE activity_log (
    account_id  bigint       REFERENCES account (id),
    action      text         NOT NULL,
    logged_at   timestamptz  NOT NULL DEFAULT now()
);

-- A table made outside ARX, its columns named outside ARX's name rule: used through aliases (Create Advanced10).
CREATE TABLE legacy_customer (
    "Customer Code"  text           PRIMARY KEY,
    "first-name"     text           NOT NULL,
    "last-name"      text,
    "order.total"    numeric(12, 2)
);

-- Naming: views vw_, materialized views mv_, functions fn_, procedures sp_; tables have no prefix. The name says what
-- an object is where only the name is visible (a query, a log, an error message), and ARX reads them all by name.

-- Retrieve: a view an application reads like a table, and a function that returns a table.
CREATE VIEW vw_account_overview AS
SELECT a.id, a.name, a.account_number, a.status, count(c.id) AS contacts
FROM account a
LEFT JOIN contact c ON c.account_id = a.id
GROUP BY a.id;

CREATE FUNCTION fn_contacts_of_account(p_account_id bigint)
RETURNS TABLE (id bigint, first_name text, last_name text, email varchar)
LANGUAGE sql STABLE
AS $$ SELECT id, first_name, last_name, email FROM contact WHERE account_id = p_account_id $$;

-- Retrieve from every kind of row source (Advanced8_ReadOtherRowSources, Advanced9_ReadFunctionsInDepth).

-- A materialized view: a stored snapshot, filled by REFRESH (created empty here, as after a deployment).
CREATE MATERIALIZED VIEW mv_account_sales_summary AS
SELECT a.id AS account_id, a.name, a.account_number, count(o.id) AS orders, coalesce(sum(o.total), 0) AS revenue
FROM account a
LEFT JOIN sales_order o ON o.account_id = a.id
GROUP BY a.id, a.name, a.account_number
WITH NO DATA;

-- A view in another schema: read as "reporting.vw_product_catalogue".
CREATE SCHEMA IF NOT EXISTS reporting;
CREATE VIEW reporting.vw_product_catalogue AS
SELECT p.id, p.sku, p.name, p.unit_price, c.name AS category
FROM product p
LEFT JOIN product_category c ON c.id = p.category_id
WHERE p.is_active;

-- A partitioned table, one partition per half year (declared NoIdColumn in SampleCatalog.cs).
CREATE TABLE web_visit (
    account_id  bigint  REFERENCES account (id),
    page        text    NOT NULL,
    visited_on  date    NOT NULL
) PARTITION BY RANGE (visited_on);
CREATE TABLE web_visit_2026_h1 PARTITION OF web_visit FOR VALUES FROM ('2026-01-01') TO ('2026-07-01');
CREATE TABLE web_visit_2026_h2 PARTITION OF web_visit FOR VALUES FROM ('2026-07-01') TO ('2027-01-01');

-- Function shapes: rows of a table's type, one row of OUT values, one value, rows of an undeclared record type.
CREATE FUNCTION fn_accounts_with_status(p_status text) RETURNS SETOF account
LANGUAGE sql STABLE
AS $$ SELECT * FROM account WHERE status = p_status $$;

CREATE FUNCTION fn_account_totals(OUT accounts bigint, OUT contacts bigint)
LANGUAGE sql STABLE
AS $$ SELECT (SELECT count(*) FROM account), (SELECT count(*) FROM contact) $$;

CREATE FUNCTION fn_order_count(p_account_id bigint) RETURNS bigint
LANGUAGE sql STABLE
AS $$ SELECT count(*) FROM sales_order WHERE account_id = p_account_id $$;

CREATE FUNCTION fn_account_rows() RETURNS SETOF record
LANGUAGE sql STABLE
AS $$ SELECT id, name, account_number FROM account $$;

-- A function that writes: refused by a read unless the source says AllowWrites().
CREATE FUNCTION fn_log_action(p_account_id bigint, p_action text) RETURNS SETOF activity_log
LANGUAGE sql VOLATILE
AS $$ INSERT INTO activity_log (account_id, action) VALUES (p_account_id, p_action) RETURNING * $$;

-- A procedure: not a row source (Execute's, not Retrieve's).
CREATE PROCEDURE sp_recalculate_order_totals()
LANGUAGE sql
AS $$ UPDATE sales_order o SET total = coalesce((SELECT sum(l.quantity * l.unit_price) FROM order_line l WHERE l.order_id = o.id), 0) $$;

-- Views in depth (Advanced10_ViewsInDepth): a directory of contacts without their private columns (email, phone,
-- birthday, notes), and a view built on another view.
CREATE VIEW vw_contact_directory AS
SELECT id, first_name, last_name, job_title, account_id
FROM contact;

CREATE VIEW vw_key_accounts AS
SELECT id, name, account_number, contacts
FROM vw_account_overview
WHERE contacts >= 2;

-- Execute: procedures (sp_) and functions (fn_) the samples call.

-- Rename an account (no result); its totals for a year (IN, INOUT and OUT values).
CREATE PROCEDURE sp_rename_account(p_account_id bigint, p_name text, p_reason text DEFAULT 'not given')
    LANGUAGE sql AS $$ UPDATE account SET name = p_name, modified_on = now() WHERE id = p_account_id $$;
CREATE PROCEDURE sp_account_totals(p_account_id bigint, INOUT p_year int, OUT p_orders bigint, OUT p_revenue numeric)
    LANGUAGE plpgsql AS $$
    BEGIN
        p_year := coalesce(p_year, extract(year FROM now())::int);
        SELECT count(*), coalesce(sum(total), 0) INTO p_orders, p_revenue
        FROM sales_order WHERE account_id = p_account_id AND extract(year FROM ordered_on) = p_year;
    END $$;

-- A value; a function run for its effect (void); rows.
CREATE FUNCTION fn_contact_count(p_account_id bigint) RETURNS bigint
    LANGUAGE sql STABLE AS $$ SELECT count(*) FROM contact WHERE account_id = p_account_id $$;
CREATE FUNCTION fn_touch_account(p_account_id bigint) RETURNS void
    LANGUAGE sql AS $$ UPDATE account SET modified_on = now() WHERE id = p_account_id $$;
CREATE FUNCTION fn_contacts_named(p_last_name text) RETURNS TABLE (id bigint, first_name text, job_title varchar)
    LANGUAGE sql STABLE AS $$ SELECT id, first_name, job_title FROM contact WHERE last_name = p_last_name ORDER BY first_name $$;

-- Place an order: writes sales_order and order_line, returns the new id and the total.
CREATE PROCEDURE sp_place_order(p_account_id bigint, p_order_number text, p_skus text[], p_quantity int, OUT p_order_id bigint, OUT p_total numeric)
    LANGUAGE plpgsql AS $$
    BEGIN
        INSERT INTO sales_order (order_number, account_id, ordered_on) VALUES (p_order_number, p_account_id, now()) RETURNING id INTO p_order_id;
        INSERT INTO order_line (order_id, product_id, quantity, unit_price)
            SELECT p_order_id, p.id, p_quantity, p.unit_price FROM product p WHERE p.sku = ANY (p_skus);
        SELECT coalesce(sum(quantity * unit_price), 0) INTO p_total FROM order_line WHERE order_id = p_order_id;
        UPDATE sales_order SET total = p_total WHERE id = p_order_id;
    END $$;

-- Tables from a procedure: an order and its lines; an account report with three tables.
CREATE PROCEDURE sp_order_with_lines(p_order_id bigint, INOUT p_order refcursor DEFAULT NULL, INOUT p_lines refcursor DEFAULT NULL)
    LANGUAGE plpgsql AS $$
    BEGIN
        OPEN p_order FOR SELECT o.order_number, o.ordered_on, o.total, a.name AS account FROM sales_order o JOIN account a ON a.id = o.account_id WHERE o.id = p_order_id;
        OPEN p_lines FOR SELECT p.sku, l.quantity, l.unit_price FROM order_line l JOIN product p ON p.id = l.product_id WHERE l.order_id = p_order_id ORDER BY p.sku;
    END $$;
CREATE PROCEDURE sp_account_report(p_account_id bigint, INOUT p_contacts refcursor DEFAULT NULL, INOUT p_orders refcursor DEFAULT NULL, INOUT p_summary refcursor DEFAULT NULL)
    LANGUAGE plpgsql AS $$
    BEGIN
        OPEN p_contacts FOR SELECT first_name, last_name, job_title FROM contact WHERE account_id = p_account_id ORDER BY first_name;
        OPEN p_orders FOR SELECT order_number, total FROM sales_order WHERE account_id = p_account_id ORDER BY order_number;
        OPEN p_summary FOR SELECT (SELECT count(*) FROM contact WHERE account_id = p_account_id) AS contacts,
                                  (SELECT count(*) FROM sales_order WHERE account_id = p_account_id) AS orders,
                                  (SELECT coalesce(sum(total), 0) FROM sales_order WHERE account_id = p_account_id) AS revenue;
    END $$;

-- Paging: a procedure that pages itself; a SQL function ARX pages; a procedure's table (a cursor) ARX pages.
CREATE PROCEDURE sp_products_page(p_prefix text, p_page int, p_page_size int, INOUT p_rows refcursor DEFAULT NULL, INOUT p_total bigint DEFAULT NULL)
    LANGUAGE plpgsql AS $$
    BEGIN
        SELECT count(*) INTO p_total FROM product WHERE sku LIKE p_prefix || '%';
        OPEN p_rows FOR SELECT sku, name, unit_price FROM product WHERE sku LIKE p_prefix || '%' ORDER BY sku LIMIT p_page_size OFFSET (p_page - 1) * p_page_size;
    END $$;
CREATE FUNCTION fn_products_like(p_prefix text) RETURNS TABLE (sku varchar, name text, unit_price numeric)
    LANGUAGE sql STABLE AS $$ SELECT sku, name, unit_price FROM product WHERE sku LIKE p_prefix || '%' $$;
CREATE PROCEDURE sp_products(p_prefix text, INOUT p_rows refcursor DEFAULT NULL)
    LANGUAGE plpgsql AS $$ BEGIN OPEN p_rows FOR SELECT sku, name, unit_price FROM product WHERE sku LIKE p_prefix || '%' ORDER BY sku; END $$;

-- Business rules the database enforces: a credit limit (RAISE with a hint), an account on hold (its own code).
CREATE PROCEDURE sp_reserve_credit(p_account_id bigint, p_amount numeric)
    LANGUAGE plpgsql AS $$
    DECLARE v_limit numeric; v_status text;
    BEGIN
        SELECT credit_limit, status INTO v_limit, v_status FROM account WHERE id = p_account_id;
        IF v_status = 'ON_HOLD' THEN
            RAISE EXCEPTION 'account % is on hold', p_account_id USING ERRCODE = 'AH001', HINT = 'release the hold first';
        END IF;
        IF p_amount > coalesce(v_limit, 0) THEN
            RAISE EXCEPTION 'credit limit exceeded' USING DETAIL = format('limit %s, asked %s', v_limit, p_amount), HINT = format('reserve %s or less', v_limit);
        END IF;
        UPDATE account SET credit_limit = credit_limit - p_amount, modified_on = now() WHERE id = p_account_id;
    END $$;

-- A nightly job that commits each batch and reports progress; a slow procedure.
CREATE PROCEDURE sp_recalculate_totals(p_prefix text, p_batch int)
    LANGUAGE plpgsql AS $$
    DECLARE v_done int := 0; v_batch int;
    BEGIN
        LOOP
            UPDATE sales_order o SET total = coalesce((SELECT sum(l.quantity * l.unit_price) FROM order_line l WHERE l.order_id = o.id), 0)
            WHERE o.id IN (SELECT id FROM sales_order WHERE order_number LIKE p_prefix || '%' AND total < 0 ORDER BY id LIMIT p_batch);
            GET DIAGNOSTICS v_batch = ROW_COUNT;
            EXIT WHEN v_batch = 0;
            v_done := v_done + v_batch;
            COMMIT;
            RAISE NOTICE 'recalculated % orders', v_done;
        END LOOP;
        RAISE INFO 'done: % orders', v_done;
    END $$;
CREATE PROCEDURE sp_import_progress(p_rows int)
    LANGUAGE plpgsql AS $$
    BEGIN
        FOR i IN 1..p_rows LOOP
            IF i % 250 = 0 THEN RAISE NOTICE 'imported % of % rows', i, p_rows; END IF;
        END LOOP;
        IF p_rows > 900 THEN RAISE WARNING 'a large import: % rows', p_rows; END IF;
    END $$;
CREATE PROCEDURE sp_slow(p_seconds double precision)
    LANGUAGE sql AS $$ SELECT pg_sleep(p_seconds) $$;

macOS and Linux (bash, zsh)

export ARX_SAMPLES_POSTGRESQL="Host=localhost;Port=5432;Database=arx_samples;Username=postgres;Password=your-password"

Windows PowerShell

$env:ARX_SAMPLES_POSTGRESQL = "Host=localhost;Port=5432;Database=arx_samples;Username=postgres;Password=your-password"

dotnet run