Rules the API Cannot See

Every night, the bookshop's supplier sends a file with new books. A small program, a batch job (a program that runs by itself, with nobody watching), reads the file and adds the books to the shop. The batch job does not go through the API. It uses the same data code as the API, and writes straight to the table. One night the file has a book with a price of -1. Nothing complains, and the shop now has a book with a negative price.

Our goal: refuse a bad book whoever saves it, and not only when it comes through the API.

Here is the route:

1. See the doors a book can use to reach the table, and which checks each door meets.
2. Make the service check every book it saves.
3. Give the table a rule of its own.
4. See who catches what.

This lesson shows only the lines that change, so the idea stays in view. It leaves out rules that need other data (such as "no two books with the same title"), turning the service's error into a 400 for the API (the API's own rules answer first, as more validation rules set up), and the web page's checks.

Three doors

A book can reach the table by three doors:

• The API checks every request against its rules first. That is the work of the last two lessons.
• The batch job uses the book service, so it never meets the request's rules. They belong to the API.
• A person with a database tool meets neither. The table is all there is.

Book already carries [Required] and [Range], but an attribute is only a label. Something has to read it and act. In the API, ASP.NET does. In the batch job, nothing does. So today only the first door has a guard.

To see it, a test saves a book with a price of -1 through the service, as the batch job would, and expects the service to refuse it:

[TestMethod]
public async Task CreateAsync_NegativePrice_IsRefused()
{
    var book = ValidBook();
    book.Price = -1m;

    await Assert.ThrowsExactlyAsync<ValidationException>(() => _service.CreateAsync(book));
}

It fails, and the message says it plainly:

Assert.ThrowsExactlyAsync failed. Expected exception type:<System.ComponentModel.DataAnnotations.ValidationException> but no exception was thrown.

The book with the price of -1 was saved. That is the gap.

The service checks the book

The service is the one class that every app using our data code goes through, so it is the right place for a check that applies to all of them. It already knows what a good book looks like: the attributes on Book. It only has to read them. In BookService, one line goes at the top of CreateAsync, and the same line at the top of UpdateAsync:

public async Task<Book> CreateAsync(Book book)
{
    Validator.ValidateObject(book, new ValidationContext(book), validateAllProperties: true);   // new
    _context.Books.Add(book);
    await _context.SaveChangesAsync();
    return book;
}

• Validator.ValidateObject reads the attributes on the book and checks every property against them. It is the checker that ASP.NET uses for requests, so the rules are the same ones.
• When a rule fails, it throws a ValidationException, and the save never happens. The message names the rule, for example The field Price must be between 0.01 and 10000.

The test now passes. The book with the price of -1 is refused.

Why an exception, when business rules and meaningful errors used a result for "not enough copies"? Not enough copies is an ordinary outcome that happens every week. A book that breaks its own rules means the caller made a mistake, and the service cannot do its job. That is what an exception is for.

Our own API will not meet this exception, because the request's rules fire first. If one ever did get through, the exception handler from errors, the professional way would answer with a 500, which is the right answer for "our own code sent a bad book".

💡 A new rule can show how sloppy the test data was. Four older service tests saved a book with only a title, and now failed with The Author field is required. The books in those tests were never complete. The fix is in the tests, not in the rule: give them a full book.

The table checks the price

Whatever the service does, a person with a database tool, or a script that never heard of our service, still gets through. The last line of defence is a rule inside the table itself, a check constraint: a condition the database tests on every row it saves, whoever saves it. If the condition fails, the database refuses the row.

EF Core describes the table in a method called OnModelCreating, in BookShopDbContext. The rule goes there:

protected override void OnModelCreating(ModelBuilder modelBuilder)                                // new
{                                                                                                 // new
    modelBuilder.Entity<Book>()                                                                   // new
        .ToTable(table => table.HasCheckConstraint("CK_Books_Price", "CAST(Price AS REAL) >= 0"));   // new
}                                                                                                 // new

• HasCheckConstraint takes a name for the rule and the condition, written in SQL. The name, CK_Books_Price, will appear in the error, so it should say which rule it is.
• CAST(Price AS REAL) turns the price into a number before comparing. SQLite keeps a decimal as text, and comparing text with a number can go wrong: in our first try, a plain Price > 0 let a price of 0 through. The cast avoids that. (A database with a real decimal type does not need it.)
• The rule is "not negative", and not the API's "at least one cent". The table's rule is its own decision, and we keep it simple: a negative price is wrong whoever says so. The API can be stricter.

The table already exists in bookshop.db, so the rule goes in as a migration, as in EF Core and SQLite:

dotnet ef migrations add AddPriceCheck --project src/BookShop.Data --startup-project src/BookShop.Api
dotnet ef database update --project src/BookShop.Data --startup-project src/BookShop.Api

EF Core compares the model with the last migration, sees the new rule, and writes a migration that adds it. Now try the third door. In DB Browser, on the Execute SQL tab, run:

INSERT INTO Books (Title, Author, Price, Stock, Category) VALUES ('Odd Price', 'Anon', -1, 1, 'Programming');

The table refuses it. The message ends with:

CHECK constraint failed: CK_Books_Price

(Your tool may word the start differently. The name of the rule is what to look for.)

⚠️ A trap on a table that already has data. A check applies to the rows that are already there, too. If the table holds a book with a negative price, the migration stops with the same CHECK constraint failed: CK_Books_Price. Find and correct those rows first, then run the migration again.

Who catches what

For a book with a price of -1:

Each layer catches what the one before it cannot reach, and they use different words: the API gives a friendly 400, the service throws an exception with a clear message, and the table gives a bare error. The table is the one that cannot be skipped.

Recap

• A book can reach the table by three doors: the API, an app that uses the service (such as a nightly batch job), and a person writing SQL. The API's rules guard only the first.
• An attribute such as [Range] is only a label until something reads it. ASP.NET reads it for requests. In the data project, nothing did.
• The service checks every book it saves with Validator.ValidateObject, using the attributes already on Book. A bad book throws a ValidationException, and nothing is saved.
• Adding a rule can break older tests that used half-filled books. Fix the data in the tests, not the rule.
• A check constraint is a rule inside the table, tested on every row by the database. HasCheckConstraint sets it, and a migration applies it.
• The table's rule is its own decision. Here it is "never negative", while the API's is "at least one cent".
• Adding a check to a table that already holds bad rows fails until those rows are corrected.

Questions to think about

Each question has its answer folded away underneath. Guess first, then open it to check.

1. Why can the API's rules not protect the table from a nightly batch job?

2. The service already checks every book. Why does the table need a rule of its own?

3. Does the table's rule have to be the same as the API's?

Next lesson: the first web page, which calls the books endpoint and shows the list.