File Handling
Devii includes built-in blob storage for file management. This document provides guidance on using Devii's file storage system.
Setting Up File Tables
A file table associates each row with a file stored in remote storage via a storage key, which is a UUID; the Devii backend will manage the file's lifecycle of the record. Each file table must include one column designated as the storage key, which should be of type string (character, character varying, text) or UUID.
Configure file table and column in Database
Include a UUID column in the table, ideally named
storagekey, but any name is acceptable.Each table should have only one column used as a
storagekeyfor associating with a file.Use separate related tables to handle other files associated with the table.
Whitelist table
- The newly created table must be whitelisted for Devii access. Currently, whitelisting can only be done through the Devii portal. Documentation can be accessed HERE.
Set file table
We recommend using Devii Portal for easy configuration of your file tables. Link to Portal Docs HERE
- Table in the database; "user_avatar" (table_name)
- column name in table; "storagekey" (column_name)
GraphQL Example
Use the roles_pbac endpoint with the following query.
mutation save_file_table($column_name: String!, $table_name: String!) {
set_file_table(column_name: $column_name, table_name: $table_name) {
table_name
column_name
status
}
}
variables:
{
"column_name": "storagekey",
"table_name": "user_avatar"
}
JavaScript Example
//Helper function for mutations calling the roles_pbac url
async function rolesPbacMutation(ROLES_PBAC_URL, mutation, accessToken, variables = null) {
try{
// make fetch request to API
const response = await fetch(ROLES_PBAC_URL, {
method: "POST",
headers: {
"Content-Type": "application/json",
Authorization: `Bearer ${accessToken}`},
body: JSON.stringify({
query: mutation,
variables: variables // Include variables if provided
})
});
if (!response.ok) {
throw new Error("Network response was not ok: ", response.statusText);
}
const result = await response.json();
console.log("Mutation result: ", result);
}catch (error) {
console.log("Error performing mutation: ", error);
}
}
async function setFileTable() {
const mutation = `
mutation {
set_file_table(
input: {
table_name: "user_avatar"
column_name: "storagekey"
}
) {
table_name
column_name
}
}
`;
const accessToken = await getAccessToken();
if (!accessToken) {
console.log('Failed to retrieve access token.');
return;
}
console.log("Access Token:", accessToken);
await rolesPbacMutation(ROLES_PBAC_URL, mutation, accessToken);
}
Introspect Database
After setting up the file table, re-introspect the underlying database by clicking the 'Introspect Database' button and
ensuring you receive a success message. This process will add a new field, called thefiles: [FileObject], to the regular
schema in /query, on the object corresponding to your file table. This field will include a nested array of dictionaries that
provide the information needed to either embed a URL for referencing the stored data or to retrieve the generated URL.
Optional - create policy
Create a policy to allow users to access and modify file table
Roles and Role Classes need upload and download capabilities to access and modify the file table
Optional - verify file_table setup
Use the roles_pbac endpoint with the following query.
table_name: Table name. Must exist in the tenant schema.
column_name: Column name of storage key column. Must exist in table and be UUID or text type that can fit UUID values.
status: Validity of this file table association. If all is well, status will be 'OK'. Otherwise, this field reports the following error conditions:
table no longer exists, column no longer exists, or column type no longer valid.
| GraphQL | JSON Response |
|---|---|
| |
Use File Table
The protocol for file upload and validation involves both client-side and server-side processes. On the client side, the file upload begins, and upon
completion, the "completion" handler triggers a fetch() on the model after a brief delay to verify if the file status has changed from pending to ok.
On the server side, a background process continuously monitors file records with a pending status. This process checks for
the existence of the file blob, and validates the MIME type and size against the details recorded. If the MIME type and size match, the status is
updated to ok. If there is a discrepancy, such as a different MIME type or a size exceeding the recorded value, the status is marked as invalid. If the
blob is missing or the size is less than expected, the status remains pending. Once the status is updated to ok, the Devii server provides download URLs
when the thefiles attribute is queried. If the status is still pending, only upload URLs are provided. File size is measured in bytes.
Initiate the file upload
FileUpload is a GraphQL type in Devii, defined within the generated schema. To upload a file, create a record using the FileUpload type with the necessary data. Once you receive the response, use the provided upload URL and headers to complete the file upload.
You can find common MIME types at the following link: Common MIME Types.
The following query will give you the information needed to upload a file to Google Storage. Use the query endpoint with the following query.
| GraphQL | JSON Response |
|---|---|
variables | |
JavaScript example
async function initiateFileUpload() {
const mutation = `
mutation create_user_avatar($input: avatarsInput!) {
create_avatars(input: $input) {
user_id
storagekey
thefiles {
storagekey
filenum
filename
mimetype
size
status
headers
url
additional
}
}
}
`;
const variables = {
"input": {
"thefiles": [
{ "filename": "Belle.jpg", "mimetype": "image/jpg", "size": 233457 }
],
"user_id": 2
}
};
const accessToken = await getAccessToken();
if (!accessToken) {
console.log('Failed to retrieve access token.');
return;
}
try {
const response = await rolesPbacQueryMutation(QUERY_URL, mutation, accessToken, variables);
} catch (error) {
console.error("Error during file upload:", error);
}
}
Upload to storage
Your response will contain all the body parameters you need to upload to google storage, contained under headers.
"headers":
"{\"content-type\": \"image/jpg\",
\"key\": \"9c6ae072afad4d73833fabd0f964706c1${filename}\",
\"bucket\": \"devii-tenant10102dev\",
\"GoogleAccessId\": \"storage-bucket-accessor@nomadic-rite-375921.iam.gserviceaccount.com\",
\"policy\": \"eyJleHBpcmF0aW9uIjogIjIwMjQtMDgtMjNUMjA6MTc6MDkuNDcwMjE4WiIsICJjb25kaXRpb25zIjogW1sic3RhcnRzLXdpdGgiLCAiJGtleSIsICIiXSwgWyJlcSIsICIkQ29udGVudC1UeXBlIiwgImltYWdlL2pwZyJdLCBbImNvbnRlbnQtbGVuZ3RoLXJhbmdlIiwgMjMzNDU3LCAyMzM0NTddLCB7ImJ1Y2tldCI6ICJkZXZpaS10ZW5hbnQxMDEwMmRldiJ9XX0=\",
\"signature\": \"kv4atx3YEG6MsusoL/yPVhppw6zcFFxnr/JFpm2kYt1SqfmKtCgaaSyMrf7pj4lqYRmx6Gza2CBqurmdQDvWEfQbcHlIJVu6T6sTZNuDa9sTizJwKl4Cz4FWT1DUyIRdkmR+8OOPOy9ylJqkMvPmV/xMuWGtsMRPaSCldUZ8mmOmro/ImPbzASRivGFdfz1e/SafThBwHiIF8h6tjjq9LexSkv9nNnNN/FacVyfxKCZwt4niV32sjjnSquMEkvrw48sCWSkrCYLslN/+1CmF5E35rfYYB1RF3wzXnIxChGpcaWBrFFFjfIIa1tjiNcPpoZvlsQrE8RbIP9lNp9aZMg==\"}",
The key must have the ${filename} portion removed to successfully upload your file to Google Cloud Storage, which is our default storage provider. Do not replace the key in the headers with the storagekey. Note that removing the ${filename} portion is specific to Google Cloud Storage; other storage providers may not require you to remove it.
Postman
After the file record has been created in Devii.
Here is what Postman should look like to upload a file to the google storage URL from thefiles.
The response has a HTTP status code of 204: No Content. In the Postman console the file shows up as “undefined” but it does upload.
To verify upload has completed after a short amount of time the file's status will change from 'pending' to 'ok'.
Upload JavaScript Example
// Example Component
// Pass in the DOM file object as the first parameter, the JSON data from "thefiles"[0] as the second, then uploadFileToBlob will handle it.
function MyComponent(): JSX.Element {
const handleFileSelected = (e: React.ChangeEvent<HTMLInputElement>): void => {
const files = Array.from(e.target.files);
console.log("files:", files);
};
return <input id="file" type="file" onChange={handleChange} />;
}
import axios from "axios";
import { BlockBlobClient } from "@azure/storage-blob";
const fileUpload: any = {
filename: file?.name,
mimetype: file?.type,
size: file?.size,
};
const user_avatarsInput: any = {
thefiles: [fileUpload],
roleid,
};
const createUserAvatar = (something) => {
// Get file from input type file
const selectedFile = event.target.files[0];
if (selectedFile) {
// Access token and endpoints are provided from the Devii auth endpoint after login.
const access_token = result.data.access_token;
const query_endpoint = result.data.routes.query;
if (access_token) {
// Set up header
const queryConfig = {
headers: {
"Content-Type": "application/json",
Authorization: `Bearer ${access_token}`,
},
};
// Check if there is an existing file, if there is delete/destroy the current file.
const personid = 2;
const fileUpload: any = {
filename: selectedFile?.name,
mimetype: selectedFile?.type,
size: selectedFile?.size,
};
const person_avatarInput: any = {
thefiles: [fileUpload],
personid,
};
// Setup Devii Create Mutation Query
const query = `mutation ($input: person_avatarInput!) {
create_person_avatar(input: $input) {
personid
storagekey
thefiles {
storagekey
filenum
filename
mimetype
size
status
url
headers
additional
}
}
}`;
const data = new FormData();
data.append("query", query);
data.append(
"variables",
`{"input": ${JSON.stringify(person_avatarInput)}}`
);
// Submit Mutation Query
const queryresult = await axios.post(query_endpoint, data, queryConfig);
const blobStorageUrl =
queryresult.data.create_person_avatar.thefiles[0].url;
// Upload the file to the blob calling uploadFileToBlob function
await uploadFileToBlob(
blobStorageUrl,
selectedFile,
queryresult.data.create_person_avatar.thefiles[0].headers
);
}
}
};
const handleChange = (event: any) => {
if (inputFile) {
if (inputFile?.current?.files) {
setSelectedFile(inputFile.current.files[0]);
}
}
};
// Helper function to upload to blob storage (Azure and Google)
const uploadFileToBlob = async (
url: string,
file: any,
file_headers: any
) => {
if (!file) return [];
let service = "default";
if (url.includes("windows")) {
service = "AZURE";
} else if (url.includes("google")) {
service = "GOOGLE";
}
switch (service) {
case "AZURE":
const blockBlobClient = new BlockBlobClient(url);
await blockBlobClient.uploadBrowserData(file);
break;
case "GOOGLE":
default:
//First parse our headers and build the form input.
let headers = JSON.parse(file_headers);
let body = new FormData();
for (let key in headers) {
var val = headers[key];
//Google does something odd with the key here, using
//the filename as a template. Override.
if (key == "key") {
val = val.replace("${filename}", "");
}
body.append(key, val);
}
//Add the file itself.
body.append("file", file);
//Set up XHR...
var req = new XMLHttpRequest();
req.open("POST", url);
//And send it.
req.send(body);
}
};
Retrieve files
Using Devii's query endpoint, the following GraphQL query will retrieve the URL of the file you uploaded to storage.
| GraphQL | JSON Response |
|---|---|
variables | |
Download JavaScript Example
// Access token and endpoints are provided from the Devii auth endpoint after login.
const ACCESS_TOKEN = result.data.access_token;
const QUERY_ENDPOINT = result.data.routes.query;
const query = `{
person_avatar {
personid
storagekey
thefiles {
storagekey
filenum
filename
mimetype
size
status
url
headers
additional
}
}
}`;
// Configure query header information
const queryConfig = {
headers: {
"Content-Type": "application/json",
Authorization: `Bearer ${ACCESS_TOKEN}`,
},
};
// Set up payload to send to Devii
const data = new FormData();
data.append("query", query);
data.append("variables", `{}`);
// Send query
const queryresult = await axios.post(QUERY_ENDPOINT, data, queryConfig);
if (queryresult.data.person_avatar[0]) {
setImageScr(queryresult.data.person_avatar[0].thefiles[0].url);
} else {
console.log("no avatar");
}
Changes to file table
Update File table
Updating a file that has not been uploaded to storage yet will create an additional file number for that 'storagekey'
| GraphQL | JSON Response |
|---|---|
variables | |
Any updates you make to a file that was previously added to storage will result in needing to re-initiate the file upload and re-upload the information to your storage file for a current URL.
| GraphQL | JSON Response |
|---|---|
variables | |
Delete file
When a file record in the table is deleted and the file has not yet been added to storage, the record will be removed immediately, provided it is the only reference to that file. If a file is referenced by multiple records, possibly across different tables, we perform a count of these references upon deletion. The file will only be deleted from storage if no other references exist.
| GraphQL | JSON Response |
|---|---|
variables | |
Remove or Unset file table
This can be done in two ways:
- Using the portal, click on 'Storage' on the left side of the screen, then click on the trash can icon to remove the file table, you should receive a success message at the bottom left corner.
- Using a GraphQL interface (in this case the GraphiQL interface in Devii)
| GraphQL | JSON Response |
|---|---|
variables | |
After removing the file table it can take up to 15 minutes for the associated files, if they aren't reference by anything else, to be removed by our garbage collector, which runs every 15 minutes, if you add the same table and column as storage before the the garbage collector has run, the associations would be restored.