Their Side

(x => x.Tags.Contains("vip")); // :0 = ANY("tags") db.Select

(x => x.Tags.Length > 2); // cardinality("tags") > :0 db.Select

(x => x.Tags.Any(t => t.StartsWith("v"))); // EXISTS (SELECT 1 FROM unnest("tags") ...) db.Select

(x => x.Scores.Count(s => s >= 90) >= 2); ``` ### Complex types need to be stored as JSON To query a property, OrmLite needs to have saved it as JSON, as an RDBMS's JSON functions can't read any other format. That's the default for dialects configured with `AddOrmLite()`, and can be enabled on others: ```csharp // The default of dialects configured with AddOrmLite() services.AddOrmLite(options => options.UsePostgres(connectionString)); // Or set on a dialect provider PostgreSqlDialect.Provider.UseJson = true; ``` Querying a complex property on a dialect that doesn't store JSON throws a `NotSupportedException` that says how to enable it, rather than silently returning nothing. If you're switching an existing App to `UseJson`, note that it changes how complex types are saved, so rows saved as JSV need to be converted to JSON before they can be queried. ## JSON in string columns Sometimes the JSON isn't a property of your model. It's an event body, an integration response or an audit snapshot that your App stores as text. A `string` has no properties to query, so `Sql.Json()` says what the document in it looks like, with a C# Data Model: ```csharp public class OrderEvent { public long Id { get; set; } public string Data { get; set; } // JSON of an OrderDocument public OrderDocument Document { get; set; } // A complex property, also stored as JSON } var q = db.From() .Where(x => Sql.Json(x.Data).Customer.Address.State == "WA" && Sql.Json(x.Data).Tags.Contains("priority") && Sql.Json(x.Data).Lines[0].Quantity > 1); ``` Nested member access becomes a nested JSON path, array indexes become zero-based JSON array indexes (constants or captured values), `[DataMember(Name = "shipping_state")]` is honored, and operations on an extracted scalar like `.State.Length == 2` continue through OrmLite's normal expression translation. Selecting a scalar member extracts its C# value, and selecting an object or collection returns a JSON fragment that OrmLite deserializes into your Data Model: ```csharp var q = db.From() .Select(x => new { x.Id, State = Sql.Json(x.Data).Customer.Address.State, Total = Sql.Json(x.Data).Total, Address = Sql.Json(x.Data).Customer.Address, }); var summaries = db.Select(q); // Or a single typed fragment var address = db.Scalar

