Using Persisting Data: SQLite and Realm

Yesterday, Realm announced the release of their latest version. v2.0. I realized that Realm it’s getting mature and I think this is the time for developers to check and try out how Realm works.

So, are you looking for ways to persist your data on mobile apps?

Do you want to know SQLite and Realm?

In this post, we will cover how to install SQLite and Realm in your projects and how to do simple CRUD on each that will persist your data on a mobile application.

Requirements:

  • Visual Studio 2017 or Visual Studio on Mac
  • 2 new projects. 1 for testing SQLite  and 1 for testing Realm

There are two popular NuGet Packages that you can use to persist your entities on your apps.

First is the SQLite on a non-MVVM demo, and the other one is Realm on an MVVM demo.

Here’s you can get SQLite and Realm via NuGet  Package Manager then install it on each platform in your solution folder.

For SQLite, just search sqlite-net-pcl. The author should be Frank A. Krueger, like the one you will see below.

While for the Realm, just search Realm. The author is also named Realm as you can see below. You

What is SQLite?

  • A lightweight, zero-configuration, transactional database engine built into most mobile devices
  • Big data in a little package
  • Portable and has no server
  • Writes directly to common media
  • Cross-platform

 

What is Realm?

  • Realm is a cross-platform mobile database, released in July 2014
  • It is a data persistence solution designed specifically for mobile applications
  • Realm store data in a universal, table-based format
  • It is simple as data is directly exposed as objects and queryable by code, removing the need for ORM’s maintenance issues
  • Realm is faster than raw SQLite on common operations while maintaining an extremely rich feature set

 

You will be doing the same UI and requirement for your SQLite demo and Realm demo.

The app is very simple. It adds, updates, deletes an object in your listview.

So let’s start and check out the differences.

Extra Work

There is an extra work that you need to do in SQLite. You need to create an interface in your PCL then implement it on your Android, iOS, and UWP project.

SQLite

    
//In your PCL project
    public interface ISQLite
    {
        SQLiteAsyncConnection GetConnection();
    }
    
//In your Android project
using FooApp;
using FooApp.Droid;
using FooApp.Persistence;
using System.IO;
using Xamarin.Forms;
using Environment = System.Environment;

[assembly: Dependency(typeof(SQLite_Android))] //Don't forget this. This one is hard to debug
namespace FooApp.Droid
{
    internal class SQLite_Android : ISQLite
    {
        public SQLiteAsyncConnection GetConnection()
        {
            var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsPath, "mydatabase.db3");

            return new SQLiteAsyncConnection(path);
        }
    }
}
    
//In your iOS project
using FooApp;
using FooApp.iOS;
using System;
using System.IO;
using FooApp.Persistence;
using Xamarin.Forms;

[assembly: Dependency(typeof(SQLite_iOS))] //Don't forget this. This one is hard to debug
namespace FooApp.iOS
{
    internal class SQLite_iOS : ISQLite
    {
        public SQLiteAsyncConnection GetConnection()
        {
            var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal);
            var path = Path.Combine(documentsPath, "mydatabase.db3");

            return new SQLiteAsyncConnection(path);
        }
    }
}

Realm

  • None

 

Model

SQLite

  • Has a PrimaryKey and AutoIncrement attributes/annotations
  • INotifyPropertyChanged updates the state of your property. This is useful in updates because ObservableCollection only updates the Add and Remove methods.

Realm

  • Does not have an AutoIncrement attribute/annotation. You implement this every time you add a new object in your collection
  • Subclass your model to RealmObject
  • Does not need INotifyPropertyChanged because it already ha Fody
    
//SQLite
    public class Recipe : INotifyPropertyChanged
    {
        [PrimaryKey, AutoIncrement]
        public int Id { get; set; }

        private string _name;

        [MaxLength(255)]
        public string Name
        {
            get => _name;
            set
            {
                _name = value;
                OnPropertyChanged();
            }
        }
   }
    
