HarperDB SQL Guide

The purpose of this guide is to describe the available functionality of HarperDB as it relates to supported SQL functionality.  A high-level view of supported features can be found here. The HarperDB SQL parser is in active development, as such this document will be updated regularly to reflect new features & functionality.

HarperDB adheres to the concept of schemas & tables.  This allows developers to isolate table structures from each other all within one database. 

NOTE: The HarperDB SQL parser puts all schema/table/attribute names to lowercase. If you do have schema element names which are mixed case reference this article.

INSERT

HarperDB supports inserting 1 to n records into a table.  The only constraint is the hash_attribute must be defined and supplied a unique value per row. HarperDB does not support selecting from one table to insert into another at this time.

Sample INSERT statement:
INSERT INTO dev.dog (id, dog_name, age, breed_id)
VALUES(1, 'Penny', 5, 347),
(2, 'Kato', 4, 347)

UPDATE

HarperDB supports updating existing table row(s) via UPDATE statements. Multiple conditions can be applied to filter the row(s) to update. At this time selecting from one table to update another is not supported.  Mathematical & date transforms are forthcoming.

Sample UPDATE statement:
UPDATE dev.dog
SET owner_name = 'Kyle'
WHERE id IN (1,2) 

DELETE

HarperDB supports deleting records from a table with condition support. At this time condition support related to math & date transforms are not supported but will are in active development.

Sample DELETE statement:
DELETE FROM dev.dog
WHERE age <= 4

SELECT

HarperDB has robust SELECT support, from simple queries all the way to complex joins with multi-conditions, aggregates, grouping & ordering.

All results are returned as JSON object arrays.

Sample simple SELECT to query all records and attributes from the dev.dog table:
SELECT * FROM dev.dog

Sample SELECT to query specific columns from all rows in the dev.dog table:
SELECT id, dog_name, age FROM dev.dog

JOINS

HarperDB allows developers to join any  number of tables and currently supports the following join types:

• INNER JOIN
• LEFT INNER JOIN
• LEFT OUTER JOIN