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.
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)
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:
SET owner_name = 'Kyle'
WHERE id IN (1,2)
HarperDB supports deleting records from a table with condition support.
Sample DELETE statements:
DELETE FROM dev.dog
WHERE age <= 4
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.
HarperDB allows developers to join any number of tables and currently supports the following join types:
- INNER JOIN
- LEFT INNER JOIN
- LEFT OUTER JOIN
Learn more about how HarperDB allows users to use SQL Date Functions here.