Shopify Template

Code to create the needed Postgres objects to sync with Shopify.

CREATE TYPE published_scope AS ENUM ('web', 'global');

CREATE TYPE sort_order AS ENUM ('alpha-asc', 'alpha-desc', 'best-selling', 'created', 'created-desc', 'manual', 'price-asc', 'price-desc');

CREATE TYPE state AS ENUM ('disabled', 'invited', 'enabled', 'declined');

CREATE TYPE status AS ENUM ('active', 'archived', 'draft');

CREATE TYPE tax_exemptions AS ENUM ('EXEMPT_ALL', 'CA_STATUS_CARD_EXEMPTION', 'CA_DIPLOMAT_EXEMPTION', 'CA_BC_RESELLER_EXEMPTION', 'CA_MB_RESELLER_EXEMPTION', 'CA_SK_RESELLER_EXEMPTION', 'CA_BC_COMMERCIAL_FISHERY_EXEMPTION', 'CA_MB_COMMERCIAL_FISHERY_EXEMPTION', 'CA_NS_COMMERCIAL_FISHERY_EXEMPTION', 'CA_PE_COMMERCIAL_FISHERY_EXEMPTION', 'CA_SK_COMMERCIAL_FISHERY_EXEMPTION', 'CA_BC_PRODUCTION_AND_MACHINERY_EXEMPTION', 'CA_SK_PRODUCTION_AND_MACHINERY_EXEMPTION', 'CA_BC_SUB_CONTRACTOR_EXEMPTION', 'CA_SK_SUB_CONTRACTOR_EXEMPTION', 'CA_BC_CONTRACTOR_EXEMPTION', 'CA_SK_CONTRACTOR_EXEMPTION', 'CA_ON_PURCHASE_EXEMPTION', 'CA_MB_FARMER_EXEMPTION', 'CA_NS_FARMER_EXEMPTION', 'CA_SK_FARMER_EXEMPTION');

CREATE TABLE public."Collects"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Collection" uuid,
"Created at" timestamp without time zone,
"Position" decimal,
"Product" uuid,
"Shopify Record ID" text,
"Updated at" timestamp without time zone
);

CREATE TABLE public."Custom Collections"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Description" text,
"Handle" text,
"Image" text,
"Published at" timestamp without time zone,
"Shopify Record ID" text,
"Sort order" sort_order,
"Title" text,
"Updated at" timestamp without time zone
);

CREATE TABLE public."Customers"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Addresses" text,
"Created at" timestamp without time zone,
"Default address" text,
"Email" text,
"Email marketing consent" text,
"First name" text,
"Last name" text,
"Last Order" text,
"Multipass identifier" decimal,
"Note" text,
"Orders count" decimal,
"Password" text,
"Password confirmation" text,
"Phone" text,
"Shopify Record ID" text,
"Sms marketing consent" text,
"State" state,
"Tax Exemption" boolean,
"Tax exemptions" tax_exemptions[],
"Total spent" decimal,
"Updated at" timestamp without time zone,
"Verified email" boolean
);

CREATE TABLE public."Images"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Created at" timestamp without time zone,
"Height" decimal,
"Image" text,
"Position" decimal,
"Shopify Record ID" text,
"Updated at" timestamp without time zone,
"Width" decimal
);

CREATE TABLE public."Options"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Name" text,
"Position" decimal,
"Shopify Record ID" text,
"Values" text[]
);

CREATE TABLE public."Products"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Created at" timestamp without time zone,
"Default Image" text,
"Description" text,
"Handle" text,
"Images_fk_Images" uuid[],
"Options_fk_Options" uuid[],
"Point of Sale" published_scope,
"Product type" text,
"Published at" timestamp without time zone,
"Shopify Record ID" text,
"Status" status,
"Tags" text[],
"Template suffix" text,
"Title" text,
"Updated at" timestamp without time zone,
"Variants_fk_Variants" uuid[],
"Vendor" text
);

CREATE TABLE public."Variants"(
"id" uuid PRIMARY KEY DEFAULT gen_random_uuid(),
"Barcode" text,
"Created at" timestamp without time zone,
"Image" uuid,
"Inventory quantity" decimal,
"Option 1" text,
"Option 2" text,
"Option 3" text,
"Position" decimal,
"Price" decimal,
"Shopify Record ID" text,
"Sku" text,
"Title" text,
"Updated at" timestamp without time zone,
"Weight (g)" decimal,
"Weight (lb)" decimal
);

ALTER TABLE public."Collects"
ADD CONSTRAINT collects_collection_custom_collections_fkey
FOREIGN KEY ("Collection")
REFERENCES public."Custom Collections"("id");

ALTER TABLE public."Collects"
ADD CONSTRAINT collects_product_products_fkey
FOREIGN KEY ("Product")
REFERENCES public."Products"("id");

ALTER TABLE public."Variants"
ADD CONSTRAINT variants_image_images_fkey
FOREIGN KEY ("Image")
REFERENCES public."Images"("id");

Useful for Experimentation and Testing

-- Delete data:
TRUNCATE public."Customers" CASCADE;
TRUNCATE public."Collects" CASCADE;
TRUNCATE public."Custom Collections" CASCADE;
TRUNCATE public."Products" CASCADE;
TRUNCATE public."Options" CASCADE;
TRUNCATE public."Variants" CASCADE;
TRUNCATE public."Images" CASCADE;

-- Delete enums and tables:
DROP TABLE public."Customers";
DROP TABLE public."Collects";
DROP TABLE public."Custom Collections";
DROP TABLE public."Products";
DROP TABLE public."Options";
DROP TABLE public."Variants";
DROP TABLE public."Images";
DROP TYPE sort_order;
DROP TYPE state;
DROP TYPE tax_exemptions;
DROP TYPE published_scope;
DROP TYPE status;