Starting Prisma with T3
How to setup and use Prisma using the Create-T3 app.
Overview
Prisma is a typescript ORM that allows you to interact with your database. Today we’ll setup a new application and switch between using postgres and sql server.
Stand up a new postgres server
sudo docker run --name youtubetest-postgres -p 5432:5432 -e POSTGRES_PASSWORD=Youtube1! -d postgres
Making sure you are on the latest LTS of node
# displays the current version of node
node -v
# will automatically install and use the latest lts version
nvm install --lts
node -v
# at the time of this writing it was v18.14.0
Using the T3 App
Link: https://create.t3.gg/ https://www.youtube.com/@t3dotgg
npm create t3-app@latest
Open in vs code and update the connection string for prisma
Link: https://www.prisma.io/
Use Postgres
# open the schema.prisma file
# change provider from sqlite to postgresql
# open the .env file and update the connection string
postgresql://postgres:Youtube1!@ubuntuserver/youtube-db
Do an initial migration
npx prisma migrate dev
Lets create another model
// YouTube Subscribers
model YouTubeSubscribers {
id Int @id @default(autoincrement())
subname String
email String
subscribedAt DateTime
}
Do another migration
npx prisma migrate dev
Use MS Sql Server
# open the schema.prisma file
# change provider to sqlserver
#open the .env and update the connection string
sqlserver://ubuntuserver:14333;database=youtube-db;user=sa;password=Youtube1!;encrypt=true;trustServerCertificate=true
Add a new table for Pets
CREATE TABLE [dbo].[PetNames](
[id] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](255) NOT NULL,
[birthdate] [datetime] NOT NULL,
CONSTRAINT [PK_PetNames] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
Pull in the new table with the prisma db pull command
npx prisma db pull
Do another migration
npx prisma migrate dev
# this will fail because our inital migration was for postgres
# delete the migrations folder and run the command again
What is we wanted to switch back to Postgres??
# open the schema.prisma file
# change provider from sqlite to postgresql
# open the .env file and update the connection string
postgresql://postgres:Youtube1!@ubuntuserver/youtube-db
Do another migration
npx prisma migrate dev
# this will fail because there is an attribute that postgres does not like
Special attributes from the pull don’t work between db systems, lets delete the extra attributes
# delete
id Int @id(map: "PK_PetNames") @default(autoincrement())
name String @db.VarChar(255)
birthdate DateTime @db.DateTime <------------------delete this
Do another migration
npx prisma migrate dev