Snowflake
Read a Snowflake warehouse with VibORM, over the SQL API
viborm/snowflake reads a Snowflake warehouse. It speaks to Snowflake’s
SQL API v2 with
fetch, and signs its key-pair JWT with WebCrypto, so it runs in Node, Bun,
Cloudflare Workers and Durable Objects. It needs no snowflake-sdk.
VibORM only reads Snowflake. Writes and migrations are refused (see What is refused).
npm install viborm
import { createClient } from "viborm/snowflake";
import { schema } from "./schema";
const db = createClient({
schema,
databaseUrl: "snowflake://READER@myorg-myaccount/SALES/PUBLIC?warehouse=WH&role=ANALYST",
privateKey: env.SNOWFLAKE_PRIVATE_KEY, // PKCS#8 or PKCS#1 PEM, unencrypted
});
const customers = await db.customer.findMany({
where: { segment: "retail" },
include: { orders: { orderBy: { placedAt: "desc" }, take: 5 } },
});
The URL names the user, the account, the database and the schema. It never
carries the key or the token: pass privateKey or token beside it.
Options
| Option | Meaning |
|---|---|
databaseUrl |
snowflake://USER@ACCOUNT/DATABASE/SCHEMA?warehouse=…&role=… |
account |
The account identifier (see below) |
username |
The user the key belongs to |
privateKey |
The RSA private key of key-pair authentication (PKCS#8 or PKCS#1 PEM, its line breaks real or written \n), or an imported extractable CryptoKey |
token |
An OAuth token, or a function answering a fresh one, asked before each request (for refreshing) |
tokenType |
"OAUTH" (default) or "PROGRAMMATIC_ACCESS_TOKEN"; workload identity federation is not offered |
warehouse, database, role |
The context every statement runs in, spelled as the catalog spells them (usually upper case) |
namespace |
The schema every statement runs in, and the one VibORM qualifies its tables with |
timeout |
Seconds a statement may run, sent to Snowflake. Absent: half an hour (1,800), held by the driver alone; false: no limit of the driver’s (see Timeouts) |
host |
The API’s https:// base URL, when it is not https://<account>.snowflakecomputing.com: the China region, a proxy. The token goes wherever it says |
fetch |
The fetch to send with: any (url, init) => Promise<Response> |
parameters |
QUERY_TAG, USE_CACHED_RESULT or CLIENT_RESULT_CHUNK_SIZE, added to every statement’s session: a string, a number or a boolean, sent as text; one undefined is not sent |
A field given beside databaseUrl takes precedence over the URL’s. Use exactly
one credential: a privateKey (with username) or a token.
The account is the first labels of the API’s host, and nothing else: an
organization and account name (myorg-myaccount), a locator with its region
(xy12345.us-east-1, xy12345.us-east-1.aws), or a private link
(xy12345.us-east-1.privatelink). VibORM refuses, before sending anything, an
account that would name another host: a scheme, a path, a user, a port or a
query. An account in Snowflake’s China region answers under
.snowflakecomputing.cn, and a proxy anywhere: pass it as host, an
https:// URL (VibORM refuses any other, since the token goes there).
A CryptoKey must be extractable: the driver reads its public half to compute
the fingerprint Snowflake checks, and refuses a key it cannot read with a
ClientInitializationError.
Every other session parameter is refused when the client is created, in any
case: the driver decodes results in the formats and time zone it fixes (a
DATE_OUTPUT_FORMAT or BINARY_OUTPUT_FORMAT would make it read wrong
values), runs one statement at a time (MULTI_STATEMENT_COUNT) and reads
every row (ROWS_PER_RESULTSET).
The user’s QUOTED_IDENTIFIERS_IGNORE_CASE must stay FALSE, its default:
with it, Snowflake upper-cases every quoted name a statement gives its columns,
and VibORM finds none of them. The SQL API refuses to set it per statement, so
the driver cannot set it for you (measured).
Names
VibORM quotes every identifier, so a model reads the table and the columns
whose names it maps to exactly. Snowflake stores an unquoted name in upper
case: a table created as CREATE TABLE orders (id NUMBER) is ORDERS, its
column ID, and its model needs .map("ORDERS") and id: s.int().id().map("ID").
introspectSchema writes each name as the catalog
spells it, so a schema built from it needs no care.
What runs
| Operation | Notes |
|---|---|
findMany, findFirst, findUnique and their OrThrow forms |
With nested include / select, where, orderBy, take, skip, distinct at the root, and cursor ordered by the model’s single-field key alone |
| Nested relations | where, orderBy on the related row’s own fields, take (negative included), skip, _count |
| Relation filters | some, every, none, is, isNot |
count, exist, aggregate, groupBy |
Sums and averages exact on NUMBER columns |
$queryRaw, $queryRawUnsafe |
Sent as written, with typed bindings |
s.vector() on VECTOR(FLOAT, n) |
Read, equals, and _distance (l2, cosine) to filter, select and order |
s.point() on GEOGRAPHY |
Read, equals, within bounds and polygons, and _distance in metres |
How a nested read is written
Snowflake refuses a correlated subquery holding a LIMIT, and a derived table
that reads an outer row. So a relation is read from an uncorrelated page of
the related table: the page states the key it joins its parent on and numbers
its rows per key (ROW_NUMBER() … QUALIFY), and only the ARRAY_AGG around it
is correlated, by an equality, which Snowflake turns into a grouped join.
That page reads the related table set-wise. On a large table, a nested read
costs what its whole relation costs, not what the parent’s rows reach. Two
levels deep, the second level’s page carries no filter from the root: expect
it to read every row of its relation, whichever parents the root keeps.
findUnique with an include pays the same.
Keys Snowflake does not enforce
Snowflake stores a PRIMARY KEY and a UNIQUE key and enforces neither. A
read that relies on one reads what the table holds: findUnique answers the
first of two rows sharing the key, and an order by a to-one relation lists a
row twice when two target rows share the key it joins on. VibORM cannot tell:
keep the keys true with Snowflake’s own tools.
Timeouts
Without timeout, a statement may run half an hour (1,800 seconds), and the
driver alone holds that limit: it sends Snowflake no timeout, so a shorter
STATEMENT_TIMEOUT_IN_SECONDS on the account, the user or the warehouse
still stops the statement first. A timeout sent with a statement replaces
the account’s and the user’s value, longer or shorter alike (measured: with
the user at 5 seconds, a statement sent with timeout: 1800 ran its full 9
seconds), while a warehouse’s lower value still wins.
timeout: n sends n to Snowflake and holds it in the driver too.
timeout: false holds no limit of the driver’s: the statement runs as long as
Snowflake lets it, STATEMENT_TIMEOUT_IN_SECONDS, two days unless the
account, the user or the warehouse lowers it. 0 is refused: Snowflake reads
it as its maximum, seven days, not as no limit.
The SQL API answers with a statement’s handle only at its first 202, after
45 seconds. Before it, Snowflake’s own timeout stops the statement (measured:
no cancel is sent, and the account’s query history shows the statement
failed). After it, the driver cancels the statement when its limit passes,
the default’s as a given one’s: Snowflake acknowledges the cancel and the
statement fails (measured with a statement abandoned after its 202). The
driver waits at most 2 seconds for that answer, and answers QUERY_TIMEOUT
whether it comes or not (tested against a local server).
The driver takes no AbortSignal of the caller’s: a read runs until its
result, its timeout, or Snowflake’s own limits.
Requests that fail
A submission that fails before Snowflake answers it (a network failure, any
5xx, a 429) is sent again with the same requestId and retry=true, which
never runs a statement twice: three sends in all. Reading a statement’s status
or a partition gets three more, since a read changes nothing. A 5xx still
answered after the last send is a ConnectionError, never Snowflake refusing
the statement. After a submission’s last 5xx the statement’s outcome is not
known: a gateway can answer 504 after Snowflake started it, and with no
statement handle the driver cannot cancel it: only Snowflake’s limit for it
bounds it (a timeout sent with it, else STATEMENT_TIMEOUT_IN_SECONDS). When reading a statement’s status fails, the driver cancels it
before it answers the failure, so it does not keep running on the warehouse.
Results are read whole
Every row of every partition is read into memory before the call returns:
about 100 MB for 200,000 rows of three columns (measured on a local server).
Page a large read with take and skip, or a key cursor.
Collations
VibORM compares text as Snowflake does for the column. A column declared with
a case-insensitive collation (COLLATE 'en-ci', or one inherited from
DEFAULT_DDL_COLLATION) makes equals, contains and startsWith
case-insensitive there (measured), and endsWith with them.
What is refused
Every one of these fails with a FeatureNotSupportedError (V8001) before
anything is sent:
create,createMany,update,updateMany,upsert,delete,deleteMany, and every nested write;$executeRawand$executeRawUnsafe.
createMigrationClient refuses a Snowflake client with a MigrationError
(V8001), so every migration command (push, apply, log, status, …) is
refused before it reads its storage or sends anything. Only
introspection reads a warehouse.
A $transaction, callback or array, fails with a TransactionError before
anything is sent: VibORM sends one statement per request and holds no session
between two, and a read needs no transaction.
And with an UnsupportedOperationError, before anything is sent:
- a recursive filter (
recursein awhere, the caller’s or a permission rule’s): Snowflake refuses the recursive walk VibORM writes (“is an invalid Recursive CTE”, measured); - a root
cursorordered by anything but the model’s single-field key (an order an extension such asviborm/orderinggives the model counts): the comparison would need a multi-column subquery, which Snowflake refuses (measured); order by the key, or page withskip; - in a nested relation:
cursor,distinct, an order by a related row or a_count, a recursive relation, a polymorphic slot, a junction’s$throughrow.
What VibORM does not refuse can still write: $queryRaw and
$queryRawUnsafe send their text as written, and so do $driver._execute
and a query interceptor. A role with only SELECT privileges is what
guarantees nothing writes. An extension’s write verbs are refused before
anything is sent too: restore and delete of viborm/soft-delete and
create of viborm/ordering by the write refusal. move of
viborm/ordering never sends a write either, but it is not refused before
anything is sent: move({ after }) fails on its first read (a cursor in the
list’s order) with an UnsupportedOperationError, and move({ first: true })
or move({ last: true }) sends one read before it is refused.
Why VibORM does not write to Snowflake
Snowflake enforces no PRIMARY KEY, UNIQUE or FOREIGN KEY (only NOT NULL),
has no savepoint and no RETURNING, and every statement takes 100 ms to a second
on a warehouse that bills while it runs. What a VibORM write promises — the row a
unique key addresses, a relation that holds, a nested write that rolls back to
where it failed — would not hold there. Write with Snowflake’s own tools
(COPY INTO, MERGE, tasks, dbt).
Types
The API answers every value as text. The driver reads each one exactly:
| Snowflake | Read as |
|---|---|
NUMBER(p,0) |
number while a safe integer, else bigint ($queryRaw); the field’s scalar otherwise |
NUMBER(p,s), s > 0 |
exact decimal text ($queryRaw); a Decimal for s.decimal() |
FLOAT |
number, Infinity and NaN included ($queryRaw); s.number() refuses NaN and Infinity, as on every provider |
VARCHAR, BOOLEAN |
string, boolean |
DATE |
"YYYY-MM-DD" |
TIME |
"HH:MM:SS.fffffffff" ($queryRaw); for s.time(), "HH:MM:SS" with its fraction to the millisecond ("12:34:56.12"), and a value finer than the millisecond is refused, flat or nested, since VibORM’s time holds milliseconds |
TIMESTAMP_NTZ, TIMESTAMP_LTZ |
an ISO instant to the nanosecond ($queryRaw); a Date for s.dateTime() |
TIMESTAMP_TZ |
the same at its own offset (…+05:30) |
BINARY |
Uint8Array |
VARIANT, OBJECT, ARRAY |
the parsed JSON; a NaN or Infinity inside reads as the string "NaN" or "Infinity", as Snowflake prints it under STRICT_JSON_OUTPUT (which the SQL API refuses per request, measured), so the value is the same on every account |
VECTOR(FLOAT, n) |
number[] for s.vector(), each member the exact double its float32 widens to (0.10000000149011612) |
GEOGRAPHY |
{ longitude, latitude } for s.point(); the GeoJSON object ($queryRaw) |
Every statement runs with TIMEZONE = 'UTC': a TIMESTAMP_NTZ holds a wall
clock with no zone, and VibORM reads it, and compares it, as UTC. It runs with
ROWS_PER_RESULTSET = 0 too, so no account or user setting caps a result, and
MULTI_STATEMENT_COUNT = 1. A Date operand binds as a TIMESTAMP_TZ at UTC,
and a bigint inside a bound JSON object as a string ({ a: 1n } binds
{"a":"1"}), so a JSON filter compares it with a string.
A list filter (has, hasSome, hasEvery) compares each member as it is
bound and, when its text is a number, as that number, so a bigint or
decimal list matches the numbers a Snowflake ARRAY holds, exactly past
2^53, and a string list’s text stays text (measured).
A $queryRaw result naming one column twice (SELECT a.id, b.id) is refused:
a row holds one value per name. Alias one of them.
A JSON number inside a VARIANT read through $queryRaw is parsed by
JSON.parse, so an integer past 2^53 there rounds, and the SQL API prints a
VECTOR with six decimals. A SQL NULL inside an ARRAY reads as null. VibORM’s own reads lose nothing: bigint,
Decimal and number values travel as text inside the documents they build (a
VARIANT prints a double with 16 significant digits, one short of every
double), a vector as the text of its exact members, and a point as two exact
JSON numbers.
Vectors and points
s.vector().dimension(n) reads a VECTOR(FLOAT, n) column; a VECTOR(INT, n)
has no VibORM scalar. Snowflake has no vector index, so every distance order is
exact. A zero vector’s cosine distance reads null, as on PostgreSQL.
s.point() reads a GEOGRAPHY column holding points: Snowflake’s GEOGRAPHY
is WGS 84, the EPSG:4326 VibORM’s point language is fixed to. A row holding
another shape does not decode. _distance is the haversine distance on
VibORM’s fixed radius, the metres every provider answers. A polygon filter is
ST_INTERSECTS with geodesic edges, its outer boundary included and its holes
excluded, with one reading of Snowflake’s own, measured: a point exactly on a
hole’s edge reads as outside, where VibORM’s polygon holds it. A GEOMETRY
is planar, in any SRID, and has no VibORM scalar.
Introspection
introspectSchema reads a Snowflake schema into a
schema document. Pass a driver, since a URL carries no credential:
import { introspectSchema } from "viborm/schema/introspect";
import { SnowflakeDriver } from "viborm/snowflake";
const { document, skipped, warnings } = await introspectSchema({
driver: new SnowflakeDriver({ databaseUrl, privateKey }),
light: true,
});
It reads INFORMATION_SCHEMA.TABLES and COLUMNS, and the keys a table declares
from SHOW PRIMARY KEYS, SHOW UNIQUE KEYS and SHOW IMPORTED KEYS. Snowflake
enforces none of them, and they are still what the warehouse’s designer said the
relations are: each declared foreign key becomes a relation. A warning says so.
NUMBER(p,0)becomesintup to nine digits,bigintbeyond (Snowflake’sINTEGERisNUMBER(38,0));NUMBER(p,s)adecimal;FLOATanumber;VARCHARastring; the three timestamps adateTime(aTIMESTAMP_NTZwith anapproximatedwarning: it is read as UTC);BINARYablob;VARIANT,OBJECTandARRAYajson;VECTOR(FLOAT, n)avectorof dimensionn(read fromSHOW COLUMNSof its table, sinceINFORMATION_SCHEMAdoes not state it);GEOGRAPHYapoint, with anapproximatedwarning (it may hold another shape);CURRENT_TIMESTAMP()anowdefault.- A view, and a column with no VibORM scalar (
GEOMETRY,VECTOR(INT, n)), are listed inskipped. - No
driftwarning is reported: nothing is ever pushed to Snowflake. - A key into another schema or another database is listed in
skipped, with the table it names (CRM.PUBLIC.CUSTOMERS): never read as a relation to this schema’s table of the same name.