prisma-drizzle-lnl

Prisma & Drizzle lunch & learn

What's included

Swagger does not works with types, so it's not included in this project. You can always create models manually or use a generator to create models from the types/schema

Global (for both Prisma & Drizzle)

• Docker compose with mysql database
• Bruno schema for the API bruno.json
• Table User, UserInfo, Role & Permission (all types of relationships)
• Logger to log time spent on each request
• Exception filter with custom error response
• Custom decorators for validation with valibot schemas
• Inherit types from schemas
• Satifies schemas types with DB types
• Auto logging of queries
• Seeder to seed the database with data
• Migrations
• CRUD operations
• Transactions findAll()
• Raw query findAllWithMaturity()
• Cte query findAllWithPermissionsCount()
• Exclude fields from response findOne()
• Add fields to response findOne()
• "Cascade" delete delete()

Prisma

• Automatically omit password field from User
• Generic search in findAll() with schemas validation PrismaSearchQuerySchema
  • Includes pagination
  • Includes sorting (keys are not validated)
  • Includes search (not validated JSON string, follow prisma where clause and must be encoded)

Drizzle

• Omit password field from User
• findAll() only include paginations, too hard to make it work with generic search/sorting, probably feasible with a lot of work
• Both core and relational queries

Prisma

• Documentation
• npx prisma format will format the schema file
• npx prisma studio will open the studio to see the data in the database
• npx prisma db seed will seed the database with the data in the seed file specified in package.json
• npx prisma generate will generate the client in node_modules/@prisma/client and can be imported in the code for type safety
• npx prisma migrate dev will create a migration and apply it to the database, you pass the --name flag to give a name to the migration (or it will be prompted). Will also generate the client
• npx prisma migrate deploy will apply the migration to the database without creating a migration file (qa, staging, production)
• npx prisma migrate reset will reset the database, apply all migrations and seed data
• npx prisma db push will apply the schema to the database without migration, useful for prototyping locally before using migrations docs
• No automatic down migrations. How to do down migrations docs

General comments

• Lots of community generators here
• Really mature with lots of feature/documentation, but some keys one are still mising (like cte queries)
• Hard to do stuffs outside of the "basic" workflow (need to use raw queries)
• Really easy to do simple "CRUD" operations
• Schema definition is simple but lacks "raw" sql options

Drizzle

• Documentation
• npx drizzle-kit studio will open the studio to see the data in the database
• No build-in seed command, use .ts file to seed the database (npm run drizzle:seed in this project)
• npx drizzle-kit generate will generate migrations based on the schema file
• npx drizzle-kit migrate will apply the migrations to the database
• npx drizzle-kit drop will drop the selected migration (CLI prompt to select)
• npx drizzle-kit push will apply the schema to the database without migration, useful for prototyping locally before using migrations
• There is not reset command
• There is no down migrations but it is in the work
• Drizzle has a config file drizzle.config.ts to configure some options

General comments

• A plugin exist to create validation schemas from the database schema, but the one for valibot is not up to date pr
• Migrations are not the best, clearly designed for push workflow (for exemple, if a migration fails midway, you need to manually revert and apply the migrations), update is in the work
• Schema definitions is verbose but similar to SQL
• Core queries (sql likes)
  • More control over the queries, but more verbose
  • Easy to do stuff outside of the "basic" workflow
  • More verbose to do "CRUD" operations
  • Typing is exceptional, but doing generic things while keeping typesafety is harder/impossible
  • They're is a way to return information from the query, but not supported in mysql
• Relationnal queries (v2 on the way)
  • Only for querying, not for creating/updating
  • Schema relations are applications level only, not enforced by the database
  • Aggregations are not supported in 'extras' fields for now
  • No way to do CTE queries

How to run locally

• npm i to install dependencies
• Copy the .env.example to .env
• docker-compose up to start the database
• Import the bruno.json file in Bruno
• npm run prisma:generate to generate the Prisma client
• npm run prisma:migrate-deploy to apply the migrations
• npm run prisma:seed to seed the database
• npm run drizzle:migrate to apply the Drizzle migrations
• npm run drizzle:seed to seed the database
• npm run dev to start the server

You can import the bruno file to have api endpoints