SODA Query

SODA (Socrata) Query Client for Deno & NodeJS.

Table of Contents

• Features
• Installation
• Quick start
  • Plain query
  • SQL Builder
• Creating a query
• Authentication
• Building the query
  • Select
  • Where
  • Field & DataType
  • Order
  • Group & Having
  • Expressions (expr)
  • Search & paging
• Fetching results
  • execute & single
  • Pagination
  • count
  • GeoJSON & CSV
  • Column metadata
  • Response headers
• Stored queries
• License

Features

• Fluent, chainable SoQL query builder
• Type-safe fields and functions via Field / DataType
• Filters (Where), transforms (Select), case(...), and arithmetic expressions
• Fetch as JSON, GeoJSON, or CSV
• Automatic pagination (async iterators or eager collect), row counts, and column metadata
• Works in Deno and Node

Note: This client targets the Socrata SODA 2.1 endpoints (/resource/{id}.json). It is only for fetching data from the Socrata Open Data API — it does not support creating, updating or deleting data, nor the SODA 3.0 (/api/v3/...) endpoints.

Installation

Deno — from JSR:

deno add jsr:@j3lte/soda

import { SodaQuery } from "@j3lte/soda";
// or import directly, without adding it: "jsr:@j3lte/soda"

Node (npm i soda-query):

import { SodaQuery } from "soda-query";

Quick start

Plain query

SodaQuery methods accept plain SoQL strings. Each where(...) string is a full clause:

import { SodaQuery } from "jsr:@j3lte/soda";

const DOMAIN = "data.cityofnewyork.us";
const DATASET = "erm2-nwe9";

const { data, error } = await new SodaQuery(DOMAIN).withDataset(DATASET)
  .select("agency", "borough", "complaint_type")
  .where("complaint_type LIKE 'Noise%'")
  .where("created_date > '2019-01-01T00:00:00.000'")
  .where("created_date < '2020-01-01T00:00:00.000'")
  .orderBy("created_date DESC")
  .limit(10)
  .execute();

SQL Builder

Or build the same query with the type-safe helpers:

import { Order, SodaQuery, Where } from "jsr:@j3lte/soda";

const { data, error } = await new SodaQuery("data.cityofnewyork.us")
  .withDataset("erm2-nwe9")
  .select("agency", "borough", "complaint_type")
  .where(
    Where.and(
      Where.like("complaint_type", "Noise%"),
      Where.gt("created_date", "2019-01-01T00:00:00.000"),
      Where.lt("created_date", "2020-01-01T00:00:00.000"),
    ),
  )
  .orderBy(Order.by("created_date").desc)
  .limit(10)
  .execute();

Every fetch resolves to { data, error, status } — errors are returned, not thrown.

Creating a query

import { createQueryWithDataset, SodaQuery } from "jsr:@j3lte/soda";

const query = new SodaQuery("data.organization.com").withDataset("dataset-id");

// Same thing, in one call:
const query2 = createQueryWithDataset("data.organization.com", "dataset-id");

Pass a row type to get typed results:

type Row = { agency: string; complaint_type: string };
const query = new SodaQuery<Row>("data.cityofnewyork.us").withDataset("erm2-nwe9");
const { data } = await query.execute(); // data: Array<Row & system fields>

Authentication

Pass auth options as the second constructor argument. An app token raises your rate limit; Basic auth and OAuth authenticate as a user.

// App token (recommended)
new SodaQuery("data.cityofnewyork.us", { apiToken: "YOUR_APP_TOKEN" });

// HTTP Basic auth
new SodaQuery("data.cityofnewyork.us", { username: "user", password: "pass" });

// OAuth access token
new SodaQuery("data.cityofnewyork.us", { accessToken: "OAUTH_TOKEN" });

A third argument toggles options such as strict (prevents changing the dataset once set):

new SodaQuery("data.cityofnewyork.us", {}, { strict: true });

Building the query

Note: the builder methods return the SodaQuery instance, so you can chain them.

Select

A Select object transforms the columns returned by the query.

import { Select } from "jsr:@j3lte/soda";

Select("column_name"); // a column
Select(); // or Select("*") — all columns
Select("column_name").as("alias"); // column_name as alias

// Functions
Select("amount").count().as("counted"); // count(amount) as counted
Select("amount").avg(); // avg(amount)
Select("amount").sum(); // sum(amount)
Select("value").log(); // ln(value)
Select("name").unaccent(); // unaccent(name)
Select("name").upperCase(); // upper(name)
Select("created_date").dateExtractYear(); // date_extract_y(created_date)

query.select(
  Select("agency"),
  Select("amount").sum().as("total"),
);

See every method on SelectImpl.

Build a case(...) with SelectCase, which takes [condition, value] pairs (the condition is a Where or a raw SoQL string; add a trailing ["true", ...] default):

import { SelectCase, Where } from "jsr:@j3lte/soda";

query.select(
  SelectCase(
    [Where.gt("score", 90), "A"],
    [Where.gt("score", 80), "B"],
    ["true", "F"],
  ).as("grade"),
);

Where

A Where filters rows. It is built from static methods.

import { Where } from "jsr:@j3lte/soda";

Where.eq("borough", "MANHATTAN"); // borough = 'MANHATTAN'
Where.ne("status", "Closed"); // status != 'Closed'
Where.gt("score", 80); // score > 80
Where.between("score", 50, 100); // score between 50 and 100
Where.in("borough", "MANHATTAN", "BROOKLYN"); // borough in ('MANHATTAN','BROOKLYN')
Where.like("complaint_type", "Noise%"); // complaint_type like 'Noise%'
Where.isNull("closed_date"); // closed_date IS NULL
Where.isNotNull("closed_date"); // closed_date IS NOT NULL

