Functions and Procedures support

[binupillai305]

In any case, can we make it to support functions and Stored procedure?

[jawj]

Can you elaborate on this a bit, please? I'm not exactly sure what you mean.

[binupillai305]

When I execute the command 'npx zapatos ', all the tables, views, materialized views are transforming into typescripts and generating a schema file. But the generated schema file not including any SQL functions and Stored procedures. So am expecting any workaround to include the SQL function and stored procedure while generating the schema file.

Config file: zapatosconfig.json

  "db": {
    "connectionString": "postgres://postgres:xxxx@localhost/dbname"
  },
  "outDir": "./src",
  "schemas": {
    "schema1": {
      "include": "*",
      "exclude":[]
    },
    "schema2": {
      "include": "*",
      "exclude":[]
    }
  }
}

[jawj]

I'm still having to kind of guess here — it would helpful if you could explain what you're trying to achieve, with examples, rather than narrowly describe a feature.

But my guess is that you're suggesting we include all function/procedure (routine) names in the SQL types, so that I could write, for example, db.sql`SELECT ${"now"}()`.run(pool);, and get type-checking that now is a real routine name, instead of simply db.sql`SELECT now()`.run(pool);?

I can see some sort of argument for that, but on balance I think it would make VS Code's autocomplete much less useful when writing queries, because the long list of valid routine names would in most cases swamp the much shorter list of valid table and column names.

Alternatively I guess we could limit this to user-defined functions, since this is a shorter list and these are the ones that may come and go, causing future type errors. I'm not fully convinced of this either, but could be open to arguments if people have real use-cases where this would make a difference.

[binupillai305]

What I am trying to achieve is to transform all the SQL objects of a database into a schema file. That means all the definitions of tables, views, materialized views, functions and stored procedures should exist in the final schema file. Currently what is happening is in the generated schema file, we cannot find the definitions of user-defined functions and stored procedure.

[jawj]

OK, but why are you trying to do that? Is this for use with the rest of the Zapatos machinery — and if so, how? — or is it for some other purpose? Without knowing that, I can't tell whether this is something that would be valuable to add to the library.

[binupillai305]

Because am trying to create a transformer script to generate SQL files from the Typescripts schema. Here the difficulty is, I have to manually write Typescript for each SQL object. But if I try to use Zapatos, I can easily generate Typescript for almost all the SQL objects except user-defined functions and procedures. That is why I asked Zapatos support for user-defined functions and stored procedures.

[shah]

@jawj good question about why -- what @binupillai305 needs is to be able to execute a PostgreSQL stored procedure or call a PostgreSQL stored function or execute a PostgreSQL view in TypeScript. What would be cool is that a PostgreSQL stored procedure/function/view, where business logic is stored in the database, can be executed as a TypeScript function (with typesafety of arguments) in NodeJS.

• Views might be tough to "type" properly in TypeScript because view definitions in PostgreSQL might not have column definitions for views.
• Stored routines / functions, I think, might have enough type information in the PostgreSQL schema to indicate arguments and return types so that wrappers could be created in TypeScript.

@binupillai305 perhaps showing @jawj one of the stored procedures and what you'd like to see generated by Zapatos makes sense?

[jawj]

Thanks @shah, this definitely clarifies things. @binupillai305 it would certainly help to see an example of the sort suggested by @shah.

[lukeramsden]

Would also be nice to be able to call built-ins in a typesafe and type-generated way, such as advisory lock functions like pg_try_advisory_lock

[binupillai305]

@jawj My requirement was to convert a PostgreSQL function or stored procedure to its equivalent typescript format. For example, It would be nice if we could represent similar PostgreSQL function using Typescript.

CREATE OR REPLACE FUNCTION totalRecords ()
RETURNS integer AS $total$
declare
    total integer;
BEGIN
   SELECT count(*) into total FROM COMPANY;
   RETURN total;
END;
$total$ LANGUAGE plpgsql;

[jfrconley]

I would also love to see this feature! Generated type safe wrappers for functions would be very useful as I use them frequently to avoid roundtrips.

[WonderPanda]

Would love to see this as well. My use case would be to be able to invoke custom postgres functions that I've written in a typesafe way from TS code

[jawj]

Hi all. You'll probably have noticed that I haven't had a lot of time to work on Zapatos lately, but I do agree that this feature is worth spending some time investigating ...

[kpamaran]

@jawj Adding a datapoint here

My team at canopyservicing.com (platform startup for loan servicing) heavily uses postgres functions and stored procedures for business logic. zapatos is great for simple cases like inserting into one table and allows our TypeScript backend devs to write code the SQL developers don't need to worry about, but we have some pg functions requiring dozens of fields that inserts into a multiple relations, sets up the FK relationships, etc, with a body comprised of 100s of lines of PL/pgsql that would be incredibly difficult to do in a single query for a TypeScript developer.

It can be argued this workflow could be split into multiple API requests and that would simplify things for sure, but we are accepting the additional complexity so that our customers' developers manage fewer API calls. Very common in Fintech.

