# Using Rezoom [Home](/rezoom-sql/master.md) > Using Rezoom [← Asynchronous programming](/rezoom-sql/tutorial/async.md) | [Configuration →](/rezoom-sql/configuration.md) ## Introduction to Rezoom Although Rezoom.SQL can be used by itself, it is designed to work best with its parent library, Rezoom. Rezoom is a general purpose library for composing tasks that involve remote data access. ### A motivating example Consider the following simple example. ```fsharp open Rezoom open Rezoom.SQL open Rezoom.SQL.Synchronous type GetPermissions = SQL<"select * from UserPermissions where UserId = @userId limit 1"> type DeleteDocument = SQL<"update Documents set DeletedByUserId = @userId where Id = @docId"> let deleteDocument (userId : int) (documentId : int) (conn : ConnnectionContext) = let permissions = GetPermissions.Command(userId).ExecuteExactlyOne(conn) if not permissions.CanDelete then failwith "User does not have permission to delete documents" else DeleteDocument.Command(docId = documentId, userId = userId).Execute(conn) ``` This code might be OK by itself. It reads pretty clearly. Of course, in a real system you'd probably put an abstraction around reading the permissions, but that's just a matter of moving that code behind an interface. The problem I want to address with this code is what happens when you try to use it in a perfectly reasonable way: ```fsharp let deleteManyDocuments userId documentIds conn = for documentId in documentIds do deleteDocument userId documentId conn ``` This function is very bad! If we pass in 500 document IDs, we'll run 1000 SQL batches in total -- half of them pointlessly re-querying for the user's permissions. Every one of them bounces back and forth to the server. If you have a mere 5ms latency on each of thouse round-trips you are already at 5 seconds even assuming the queries themselves are executed instantly by the server. One solution would be to move the *real* implementation into `deleteManyDocuments`, and make `deleteDocument` the wrapper, instead of the other way around. This is an easy change to make here, but has its downsides when you try to use it as the universal solution to this type of problem: * All callers must also be coded to batch up their document IDs to pass into `deleteManyDocuments`, instead of calling `deleteDocument` immediately whereever needed. * The permissions check still isn't shared outside this scope: if higher-level code uses, say `deleteManyDocuments` and `deleteManyFoos`, they'll each do their own permission check. * Sometimes the batching logic is harder, obscuring the business logic and opening the door to bugs. For example, suppose `deleteManyDocuments` needed to accept a list of arbitrary `(userId, docId)` pairs. It would need to group by the user ID to avoid duplicate permission queries. This gets more frustrating with trickier requirements, such as checks that can be bypassed depending on other factors like the status of the document. Another solution is to say that it's not `deleteDocument`'s responsibility to check permissions. The caller should check permissions before calling it! This has an obvious downside of cluttering caller code and being easy to forget. At *some* level of your system, you'll really want to have a function that combines the permission check and the action it permitted, and then you're back to this problem. Finally, you could write a caching layer for obtaining permissions. This is probably the best approach so far, but can be a lot of work, especially making sure the cache gets invalidated correctly when permissions change. It also won't help at all with the 500 separate `update` commands. ### Solving it with Rezoom Using Rezoom, you can define `deleteDocument` as a `Plan`. When you see `Plan<'a>`, think: "some work that will eventually return an `'a`, possibly dependent on requests to external services". ```fsharp open Rezoom.SQL.Plans let deleteDocument (userId : int) (documentId : int) : Plan = plan { let! permissions = GetPermissions.Command(userId).ExactlyOne() if not permissions.CanDelete then failwith "User does not have permission to delete documents" else do! DeleteDocument.Command(docId = documentId, userId = userId).Plan() } let deleteManyDocuments (userId : int) (documentIds : seq) : Plan = plan { for documentId in documentIds do do! deleteDocument userId documentId } ``` Notice that these functions do not take a connection context. This is because a `Plan` is a *recipe for how* to run something. A plan doesn't *do x*, it says "if I only had a connection, I *could do x*". Here's an example of how to actually run such a `Plan`: ```fsharp open Rezoom open System.Threading.Tasks // `services` here is the host's IServiceProvider. See // [Runtime configuration](../../doc/Configuration/Configuration.md). In an // ASP.NET Core app it's typically obtained from DI as `PlanExecutor`. let example (services : System.IServiceProvider) = let plan : Plan = deleteManyDocuments 1 [1..500] let task : Task = PlanExecutor(services).Execute(plan) // Note: only use .Wait() if you want to wait synchronously for the task to finish task.Wait() ``` Executing this version of `deleteManyDocuments` will only query for permissions **once**. This is thanks to the static analysis provided by Rezoom.SQL, which tells Rezoom: 1. `GetPermissions` doesn't have side effects or use any nondeterministic functions like `random`, so its result can be cached for the rest of this transaction unless we update the data in the `UserPermissions` table. 2. `DeleteDocument` does have side effects, but doesn't touch the `UserPermissions` table, so it doesn't invalidate the cache for `GetPermissions`. The above code will still make 500 round-trips to the database since it runs the `DeleteDocument` commands one at a time. However, fixing this is also very simple. You would just change: ```fsharp for documentId in documentIds do ... ``` to: ```fsharp for documentId in batch documentIds do ... ``` Now the function will execute with **two round-trips** to the database: one containing the permissions query, another containing all the `update` statements to delete the documents. ### Benefits This automated caching and batching allows you to write very simple, self-contained units of business logic which you can compose into much more complex transactions without incurring massive performance costs. This is a breath of fresh air compared to typical database work, where to get acceptable efficiency you usually have to either write your logic in large chunks with minimal abstraction, or pass around a lot of shared state explicitly. If you design carefully, you can end up with a rich domain layer (100s of methods) that is completely ignorant of the SQL backend, and is built upon a relatively small (30-50 methods) persistence API. Such a small persistence API can be worth implementing twice: once with Rezoom.SQL for real world usage, and once in-memory for integration testing the domain logic. ### Caching and you If shivers ran down your spine when I mentioned caching, I don't blame you! Cached answers can be *wrong*. The old adage goes: > There are two hard things in computer science: cache invalidation, naming things, and off-by-one errors. So it's natural to be suspicious of a "magic" cache, especially when we're talking to an external database that could be updated by another thread or even by a program running on another machine somewhere. The automatic cache invalidation could be perfect for our own code, but how can it know about those external changes to the data? It doesn't know and it doesn't want to. * A `Plan` in Rezoom represents a short-lived chunk of work, which by default will be executed within a transaction. It does not represent long-running, ongoing tasks like polling loops. * The cache is local to each Plan's execution. When multiple plans are running at the same time (for example, servicing different web requests), they **do not** share any cached data. This means that Rezoom's automatic caching is intended to **simulate you explicitly passing already-loaded data** around between your functions, **without cluttering your interfaces with brittle implementation details**. #### More concretely A typical use case would be a web API, where in each endpoint you build a `Plan` from your domain layer and execute it. The execution of the `Plan` is the unit-of-work and runs in a transaction. Its cache is private, not shared with other `Plans` being executed to serve other API endpoints that are executing at the same time. The cache is only there so that within that single unit-of-work you don't requery the same things more than needed. We would often do this manually when working with Entity Framework or other ORMs, passing already-loaded stuff explicitly down into domain methods so they didn't have to hit the DB again in a loop. Rezoom just makes those annoying, error prone, explicit "here's what I already loaded" parameters unnecessary. *** [← Asynchronous programming](/rezoom-sql/tutorial/async.md) | [Configuration →](/rezoom-sql/configuration.md) --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://humbobst.gitbook.io/rezoom-sql/rezoom.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.