SODA Query

SODA (Socrata) Query Client for Deno & NodeJS.

Table of Contents

• SODA Query
  • Table of Contents
  • Features
  • Installation
  • Example
    • Plain query
    • SQL Builder
    • SodaQuery
  • Querying data
    • Select
    • Where
    • Field
    • Order
    • Group
  • Development
  • License

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