//Realm
    public class Recipe : RealmObject
    {
        [PrimaryKey]
        public int Id { get; set; }
        public string Name { get; set; }
    }

 

Connecting to Database

    
//SQLite
        public MainPage()
        {
            InitializeComponent();

            _databaseConnection = DependencyService.Get().GetConnection();
        }

        protected override async void OnAppearing() //This method runs before the UI appears
        {
            await _databaseConnection.CreateTableAsync();
            List recipes = await _databaseConnection.Table().ToListAsync();
            _observableRecipes = new ObservableCollection(recipes);
            MyListView.ItemsSource = _observableRecipes;
            base.OnAppearing();
        }
    
//Realm
        public MainPageViewModel()
        {
            AddCommand = new Command(Add);
            UpdateCommand = new Command(Update);
            DeleteCommand = new Command(Delete);
            _realmDb = Realm.GetInstance(); //Connecting to the instance of your apps' Realm Db
            recipes = _realmDb.All().ToList(); //Getting all your objects and putting them in a List
            Recipes = new ObservableCollection(recipes); // Converting your List to ObservableCollection
        }  

 

Creating

    
//SQLite
        private async void OnAdd(object sender, EventArgs e)
        {
            Recipe recipe = new Recipe { Name = $"Recipe {DateTime.UtcNow.Ticks}" }; //with time stamp
            await _databaseConnection.InsertAsync(recipe);
            _observableRecipes.Insert(0, recipe);
        }
    
//Realm
        private void Add()
        {
            var recipe = new Recipe
            {
                Name = $"Recipe {DateTime.UtcNow.Ticks}", //with time stamp
                Id = _realmDb.All().Count() + 1 //this is how you increment your id by 1
            };
            _realmDb.Write(() => { _realmDb.Add(recipe); });

            Recipes.Add(recipe);
        }

 

Updating

    
//SQLite
        private async void OnUpdate(object sender, EventArgs e)
        {
            Recipe recipe = _observableRecipes[0];
            recipe.Name += " (updated)";
            await _databaseConnection.UpdateAsync(recipe);
        }
    
//Realm
        private void Update()
        {
            var i = _realmDb.All().Count() - 1;
            var recipe = Recipes[i];
            _realmDb.Write(()=> { _realmDb.Add(recipe, true).Name += $" [update]" ; });
        }

 

Deleting

    
//SQLite
        private async void OnDelete(object sender, EventArgs e)
        {
            Recipe recipe = _observableRecipes[0];
            await _databaseConnection.DeleteAsync(recipe);
            _observableRecipes.Remove(recipe);
        }
    
//Realm
        private void Delete()
        {
            var i = _realmDb.All().Count() - 1;
            var recipe = Recipes[i];
            using (var transact = _realmDb.BeginWrite())
            {
                _realmDb.Remove(recipe);
                transact.Commit(); //You must explicitly Commit the transaction, or it will automatically be rolled back. 
            }
            Recipes.Remove(recipe);
        }

 

Use Cases:

I personally use Realm whenever I have a one-to-many relationship in my table.

For instance

    
//Realm
    public class Product
    {
        public string ProductId { get; set; }

        public string Brand { get; set; }

        public IList<ProductColor> ProductColors { get; } //Not easy to use in SQLite
   }

 

And if your model doesn’t have a one-to-many relationship, you can just use SQLite

    
//SQLite
    public class Product
    {
        public string ProductId { get; set; }

        public string Brand { get; set; }

        public string Description { get; set; }
   }

 

There you have it. These are just two very simple CRUD in developing an iOS and Android apps. The purpose of this is for you to know what to download and how to do a simple CRUD in a persisting data. You can download or clone these two repositories from my GitHub.

 

My SQLite sample on GitHub (Logic in Code-behind)

https://github.com/webmasterdevlin/Xamarin-Forms-CRUD-in-SQLite

 

My Realm sample on GitHub (in MVVM design Pattern)

https://github.com/webmasterdevlin/XamarinForms-CRUD-in-Realm