SODA Query

SODA (Socrata) Query Client for Deno.

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();

Development

TODO:

Add case method
Missing functions (investigate this doc)
- centroid
- datetime_add_d
- datetime_add_mm
- datetime_add_hh
- datetime_add_ss
- datetime_diff
- is_empty

License

MIT