Roles Seed
Run locally for transcripts
π¨βπΌ Perhaps in the future we'll want pretty fine-grained roles and permissions,
but for now we really only have two roles:
admin- which can do anything to everythinguser- which can do anything to their own data
We want to apply these roles and permissions to all new users who are created.
That means that our database needs to have these values before we can create
any users. Our is intended to seed the
local development database, but we're going to need this seeding in production
as well.
So, what we need is to manually update our
migration.sql file we just made
for our permissions to create the roles and permissions. That way when we deploy
our updates to production, the production database will automatically get these
roles and permissions.The tricky bit is knowing how to write the SQL to create the roles and
permissions. If you're a SQL wiz, feel free to write it by hand, but for the
rest of us, here's a strategy:
- Create a temporary script file that generates a temporary database and seeds it with the data we want:
Feel free to use my script
import { PrismaClient } from '@prisma/client'
import { execaCommand } from 'execa'
const datasourceUrl = 'file:./tmp.ignored.db'
console.time('ποΈ Created database...')
await execaCommand('npx prisma migrate deploy', {
stdio: 'inherit',
env: { DATABASE_URL: datasourceUrl },
})
console.timeEnd('ποΈ Created database...')
const prisma = new PrismaClient({ datasourceUrl })
console.time('π Created permissions...')
const entities = ['user', 'note']
const actions = ['create', 'read', 'update', 'delete']
const accesses = ['own', 'any']
for (const entity of entities) {
for (const action of actions) {
for (const access of accesses) {
await prisma.permission.create({ data: { entity, action, access } })
}
}
}
console.timeEnd('π Created permissions...')
console.time('π Created roles...')
await prisma.role.create({
data: {
name: 'admin',
permissions: {
connect: await prisma.permission.findMany({
select: { id: true },
where: { access: 'any' },
}),
},
},
})
await prisma.role.create({
data: {
name: 'user',
permissions: {
connect: await prisma.permission.findMany({
select: { id: true },
where: { access: 'own' },
}),
},
},
})
console.timeEnd('π Created roles...')
console.log('β
all done')
-
Run the script with
node tmp.ignored.js -
Create a "dump" of all the data in the temporary database using
sqlite3(install instructions in exercise 9 of the Data workshop):sqlite3 ./prisma/tmp.ignored.db .dump > tmp.ignored.sql -
Copy the
INSERTstatements (only theINSERTstatements) from thetmp.ignored.sqlfile into the permissionsmigration.sqlfile inprisma/migrations.
Once you've done that, you can update
to assign the "user" roles to the generated users and the "admin" and "user"
roles to the
Kody user.Once you've finished updating the seed script, you can reset the database and
run the seed with:
npx prisma migrate reset --force
But before we wrap this up, you'll want to make sure we create all new users
with the
user role, otherwise new users won't have the right permissions!
