Express JS Prisma ORM Query Fundamentals

Set up the environment by installing tools, including Visual Studio Code and Docker, and build Docker containers for Postgres and Express to prep for database work.

Install Visual Studio Code and learn to create, connect, and manage projects across Windows, macOS, and Linux, using a built-in terminal, autosave, and basic interface customizations.

Explore how linting and formatting improve code quality by using Prettier as the default formatter in VS Code, and enable editor format on save for JavaScript and TypeScript projects.

Learn how Docker Desktop simplifies installing, managing, and running software using images, containers, and volumes; pull official images from Docker Hub and run Postgres in containers for consistent development.

Set up a postgres database in Docker using docker compose, configure a postgres user and database via environment variables, map port 5432, and verify by logging into the container database.

Set up Express and Prisma inside Docker using Docker Compose, connect to a Postgres container, and map local code via volumes for a consistent development environment.

Define a Prisma-based database schema in Express.js, model tables and relationships, migrate changes to the database, and automate table creation for new containers.

Learn how an entity relationship diagram visualizes tables, fields, and keys to design and build a relational database and support query creation.

Define the database schema with Prisma by configuring the Prisma generator and the Postgres data source, and model eight tables with maps for their database names.

Explore migrating a Prisma schema to a database by generating and applying migrations, versioning changes, rolling back if needed, and evolving tables across environments.

Explore how prisma maps common data types—strings, numbers, and booleans—to Postgres, including varchar, text, integers, smallint, decimal, float, and boolean, and learn field type mapping in schemas.

Prisma uses the DateTime type to store both date and time as a timestamp in Postgres, with createdAt and updatedAt fields for auditing and an option to allow null values.

Explore how Prisma schema defines required, optional, and default fields to enforce data integrity when inserting records, using examples like user and product tables, default values, and now for timestamps.

Learn how unique constraints in Prisma enforce uniqueness across table fields and combinations, using unique fields, alternate keys, and composite constraints to prevent duplicates and enforce data integrity.

Learn how to define a primary key as the unique, non-null identifier for each record using an auto-incrementing integer ID in Prisma schemas, with a surrogate key across tables.

Explore foreign keys and one-to-many relationships in Prisma ORM, detailing how to model category and product links, user orders, and self-referencing, crow's foot relationships for hierarchical categories.

Explore referential actions in Prisma: define on delete behavior for foreign keys, including cascade, restrict, set null, and no action, with category and product and stock management examples.

Define many-to-many relationships between products and promotion events and between orders and products. Create a link table with foreign keys, add a composite unique constraint, and prepare for cross-table queries.

Establish a one-to-one relationship by adding a unique foreign key on the stock management table referencing the product id. Ensure each product links to a single stock management record.

Finalize Prisma schema from the ERD, detailing category hierarchies with self-referencing keys and unique slugs and names; define product, promotion, stock, and orders relations, then migrate and prepare Prisma Studio.

Explore how opposite relation fields enable bidirectional queries in Prisma, define and name relations on both sides, handle one-to-one, one-to-many, and many-to-many through explicit join tables, and ensure successful migrations.

Learn how Prisma Studio provides a lightweight graphical interface to view, edit, and manage your database during development without SQL, including setup, migration, and model inspection.

Automate Prisma migrations in docker by cleaning existing migrations, running Prisma migrate dev, and provisioning a database from your Prisma schema.

Build and manage rest APIs in express.js by performing create, update, delete, and bulk operations. Explore seeding databases, handling one-to-one, one-to-many, and many-to-many relationships, and document endpoints with Swagger.

Set up an Express application with routes and controllers, start docker containers, and create a router to handle root endpoints, enabling API requests and database interaction.

Learn to insert records from an express.js app, starting with the category table and its dependencies, using Prisma client via a post endpoint with JSON body parsing and error handling.

Document and visualize your Express API endpoints using OpenAPI with Swagger and JSDoc, and serve an interactive docs interface via Swagger UI Express at /docs, enabling try-out requests.

Automate server restarts with node monitor to instantly reload on code changes, eliminating manual Docker container restarts and ensuring a live development server via npm run dev.

Learn how to perform bulk inserts in Prisma using createMany with Express.js, including validating array input, handling duplicates with skipDuplicates, and improving API and database efficiency.

Update records in Prisma with the update method, using a where clause on a unique field (id, name, or slug) to modify a single row and return the updated data.

Demonstrate Prisma's upsert on the category table, updating a record when a matching name and slug exists or inserting a new one, using where, update, and create with swagger testing.

