Skip to main content

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​

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"}

More details

Create multiple objects​

Add objects to a collection in bulk.

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"}]

More details

Read​

Get 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.

More details

Get object's 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.

More details

Get objects meeting a 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='Doe'
Vault Data API equivalent​

Operation: POST /api/pvlt/1.0/data/collections/customers/query

Body: {"props":"email,nationality","match":{ "last_name" : "Doe"} }

More details

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.

More details

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}

More details

On this page