SQL to Vault mappings
Vault includes data access APIs that provide features similar to those in many SQL-typed databases.
This page presents mappings between some common SQL queries and the Piiano Vault REST API calls for those familiar with SQL.
These mappings are divided into:
- Create: queries that insert information.
- Read: queries that return information. These include powerful search options.
- Update: queries that replace information.
- Delete: queries that delete information.
Create
An object
Add an object to a collection.
SQL query
INSERT INTO customers (first_name, last_name) VALUES (@first_name, @last_name)
Vault Data API equivalent
Operation: POST /api/pvlt/1.0/data/collections/customers
Body: {"first_name":"@first_name","last_name":"@last_name"}
Though not shown here, all non-nullable properties in the schema of the collection must be included in the body of the request.
Objects
Add objects to a collection.
SQL query
BULK INSERT
Vault Data API equivalent
Operation: POST /api/pvlt/1.0/data/collections/customers?options=many
Body: [{"first_name":"@first_name","email":"@email"},{"first_name":"@first_name2","email":"@email2"}]
Read
An object with all properties
Return all the properties for an object.
SQL query
SELECT * FROM customers WHERE id=@id
Vault Data API equivalent
Operation: GET /api/pvlt/1.0/data/collections/customers?id={id}&options=unsafe
Vault limit access to the entire object record and encourage users to access only specific data. Therefore, requests for all properties are designated as "unsafe". However, this call only works if the user has permission to read all the properties within the collection. See About data access policies for more information.
Object with selected properties
Return an object's first and last name properties.
SQL query
SELECT first_name, last_name FROM customers WHERE id=@id
Vault Data API equivalent
Operation: GET /api/pvlt/1.0/data/collections/customers?id={id}?props=first_name,last_name
The preferred way to use Vault is to explicitly specifying the properties to read.
Objects meeting criteria
Return all objects where the last name is "Doe", returning email and nationality properties.
SQL query
SELECT email, nationality FROM customers WHERE last_name LIKE %Doe%
Vault Data API equivalent
Operation: POST /api/pvlt/1.0/data/collections/customers/query
Body: {"select":["email","nationality"],"where":"last_name LIKE '%Doe%'"}
The "where" property provided in the body supports several common SQL "where" clauses and is rigorously checked to eliminate any chance of SQL injection.
Update
Update an object's properties.
SQL query
UPDATE customers SET first_name=@first_name, email=@email WHERE id=@id
Vault Data API equivalent
Operation: PATCH /api/pvlt/1.0/data/collections/customers?id={id}
Body: {"first_name":"@first_name","email":"@email"}
Include the properties to update in the body.
Delete
Delete an object by ID.
SQL query
DELETE from customers WHERE id=@id
Vault Data API equivalent
Operation: DELETE /api/pvlt/1.0/data/collections/customers?id={id}