Show HN: TypeScript에서 PostgreSQL 함수를 작성하기 위한 Postgres 확장

A PostgreSQL extension that lets you write functions in TypeScript, powered by Deno's APIs and the v8 runtime. This borrows some ideas from plv8, but aims to support TypeScript directly, as well as a larger set of features, including access to common Node.js APIs. - Full TypeScript with type annotations and async /await . - npm packages can be imported and then are cached in PostgreSQL at CREATE FUNCTION time. - Sandboxed execution, but targeting full Node.js compatibility with Deno's permission model. - Aims to support the full PostgreSQL type system, including composite types. Types like RECORD ,JSONB are mapped automatically between PostgreSQL and JavaScript. - Provides an API for calling back into PostgreSQL from TypeScript ( _pg.execute() ). LOAD 'pg_typescript'; CREATE EXTENSION IF NOT EXISTS pg_typescript; CREATE FUNCTION slugify(title text) RETURNS text LANGUAGE typescript SET "typescript.import_map" = '{"imports":{"lodash":"https://esm.sh/lodash@4"}}' AS $$ return lodash.kebabCase(title); $$; SELECT slugify('Hello World'); -- 'hello-world' This is alpha quality software. Think carefully before using it in production. The integration tests are comprehensive and in profiling the performance appears competitive. Eventually, releases will be tagged and published on GitHub releases. let me know how it works for you please! Supports PostgreSQL 16, 17, and 18. cargo pgrx run pg18 # or pg17, pg16 Pre-built Linux packages The CI workflow builds and uploads packages for Linux x86_64 and aarch64 on every push. Download the artifact for your PG version and architecture, then extract into your PostgreSQL installation: tar -xzf package-pg18-x86_64.tar.gz cp -r pg_typescript-*/lib/postgresql/* $(pg_config --pkglibdir)/ cp -r pg_typescript-*/share/postgresql/extension/* $(pg_config --sharedir)/extension/ Then in psql: CREATE EXTENSION pg_typescript; The first time pg_typescript loads in a backend it initializes the V8 runtime, which can be slow. To avoid the cold-start on the first query, preload the library by adding it to shared_preload_libraries in postgresql.conf : # postgresql.conf shared_preload_libraries = 'pg_typescript' Postgres extension tests: cargo pgrx test pg18 Regression tests: just regress PostgreSQL uses one process per backend connection. This extension keeps both the Deno runtime and the tokio runtime local to that backend and stores them in thread_local state. When the extension library is loaded in a backend process, _PG_init() prewarms the Deno runtime; subsequent calls in that same backend reuse it. When a function is called, we read the function source from PostgreSQL, wrap it in a synthetic module, and evaluate it inside the shared backend-local runtime. Imported module source is fetched ahead of time and stored in a PostgreSQL table, and the runtime loader reads from that store during module evaluation. A separate per-backend cache stores the compiled default export handle for each loaded function so repeated calls do not need to reload and reevaluate the module. Permissions are managed by PostgreSQL GUCs (configuration variables), and can be enforced either on the function call level or by a Superuser with a "maximum" set. These are set on the Deno runtime on each execution. The TypeScript runtime is provided with a _pg global variable that a module can use to call into PostgreSQL. This provides a function, execute , that can be used to execute a PostgreSQL query and return the results mapped back into a JavaScript object. The types for this can be found in packages/types . Execution can be enabled for a function or DO block via typescript.allow_pg_execute , subject to the superuser cap typescript.max_allow_pg_execute . CREATE FUNCTION active_user_emails() RETURNS jsonb LANGUAGE typescript SET "typescript.allow_pg_execute" = 'on' AS $$ const result = await _pg.execute( "SELECT email FROM users WHERE active = $1", [true] ); return result.rows.map(r => r.email); $$; The types for _pg.execute() can be found in packages/types . By default all capabilities (network, filesystem, env, etc.) are denied. Permissions are granted via GUCs attached with SET : -- Allow network access to a specific host CREATE FUNCTION call_api(url text) RETURNS jsonb LANGUAGE typescript SET "typescript.allow_net" = 'api.example.com' AS $$ const res = await fetch(url); return await res.json(); $$; -- Allow calling back into PostgreSQL CREATE FUNCTION count_users() RETURNS int LANGUAGE typescript SET "typescript.allow_pg_execute" = 'on' AS $$ const result = await _pg.execute('SELECT count(*) as n FROM users'); return result.rows[0].n; $$; Superusers can set max_* GUCs to cap what any function may request. The types for _pg.execute() can be found in packages/types . Full GUC reference Userset GUCs can be applied with SET / SET LOCAL for a session or transaction, or attached to a function with CREATE FUNCTION ... SET . The max_* GUCs are superuser-only caps that bound what a function or DO block may request. Permission-l