(db.From() .Where(x => x.Id == id) .Select(x => Sql.Json(x.Data).Customer.Address)); ``` | JSON is in | Use | |-|-| | A complex type property, on a dialect with `UseJson` | The property: `x.Address.City` | | A `string` column | `Sql.Json(x.Data)` with the type of its document | | A complex type property, on a dialect without `UseJson` | `Sql.Json(x.Address)` | | A property with the type of a table the query joins | `Sql.Json(x.Customer)` | The last row is a subtle one. In a query that joins `Customer`, OrmLite has always resolved `x.Customer.Name` to the `Name` column of the joined table. If the property is *also* stored as JSON, e.g. a copy of the customer as they were when an invoice was created, use `Sql.Json(x.Customer).Name` to query the copy instead of the table. ## Explicit paths for everything else Some JSON questions don't have a C# Data Model. The document may have no fixed shape, the path may only be known at runtime, or you may want to ask something about the JSON itself rather than its values. For those there's an explicit SQL/JSON path API. Paths begin with `$` and use the portable subset every database supports: members, quoted members and zero-based array indexes, e.g. `$.Customer.Address.shipping_state` or `$.Lines[0].Quantity`. ```csharp var statePath = "$.Customer.Address.shipping_state"; // chosen at runtime var q = db.From() .Where(x => Sql.JsonValue(x.Data, statePath) == "WA" && Sql.JsonExists(x.Data, "$.Tags[0]")) .OrderByDescending(x => Sql.JsonValue(x.Data, "$.Total")); ``` Query values are db params, and paths are emitted as escaped SQL string literals for the dialect. The full set is: | Task | Typed expression | Explicit path API | | --- | --- | --- | | Read scalar | `Sql.Json(json).Member` | `Sql.JsonValue(json, path)` | | Read object or array | `Sql.Json(json).Member` | `Sql.JsonQuery(json[, path])` | | Array length | `Sql.Json(json).Items.Count` | `Sql.JsonArrayLength(json[, path])` | | Scalar array membership | `Sql.Json(json).Items.Contains(value)` | `Sql.JsonArrayContains(json[, path], value)` | | Array indexing | `Sql.Json(json).Items[index]` | Include `[index]` in the path | | Validate JSON | - | `Sql.IsJson(json)` | | Path exists | - | `Sql.JsonExists(json, path)` | | Read JSON type | - | `Sql.JsonType(json[, path])` | | Document containment | - | `Sql.JsonContains(json, candidate[, path])` | ### Missing, null and absent JSON has a distinction most SQL doesn't: a property can be missing, present with a `null` value, or present with a value. The portable API keeps all three distinguishable where the databases allow it, with `JsonExists()` telling you whether the path exists at all and `JsonType()` returning a normalized `JsonValueType` (`Null`, `String`, `Number`, `Boolean`, `Array` or `Object`) independent of each database's native type names: | Expression | JSON `null` | Missing path | Object or array | | --- | --- | --- | --- | | `JsonExists()` | `true` | `false` | `true` | | `JsonType()` | `JsonValueType.Null` | `null` | `Object` or `Array` | | `JsonValue()` | SQL `NULL` | SQL `NULL` | SQL `NULL` | | `JsonQuery()` | SQL `NULL` | SQL `NULL` | JSON fragment | | `JsonArrayLength()` | SQL `NULL` | SQL `NULL` | Count for arrays; `NULL` for objects | Use them together to tell a property that was never set from one that was explicitly cleared: ```csharp // true when the first element exists, even when it is JSON null Sql.JsonExists(x.Data, "$.NullableTags[0]") var type = db.Scalar(db.From() .Where(x => x.Id == id) .Select(x => Sql.JsonType(x.Data, "$.Customer.Address"))); // JsonValueType.Object ``` ### Validate and contain `Sql.IsJson()` tests whether a string holds a valid JSON value, which is useful for finding bad rows before they break a query: ```csharp var validJsonRows = db.Count(x => Sql.IsJson(x.Data) == true); ``` Extraction functions can report an RDBMS error given malformed JSON, and a database is free to evaluate predicates in any order, so don't rely on `IsJson(...) && ...` to protect another JSON operation. Validate JSON when it's written, or use a database constraint. On PostgreSQL and MySQL, `Sql.JsonContains()` tests whether a document contains a partial document, with the candidate sent as a db param: ```csharp var candidate = new { Customer = new { Address = new { shipping_state = "WA" } } }; var q = db.From() .Where(x => Sql.JsonContains(x.Data, candidate)); var requiredTags = new[] { "priority" }; var tagged = db.From().Where(x => Sql.JsonContains(x.Data, requiredTags, "$.Tags")); ``` SQLite and SQL Server throw `NotSupportedException`, as they have no equivalent operation with the same semantics. ## One query, four databases Each API is translated to the native functions of the configured database: | Operation | SQLite | PostgreSQL | SQL Server | MySQL | | --- | --- | --- | --- | --- | | Validate | `json_valid` | `IS JSON` | `ISJSON` | `JSON_VALID` | | Scalar value | `json_extract` | `jsonb_path_query_first` | `JSON_VALUE` | `JSON_EXTRACT` | | Object/array | `json_extract` | `jsonb_path_query_first` | `JSON_QUERY` | `JSON_EXTRACT` | | Exists | `json_type` | `jsonb_path_exists` | `JSON_PATH_EXISTS` | `JSON_CONTAINS_PATH` | | Type | `json_type` | `jsonb_typeof` | `OPENJSON` | `JSON_TYPE` | | Array length | `json_array_length` | `jsonb_array_length` | `OPENJSON` | `JSON_LENGTH` | | Array membership | `json_each` | `jsonb` containment | `OPENJSON` | `JSON_CONTAINS` | | Document containment | - | `@>` | - | `JSON_CONTAINS` | SQL Server 2016-2019 can use `JsonValue()`, `JsonQuery()`, `JsonType()`, `JsonArrayLength()` and `JsonArrayContains()`. Use `SqlServer2022Dialect.Provider` when you need the complete API, including `JsonExists()`. You can always see what was generated. JSON expressions are normal `SqlExpression` queries, so `ToSelectStatement()` and `Params` show the SQL and its parameters, and they work with the async APIs too. ## Keeping JSON queries fast A query of a JSON property reads the JSON of every row, which is fine until the table gets large. When a property is queried often, e.g. to find customers by `x.Address.City`, store it in a column of its own and index it with `[Index]`. That works the same way on every database: ```csharp public class Customer { [Index] public string City { get; set; } public Address Address { get; set; } } ``` SQLite and PostgreSQL can also index the expression a query reads the property with. Copy the expression from the SQL of the query, as it has to match exactly, and create the index in a migration: ```csharp var q = db.From().Where(x => x.Address.City == "London"); var sql = q.ToSelectStatement(); // ... WHERE (CASE WHEN json_type("Address", '$.City') NOT IN ('object','array','null') // THEN json_extract("Address", '$.City') END = @0) Db.ExecuteSql(""" CREATE INDEX ix_customer_city ON "Customer" ((CASE WHEN json_type("Address", '$.City') NOT IN ('object','array','null') THEN json_extract("Address", '$.City') END)) """); ``` Check the index is used with `EXPLAIN QUERY PLAN` on SQLite or `EXPLAIN` on PostgreSQL, and check it again after upgrading OrmLite, as it's only used while the query's SQL stays the same. SQL Server and MySQL can only index an expression through a computed or generated column, so a column of its own is the better choice for them. The new `db.Explain()` and the Slowest Queries view in the Admin UI help find the queries worth indexing. ## Get Started Querying complex type properties is available in **ServiceStack v10.4**, and works on every dialect configured with `AddOrmLite()`. Upgrade your `ServiceStack.OrmLite.*` packages and remove the `Sql.Json()` wrappers around your complex properties, if you have any. See the docs for the complete reference: - [OrmLite JSON Support](https://docs.servicestack.net/ormlite/json) - complex type queries, `Sql.Json()`, explicit paths, validation, containment and indexing - [Type-safe JSON access and Upserts](/posts/omlite-json-upsert) - the v10.2 introduction to `Sql.Json()` - [v10.4 Release Notes](https://docs.servicestack.net/releases/v10_04) - everything else in this release # Advanced SQL queries in typed OrmLite Source: https://servicestack.net/posts/ormlite-advanced-queries :::youtube 1e5TXrOapqg Advanced SQL with a Typed C# ORM: Window Functions, CTEs & Temporal Tables ::: Typed queries cover the first 90% of an App's SQL very well. Then comes the query that's easy to describe and awkward to write: *every subject under Fiction, at any depth*, *each customer's 3 latest orders*, *everyone who has written or reviewed a book*, *the next page of 50 after the last row I saw*. Those are the queries that sent code back to hand-written SQL strings, with their string concatenation, their per-database differences and their lack of refactoring. **ServiceStack v10.4** adds typed support for the ones that come up most, in the same fluent `SqlExpression` style as the rest of OrmLite. They're parameterized, they compose with your filters, joins and [connection filters](/posts/ormlite-multitenancy), and they run on the databases that support them: - **`WithRecursive()`** for hierarchies like categories, org charts and threaded comments - **`With()`** to name sub queries as common table expressions - **Window functions** for rankings, running totals, previous rows, and `TopPerGroup()` - **`Union()`, `Intersect()` and `Except()`** to combine queries - **`SeekAfter()`** for fast, stable keyset paging - **`SelectLazyAsync()`** to stream large result sets with `await foreach` ## Query hierarchical data with WithRecursive Hierarchies are everywhere, and walking one used to mean a recursive common table expression written by hand. `WithRecursive()` starts with the rows of a seed query and repeatedly adds the rows matching a relationship: ```csharp // Fiction and every subject under it, at any depth var q = db.From() .WithRecursive( seed: db.From().Where(x => x.Name == "Fiction"), recurse: (parent, child) => child.ParentId == parent.Id); var subjects = db.Select(q); ``` ```sql WITH RECURSIVE "cte" ("Id", "ParentId", "Name", "Active") AS ( SELECT "Id", "ParentId", "Name", "Active" FROM "Subject" WHERE ("Name" = @0) UNION ALL SELECT "c"."Id", "c"."ParentId", "c"."Name", "c"."Active" FROM "Subject" "c" INNER JOIN "cte" ON ("c"."ParentId" = "cte"."Id") ) SELECT "Id", "ParentId", "Name", "Active" FROM "cte" "Subject" -- @0 = 'Fiction' ``` Swapping the relationship walks up the hierarchy instead, e.g. the path from a subject to the root: ```csharp var ancestors = db.From() .WithRecursive( seed: db.From().Where(x => x.Id == subjectId), recurse: (child, parent) => parent.Id == child.ParentId); ``` The rest of the query applies to all the rows found, so typed filters, ordering, projections, `Count()`, paging, async APIs and keyset pagination work as usual: ```csharp var q = db.From() .WithRecursive( seed: db.From().Where(x => x.Id == 1), recurse: (parent, child) => child.ParentId == parent.Id) .Where(x => x.Active) .OrderBy(x => x.Name) .Select(x => x.Name); var names = await db.ColumnAsync(q); ``` ```sql WITH RECURSIVE "cte" ("Id", "ParentId", "Name", "Active") AS ( SELECT "Id", "ParentId", "Name", "Active" FROM "Subject" WHERE ("Id" = @0) UNION ALL SELECT "c"."Id", "c"."ParentId", "c"."Name", "c"."Active" FROM "Subject" "c" INNER JOIN "cte" ON ("c"."ParentId" = "cte"."Id") ) SELECT "Name" FROM "cte" "Subject" WHERE "Active"=1 ORDER BY "Name" -- @0 = 1 ``` Real data has edge cases, and the options cover them. `maxDepth` limits how many levels are selected, `Sql.RecursiveDepth()` is the level of each row, and `detectCycles` stops at rows that were already visited, for data that can have loops: ```csharp // Books, its children and grandchildren, a level at a time var q = db.From() .WithRecursive( seed: db.From().Where(x => x.Name == "Books"), recurse: (parent, child) => child.ParentId == parent.Id, maxDepth: 2, detectCycles: true) .OrderBy(x => Sql.RecursiveDepth()) .Select(x => new { x.Id, x.Name, Depth = Sql.RecursiveDepth() }); ``` Raw SQL statements that start with a CTE, e.g. `WITH roots AS (...) SELECT ...`, are now also executed as-is on all databases. ## Name sub queries with With `With()` names a sub query as a common table expression, which the rest of the query reads like a table. A sub query that's needed in several places is written once, and a complex query can be built in steps that are each simple to read. The columns of the sub query are described by a class, in the order it selects them: ```csharp public class AuthorTotal { public string Author { get; set; } public int Books { get; set; } public decimal Total { get; set; } } var totals = db.From() .GroupBy(x => x.Author) .Select(x => new { x.Author, Books = Sql.Count("*"), Total = Sql.Sum(x.Price) }); // Each book since 1970, with how many books its author has var q = db.From() .With(totals) .Join((b, t) => b.Author == t.Author) .Where(b => b.Year >= 1970) .Select((b, t) => new { b.Title, b.Author, t.Books }); ``` ```sql WITH "AuthorTotal" ("Author", "Books", "Total") AS ( SELECT "Author", Count(*) AS Books, Sum("Price") AS Total FROM "Book" GROUP BY "Author" ) SELECT "Book"."Title", "Book"."Author", "AuthorTotal"."Books" AS "Books" FROM "Book" INNER JOIN "AuthorTotal" ON ("Book"."Author" = "AuthorTotal"."Author") WHERE ("Book"."Year" >= @0) -- @0 = 1970 ``` As the sub query is named after a class, it's used with the same typed APIs as a table, including `db.From()` to select from it. A query can have several sub queries where each can read the ones before it, and they can be combined with `WithRecursive()`. ## Rankings, running totals and top rows per group Window functions calculate a value for each row from the rows related to it, like its rank within a group, a running total or the previous row's value. They're the right tool for a whole family of reporting questions, and they now have typed APIs instead of raw SQL: ```csharp var q = db.From() .Select(x => new { x.Id, x.Customer, x.Total, Rank = Sql.Rank(w => w.PartitionBy(x.Customer).OrderByDescending(x.Total)), RunningTotal = Sql.Sum(x.Total, w => w.PartitionBy(x.Customer).OrderBy(x.CreatedDate)), PreviousTotal = Sql.Lag(x.Total, w => w.PartitionBy(x.Customer).OrderBy(x.CreatedDate)), WeeklyAverage = Sql.Avg(x.Total, w => w.OrderBy(x.CreatedDate).RowsBetween(6, 0)), }); ``` ```sql SELECT "Id", "Customer", "Total", RANK() OVER (PARTITION BY "Customer" ORDER BY "Total" DESC) AS "Rank", SUM("Total") OVER (PARTITION BY "Customer" ORDER BY "CreatedDate") AS "RunningTotal", LAG("Total") OVER (PARTITION BY "Customer" ORDER BY "CreatedDate") AS "PreviousTotal", AVG("Total") OVER (ORDER BY "CreatedDate" ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS "WeeklyAverage" FROM "Order" ``` `RowNumber`, `Rank`, `DenseRank`, `Ntile`, `Lag`, `Lead`, `FirstValue`, `LastValue` and the `Sum`, `Count`, `Min`, `Max` and `Avg` aggregates are supported. ### Top rows of each group *Each customer's 3 latest orders* is one of the most common queries that needs window functions, and one of the easiest to get subtly wrong. `TopPerGroup()` returns the first rows of each group in the order of the query: ```csharp // Each customer's 3 latest orders var latest = db.Select(db.From() .OrderByDescending(x => x.CreatedDate) .TopPerGroup(x => x.Customer, take: 3)); ``` ```sql SELECT "Id", "Customer", "Total", "CreatedDate" FROM ( SELECT "Order".*, ROW_NUMBER() OVER (PARTITION BY "Customer" ORDER BY "CreatedDate" DESC) AS "_rn" FROM "Order" ) "Order" WHERE "Order"."_rn" <= 3 ORDER BY "CreatedDate" DESC ``` Window functions are supported on PostgreSQL, SQL Server, SQLite 3.25+, MySQL 8+, MariaDB and Oracle. ## Combine queries with Union, Intersect and Except Typed queries can now be combined with `Union()`, `UnionAll()`, `Intersect()` and `Except()`, without dropping down to raw SQL. Each query selects the same number of compatible columns, and its params are merged automatically: ```csharp // Everyone who has written or reviewed a book var q = db.From().Select(x => x.Author) .Union(db.From().Select(x => x.Reviewer)); var people = db.Column(q); // Fantasy books or anything under $9 var q2 = db.From().Where(x => x.Genre == Genre.Fantasy).Select(x => x.Title) .Union(db.From().Where(x => x.Price < 9m).Select(x => x.Title)); ``` ```sql -- Everyone who has written or reviewed a book SELECT "Author" FROM "Book" UNION SELECT "Reviewer" FROM "BookReview" -- Fantasy books or anything under $9 SELECT "Title" FROM "Book" WHERE ("Genre" = @0) UNION SELECT "Title" FROM "Book" WHERE ("Price" < @1) -- @0 = 'Fantasy', @1 = 9 ``` `Intersect()` returns rows in both queries, and `Except()` returns rows in the first query that aren't in the other, which is a tidy way to ask what's missing: ```csharp var available = db.From().Where(x => x.Available).Select(x => x.Id); var reviewed = db.From().Select(x => x.BookId); var availableAndReviewed = db.Column(available.Clone().Intersect(reviewed)); var awaitingReviews = db.Column(available.Clone().Except(reviewed)); ``` ```sql -- availableAndReviewed SELECT "Id" FROM "Book" WHERE "Available"=1 INTERSECT SELECT "BookId" FROM "BookReview" -- awaitingReviews SELECT "Id" FROM "Book" WHERE "Available"=1 EXCEPT SELECT "BookId" FROM "BookReview" ``` `OrderBy()`, `Skip()` and `Take()` on the first query apply to the combined results, and `Count()` counts them, so a combined query can be paged like any other. Queries being combined keep their own `OrderBy()` and `Take()`: ```csharp // Page through everyone, sorted by name var q = db.From().Select(x => x.Author) .Union(db.From().Select(x => x.Reviewer)) .OrderBy(x => x.Author) .Skip(20).Take(10); // Fantasy books plus the 2 cheapest books var q2 = db.From().Where(x => x.Genre == Genre.Fantasy).Select(x => x.Title) .UnionAll(db.From().OrderBy(x => x.Price).Take(2).Select(x => x.Title)); ``` ```sql -- Page through everyone, sorted by name SELECT * FROM ( SELECT "Author" FROM "Book" UNION SELECT "Reviewer" FROM "BookReview" ) q ORDER BY "Author" LIMIT 10 OFFSET 20 -- Fantasy books plus the 2 cheapest books SELECT "Title" FROM "Book" WHERE ("Genre" = @0) UNION ALL SELECT * FROM (SELECT "Title" FROM "Book" ORDER BY "Price" LIMIT 2) q1 -- @0 = 'Fantasy' ``` Combined queries work with `Select()` into POCOs and the async APIs. Oracle uses `MINUS` for `Except()`, and Firebird, which doesn't support `INTERSECT` or `EXCEPT`, throws a `NotSupportedException`. ## Fast, stable paging with SeekAfter `Skip(n).Take(m)` gets slower the deeper you page, as the database still reads every skipped row, and rows can be skipped or repeated when data changes between requests. `SeekAfter()` pages through results by continuing after the last row of the previous page (keyset pagination), so every page is fast and stable: ```csharp var q = db.From() .OrderByDescending(x => x.CreatedDate).ThenBy(x => x.Id) // end with a unique column .Take(50); if (lastRow != null) q.SeekAfter(lastRow); // continue after the last row of the previous page var page = db.Select(q); ``` ```sql SELECT "Id", "Customer", "Total", "CreatedDate" FROM "Order" WHERE (("CreatedDate" <= @0) AND (("CreatedDate" < @0) OR ("CreatedDate" = @0 AND "Id" > @1))) ORDER BY "CreatedDate" DESC, "Id" LIMIT 50 -- @0 = '2026-09-30 00:00:00', @1 = 42 ``` It generates `(a > @0) OR (a = @0 AND b > @1)` conditions from the query's `ORDER BY`, which work on every database, including with mixed sort directions, after an `a >= @0` bound that lets the database seek to the page in an index. | | Skip / Take | SeekAfter | |-|-|-| | Deep pages | Slower the deeper you page | Same speed at any depth | | Rows added or removed between requests | Rows can be skipped or repeated | Continues where it left off | | Jump to page N | Yes | No, pages are sequential | That makes it a fit for feeds, infinite scrolling, exports and sync APIs. In an API, pass the `ORDER BY` values directly from a cursor, and it combines with filters and with user-selected sort orders from [OrderBySafe()](/posts/ormlite-safe-sql): ```csharp var page = db.Select(db.From() .Where(x => x.Available) .OrderBy(x => x.Price).ThenByDescending(x => x.Year).ThenBy(x => x.Id) .SeekAfter(cursor.Price, cursor.Year, cursor.Id) .Take(50)); ``` Call it after `OrderBy()`, end the order with a unique column such as the primary key, and sort by non-nullable columns. ## Stream large result sets Large exports and background processing shouldn't have to load an entire table into memory or block a thread. `SelectLazyAsync()` and `ColumnLazyAsync()` stream results one row at a time with `await foreach`: ```csharp await foreach (var order in db.SelectLazyAsync( db.From().Where(x => x.Status == Status.Shipped), token)) { await writer.WriteAsync(order); } await foreach (var email in db.ColumnLazyAsync(db.From().Select(x => x.Email))) ... ``` Queries can be typed, parameterized or use [`Sql.Fmt()`](/posts/ormlite-safe-sql). Breaking out of the loop or cancelling the token closes the reader straight away, so the connection can be reused immediately. ## Row-returning, locking and joined writes Three more additions solve the same kind of problem: a write that needs to be one statement to be correct. `UpdateOnlyReturning()` and `DeleteReturning()` return the rows affected by an UPDATE or DELETE in the same statement, instead of a separate query that could see different rows. Deleting and returning in one statement also makes a simple work queue, as each row is only ever returned to one caller: ```csharp List shipped = db.UpdateOnlyReturning(() => new Order { Status = "Shipped" }, where: x => x.Status == "Packed"); List jobs = db.DeleteReturning(x => x.Queue == "emails"); ``` `ForUpdate()` locks the rows a query selects until the end of the transaction, to prevent lost updates on balances, counters and stock levels, and `ForUpdate(skipLocked: true)` lets multiple workers take different items from the same queue table. It uses `FOR UPDATE [SKIP LOCKED]` on PostgreSQL, MySQL 8+, MariaDB 10.6+ and Oracle, `WITH (UPDLOCK, ROWLOCK[, READPAST])` on SQL Server, and is ignored on SQLite so the same code runs in development: ```csharp using var trans = db.OpenTransaction(); var account = db.Single(db.From().Where(x => x.Id == id).ForUpdate()); account.Balance -= amount; // other transactions wait until this one ends db.Update(account); trans.Commit(); ``` `UpdateFrom()` updates rows with values from joined tables in a single statement, without reading them into .NET or writing RDBMS-specific SQL: ```csharp // Apply each UK warehouse's markup to the price of its parts var q = db.From() .Join((p, w) => p.WarehouseId == w.Id) .Where(w => w.Country == "UK"); int updated = db.UpdateFrom((p, w) => new Part { Price = p.Price * w.Markup }, q); ``` ## Get Started These queries are available in **ServiceStack v10.4**. Upgrade your `ServiceStack.OrmLite.*` packages and replace the raw SQL you were maintaining for hierarchies, rankings and paging. See the docs for the complete reference, which has more examples and the database support of each API: - [Recursive Queries](https://docs.servicestack.net/ormlite/recursive-queries) - ancestors, multiple roots, depth, cycles and paging - [Named Sub Queries](https://docs.servicestack.net/ormlite/common-table-expressions) - building queries in steps and combining with recursion - [Window Functions](https://docs.servicestack.net/ormlite/window-functions) - rankings, running totals, moving averages, `TopPerGroup()` and joins - [Union, Intersect & Except](https://docs.servicestack.net/ormlite/set-operations) - paging and ordering combined queries - [Keyset Pagination](https://docs.servicestack.net/ormlite/keyset-pagination) - cursors in APIs and user-supplied sort orders - [Streaming Results](https://docs.servicestack.net/ormlite/streaming) - `SelectLazyAsync()` and `ColumnLazyAsync()` - [Returning Rows](https://docs.servicestack.net/ormlite/returning), [Locking Rows](https://docs.servicestack.net/ormlite/locking) and [Update from Joined Tables](https://docs.servicestack.net/ormlite/update-from) - [v10.4 Release Notes](https://docs.servicestack.net/releases/v10_04) - everything else in this release # Safe SQL by default in OrmLite Source: https://servicestack.net/posts/ormlite-safe-sql :::youtube GGXmyUx5qqY Dynamic SQL Without SQL Injection in .NET: Safe Interpolation & Sorting ::: Typed queries have always been the safe way to use OrmLite. Every value in `db.Select(x => x.Author == author)` is sent as a db parameter, so there's nothing to inject into. SQL injection creeps in where typed queries stop being convenient, and it almost always arrives the same two ways: - **Concatenating a value into raw SQL**, usually with C# string interpolation, because `$"Author = '{author}'"` is so much nicer to write than a parameter object. - **Letting users choose the sort order**, e.g. `?orderBy=-Price`, which is a column name rather than a value, so it can't be a parameter and ends up pasted into `ORDER BY`. **ServiceStack v10.4** gives both a safe API that's as easy to write as the unsafe one, and hardens the defences around everything else: - **`Sql.Fmt()`** keeps writing natural C# interpolated strings, but every interpolated value is sent as a db param - **`OrderBySafe()`** resolves user-supplied field names to properly quoted columns, so only real fields can be used - **Stronger injection protection** for SQL fragments, identifiers, inlined values and schema queries ## Safe interpolated SQL with Sql.Fmt The unsafe version and the safe version differ by a single method call: ```csharp // Unsafe: the value is embedded in the SQL db.Select($"Author = '{request.Author}'"); // Safe: the value is sent as a db param db.Select(Sql.Fmt($"Author = {request.Author}")); ``` `Sql.Fmt()` is a C# interpolated string handler. Rather than building a string, it records the SQL with a placeholder for each interpolated value, and OrmLite generates the SQL and params for your RDBMS: ```csharp var author = request.Author; // user input is never embedded in SQL var books = db.Select(Sql.Fmt($"Author = {author} AND Price < {request.MaxPrice}")); ``` ```sql SELECT "Id", "Title", "Author", "Genre", "Price", "Year", "Available" FROM "Book" WHERE Author = @p0 AND Price < @p1 -- @p0 = 'J.R.R. Tolkien', @p1 = 20 ``` A value that would otherwise be an injection attack is just a value that doesn't match anything: ```csharp var malicious = "x' OR '1'='1"; db.Select(Sql.Fmt($"Author = {malicious}")); // returns no rows ``` Values are converted the same way as in typed queries, so enums, dates, decimals and bools just work, and collections are expanded into `IN` lists: ```csharp var genres = new[] { Genre.Fantasy, Genre.Science }; var books = db.Select(Sql.Fmt($"Genre IN ({genres}) AND Year >= {since}")); ``` ```sql SELECT "Id", "Title", "Author", "Genre", "Price", "Year", "Available" FROM "Book" WHERE Genre IN (@v0,@v1) AND Year >= @p1 -- @v0 = 'Fantasy', @v1 = 'Science', @p1 = 1960 ``` Empty collections match nothing, rather than generating invalid SQL. ### Works with every raw SQL API `Sql.Fmt()` is accepted anywhere OrmLite takes a SQL string, sync or async: `SqlList`, `SqlColumn` and `SqlScalar`, the shorthand `Select`, `Single`, `Exists`, `Column` and `Scalar` APIs, `ExecuteSql`, `Delete`, the collection APIs `ColumnDistinct`, `Lookup`, `Dictionary` and `KeyValuePairs`, `RowCount` and the lazy streaming APIs: ```csharp var genres = new[] { Genre.Fiction, Genre.Science }; Dictionary> titlesByAuthor = db.Lookup( Sql.Fmt($"SELECT Author, Title FROM Book WHERE Genre IN ({genres})")); db.Delete(Sql.Fmt($"Author = {author} AND Year < {1950}")); ``` It's an explicit opt-in, so existing `string` APIs keep their current behaviour, and passing an interpolated string directly to one still concatenates the value. ### Table and column references Values are the only thing that can be a parameter. Table and column names can't, and are the other thing people end up concatenating. `Sql.Fmt()` embeds them as names quoted by the RDBMS dialect, including their schema and naming convention. Naming the references after their table and column keeps the SQL readable, and makes it clear which parts of the SQL are names and which are params: ```csharp var Book = db.TableRef(); var (Title, Author, Price) = db.ColumnRefs(x => new { x.Title, x.Author, x.Price }); var titles = db.SqlColumn(Sql.Fmt($"SELECT {Title} FROM {Book} WHERE {Author}={author}")); db.ExecuteSql(Sql.Fmt($"UPDATE {Book} SET {Price} = {Price} * {0.9m} WHERE {Author} = {author}")); ``` ```sql SELECT "Title" FROM "Book" WHERE "Author"=@p0 -- @p0 = 'J.R.R. Tolkien' UPDATE "Book" SET "Price" = "Price" * @p0 WHERE "Author" = @p1 -- @p0 = 0.9, @p1 = 'J.R.R. Tolkien' ``` The quoted names also make the SQL portable, as they use the table's `[Alias]` and the RDBMS naming convention, e.g. PostgreSQL's convention stores the `BookReview` table as `book_review` and its `BookId` column as `book_id`. A table can also be referenced by its type, and joins can qualify columns with their table using `prefixTable: true`: ```csharp var (Book, BookReview) = db.TableRefs(); var (Id, Title) = db.ColumnRefs(x => new { x.Id, x.Title }, prefixTable:true); var (BookId, Rating) = db.ColumnRefs(x => new { x.BookId, x.Rating }, prefixTable:true); var reviewed = db.SqlColumn(Sql.Fmt( $"SELECT DISTINCT {Title} FROM {Book} JOIN {BookReview} ON {Id}={BookId} WHERE {Rating}>={4}")); ``` ```sql SELECT DISTINCT "Book"."Title" FROM "Book" JOIN "BookReview" ON "Book"."Id"="BookReview"."BookId" WHERE "BookReview"."Rating">=@p0 -- @p0 = 4 ``` Anything else that really is trusted SQL, like a sort direction you chose in code, can be embedded verbatim with `Sql.Raw()`. It's a signal that the value is SQL rather than data, so it's never used with user input. ### Mix it with typed queries `Sql.Fmt()` also works inside typed `SqlExpression` queries through `Where`, `And`, `Or` and `Having`, which is useful for conditions that are hard to express in C#, like aggregates in a `HAVING` clause: ```csharp var Year = db.ColumnRef(x => x.Year); var q = db.From() .Where(x => x.Available) .And(Sql.Fmt($"{Year} >= {minYear}")); // Genres with at least 2 books, one published since 1980 var byGenre = db.From() .GroupBy(x => x.Genre) .Having(Sql.Fmt($"COUNT(*) >= {2} AND MAX({Year}) >= {1980}")) .Select(x => x.Genre); ``` ```sql -- q SELECT "Id", "Title", "Author", "Genre", "Price", "Year", "Available" FROM "Book" WHERE "Available"=1 AND "Year" >= @0 -- @0 = 1970 -- byGenre SELECT "Genre" FROM "Book" GROUP BY "Genre" HAVING COUNT(*) >= @0 AND MAX("Year") >= @1 -- @0 = 2, @1 = 1980 ``` Format specifiers like `{date:yyyy-MM-dd}` are rejected with a `FormatException`, since params are sent as typed values rather than text. Pass the typed value, or format it before interpolating it. ## Let users choose the sort order with OrderBySafe A sort order is a column name, so it can't be a parameter, and it's the most common way user input ends up in SQL. Passing `?orderBy=-Price` straight to `OrderBy(string)` is only ever as safe as the validation behind it. `OrderBySafe()` never embeds its input at all. It resolves field names to properly quoted columns, so only real fields can ever be used: ```csharp // ?orderBy=-Price,Title&skip=20&take=10 var q = db.From() .OrderBySafe(request.OrderBy, [nameof(Book.Title), nameof(Book.Price), nameof(Book.Year)]) .Skip(request.Skip).Take(request.Take); ``` `orderBy` is a comma-delimited list of field names: | orderBy | SQL | |-------------------------|-----------------------------------| | `Price` | `ORDER BY "Price"` | | `-Price` | `ORDER BY "Price" DESC` | | `Price DESC` | `ORDER BY "Price" DESC` | | `price asc, title` | `ORDER BY "Price", "Title"` | | `-Year,Title` | `ORDER BY "Year" DESC, "Title"` | Fields can be prefixed with `-` or suffixed with `DESC` to sort descending, names are case-insensitive and are resolved to the RDBMS column name, e.g. `in_stock` for `InStock` on PostgreSQL. ### Only the fields you allow The list of fields users can sort by does more than prevent injection. Sorting by a field reveals information about its values, even if the field itself is never returned, so an unrestricted sort lets a client probe the contents of fields it can't see, like a `PasswordHash` or `ApiKey`, by watching how the results are ordered: ```csharp static readonly string[] SortableFields = [nameof(Book.Title), nameof(Book.Price), nameof(Book.Year)]; db.From().OrderBySafe("-Year", SortableFields); // OK db.From().OrderBySafe("Author", SortableFields); // throws ArgumentException ``` Limiting sorting to fields with an index also keeps sorting large tables fast. `OrderBySafe(orderBy)` without a list allows any field on the queried tables, including joined tables, so only use it on tables without sensitive fields. ### Everything else is rejected Input is only ever treated as field names and sort directions. Anything else throws an `ArgumentException`, which ServiceStack returns to API clients as a `400 Bad Request`: ```csharp q.OrderBySafe("Unknown"); // not a field q.OrderBySafe("Id--"); // not a field q.OrderBySafe("Id;DROP TABLE Book"); // not a field q.OrderBySafe("(SELECT 1)"); // not a field q.OrderBySafe("Id SIDEWAYS"); // invalid direction ``` An empty `orderBy` leaves the query's existing order unchanged, so it's easy to keep a default sort: ```csharp var q = db.From() .Join((b, r) => b.Id == r.BookId) .OrderBy(x => x.Title) // default order .OrderBySafe(request.OrderBy); // e.g. "-Rating, Reviewer" ``` It also works with the new [keyset pagination](https://docs.servicestack.net/ormlite/keyset-pagination), so a user-selected sort order stays fast on large tables: ```csharp // ?orderBy=-Price,Id var q = db.From().OrderBySafe(request.OrderBy, [nameof(Book.Price), nameof(Book.Id)]) .SeekAfter(lastRow).Take(50); ``` ```sql SELECT "Id", "Title", "Author", "Genre", "Price", "Year", "Available" FROM "Book" WHERE (("Price" <= @0) AND (("Price" < @0) OR ("Price" = @0 AND "Id" > @1))) ORDER BY "Price" DESC, "Id" LIMIT 50 -- @0 = 12.99, @1 = 42 ``` [AutoQuery](https://docs.servicestack.net/autoquery/rdbms) APIs already support validated `?orderBy=` sorting out of the box, so `OrderBySafe()` is for the APIs you write yourself. ## Stronger SQL injection protection Some APIs accept SQL fragments by design, like `Where(string)`, `OrderBy(string)` and `Select(string)`. OrmLite validates those fragments and throws an `ArgumentException` if one looks like SQL injection. Validation is a safety net rather than a substitute for params, but it's the last line of defence for apps that pass fragments around, so this release closes the ways it could be bypassed: - **Tighter fragment validation**: comments (`--`, `/* */`) and statement separators (`;`) are now rejected anywhere in a fragment, closing several ways to bypass the validation. Values quoted with MySQL-style backslash escapes can no longer disguise SQL either, as quoted strings are checked as both ANSI SQL and MySQL would parse them. - **Safer identifiers on every database**: table, column and schema names containing quote characters are now always escaped, including on Oracle and Firebird, which previously left most names unquoted. - **Escaped date formats and currency symbols**: formats in `x.Created.ToString(format)` and the SQL date format and currency helpers are now always escaped. Previously a runtime value could inject SQL on SQLite and MySQL. - **Safer schema queries**: the catalog queries that check whether tables and schemas exist, and that reset sequences, now escape the names they use on PostgreSQL, Oracle and Firebird, as do Firebird's schema lookups like `GetTable()` and `GetColumns()`. Savepoint names are now validated. - **Escaped spatial values on SQL Server**: `SqlGeography`, `SqlGeometry` and `SqlHierarchyId` values inlined in SQL now have embedded quotes escaped. - **`Contains()` values are always db params**: the value of `x.Tags.Contains(value)` on a list or array property was previously written into the SQL as it was, so a value from a user could change the query. It's now a param of a JSON query, and throws where the property can't be queried. Some fragments that were accepted before are now rejected, e.g. `x--y` or a `;` in the middle of a fragment. If you hit one, pass values as parameters or use `Sql.Fmt()`. For trusted SQL you wrote yourself, the `Unsafe*` APIs skip validation: ```csharp q.UnsafeWhere("Id IN (SELECT BookId FROM BookReview WHERE Rating >= {0})", 4); // {0} is sent as a param q.UnsafeOrderBy("CASE WHEN Available THEN 0 ELSE 1 END, Title"); ``` ## Choose the safe API for each job | You need to... | Use | |-------------------------------------------------|---------------------------------------------------------| | Filter by user input | Typed `Where()`, or params with raw SQL | | Write raw SQL with interpolated values | `Sql.Fmt()` | | Let users choose the sort order | `OrderBySafe()` | | Reference a table name in raw SQL | `db.TableRef()` or `typeof(T)` in `Sql.Fmt()` | | Reference a column name in raw SQL | `db.ColumnRef()`, `db.ColumnRefs()` in `Sql.Fmt()` | | Embed trusted SQL that fails validation | `Unsafe*` APIs, e.g. `q.UnsafeWhere()` | ## Get Started `Sql.Fmt()`, `OrderBySafe()` and the stronger injection protection are available in **ServiceStack v10.4**, on every database OrmLite supports. Upgrade your `ServiceStack.OrmLite.*` packages and replace the interpolated strings you pass to raw SQL APIs with `Sql.Fmt()`. See the docs for the complete reference: - [Interpolated SQL](https://docs.servicestack.net/ormlite/sql-fmt) - every supported API, table and column references, and inspecting the generated SQL - [Dynamic Sorting](https://docs.servicestack.net/ormlite/order-by-safe) - syntax, allowed fields, defaults and paging - [SQL Injection Protection](https://docs.servicestack.net/ormlite/sql-injection) - fragment validation, identifier quoting, `Unsafe*` APIs and how to customize validation - [v10.4 Release Notes](https://docs.servicestack.net/releases/v10_04) - everything else in this release # Keep your database in sync with your C# models Source: https://servicestack.net/posts/ormlite-schema-diff :::youtube chmFo1TE-Wc Keep Your Database in Sync With Your C# Models: Schema Diff for .NET ::: In a code-first App your C# classes are your schema. You add two properties to an `Invoice`, build and deploy: ```csharp public class Invoice { [AutoIncrement] public int Id { get; set; } [StringLength(200)] public string Reference { get; set; } [Index] public int CustomerId { get; set; } public decimal Total { get; set; } public DateTime? PaidDate { get; set; } // new public string Currency { get; set; } // new } db.CreateTableIfNotExists(); // the table exists, so it's unchanged var unpaid = db.Select(x => x.PaidDate == null); ``` But the table was created months ago, and `CreateTableIfNotExists` only creates tables that don't exist. Nothing tells you the table didn't change with its model, until the first query that uses the new columns fails: ``` SqlException: Invalid column name 'PaidDate'. Invalid column name 'PaidDate'. Invalid column name 'Currency'. ``` When that happens in production, it's the first you hear of it. **ServiceStack v10.4** adds **Schema Diff** to OrmLite: one call that compares your models with their tables, tells you everything that's different, and writes the migration that fixes it for you to review. ## One call, every difference ```csharp var diff = db.GetSchemaDiff(typeof(Invoice), typeof(Customer)); if (diff.HasChanges) log.LogWarning("The database doesn't match its models:\n{Diff}", diff); ``` ``` Invoice ~ Reference VARCHAR(50) NOT NULL -> VARCHAR(200) NULL + PaidDate DATETIME NULL + Currency VARCHAR(8000) NULL (renamed from LegacyCode?) - LegacyCode VARCHAR(8000) NULL (not in Invoice) (renamed to Currency?) + index idx_invoice_customerid Customer + table isn't in the database ``` `+` is in your model and not the database, `-` is in the database and not your model, and `~` is in both, but different. The two new columns, a `Reference` column that's now larger and allows nulls, a column the model no longer has, a missing index and a missing table, all in one readable report. It doesn't guess what types your C# maps to. It creates the model's columns in a temporary table and compares them as your database reports them, so a table created from its model never has false differences, whichever [type converters](https://docs.servicestack.net/ormlite/type-converters) or naming strategy you use. Common equivalent types in tables that weren't created by OrmLite, e.g. by Entity Framework, like `TEXT` vs `VARCHAR(n)` or `INTEGER` vs `BIGINT`, aren't reported either. ## More than columns Your models already describe far more of your schema than their columns, and this release lets them describe even more, with attributes for filtered and covering indexes, enum checks and generated columns, instead of SQL you write by hand: ```csharp [CompositeIndex(nameof(TenantId), nameof(Status), Include = [nameof(Name), nameof(Size)])] public class Upload { [AutoIncrement] public int Id { get; set; } public int TenantId { get; set; } [Index(Unique = true, Where = "{DeletedDate} IS NULL")] // unique among rows that aren't deleted public string Name { get; set; } [CheckEnum] // only allow UploadStatus values public UploadStatus Status { get; set; } public long Size { get; set; } [Compute("{Size} / 1024"), Persisted] // calculated by the database public long SizeKb { get; set; } public DateTime? DeletedDate { get; set; } } ``` Because it's all in your model, Schema Diff compares all of it. Change a `PurchaseOrder`'s default, make its foreign key cascade deletes, restrict its `Status` to its enum's values and add a filtered index: ```csharp public class PurchaseOrder { [AutoIncrement] public int Id { get; set; } [ForeignKey(typeof(Supplier), OnDelete = "CASCADE")] // was no action public int SupplierId { get; set; } [CheckEnum] // was any string public OrderStatus Status { get; set; } [Default(5)] // was Default(1) public int Quantity { get; set; } public DateTime CreatedDate { get; set; } [Index(Where = "{ShippedDate} IS NULL")] // the unshipped orders public DateTime? ShippedDate { get; set; } } ``` And each one is found: ``` PurchaseOrder ~ Quantity default DEFAULT ((1)) -> DEFAULT ((5)) ~ foreign key FK_PurchaseOrder_Supplier_SupplierId (SupplierId) REFERENCES Supplier (Id) -> (SupplierId) REFERENCES Supplier (Id) ON DELETE CASCADE + constraint CHK__PurchaseOrder_Status CHECK ("Status" IN ('Pending','Shipped','Delivered')) + index idx_purchaseorder_shippeddate ``` Schema Diff compares: - **Tables and columns**: missing tables, missing and extra columns, and columns of another type, size or nullability - **Defaults**: a `[Default]` that changed, or a default the property doesn't have - **Indexes**: by their columns, order, uniqueness, `Include` columns and `Where` condition - **Foreign keys**: the table they reference and their `OnDelete` and `OnUpdate` actions - **Constraints**: `[Unique]`, `[UniqueConstraint]`, `[CheckConstraint]` and `[CheckEnum]` - **Primary keys** with other columns, and **full-text indexes** of [`[FullTextIndex]`](https://docs.servicestack.net/ormlite/full-text-search) models Databases rewrite conditions, e.g. PostgreSQL writes an `IN` list as `= ANY (ARRAY[...])`, so index conditions, check constraints and defaults are compared the way your database writes them, not as false differences. ## Built not to lose your data A diff that's quick to drop the wrong thing is worse than no diff at all, so Schema Diff errs on the side of keeping your data. **Likely renames are suggested, never assumed.** A column that's no longer in the model and a new column of the same type are probably the same column renamed, so the diff says so: `Currency (renamed from LegacyCode?)`. It's only a guess, so it's never renamed for you. You decide. **Destructive changes are marked.** Every change that can lose data or fail with the rows already in a table, like dropping a column, shrinking one, making an index unique or adding a check constraint, has `IsDestructive` set, and isn't made unless you allow it. **Hand-added indexes are kept.** Someone added an index to the database to speed up a slow query? It's never dropped, even when destructive changes are allowed. Instead the diff tells you the exact attribute to add, so your model has it too: ```csharp // CREATE INDEX ix_purchaseorder_status_created ON "PurchaseOrder" ("Status", "CreatedDate") var diff = db.GetSchemaDiff(); db.ApplySchemaDiff(diff, allowDestructive: true); // Makes 0 changes: the index is kept ``` ``` PurchaseOrder ! index ix_purchaseorder_status_created (Status, CreatedDate) (not in PurchaseOrder, kept: add [CompositeIndex("Status", "CreatedDate", Name = "ix_purchaseorder_status_created")] to PurchaseOrder) ``` ## The migration, written for you to review Knowing what's different is half the job. `ToMigration()` writes the other half, as an OrmLite [DB Migration](https://docs.servicestack.net/ormlite/db-migrations): ```csharp File.WriteAllText("Migrations/Migration1005.cs", diff.ToMigration("Migration1005", "MyApp.Migrations")); ``` ```csharp #warning Generated by Schema Diff: review this migration before running it, then delete this line public class Migration1005 : MigrationBase { public class Invoice { [AutoIncrement] public int Id { get; set; } [StringLength(200)] public string Reference { get; set; } public DateTime? PaidDate { get; set; } public string Currency { get; set; } } public override void Up() { // Reference is VARCHAR(50) NOT NULL Db.AlterColumn(x => x.Reference); Db.AddColumn(x => x.PaidDate); // Currency is likely LegacyCode renamed. If it is, rename it instead of adding it: // Db.RenameColumn(x => x.Currency, "LegacyCode"); Db.AddColumn(x => x.Currency); // LegacyCode (VARCHAR(8000) NULL) isn't in Invoice. If it was renamed, rename it instead: // Db.RenameColumn("LegacyCode", "Currency"); // Db.DropColumn("LegacyCode"); Db.ExecuteSql("CREATE INDEX idx_invoice_customerid ON \"Invoice\" (\"CustomerId\");"); } public override void Down() { /* ... */ } } ``` The migration has its own copy of the model, with only the properties that change, so it keeps working after your model changes again. Look at the likely rename: the migration adds `Currency`, and leaves dropping `LegacyCode` as commented-out code with the rename to use instead, so no data is dropped unless you decide it should be. :::warning A generated migration is a guide, not a finished migration. Schema Diff is new, so every migration it writes starts with a `#warning` that's reported each time your App is built, until you've reviewed it and deleted it. ::: ## From your browser, or the command line If you use ServiceStack, the [Database Admin UI](https://docs.servicestack.net/admin-ui-database#schema-diff) has a **Schema Diff** for each database. It compares the data models of your AutoQuery APIs and the tables your migrations create, shows each difference with the SQL that fixes it and marks the destructive ones. Comparing doesn't change your tables: With the migration class next to them, ready to copy into your App: Or have the `migrate.new` [App Task](https://docs.servicestack.net/app-tasks) write it straight into your App's `Migrations` folder, named after your last migration: :::sh dotnet run --AppTasks=migrate.new ::: It doesn't write anything when your database already matches its models, and never replaces a migration that's already there. ## Find out when your App starts, not in production Better than finding out when a query fails is finding out before it can. Turn on `LogSchemaDiff` in development and the differences of each database are logged in the background every time your App starts: ```csharp services.AddPlugin(new AdminDatabaseFeature { LogSchemaDiff = context.HostingEnvironment.IsDevelopment(), }); ``` ``` Schema Diff: the tables of the main database aren't the same as their models Invoice ~ Reference VARCHAR(50) NOT NULL -> VARCHAR(200) NULL + PaidDate DATETIME NULL + Currency VARCHAR(8000) NULL (renamed from LegacyCode?) - LegacyCode VARCHAR(8000) NULL (not in Invoice) (renamed to Currency?) + index idx_invoice_customerid Ignored: AspNetUsers ``` Or make it a test, so a model that changed without its migration fails the build before it's ever deployed: ```csharp [Test] public void Database_matches_its_models() { using var db = dbFactory.OpenDbConnection(); var diff = db.GetSchemaDiff(typeof(Invoice), typeof(Customer)); Assert.That(diff.HasChanges, Is.False, diff.ToString()); } ``` Tables OrmLite doesn't manage, like ASP.NET Core Identity's `AspNet*` tables and EF Core's `__EFMigrationsHistory`, are ignored by default, and you can ignore others by name, wildcard or model: ```csharp OrmLiteConfig.SchemaDiff.IgnoreTables.Add("Legacy*"); OrmLiteConfig.SchemaDiff.IgnoreTypes.Add(typeof(AuditArchive)); ``` ## Keep dev databases in sync While you're developing, `ApplySchemaDiff()` makes the changes for you. By default only safe ones, like creating tables, adding columns and indexes, making columns larger or allowing nulls, are applied: ```csharp var diff = db.GetSchemaDiff(typeof(Invoice), typeof(Customer)); var applied = db.ApplySchemaDiff(diff); // safe changes only db.ApplySchemaDiff(diff, allowDestructive: true); // also drop and alter columns that can lose data ``` It's for development and test databases. The databases you deploy to should get reviewed migrations that are run once and recorded. ## SQLite, rebuilt SQLite can't alter a column, its default, foreign keys or constraints. The only way to change them is to create the table again and copy its rows. So on SQLite those changes are made by rebuilding the table, and the diff says so: ``` PurchaseOrder ~ Quantity default DEFAULT 1 -> DEFAULT 5 (by rebuilding the table) ~ foreign key FK_PurchaseOrder_Supplier_SupplierId (SupplierId) REFERENCES Supplier (Id) -> (SupplierId) REFERENCES Supplier (Id) ON DELETE CASCADE (by rebuilding the table) + constraint CHK__PurchaseOrder_Status CHECK ("Status" IN ('Pending','Shipped','Delivered')) (by rebuilding the table) + index idx_purchaseorder_shippeddate ~ rebuild table to change the default of Quantity, foreign key FK_PurchaseOrder_Supplier_SupplierId, constraint CHK__PurchaseOrder_Status ``` The new `RebuildTable()` API does it from your model, and is also available to your own migrations: ```csharp db.RebuildTable(); ``` It runs in a transaction, keeping the table's rows, its indexes (including ones added by hand), its triggers and its next `AUTOINCREMENT` id. If a row breaks the new table, e.g. it fails a new check constraint, the table is left as it was. ## Know the limits Schema Diff is built to tell you the truth about your database, so here's the truth about Schema Diff: - **Generated migrations are a guide to review**, not finished migrations you can trust without reading - **`ApplySchemaDiff()` is for development and test databases**, and applies changes one at a time, not in a transaction - **A primary key with other columns is only reported**, as changing it needs its foreign keys and data migrated too - **Not compared**: the order of columns, the expressions of generated columns and comments - **Temporary tables**: your connection's user needs to be able to create them, e.g. MySQL's `CREATE TEMPORARY TABLES` privilege, otherwise column types aren't compared and `diff.Warnings` says why Within those limits, your models stay the source of truth, and you find out the moment your database drifts from them. ## Get Started Schema Diff is available in **ServiceStack v10.4** on **SQLite, PostgreSQL, SQL Server, MySQL** and **MariaDB**. Upgrade your `ServiceStack.OrmLite.*` packages, then either open the Schema Diff in the Database Admin UI, turn on `LogSchemaDiff`, or call `db.GetSchemaDiff()` with your models to see how far your database has drifted. See the docs for the complete reference: - [Schema Diff](https://docs.servicestack.net/ormlite/schema-diff) - everything that's compared, the changes, ignoring tables, generated migrations and rebuilding SQLite tables - [Schema Attributes](https://docs.servicestack.net/ormlite/ddl-attributes) - filtered and covering indexes, enum checks, generated columns and comments declared on your models - [DB Migrations](https://docs.servicestack.net/ormlite/db-migrations) - writing, running and reverting OrmLite migrations - [Database Admin UI](https://docs.servicestack.net/admin-ui-database) - browse, query and compare your databases - [v10.4 Release Notes](https://docs.servicestack.net/releases/v10_04) - everything else in this release # Prompt it, Publish it - AI-built works from your .NET APIs Source: https://servicestack.net/posts/ai-chat-projects Your customer, revenue and usage data already lives inside your .NET App, yet answering a simple business question still means opening several screens, exporting CSVs and assembling the story somewhere else. The common shortcut is pasting that data into an external ChatGPT window, which knows nothing about your App, can't fetch anything new and now has a copy of data that was never meant to leave it. :::youtube -BfooJPT3-I Prompt It, Publish It: Create AI Works directly from your .NET APIs ::: [AI Chat](https://docs.servicestack.net/chat/) takes the opposite approach. It's a ChatGPT-like assistant that runs **inside** your App at `/chat`, with your App's users, identity and permissions. With [API Tools](/posts/api-tools) it can call the APIs you've opted in, as the signed-in user, and with **Projects** it has a workspace to turn the results into something more useful than a paragraph of chat. | | What you get | |-|-| | **Same security boundary** | The assistant runs in your App, as the signed-in user, so it can only see what they could | | **No parallel backend** | Your existing Request DTOs are the AI contract, with no AI-specific gateway or schema to keep in sync | | **Always current data** | It discovers and calls your live APIs instead of working from a stale export | | **Humans approve changes** | Reads run immediately, writes pause for an editable approval form | | **Artifacts, not answers** | Generated pages are saved to a project workspace where they can be previewed and refined | | **Explicit publishing** | Nothing leaves the workspace until a user chooses to publish it | To show what that looks like in practice, the video follows two prompts in the [Next SaaS](/react/#next-saas) template, using its seeded example data. ## Your APIs are the assistant's tools There's no new AI-specific layer to build. The APIs the assistant uses are the same Request DTOs your App's UI already calls, annotated with a `[Tool]` attribute that tells it **when** to use them: ```csharp [Tool("the user asks which customers are at risk, healthy, churning, inactive, likely to upgrade or need attention, or how a customer is doing", Safety = ToolSafety.ReadOnly, Keywords = ["health", "at risk", "churn risk", "attention", "inactive", "upsell", "upgrade", "expansion", "engagement"], FollowUps = ["GetSaasCustomer"], Examples = ["""{"grade":"AtRisk","take":10}"""])] [ValidateIsAuthenticated] [Route("/saas/admin/customer-health", "GET")] public class GetCustomerHealth : IGet, IReturn { [Description("Only this customer")] public string? WorkspaceId { get; set; } [Description("Only customers with this grade")] public CustomerHealthGrade? Grade { get; set; } [Description("Include customers whose subscription was canceled")] public bool IncludeChurned { get; set; } [Description("The most customers to return, from 1 to 200")] public int Take { get; set; } = 25; } ``` Or you can opt in whole groups of APIs by tag when registering `ChatFeature`: ```csharp services.AddPlugin(new ChatFeature { // Require authentication to access /chat RequireAuth = true, ApiTools = { IncludeTags = [ ApiTags.Organizations, ApiTags.Team, ApiTags.Billing, ApiTags.Usage, ApiTags.Documents, ApiTags.ApiKeys, ApiTags.Notifications, ApiTags.Audit, ApiTags.Platform, ], // Metering is for the product's own API clients, not for an assistant to consume quota ExcludeTypes = [nameof(RecordUsage)], }, }); ``` Opting an API in doesn't bypass anything. Every call goes through the normal ServiceStack request pipeline, so the same validation, `[ValidateIsAuthenticated]` and `[ValidateHasRole]` attributes, and the capability checks inside your Services, decide what the assistant gets back: ```csharp public async Task