SQL statements in the data model

This page describes the SQL statements used to implement CRUD functionality for models based on the sql storage type.

Zendro uses the promise-based ORM Sequelize to make the needed database calls. Sequelize provides a Model class to represent tables in a database, with instances of this class representing single rows; in Zendro, model classes are extended from this class.

We’ll use the model event from the Breeding API to see how various Zendro commands translate into SQL.

Table of contents

1. Case ‘Create’
2. Case ‘Read’
3. Case ‘Update’
4. Case ‘Delete’

---

Case ‘Create’

The following GraphQL command is given to Zendro:

mutation{addEvent(eventType:"Test Event") {eventType}}

This is transformed into the following SQL:

START TRANSACTION;
INSERT INTO "events" ("eventType","createdAt","updatedAt") VALUES ('Test Event','2020-06-03 13:01:11.715 +00:00','2020-06-03 13:01:11.715 +00:00') RETURNING *;
COMMIT;

Case ‘Read’

The following GraphQL command is given to Zendro:

{events{eventType}}

This is transformed into the following SQL:

SELECT count(*) AS "count" FROM "events" AS "event";
SELECT count(*) AS "count" FROM "events" AS "event";
SELECT "eventType", "eventDbId", "eventDescription", "eventTypeDbId", "studyDbId", "date", "createdAt", "updatedAt" FROM "events" AS "event" LIMIT 1 OFFSET 0;

Case ‘Update’

The following GraphQL command is given to Zendro:

mutation{updateEvent(eventType:"Test Event", eventDbId:"1") {eventType eventDbId}}

This is transformed into the following SQL:

START TRANSACTION;
SELECT "eventType", "eventDbId", "eventDescription", "eventTypeDbId", "studyDbId", "date", "createdAt", "updatedAt" FROM "events" AS "event" WHERE "event"."eventType" = 'Test Event';
UPDATE "events" SET "eventDbId"='1',"updatedAt"='2020-06-03 13:02:55.800 +00:00' WHERE "eventType" = 'Test Event'
COMMIT;

Case ‘Delete’

The following GraphQL command is given to Zendro:

mutation{deleteEvent(eventType:"Test Event")}

This is transformed into the following SQL:

SELECT "eventType", "eventDbId", "eventDescription", "eventTypeDbId", "studyDbId", "date", "createdAt", "updatedAt" FROM "events" AS "event" WHERE "event"."eventType" = 'Test Event';
SELECT count(*) AS "count" FROM "eventParameters" AS "eventParameter" WHERE "eventParameter"."eventDbId" = 'Test Event';
SELECT "eventType", "eventDbId", "eventDescription", "eventTypeDbId", "studyDbId", "date", "createdAt", "updatedAt" FROM "events" AS "event" WHERE "event"."eventType" = 'Test Event';
DELETE FROM "events" WHERE "eventType" IN (SELECT "eventType" FROM "events" WHERE "eventType" = 'Test Event' LIMIT 1)