CRUD Operations and Schema Testing for SaaS Databases

Previously in this course, we covered Refactoring for Query Efficiency: Optimizing Your SaaS Schema, where we learned how to align our physical design with read-heavy access patterns. Now that your schema is optimized, it’s time to move from theory to practice.

In this lesson, we will perform CRUD (Create, Read, Update, Delete) operations. This is not just about moving data; it is the ultimate test of your schema validation. If your constraints are too loose, bad data will slip in. If they are too rigid, your application logic will break.

The Logic of CRUD Testing

Think of your schema as a set of house rules. You’ve defined the rooms (tables) and the doorways (foreign keys), but until you actually move furniture in, you don’t know if the floor plan works.

When we perform CRUD operations, we are verifying that:

1. Constraints work: Do NOT NULL or CHECK constraints prevent bad data?
2. Relationships hold: Do foreign keys prevent orphan records?
3. Defaults trigger: Do auto-populated fields (like created_at) behave as expected?

Worked Example: Testing the Subscription Flow

Let’s use our Advanced Subscription Modeling: Features and Junction Tables project as the test bed. We need to verify that we can create a subscription for a user and then update their status.

1. Create (INSERT)

First, we insert a record. If this fails, your schema likely has a missing required field or a broken foreign key.

SQL
INSERT INTO subscriptions (account_id, plan_id, status, start_date)
VALUES (101, 2, 'active', CURRENT_TIMESTAMP);

Verification: If this returns an error, check that account_id 101 actually exists in your accounts table. This is the first test of your relational integrity.

2. Read (SELECT)

Retrieval is the most common operation. You must test that your joins correctly pull data across the junction tables we designed earlier.

SQL
SELECT a.name, p.plan_name, s.status
FROM subscriptions s
JOIN accounts a ON s.account_id = a.id
JOIN plans p ON s.plan_id = p.id
WHERE s.status = 'active';

3. Update (UPDATE)

This is where you test business logic. What happens if a user upgrades their plan?

SQL
UPDATE subscriptions
SET plan_id = 3, updated_at = CURRENT_TIMESTAMP
WHERE account_id = 101;

Pro Tip: Always include a WHERE clause. Updating without one is a common disaster that wipes out entire tables.

4. Delete (DELETE)

Finally, test your cascade rules. If you delete an account, what happens to the subscription?

SQL
DELETE FROM subscriptions WHERE account_id = 101;

If you set up ON DELETE CASCADE in your DDL, this will clean up the subscription automatically. If you didn’t, the database should throw a constraint violation error. Both outcomes are "correct"—but only if they match your intended business rules.

Hands-on Exercise: The "Lifecycle" Test

To verify your schema, perform the following sequence in your local environment:

1. Create: Insert a new user and a new account.
2. Relate: Attempt to create a subscription for that account using an invalid plan_id. (It should fail).
3. Correct: Insert a valid plan_id and complete the subscription.
4. Update: Change the subscription status to 'canceled'.
5. Delete: Attempt to delete the account while the subscription is still active. Observe if the foreign key constraint prevents this (as it should).

Common Pitfalls

• Ignoring Foreign Key Errors: When a query fails, developers often drop the constraint to "fix" the problem. Don’t. If it fails, your data model is protecting you from a logical error. Fix the data, not the schema.
• Testing with "Clean" Data: Always test with edge cases. What happens if you try to UPDATE a null value into a column that is NOT NULL?
• Over-relying on ORMs: Many developers use tools like Prisma or Eloquent. While helpful, you must occasionally run raw SQL to ensure you understand what the database is actually doing.

FAQ

Q: How do I know if my schema is "validated"? A: A schema is validated when you can perform your core business actions (Create User, Subscribe to Plan, Cancel) without manual intervention or data corruption.

Q: Should I use transactions when testing? A: Yes. Use BEGIN; and ROLLBACK; to test operations without permanently altering your test data.

Recap

CRUD operations are the heartbeat of your application. By testing your schema against INSERT, SELECT, UPDATE, and DELETE queries, you ensure that your Designing for Scalability: Planning Your Schema for Growth efforts weren't just academic exercises but solid foundations for a real product.

Up next: We will dive into Understanding Indexes to see how we can speed up those SELECT queries we just wrote.