Sqlstone: Every User Owns A Personal Copy of the Database: Solving Backend Challenges (Data Availability / Ownership)

• Download sqlstone_v001.zip - 20.4 MB

 GitHub - raddevus/sqlstone: C# .NET Core 8.x ASP.NET MVC which creates a new sqlite db for every registered user to store their own data in. Because? Why not?[^]

Introduction

I want to write a SaaS (Software as a Service) which:

1. requires as little support & maintenance as possible
2. is highly available
3. solves a problem which real users have
4. costs little to run
5. earns $$$

Over time I've solved many of these challenges.  For example, I've fulfilled items number one, two and four  in the previous list by discovering how to get a .NET Core WebAPI running on a DigitalOcean (Debian-based) droplet (which only costs $6 / month). 

Storing Data: Main Challenge

In the modern world of building apps the real challenge lies in the realm of data storage.

Building the front-end is relatively easy and basically requires nothing more than some text files, text editor and a web browser.  (Note: I'm not saying that creating a beautiful, functional UI design is easy.  I am saying those challenges don't sit so much in the technology realm (as backend does).)

In contrast, saving data so it is remotely accessible from any device still lands high on the list of difficult technology-based challenges. 

Also, this article is somewhat of a Part-2 to my previous article here on CP where I posed the question: How Much Can Sqlite Handle? Multiple Threads Concurrently Inserting Into Sqlite[^]

Making Data Accessible From Any Device

These days, users have multiple devices (iPads, Android Phones, Desktop computers, laptops) and may want to access their data (use your app) from any one of these devices at any given time.

To make data accessible from any device you're going to need to build some server side software.  In addition to that you're going to need to know how to configure various software (web servers, databases, etc.).  You're also going to have to create a data schema (structures for storing the user's data).  After that, you're going to need to insure the data is available at all times:

1. making sure the web site is up,
2. making sure the database is running,
3. making sure there is proper connectivity to your app

There are just so many things to do when you attempt to make data accessible from any device and any location.  

What This Article Is About

This article is an attempt to simplify those challenges at least a little bit by:

1. Creating a reproducible way of providing each user with their own copy of their data
2. Making it so the user is able to manage accessibililty to their own data (providing the ability to download their copy of the database & use it in an offline local copy of the associated app.

Technologies Used

To make these things possible, I've used the simplest technologies available (what I consider to be the most direct / easy way to store the user's data remotely):

1. .NET Core WebAPI - to post data to remote data store
2. Sqlite - text-based database which supports SQL queries (so users can use their data in numerous ways)
3. Entity Framework Core - In the past I never used EF because it felt bad & I like to create my own Stored Procs.  However, Sqlite doesn't support Store Procs anyways and the SQL used for this project is extremely simple & it makes sense to use EF Core.  Also, once you start using EF you kind of get addicted to it, because it is so easy. 🤓

What Will the WebAPI Framework Provide

I've used these two technologies to provide a framework of sorts which a developer can quickly build upon to create the specific target SaaS to create end-user apps.

Example Target App

An example target app (wrapped up in my extremely small "framework") is a Daily Journal app which allows the user to store daily free-form notes of activities they accomplished, ideas they have, etc.

Sqlstone: The Importance of a Project Name

One of the first things you need, when starting a project, is a cool / unique project name.  To create a cool and unique project name, you should put words together that don't necessarily make sense.

That's whay I've named this project Sqlstone.  See how it confuses you and inspires confidence that this will be a huge Open Source Project?  🤓😆

Daily Journal App: Try It Online

I've built an extremely basic version of a Daily Journaling app (on top of Sqlstone) and we'll use that code to show how this idea of each user getting his own database will work.  

You can try the app out where it is running on my web site: https://newlibre.com/journal^ 

I will walk you through the steps of using the Daily Journal app, but first let's talk about the simple Sqlstone Framework to discover what it can do for your app.

Sqlstone Summarized

The main thing the Sqlstone project does for you is:

Copies your custom Sqlite DB to the user's File Space (on your web server).

Where Is User's File Space Located?

wwwroot

For .NET Core WebAPIs and MVC apps the projects create a wwwroot folder where web content is served from. 

UUID Folder Name

Underneath that folder, the Sqlstone code will create a uniquely named folder based upon a UUID.