// Combine
Where.and(
  Where.eq("borough", "BRONX"),
  Where.or(
    Where.eq("status", "Open"),
    Where.eq("status", "Pending"),
  ),
);

// From an object (all AND-ed equals)
Where.from({ borough: "BRONX", status: "Open" });

Bind a field once with Where.field:

Where.field("score").gt(80); // score > 80
Where.field("borough").in("MANHATTAN", "BROOKLYN");

Geospatial filters (work on Location / Point / Line / Polygon / Multi* fields):

Where.withinBox("the_geom", 40.78, -73.98, 40.74, -73.94);
Where.withinCircle("the_geom", 40.7128, -74.006, 1000); // radius in meters
Where.withinPolygon("the_geom", "MULTIPOLYGON (((...)))"); // WKT, longitude-first
Where.intersects("the_geom", "POINT (-73.98 40.75)");
Where.startsWith("complaint_type", "Noise"); // starts_with(...)

See every method on Where.

Field & DataType

Field(name, type) returns a typed field that enables type-checked use of Select / Where. The type comes from the DataType enum.

import { DataType, Field } from "jsr:@j3lte/soda";

Field("borough"); // untyped (DataType._Unknown)
Field("score", DataType.Number); // typed

import { DataType, Field, Select } from "jsr:@j3lte/soda";

// Fine
query.select(Select(Field("name", DataType.Text)).as("alias"));

// Throws — avg() is not valid on a Text field
query.select(Select(Field("name", DataType.Text)).avg());

| DataType | String | Available | Docs | | --- | --- | --- | --- | | Checkbox | "checkbox" | 2.0, 2.1, 3.0 | checkbox | | FixedTimestamp | "fixed_timestamp" | 2.0, 2.1, 3.0 | fixed_timestamp | | FloatingTimestamp | "floating_timestamp" | 2.0, 2.1 | floating_timestamp | | Line | "line" | 2.1, 3.0 | line | | Location | "location" | 2.0, 2.1, 3.0 | location | | MultiLine | "multiline" | 2.1, 3.0 | multiline | | MultiPoint | "multipoint" | 2.1, 3.0 | multipoint | | MultiPolygon | "multipolygon" | 2.1, 3.0 | multipolygon | | Number | "number" | 2.0, 2.1, 3.0 | number | | Point | "point" | 2.1, 3.0 | point | | Polygon | "polygon" | 2.1, 3.0 | polygon | | Text | "text" | 2.0, 2.1, 3.0 | text | | URL | "url" | 2.0, 2.1, 3.0 | url |

SystemFields exposes the :id, :created_at, and :updated_at system columns.

Order

Order.by(field).asc / .desc build order entries (.asc / .desc are getters). Plain strings work too.

import { Order } from "jsr:@j3lte/soda";

query.orderBy(
  Order.by("created_date").desc,
  Order.by("agency").asc,
);

query.orderBy("created_date DESC"); // a string also works

Group & Having

query
  .select(Select("borough"), Select("amount").sum().as("total"))
  .groupBy("borough")
  .having(Where.gt("total", 1000)); // having requires a groupBy

groupBy also accepts typed Field objects.

Expressions (expr)

expr builds raw SoQL expression strings — boolean (and/or) and arithmetic (add/sub/mul/div/mod/pow → + - * / % ^). Each call parenthesizes its result, so they nest safely.

import { expr, Select } from "jsr:@j3lte/soda";

expr.mul("price", "qty"); // (price * qty)
expr.div(expr.add("a", "b"), 2); // ((a + b) / 2)

query.select(Select(expr.mul("price", "qty")).as("total"));

Search & paging

query.search("noise"); // full-text search ($q)
query.limit(50).offset(100); // manual paging
query.withSystemFields(); // include :id / :created_at / :updated_at

Fetching results

execute & single

const { data, error, status } = await query.execute(); // Array of rows
const { data: row } = await query.single(); // first row, or null

Pagination

$limit defaults to 1000 rows per request. These iterate the whole result set, advancing $offset automatically. Set a stable order (e.g. orderBy(":id")) for reliable full scans.

// Lazy — one page (array) at a time
for await (const page of query.pages({ pageSize: 1000 })) { /* ... */ }

// Lazy — one row at a time
for await (const row of query.rows()) { /* ... */ }

// Eager — every row in one array
const { data } = await query.executeAll({ max: 50000 }); // optional row cap

count

const { data: total } = await query.where(Where.eq("borough", "BRONX")).count();
// total: number of matching rows ($select=count(*))

GeoJSON & CSV

const { data: geojson } = await query.executeGeoJSON(); // FeatureCollection
const { data: csv } = await query.executeCSV(); // raw CSV string

Column metadata

const { data: columns } = await query.getColumns();
// [{ fieldName: "the_geom", dataTypeName: "point", name, renderTypeName }, ...]

Response headers

After any request, read the last response's headers:

await query.execute();
query.headers.lastModified;
query.headers.etag;
query.headers.fields; // X-SODA2-Fields
query.headers.types; // X-SODA2-Types

Stored queries

prepare(id) snapshots the current query under an id and resets the builder, so you can run several queries from one instance:

query.select("a", "b").where("a > 1").prepare("first").clear();
query.select("c").prepare("second");

await query.execute("first");
await query.execute("second");
query.getURL("first"); // inspect the built URL

License

MIT