Skip to main content

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.

Development

TODO:

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

License

MIT