Photo by Sometimes, in my research, I come across helpful and great tips to keep in mind but likely wouldn’t be enough to fill a whole post. Rather than let those discoveries fade into the ether, I thought I’d compile them into a pseudo-random blog post. These tips will focus on technologies and techniques that can help build an application, and hopefully, knowing some of these tips will help you solve future problems.
Let’s get into it!
SQLite Discoveries
SQLite is a small, fast, self-contained, high-reliability, full-featured SQL database engine. According to its creators, it is the most used database engine globally, used in almost every imaginable scenario from mobile devices, embedded programming, and web applications. SQLite has all the typical features we’d expect from an ACID-compliant relational database system. SQLite delivers all the functionality in a cross-platform file format we can share across devices by a simple copy.
SQLite supports Full-Text Search
In addition to basic relational queries, SQLite also has support for full-text search. Full-text search is a technique that allows users to search for specific tokens in what is considered complex and significant data records. While traditional table storage saves records in tables, rows, and columns, a full-text search engine requires techniques that tokenize input into optimized searchable constructs for quick queries.
SQLite has a module named FTS5, short for Full-Text Search version 5. To use this module, we need to create a virtual table that will store our initial row values and process data into optimized search structures. When connected to an SQLite instance, we can create a virtual table with the following command.
CREATE VIRTUAL TABLE email USING fts5(sender, title, body);
From here, we can insert data into our virtual table and execute SQL queries.
-- Query for all rows that contain at least once instance of the term
-- "fts5" (in any column). The following three queries are equivalent.
SELECT * FROM email WHERE email MATCH 'fts5';
SELECT * FROM email WHERE email = 'fts5';
SELECT * FROM email('fts5');
There are additional features to FTS5 that can make it a compelling choice for search. For one, we can highlight matching terms using the highlight function. Here, we’ll highlight the phrase fts5 when found in our email field.
-- Query for rows that match "fts5". Return a copy of the "body" column
-- of each row with the matches surrounded by <b></b> tags.
SELECT highlight(email, 2, '<b>', '</b>') FROM email('fts5');
We can also have advanced search criteria, such as * (wildcard), and, or, and near operators, to help us find fuzzy matches.
While not as powerful as purpose-built search engines like Elasticsearch, having a native search engine for simple scenarios is a compelling use case for folks writing demos or proof of concept applications.
To read more about FTS5, check out the documentation at the official SQLite documentation site.
SQLite can Parse JSON
Storing JSON in a database typically comes with trade-offs, depending on the database engine. Luckily most modern relational engines have JSON capabilities built-in, and SQLite is no different. SQLite has an array of json_ prefixed functions for interacting with JSON values stored in tables. Take the following example.
-- columns include key, value, type, atom, id, parent, fullkey, path
SELECT key, value, type
FROM json_tree('{ "name" : "khalid", "info" : ' ||
'{ "twitter" : "@buhakmeh", "followers" : [10000] }' ||
'}');
Executing the query results in the following output:
| key | value | type |
|---|---|---|
| NULL | {“name”:”khalid”,”info”:{“twitter”:”@buhakmeh”,”followers”:[10000]}} | object |
| name | khalid | text |
| info | {“twitter”:”@buhakmeh”,”followers”:[10000]} | object |
| @buhakmeh | text | |
| followers | [10000] | array |
| 0 | 10000 | integer |
Wow! Other JSON specific functions include json_each, json_insert, json_array, and more. Here’s another example where we query the values from a JSON array.
SELECT key as [index], value
FROM json_tree(json_array(1, 2, 3))
where type = 'integer'
| index | value |
|---|---|
| 0 | 1 |
| 1 | 2 |
| 2 | 3 |
Read more about SQLite’s JSON functions at the official documentation.
string.IsNullOrEmpty Three Ways
With C# 9, we have many more options for writing the same logic. Whether variety is a good thing is a contested argument in the community, but let’s look at three ways to check whether a string is null or empty.
string? empty = null;
if (string.IsNullOrEmpty(empty))
Console.WriteLine($"{nameof(string.IsNullOrEmpty)}");
if (empty is null || empty is { Length: 0})
Console.WriteLine("Pattern Matching");
if ((empty?.Length).GetValueOrDefault() == 0)
Console.WriteLine("Elvis operator");
The use of string.IsNullOrEmpty is the tried and true method of checking string values for null or empty. The second approach uses C# 9 pattern matching, and the last method uses the Elvis (.?) operator. Which of these approaches do you prefer and why?
.NET 6 introduces Default Values to OrDefault LINQ methods
With the .NET 6 release, LINQ users will get an additional extension method for all the OrDefault methods, allowing us to set a default value if we find no matches in a collection. Let’s take a look at the following example using FirstOrDefault.
var numbers = Array.Empty<int?>();
var ugh = numbers.FirstOrDefault() ?? 42;
Since our collection is empty, we’ll always get our collection type’s default value, which is a nullable integer. In .NET 6, we’ll be able to shorten our code.
var meaningOfLife = numbers.FirstOrDefault(42);
Nice! There are several more quality of life improvements coming to LINQ in the .NET 6 release.
Fake JSON REST API for Samples
One less thing to worry about when learning a new framework can be helpful. The {JSON}Placeholder website exposes a straightforward REST API for concepts like todos and blogs to help you focus on learning UI frameworks, rather than building tedious backend APIs.
Running Docker Containers for Integration Tests
With ASP.NET Core and Docker, it’s never been easier to run complete end-to-end integration tests. These kinds of tests give us confidence that what we’re delivering is going to work.
I’ve used a NuGet package called Ductus.FluentDocker. Here is some sample code to start a Microsoft SQL Server for Linux container from C#. Note, you will need Docker installed and running on the host machine.
using var container =
new Builder()
.UseContainer()
.WithName("dapper_benchmark")
.UseImage("mcr.microsoft.com/mssql/server")
.ExposePort(11433, 1433)
.WithEnvironment("SA_PASSWORD=Pass123!", "ACCEPT_EULA=Y")
.WaitForMessageInLog("Starting up database 'tempdb'.", TimeSpan.FromSeconds(30))
.Build()
.Start();
There are some caveats with this approach, the most significant being that if the tests fail to end gracefully, you’ll have an orphaned container.
Debugging The IDE with an IDE
I’m a JetBrains Rider user, and I also like to live my virtual life dangerously. I’m typically testing out Early-Access Program releases of the product, and while it is stable the majority of the time, it can have unexpected issues.
Luckily, JetBrains Toolbox allows installing multiple versions of Rider side-by-side. When there is a recurring issue, I can attach a stable older version of Rider to the new EAP version and see precisely the exception thrown by the codebase.
Seeing the exception makes understanding and filing bugs much more straightforward and helps me make my favorite IDE that much better. Awesome!
Conclusion
Well, that’s a few things I’ve learned over the last month. While some of it is interesting, my hope is at least one of these will be helpful. If you find any of these tips useful, please be sure to share this post and mention me on Twitter at @buhakmeh. As always, thanks for reading.