I made an in-house prototype typegen tool that generates types for postgres functions (both input and return types) given some constraints over the way functions are defined (handling optional values; unnamed params; but doesn't handle all cases like variadic functions). Our postgres functions do a lot of the backend work for our APIs and it was easy to maintain types when the response bodies were small and we could manually write the types ourselves over using Record<string, unknown> for json. Instead of creating a whole new tool, I would like to help or even create an exploratory PR to extend zapatos to support typegen for fns/procedures with your consultation.

Here's an example of what one of our API responses looks like. Since postgres returns this as json and json is not strongly typed we have to handcode the type for the response, if we wanted to facilitate typegen for the output structured like json it seems I'd have to write composite types inside other composite types. That doesn't get parsed too well by the pg lib; or maybe we should have a single composite type that flattens all of this and let the middleware structure it out. But some json value in that composite type would be needed to handle arrays of structured data, and that isn't quite elegant. Obviously we're still figuring this out.

Here's an example of a function signature and a return value in the mean time :)

-- weakly typed json; both input and output types are huge
CREATE FUNCTION create_product(product_input: json) RETURNS json 
    LANGUAGE PlpgSQL
AS
$$
  -- lots of business logic here
$$

Most of this is handled by our postgres function.

{
    "product_id": "product-c0e49a8d-21f3-469a-b29a-7dda9d5dbda8",
    "created_at": "2021-11-12T13:25:38-05:00",
    "effective_at": "2010-02-20T04:11:28-05:00",
    "admin": {
        "migration_mode": false
    },
    "product_overview": {
        "product_name": "Student Credit Card",
        "product_type": "MIXED_RATE_INSTALLMENT",
        "product_short_description": "12mmcc",
        "product_long_description": "For Automations Testing.",
        "product_color": "#000000",
        "external_fields": []
    },
    "product_lifecycle_policies": {
        "payment_due_policies": {
            "delinquent_on_n_consecutive_late_fees": 1,
            "charge_off_on_n_consecutive_late_fees": 2
        },
        "payment_pouring_policies": {
            "pending_pmt_affects_avail_credit": false
        },
        "fee_policies": {
            "late_fee_grace": "5 days",
            "surcharge_fee_interval": null,
            "default_surcharge_fee_structure": null
        },
        "billing_cycle_policies": {
            "cycle_interval": "14 days",
            "cycle_due_interval": "-1 days",
            "first_cycle_interval": "14 days",
            "close_of_business_time": "00:17:00.000001",
            "product_time_zone": "America/New_York"
        },
        "interest_policies": {
            "interest_calc_time": "00:18:00.000001-05"
        },
        "default_attributes": {
            "default_credit_limit_cents": 50000,
            "default_late_fee_cents": 0,
            "default_payment_reversal_fee_cents": null
        }
    },
    "promotional_policies": {
        "promo_len": 1,
        "promo_min_pay_type": "AM",
        "promo_min_pay_percent": 100,
        "promo_purchase_window_len": null,
        "promo_interest_deferred": false,
        "promo_default_interest_rate_percent": 3,
        "promo_apr_range_inclusive_lower": null,
        "promo_apr_range_inclusive_upper": null
    },
    "post_promotional_policies": {
        "post_promo_len": 26,
        "post_promo_am_len_range_inclusive_lower": null,
        "post_promo_am_len_range_inclusive_upper": null,
        "post_promo_min_pay_type": "AM",
        "post_promo_default_interest_rate_percent": 2.875,
        "post_promo_apr_range_inclusive_lower": null,
        "post_promo_apr_range_inclusive_upper": null
    },
    "product_summary": {
        "accounts_overview": {
            "account_count_total": 0
        }
    }
}

Another example. Our inhouse tool can generate a return type for this and our situation is gradually improving, but there's a lot of work Zapatos already does we want to avoid.

-- 1. returns composite type, but it doesn't have a nested structure.
-- we have not tried nested composite types yet
-- 2. for view-like data that aggregates from multiple tables,
-- we create flat composite types directly instead of tables
CREATE FUNCTION update_cards(input json) RETURNS SETOF cards
    LANGUAGE PLpgSQL
AS
...

[kpamaran]

Here's one SQL query I wrote to derive enough data from the pg system catalog to generate TS types for function return types.

-- get return types across fns
-- first scalar types, then composite types
select distinct on (pp.prorettype)
    pp.prorettype rettype_oid
    , pt.typname type_name -- TODO: handle void
    , false is_composite
    , null::text[] atts_name
    , null::int[] atts_type_oid
    , null::bool[] atts_not_null
from pg_proc pp
join pg_namespace pn
    on nspname = 'public' -- TODO: Let user configure which schemas to introspect
    and pp.pronamespace = pn.oid
left join pg_type pt
    on pp.prorettype = pt.oid
where pt.typrelid = 0                           -- 0 means type is not a composite type
    and pp.prorettype != 2279          -- exclude fns returning triggers
    and pp.prokind in ('f', 'p')             -- exclude window/aggregate fns; out of scope
union all
select pp.prorettype rettype_oid
    , pt.typname
    , true is_composite 
    , array_agg(pa.attname)
    , array_agg(pa.atttypid)
    , array_agg(pa.attnotnull)
from (
    select distinct on (prorettype) * 
    from pg_proc
) pp
join pg_namespace pn
    on nspname = 'public'
    and pp.pronamespace = pn.oid
left join pg_type pt
    on pp.prorettype = pt.oid
join pg_attribute pa
    on pt.typrelid = pa.attrelid
where pa.attnum > 0                    -- exclude system columns (their attnum values are below 0)
    and pp.prorettype != 2279
    and pp.proowner != 10    
    and pp.prokind in ('f', 'p')
    and pa.attisdropped != true        -- exclude dropped columns 
group by pp.prorettype, pt.typname
;

[palamethos]

Hi all, adding to the discussion with an example

If we had a sql function

create function super_special_add(a int, b int) returns int as $$
select a + b + 1;
$$ language sql;

And then have this executable/typed in js

const specialAdd = await db.function('super_special_add', { a: 5, b: 7 }).run(pool),
//     ^- TS knowing it is number                                 ^- and TS giving us argument shape knowing "a" and "b" and that they numbers

None the less thank you for excellent library!

[jawj]

Hi all. OK, I can see the value of this, and it's definitely on the TODO list, though I'm not sure exactly how soon I'll be able get to it (unless anyone feels like sponsoring it 😀).