SODA Query

SODA (Socrata) Query Client for Deno & NodeJS.

Features
Installation
Quick start
- Plain query
- SQL Builder
Creating a query
Authentication
Building the query
Fetching results
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