prisma test

.env

# Environment variables declared in this file are automatically made available to Prisma.
# See the documentation for more detail: https://pris.ly/d/prisma-schema#using-environment-variables

# Prisma supports the native connection string format for PostgreSQL, MySQL and SQLite.
# See the documentation for all the connection string options: https://pris.ly/d/connection-strings

# remember to replace {SERVER}

DATABASE_URL="sqlserver://{SERVER}.database.windows.net:1433;database=database-test-prisma;user=sampleLogin@server-test-prisma;password=samplePassword123!;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
DATABASE_URL="sqlserver://{SERVER}.database.windows.net:1433;database=shadow_database-test-prisma;user=sampleLogin@server-test-prisma;password=samplePassword123!;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"

migration.sql

-- CreateTable
CREATE TABLE [dbo].[User] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__User__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [updatedAt] DATETIME2 NOT NULL,
    [name] NVARCHAR(1000) NOT NULL,
    [email] NVARCHAR(1000) NOT NULL,
    [password] NVARCHAR(1000) NOT NULL,
    [storeId] INT NOT NULL,
    CONSTRAINT [PK__User__id] PRIMARY KEY ([id]),
    CONSTRAINT [User_email_unique] UNIQUE ([email]),
    CONSTRAINT [User_storeId_unique] UNIQUE ([storeId])
);

-- CreateTable
CREATE TABLE [dbo].[Address] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__Address__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [updatedAt] DATETIME2 NOT NULL,
    [name] NVARCHAR(1000) NOT NULL,
    [contactName] NVARCHAR(1000) NOT NULL,
    [zipCode] NVARCHAR(1000) NOT NULL,
    [streetName] NVARCHAR(1000) NOT NULL,
    [streetNumber] NVARCHAR(1000) NOT NULL,
    [district] NVARCHAR(1000) NOT NULL,
    [complement] NVARCHAR(1000) NOT NULL,
    [userId] INT NOT NULL,
    CONSTRAINT [PK__Address__id] PRIMARY KEY ([id]),
    CONSTRAINT [Address_userId_unique] UNIQUE ([userId])
);

-- CreateTable
CREATE TABLE [dbo].[Order] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__Order__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [updatedAt] DATETIME2 NOT NULL,
    [customerId] INT NOT NULL,
    [addressId] INT NOT NULL,
    [storeId] INT NOT NULL,
    CONSTRAINT [PK__Order__id] PRIMARY KEY ([id]),
    CONSTRAINT [Order_addressId_unique] UNIQUE ([addressId])
);

-- CreateTable
CREATE TABLE [dbo].[Store] (
    [id] INT NOT NULL IDENTITY(1,1),
    [createdAt] DATETIME2 NOT NULL CONSTRAINT [DF__Store__createdAt] DEFAULT CURRENT_TIMESTAMP,
    [updatedAt] DATETIME2 NOT NULL,
    [name] NVARCHAR(1000) NOT NULL,
    CONSTRAINT [PK__Store__id] PRIMARY KEY ([id])
);

-- AddForeignKey
ALTER TABLE [dbo].[User] ADD CONSTRAINT [FK__User__storeId] FOREIGN KEY ([storeId]) REFERENCES [dbo].[Store]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Address] ADD CONSTRAINT [FK__Address__userId] FOREIGN KEY ([userId]) REFERENCES [dbo].[User]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Address] ADD CONSTRAINT [FK__Address__userId] FOREIGN KEY ([userId]) REFERENCES [dbo].[User]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Address] ADD CONSTRAINT [FK__Address__userId] FOREIGN KEY ([userId]) REFERENCES [dbo].[User]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Order] ADD CONSTRAINT [FK__Order__customerId] FOREIGN KEY ([customerId]) REFERENCES [dbo].[User]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Order] ADD CONSTRAINT [FK__Order__addressId] FOREIGN KEY ([addressId]) REFERENCES [dbo].[Address]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

-- AddForeignKey
ALTER TABLE [dbo].[Order] ADD CONSTRAINT [FK__Order__storeId] FOREIGN KEY ([storeId]) REFERENCES [dbo].[Store]([id]) ON DELETE CASCADE ON UPDATE CASCADE;

schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs https//pris.ly/d/prisma-schema

datasource db {
  provider          = "sqlserver"
  url               = env("DATABASE_URL")
  shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}

generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["microsoftSqlServer"]
}

model User {
  id              Int       @id @default(autoincrement())
  createdAt       DateTime  @default(now())
  updatedAt       DateTime  @updatedAt
  name            String
  email           String    @unique
  password        String
  store           Store     @relation("Store", fields: [storeId], references: [id])
  storeId         Int
  addresses       Address[] @relation("Addresses")
  shippingAddress Address?  @relation("ShippingAddress")
  billingAddress  Address?  @relation("BillingAddress")
  order           Order[]
}

model Address {
  id           Int      @id @default(autoincrement())
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  name         String
  contactName  String
  zipCode      String
  streetName   String
  streetNumber String
  district     String
  complement   String
  userAddress  User     @relation("Addresses", fields: [userId], references: [id])
  userShipping User     @relation("ShippingAddress", fields: [userId], references: [id])
  userBilling  User     @relation("BillingAddress", fields: [userId], references: [id])
  userId       Int
  Order        Order?
}

model Order {
  id              Int      @id @default(autoincrement())
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt
  customer        User     @relation(fields: [customerId], references: [id])
  customerId      Int
  shippingAddress Address  @relation(fields: [addressId], references: [id])
  addressId       Int
  store           Store    @relation(fields: [storeId], references: [id])
  storeId         Int
}

model Store {
  id        Int      @id @default(autoincrement())
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  name      String
  owner     User?    @relation("Store")
  order     Order[]
}

setup.sh

location="Brazil South"

resource="resource-test-prisma"
server="server-test-prisma" # this has to be unique. remember to update the connection string in the .env file
database="database-test-prisma" # remember to update the connection string in the .env file
shadow_database="shadow_database-test-prisma" # remember to update the connection string in the .env file

login="sampleLogin"
password="samplePassword123!"

startIP=0.0.0.0
endIP=255.255.255.255

echo "Creating $resource..."
az group create --name $resource --location "$location"

echo "Creating $server in $location..."
az sql server create --name $server --resource-group $resource --location "$location" --admin-user $login --admin-password $password

echo "Configuring firewall..."
az sql server firewall-rule create --resource-group $resource --server $server -n AllowYourIp --start-ip-address $startIP --end-ip-address $endIP

echo "Creating $database on $server..."
az sql db create -g $resource -s $server -n $database -e Basic --max-size 2147483648 --zone-redundant false --yes

echo "Creating $shadow_database on $server..."
az sql db create -g $resource -s $server -n $shadow_database -e Basic --max-size 2147483648 --zone-redundant false --yes

teardown.sh

resource="resource-test-prisma"

az group delete --name $resource