Skip to main content
Deno 2 is finally here 🎉️
Learn more

SODA Query

GitHub release (latest by date) NPM Version GitHub Release Date GitHub code size in bytes GitHub GitHub last commit GitHub issues GitHub Workflow Status Codecov Deno docs

SODA (Socrata) Query Client for Deno & NodeJS.

Table of Contents

Features

  • Create SODA queries
  • Use SODA queries to fetch from Socrata Open Data API
  • Build complex queries with ease, in a functional way
  • SQL Builder, inspired by sql-builder

Note: This client is only for fetching data from Socrata Open Data API. It does not support creating, updating or deleting data.

Installation

Deno:

import { SodaQuery } from "https://deno.land/x/soda/mod.ts";

Node: (npm i soda-query)

import { SodaQuery } from "soda-query";

Example

Plain query

The SodaQuery class accepts plain strings in its methods:

import { SodaQuery } from "https://deno.land/x/soda/mod.ts";

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

You can also use the SQL Builder to create your queries:

import { Order, SodaQuery, Where } from "https://deno.land/x/soda/mod.ts";

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

// Using the SQL Builder
const { data, error } = await new SodaQuery(DOMAIN).withDataset(DATASET)
  .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();

SodaQuery

You can create a new SodaQuery instance by passing a domain and optionally an authOptions object and an options object.

import { createQueryWithDataset, SodaQuery } from "https://deno.land/x/soda/mod.ts";

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

const query = createQueryWithDataset("data.organization.com", "dataset-id");

Querying data

Note: Most methods return the instance of SodaQuery. This means that you can chain methods together.

Select

A Select object can be used to transform the data returned by the query.

import { Select, SodaQuery } from "https://deno.land/x/soda/mod.ts";

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

// Selecting columns
query.select(
  //...Select objects
);

// Just a column:
Select("column_name");

// Select all:
Select(); // or Select("*")

// Select with alias:
Select("column_name").as("alias");

// Select with function:
Select("column_name").count().as("counted");
Select("column_name").avg();
Select("column_name").sum();

See all methods in <SelectImpl> interface.

Where

A Where object can be used to filter the data returned by the query. It uses static methods to create the Where object.

import { SodaQuery, Where } from "https://deno.land/x/soda/mod.ts";

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

// Filtering data
query.where(
  //...Where objects
);

// Eq
Where.eq("column_name", "value");

// null
Where.isNull("column_name");
Where.isNotNull("column_name");

// Combined
Where.and(
  Where.eq("column_name", "value"),
  Where.or(
    Where.eq("column_name", "value"),
    Where.eq("column_name", "value"),
  ),
);

See all methods in <Where> interface.

Field

You can use the Field method that returns a FieldImpl object, which can be used to ensure type safety when using the Select and Where methods.

It uses the DataType enum to tell what type of data the field is.

DataTypes:

DataType String representation Socrata Type Notes
Checkbox "checkbox" Checkbox
FixedTimestamp "fixed_timestamp" Fixed Timestamp
FloatingTimestamp "floating_timestamp" Floating Timestamp
Line "line" Line
Location "location" Location
MultiLine "multiline" MultiLine
MultiPoint "multipoint" MultiPoint
MultiPolygon "multipolygon" MultiPolygon
Number "number" Number
Point "point" Point
Polygon "polygon" Polygon
Text "text" Text
URL "url" URL
ROWIdentifier "row_identifier" - Special tag that is only used internally, for the ‘:id’ column.
Unknown "_unknown" - Default type for a field, does not check types

These Datatypes can be used to define your fields:

import { Field, DataType } from "https://deno.land/x/soda/mod.ts";

// Just a field, will be of type FieldImpl<DataType.Unknown>
const field = Field("column_name");

// Field with type
const field = Field("column_name", DataType.Text);

If you define your fields like that instead of using strings, you can use the Select and Where methods with type safety:

import { Select, SodaQuery, Field, Where } from "https://deno.land/x/soda/mod.ts";

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

// This works fine
query.select(
  Select(Field("column_name", DataType.Text)).as("alias"),
);

// This will throw an error, as you cannot use `avg` on a text field
query.select(
  Select(Field("column_name", DataType.Text)).avg(),
);

Order

A Order object can be used to order the data returned by the query.

import { Order, SodaQuery } from "https://deno.land/x/soda/mod.ts";

const query = new SodaQuery("...");

// Ordering data
query.order(
  Order.by("column_name").asc(),
  Order.by("column_name2").desc(),
);

Group

You can use groupBy to group the data returned by the query.

query.groupBy(
  "column_name",
  "column_name2",
);

// Or with Fields
query.groupBy(
  Field("column_name", DataType.Text),
  Field("column_name2", DataType.Number),
);

Development

TODO:

  • Add case method
  • Missing undocemented functions (investigate this doc)
  • Improve docs (JSDoc categories etc)

License

MIT

"Buy Me A Coffee"