RazorSQL DynamoDB SQL Support

RazorSQL provides the ability to connect to Amazon DynamoDB databases. It provides visual tools for creating and dropping tables, editing table data, and more. RazorSQL also allows users to use SQL syntax to execute SQL selects, inserts, updates, and deletes against DynamoDB databases and has specific syntax to allow the user to force a query or scan operation.

Listed below is information on the SQL syntax supported by RazorSQL with DynamoDB.

General Notes

The Amazon DynamoDB database does not natively support SQL. Any SQL statements executed in RazorSQL are translated into DynamoDB specific API calls by RazorSQL. RazorSQL does not support the full SQL standard for DynamoDB. Listed below are the select, select_query, select_scan, insert, update, and delete SQL syntax supported by RazorSQL.

Select

RazorSQL supports select statements against single tables when querying DynamoDB. Selecting all attributes via a select * statement or selecting specific attributes by adding the attribute names to the select clause are supported. Joins against multiple tables are not supported. RazorSQL also supports adding filter criteria to the where clause of SQL statements. See the examples below for more information.

select * from table_name select * from employee select attribute_name1, attribute_name2, . . . attribute_namen from table_name select first_name, last_name, address from employee select count(*) from table_name select count(*) from employee When filtering data via a where clause, RazorSQL supports the =, !=, <>, like, not like, and in operators for character data and =, !=, <>, >, >=, <, <=, and in operators for numeric data. The "is null" and "is not null" options are also supported. select * from table_name where string_attribute = 'value' select * from employee where first_name = 'John' select * from table_name where numeric_attribute < value select * from employee where salary < 40000 select * from table_name where string_attribute like 'value%' select * from table_name where string_attribute not like '%value%' select * from table_name where string_or_numeric_attribute in ('value1', 'value2') select * from table_name where string_or_numeric_attribute is not null When filtering data, only "and" clauses are supported. "Or" clauses are not supported. select * from employee where first_name = 'John' and salary < 40000

Select Query

RazorSQL includes a "select_query" syntax to force a query to use a query operation instead of relying on RazorSQL to determine whether to use a query or a scan operation. The select_query syntax can also be used to execute a query operation on a specific index if the index name is passed as a hint to the query. Here are examples of using the select_query syntax: select_query * from table_name where hash_attribute = value select_query * from CustomerOrders where CustomerId = 'alice@example.com' select_query * from table_name where hash_attribute = value and range_attribute > value select_query * from CustomerOrders where CustomerId = 'alice@example.com' and OrderId > 1 select_query /* IndexName */ * from table_name where hash_attribute = value and index_column < value select_query /* OrderCreationDateIndex */ * from CustomerOrders where CustomerId = 'bob@example.com' and OrderCreationDate < 20130131;

Select Scan

RazorSQL includes a "select_scan" syntax to force a query to use a scan operation instead of a query operation. Here is an example of the select_scan syntax: select_scan * from CustomerOrders where CustomerId = 'alice@example.com'

Insert

RazorSQL adds its own support for DynamoDB SQL insert statements. RazorSQL requires the insert statement to be in a specific syntax to be accepted. Listed below is the required syntax.

insert into table_name (attribute_name_list) values (value_list) INSERT INTO employee(first_name, last_name, salary) VALUES ('John', 'Smith', 40000)

To insert data into a set, separate each value in the set with the | character. For example:

INSERT INTO string_set_test1(id, sset) VALUES (1, 'one|two')

Update

RazorSQL adds its own support for DynamoDB SQL update statements. RazorSQL requires the update statement to be in a specific syntax to be accepted. All keys for the table must be specified in the update where clause. If the table only has a HASH key, that key must be in the where clause. If the table has both a HASH and a RANGE key, both keys must be in the where clause.

update table_name set attribute_name = value where hash_key_attribute_name = value [and range_key_attribute_name = value] update employee set first_name = 'John' where id = 44 update forum set status = 'Deleted' where forum_name = 'Sports' and forum_subject = 'Hockey'

Delete

RazorSQL adds its own support for DynamoDB SQL delete statements. RazorSQL requires the delete statement to be in a specific syntax to be accepted. All keys for the table must be specified in the delete where clause. If the table only has a HASH key, that key must be in the where clause. If the table has both a HASH and a RANGE key, both keys must be in the where clause.

delete from table_name where hash_key_attribute_name = value [and range_key_attribute_name = value] delete from employee where id = 1