HarperDB SQL Guide

The purpose of this guide is to describe the available functionality of HarperDB as it relates to supported SQL functionality.  The SQL parser is still actively being developed and this document will be updated as more features and functionality becomes available.  A high-level view of supported features can be found here.

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.

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.

Sample DELETE statements:

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 query for all records and attributes in 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 

Sample simple SELECT query for all records and attributes in the dev.dog table ORDERED BY age in ASC order:

SELECT * FROM dev.dog
  ORDER BY age

*The ORDER BY keyword sorts in ascending order by default. To sort in descending order, use the DESC keyword.

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
  
  

DATE FUNCTIONS

Learn more about how HarperDB allows users to use SQL Date Functions here.