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 limits access to all the properties in object records and encourages users to access only specific data. Requests for all properties require "unsafe" to be set in the options query parameter for the relevant REST API operations and the --all-unsafe or -a flags in relevant CLI commands. However, an "unsafe" call only works if the user has an IAM policy, allowing them to read all the properties within the collection.

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