SODA Query

GitHub code size in bytes GitHub GitHub last commit GitHub issues GitHub Workflow Status Codecov Deno docs

SODA (Socrata) Query Client for Deno.

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

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

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 <SelectObject> 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.

Development

TODO:

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

License

MIT