That folder will be created for the user when she register's her UUID (which is generated by the target app).  You'll see all of this with associated screen shots when we walk through the Daily Journal app further along in this article.

What Does the Sqlstone Code Do?

Almost all of the code for Sqlstone is implemented in the UserController.cs class.

When the user posts to the UserController to register her UUID the system will call the RegisterUser API method.  When that method is called, the UserController constructor will run and configure a few items.  

Let's take a look at the UserController Constructor because it helps illuminate a few things.

public UserController(ILogger<UserController> logger, 
            IConfiguration _configuration,
            IWebHostEnvironment webHostEnvironment)
    {
        _logger = logger;
        templateDbFile = _configuration["templateDbFile"];
        Console.WriteLine($"content rootPath: {webHostEnvironment.WebRootPath}");
        webRootPath = webHostEnvironment.WebRootPath;
        contentRootPath = webHostEnvironment.ContentRootPath;
    }

Two Injected Interfaces Are Used

The default Controller normally just has the one ILogger parameter.  But, in our UserController I've injected two Interfaces:

1. IConfiguration
2. IWebHostEnvironment

I've added those so that we can:

1. Read values from our App configuration (set in the appSettings.json file in the project).
2. Read some WebHost environment settings that we will use so we can store a fresh copy of the target solution's Sqlite Database in the user's file space.

AppSettings.json: templateDbFile

The templateDbFile is the file which will contain all of the tables which the target project (the project you are building on top of Sqlstone).  In our case, this will be the database for the Daily Journal App.

Here's the entire contents of the appSettings.json file as it is defined in the source code for the Journal app.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "templateDbFile": "sqlstone_journal.db"
}

The Logging section contains the default values that are included in .NET Core MVC apps.

The value we want to focus on here is the templateDbFile which I've set to be: sqlstone_journal.db.

This is the value which is read from the UserController constructor on the following line of code:

templateDbFile = _configuration["templateDbFile"];

That allows us to use whatever value we want to use to refer to the target database.

In the constructor we load the value into a member variable so we can use it later (in the RegisterUser() API call).

IWebHostEnvironment: Getting Paths

Now, we need the paths that we'll use to:

1. copy the database template file from
2. copy the database to the user's UUID folder

Because we've injected the IWebHostEnvironment into our constructor we can now get the:

1. webRootPath: path to web root (this is basically the folder above the wwwroot -- this is the locaton where the web binaries of your solution are deployed to
2. contentRootPath: path to the wwwroot folder (all content is served from wwwroot folder)

Now that we have an idea of those items which are set, we can talk about what happens when a User registers his UUID.

User / UUID Registration

For a user to use an app which is built on top of this very simple Sqlstone framework they will have to register a UUID. 

Registering Creates User File Space & Personal Database

This will be required for all Apps built on top of Sqlstone, because this will :

1. Create the User's File Space (under the wwwroot directory). The directory will have the format of wwwroot/<UUID>
2. Copy a fresh instance of the target Sqlite database into the user's wwwroot/<UUID> directory.

Very Simple Identification

Registering the UUID creates a very simple way to identify which database the User's content will be stored in.  

Yes, this means there are no passwords (at this time) required to store data in the target database.  We will discuss this further later.  (Hint: At some point in a future article, I will even show you how to encrypt each user's data using Authenticated AES256 encryption.)

Keeping the Main Idea, The Main Focus

For now, understand that this is a prototype to discover if this method of allowing every user to have their own copy of their own Sqlite database works well or not.

Work Flow of Steps When User Registers UUID

Here's the entire RegisterUser() method which takes a String which represents the user's UUID.

[HttpPost]
    public ActionResult RegisterUser([FromQuery] string uuid){
        Console.WriteLine($"uuid: {uuid}");
        User u = new User(uuid);
        var ipAddr = HelperTool.GetIpAddress(Request);
        
        var userDir = Path.Combine(webRootPath,uuid);
        var journalDb = Path.Combine(contentRootPath,templateDbFile);
        
        Directory.CreateDirectory(userDir);
        var userDbFile = Path.Combine(userDir,templateDbFile);
        Console.WriteLine($"userDbFile: {userDbFile}");
        if (!System.IO.File.Exists( userDbFile)){
            
            try{
                Console.WriteLine($"{journalDb} \n {userDir}");
                System.IO.File.Copy(journalDb,Path.Combine(userDir, userDbFile));
            }
            catch{
                return new JsonResult(new {result=false, error="Couldn't register user. Try again."});
            }
            UuidInfo info = new UuidInfo{Uuid=uuid,IpAddr=ipAddr};
            UuidInfoContext uuidCtx = new UuidInfoContext(contentRootPath);
            uuidCtx.Add(info);
            uuidCtx.SaveChanges();
        }
        else{
            Console.WriteLine("User is already registered.");
        }
    

        return new JsonResult(new {result=true, directory=webRootPath, ip=ipAddr});
    }

Here is basically what happens during User Registration.

1. Get the UUID from the data posted by the user.  Yes, in this case I have the client generate the UUID.  (I'll show you the JavaScript that does that further down in the article.)  I can easily have the .NET C# code generate the UUID but I just arbitrarily decided to put this on the client side.  If you want it that way, you can easily convert this method to do the work on Server side.
2. Get the IpAddress so we can track the IP address which registers each UUID.  I do this just so I can control an attack of someone generate thousands of UUIDs (and resulting folders).
3. Create the User's File Space (UUID directory under wwwroot) if it doesn't exist.
4. Check to see if the UserDbFile already exists in the User File Space.  If the template file has already been created, we obviously don't want to overwrite it.
5. If User's db file doesn't already exist then copy the template one found at webRootPath\sqlstone_journal.db 
6. Create the UuidInfo object, a simple object which wraps up the UUID, IpAddress, and Created date. 
7. Store UuidInfo data in the sqlstone.db. The sqlstone.db is only accessible to Admins of the Sqlstone framework.  It's just a simple way to determine how many users have registered to try the project out.

That's it.  Once the User is registered, she will be able to use the rest of the system. I'll walk you through using the Journal App so you can try it at my web site (or by downloading the code and trying it locally).

But, first, let me explain a bit more about the Database Template file.

A Bit More About The Database Template File

The database template file (sqlstone_journal.db) is an empty database which contains only the table schemas for your target project.

For the Journal App we only have one table, named JEntry (Journal Entry), in the database which is defined in the following way:

CREATE TABLE [JEntry]
( [ID] INTEGER NOT NULL PRIMARY KEY,
  [Title] NVARCHAR(250) check(length(Title) <= 250),
  [Note] NVARCHAR(3000) NOT NULL check(length(Title) <= 3000),
  [Created] NVARCHAR(30) default (datetime('now','localtime')) 
  check(length(Created) <= 30),
  [Updated] NVARCHAR(30) check(length(Updated) <= 30)
);

You can find that definition in a file in the project source code, in the sql folder.

In your target solution you will have a different set of tables which will be used by your target app to store its data.  Then you'll place your database template file (containing only tables & no data) in the webRootFolder (folder above wwwroot) and each time a user registers to use your target app, the template database will be copied to her wwwroot/<UUID> folder so that her data will be saved in her own private copy.

Now that we've discussed the basics of how this very small Sqlstone framework operates, let's take a look at a solution that is built on top of it.

Journal App: Built On Top Of Sqlstone

You are probably thinking, "Hmm...this is a very simple idea.  All he is doing here is giving every user their own Sqlite database."  You are right.  It's very simple.  Now I want to know if it is feasible to use in a true poduction environment.   I think it'll work.

Here's the current (simply & basic UI) of my Journal App.

That's a snapshot, after the user has already:

1. registered a UUID
2. Added some data

Before Registering a UUID

However, when you visit my web site and the Journal App loads (https://newlibre.com/journal^) or run it locally, you're going to see something different, since your UUID won't be registered yet.

As you can see, the user has not generated or registered a UUID so there is not much here.

Generate and Set A UUID

To begin, click the [Gen / Set UUID] button.

That will:

1. Generate a brand new UUID
2. Store it in your localStorage (now any time you visit this page from this device and browser you'll see that UUID).
3. Display it in the UUID text box.

Register Your UUID

You now have to click the [Register UUID] button to post the UUID to the server so it can create your user file space and copy the database template file to that space.

The client-side UI will let you know that the UUID was registered with a temporary alert:

Now you can create your first Journal Entry.

NOTE: The Journal App will not allow you to create a new Journal Entry withou first Generating and Registering your UUID.  

Create Your First Journal Entry

Now that you've registered your UUID and your remote file system space is created, you are ready to create a Journal Entry.

Click the [Add New Entry] button and you'll see a blank entry appear.

Fill out the Note field (an HTML TextArea) with some text.  You can even save emoticons if you want.  

Click the [Save] button to POST the data to the web site and save it in your copy of the sqlstone_journal.db.

When you click the [Save] button, a Prompt dialog will popup so you can add a Title if you want.

To save a Title, just type some text in the Prompt field and press <ENTER> or click the [OK] button.

If you don't want a Title to be saved, then you can click the [Cancel] button or hit the <ESC> button.

Your data will be posted to the web site and stored in your Sqlite database.

Again, you'll see an alert stating that the data was saved.

You Can Edit Your Data Too

If you want to change or add to the Note field or just add a Title, then just make your changes and click the [Save] button again.  This time the data for the record will be updated in the database.
If you do Update the data, then the Updated field will show a date when you last updated.

Now that you've seen it in action, let's take a look at what happens when the user saves an entry to the Journal.  

How The User's Target Database is Used

When you build on top of Sqlstone you'll make sure that the constructor of your WebAPI class methods look very similar to the constructor in I showed you earlier.

JournalEntry Constructor

My target database for the Journal App has a table named JEntry.  When the user creates a new record, the app posts to the JournalEntryController class which has a Save method.

Here's the constructor for that class:

public JournalEntryController(ILogger<JournalEntryController> logger, 
            IConfiguration _configuration,
            IWebHostEnvironment webHostEnvironment)
    {
        _logger = logger;
        templateDbFile = _configuration["templateDbFile"];
        Console.WriteLine($"content rootPath: {webHostEnvironment.WebRootPath}");
        webRootPath = webHostEnvironment.WebRootPath;
        contentRootPath = webHostEnvironment.ContentRootPath;
    }

Each Time The User Posts Data, We Reference Their Copy of Database

This is basically an exact copy of the constructor we looked at for the UserController.  That's because each time the user posts data to the target app (Journal App in our example) we need to know which Database where the data should be saved.  Since we are using the user's specific database we need to know the name of the target database.

When User Posts Data, We Need Two Additional Things

Then, when the user posts data we also need:

1. User's UUID -- so we can get to their File Space directory where there database is stored
2. The JournalEntry which will be saved or updated.

Here's what the Save method looks like that the user will post to.

[HttpPost]
   public ActionResult Save([FromForm] String uuid,[FromForm] JournalEntry jentry){
       Console.WriteLine(jentry.Note);
       Console.WriteLine(jentry.Title);

       ConvertEmptyStringToNull(jentry);
       var userDir = Path.Combine(webRootPath,uuid);
       var userDbFile = Path.Combine(userDir,templateDbFile);
       try{
           JournalEntryContext jec = new JournalEntryContext(userDbFile);
           // id = 0 indicates a new jentry
           if (jentry.Id == 0){
               jec.Add(jentry);
           }
           else{
               JournalEntry? currentEntry = jec.Find<JournalEntry>(jentry.Id);
               currentEntry.Note = jentry.Note;
               currentEntry.Title = jentry.Title;
               currentEntry.Updated = DateTime.Now.ToString("yyyy-MM-dd");
               Console.WriteLine($"updated; {currentEntry.Updated}");
               jec.Update(currentEntry);
               jentry = currentEntry;
               Console.WriteLine($"updated; {jentry.Updated}");

           }
           jec.SaveChanges();

       }
       catch (Exception ex){
           // It's possible that the user has attempted to save an Entry
           // but has never registered the UUID they see in their text box.
           return new JsonResult(new {success=false,error=$"{ex.Message}"});
       }

       return new JsonResult(new {success=true,jentry=jentry});
   }

The code to focus on here is where we build the path to the user's specific database file:

// Create the path to the user's directory using their UUID
var userDir = Path.Combine(webRootPath,uuid);

// Use the wwwroot/<UUID> directory and append the name of the 
// db file (sqlstone_journal.db)
var userDbFile = Path.Combine(userDir,templateDbFile);

try{

     // Create DbContext so we can do SQL queries against user's db
     JournalEntryContext jec = new JournalEntryContext(userDbFile);

Get All The User's Journal Entries

Getting the User's journal entries is very similar.   In that case, we call the JournalEntryController GetAll method, again passing the UUID so we'll reference the correct database.

[HttpPost]
    public ActionResult GetAll([FromForm] string uuid){
        var userDir = Path.Combine(webRootPath,uuid);
        var userDbFile = Path.Combine(userDir,templateDbFile);

        if (!System.IO.File.Exists(userDbFile)){
            return new JsonResult(new {result="No data"});
        }

        JournalEntryContext jec = new JournalEntryContext(userDbFile);

        var entries = jec.JEntry;

        List<JournalEntry> allItems = new List<JournalEntry>();

        foreach (JournalEntry je in entries.AsParallel<JournalEntry>()){
            Console.WriteLine($"{je.Id} : {je.Title} : {je.Note}");
            allItems.Add(je);
        }

        return new JsonResult(allItems);
    }

That's it.  We just use some ReactJS on the client side to iterate over the JSON returned from the GetAll method so we can display all the rows.

Summary of Use

This means that you can get all of this working with your app by:

1. Create your Sqlite database schema -- create a Sqlite database with your tables which will be used as your template.
2. Alter the appSettings.json to reference your Sqlite db.
3. Add the Constructor code to your Controller(s).
4. Include the UUID in each to your Controller methods, so you can determine which user's db to reference.

Get Code & Run Locally

Get the source at my GitHub repo (link at top of this article) or download the code from this article and try it out.

You need .NET Core 8.x.

Once you download an unzip the code, just run the following command from the main sqlstone solution directory.

$ dotnet run --project sqlstone

If you are in the project directory (where .csproj is located) then you can just do the following:

$ dotnet run

Forgot To Mention: Loading Data From UUID

I forgot to mention a huge part of the functionality in this app.  I think it is kind of cool so I want to mention it.  

If you are on one device (desktop computer, for example) and you register your UUID and save numerous Journal Entries, you can retrieve that data on any other device.

Retrieve Your Data To Any Device

Since the JournalEntryController GetAll method depends upon the UUID, if you supply the UUID in the main form it will instantly retrieve all your data.

Here's what I mean.

Retrieve Data Steps

1. Save some data on one device
2. Write down or text your UUID to the other device
3. Load https://newlibre.com/journal on the 2nd device
4. Paste (or type) your UUID into the to the UUID text box
5. Click the [Gen/Set UUID] button -- this loads the UUID and instantly retrieves any associated data from the remote server.

Isn't that cool!?

What Are The Implications?

My theory is that this will scale "better".  Now, instead of hitting one Sqlite database for every user, your app will hit individual user databases.  As I learned in my previous article about how Sqlite handles data -- this will alleviate the wait that could occur while other users are inserting into a single instance of the database. 

Less Waits On Db Inserts

Since .NET Core WebAPIs and WebApps should handle all the concurrent connections I believe this will alleviate a lot of wait time for database resources.

Put the Database Maintenance / Data Ownership On User

With Sqlstone, instead of worrying about backing up the user's data or controlling it in any way we can turn this all back to the user.  If they want their data locally, we will allow them to download the entire Sqlite database (coming in next article).

If they want to entirely delete the Sqlite database from the server, we will allow them to destroy the UUID and all associated data (coming in next article).

Next Article / More Sqlstone Functionality

In the next article we'll add three new pieces of functionality:

1. User delete of individual Journal Entries
2. User download of entire Sqlite database (and how user can use it locally)
3. User delete of remote database and UUID.
4. Add QR Code generator for UUID for easily getting UUID and loading data on other device

Discussion: What Do You Think?

I hope you'll look at the code, try it out and let me know what you think.

I'm very interested in the facts if this would be a valid way to provide a "highly available" data and data ownership for the user.  I'm also wanting interested in whether or not this could make it much easier (less worry) to insure a user's data is maintained (not lost since they can download it). 

I'm thinking about these issues as a single developer who is attempting to support a complete system (required to wear many hats as a sole proprietor).  And, also this would be an alternative to things like Amazon web services where I have to worry about cost overrun and suddenly getting huge bills because of overuse.

History

First publication: 2024-06-20