Perform bulk updates in prisma by using the update many method to modify multiple category records identified by names, validating a non-empty input array and updating fields like is_active.

Create a post endpoint to insert a category and its products using express and prisma, leveraging the one-to-many relation and returning the category with included products.

Learn to handle one-to-one inserts with Prisma by creating a new product and its stock management record in a single operation, using reverse relations and including stock details.

Create a two-step workflow to handle many-to-many inserts with Prisma: first create a user, then an order linked to multiple products via the order product link table.

Learn how to build a Prisma delete endpoint that uses find unique to locate a record, handles on delete options, and supports cascade or soft delete for safe data management.

Build an endpoint to bulk delete categories using Prisma delete many, accepting and validating an array of category IDs from the request body, returning the deleted count.

Explore Prisma basics for querying with find many, select, and include, applying filtering, sorting, pagination, and dynamic queries; automate Postgres seeding with Docker to initialize the database.

Automate database seeding with Docker by starting the database container, creating tables, and populating data while removing migrations and using Prisma to connect and query.

Learn how to use Prisma's findMany to retrieve multiple records, filter with query parameters, and select fields to return in a product-table example.

Apply Prisma's where clause to filter records with conditions on fields, including the boolean isActive and category ID, using find many or find unique, and combine filters for precise queries.

Create dynamic endpoints using query parameters, path parameters, and request bodies to filter, sort, and paginate data with Prisma findMany.

Explore how Prisma's order by sorts query results by one or more fields in ascending or descending order. Learn to specify the field and direction, with multi-field ordering using objects.

Retrieve the first record with Prisma's find first by applying where filters and an order by clause to fetch the active product based on price.

Implement pagination in Prisma using take and skip to limit results and improve performance for large datasets, with client-specified page and page size via query parameters.

Apply prisma's distinct to remove duplicate category IDs when querying products, returning a unique list of category IDs for each product and enabling clean reports.

Explore core filtering techniques with Prisma to efficiently query data, covering logical operators, value ranges, pattern matching, and list filtering for optimized, fast data retrieval.

Master using logical operators and, or, and not in Prisma queries to filter data with and/or/not conditions in findMany.

Use Prisma comparison operators to filter records by equals, not equals, less than, less than or equal to, greater than, and greater than or equal to, via a post endpoint.

Explore Prisma pattern matching with contains, starts with, and ends with to search product names using a single endpoint, with case-insensitive filters and when to add a dedicated search engine.

Use the Prisma in operator to filter products by a list of category IDs, converting client input to a number array and querying with find many.

Prisma lacks a between operator; use greater than or equal to and less than or equal to to simulate ranges, such as finding products priced between 100 and 500.

Master list slicing in Prisma by applying take and skip to limit results, paginate a product list, and optimize data retrieval for a responsive app.

Explore querying related data across tables with Prisma ORM, covering joins, relationships, and query optimization. Learn basic database theory and how raw SQL can enhance Prisma queries.

Explore SQL inner joins, foreign keys, and how Prisma uses relations, select and include to fetch related data from products and categories.

Explore aliases in raw SQL to shorten table and column names, improving readability and joins. Prisma lacks select aliases, so rename fields in JavaScript after querying.

Learn how to use Prisma's raw SQL queries to access database-specific features, handle legacy data, and perform complex admin tasks like migrations and index creation.

Explore querying a 1-to-1 relationship between products and stock management, building an endpoint to fetch a product with stock and category data via Prisma's findUnique and include.

Master querying many-to-many relationships using a link table, traversing from products to promotion events and from users to orders to products with nested includes.

Demonstrate how Prisma emulates inner and left joins by querying products, categories, and promotions, using filters like some to return related data while handling null results.

Explore full outer joins, which return all records from both tables with nulls for unmatched rows, and simulate them in Prisma by merging left join results.

Master aggregation to extract insights from databases using count, sum, and average, plus grouping and filtering aggregated results.

Learn how to use Prisma's count method to count rows with optional where conditions, including active products, category filters, and unique users with orders, using grouping and aggregations.

Master Prisma's aggregate sum to total product prices directly in the database, and learn when to fetch data or use raw SQL for cross-table calculations like stock value.

Discover how to use Prisma's aggregate avg function to calculate the average of numeric fields, such as product price. See examples of averaging order quantities and stock, with active filtering.

Group by lets you aggregate data by common fields, such as grouping products by category ID to count items and compute the average price with Prisma.