Turning Firebase into a Local-First, Reactive Experience

Have you ever wondered why JSON databases became so popular? Ultimately, it was their simplicity that won over developers. JSON databases let you put your objects straight into the database without worrying about schemas. Plus, if your app runs without a server then everything you need is right there on the client, synced and ready to go at your fingertips.

But, JSON databases come with at least two pretty big catches. First, the query language often leaves much to be desired. Second, you find yourself constantly making trips to the server, making your app slow and pretty much useless when you’re offline.

What if you could keep all your data local, always in-sync, and easily queryable with SQL? Well, you can, and it’s surprisingly simple with SKDB. We’ll use Firebase for our example, but the same approach works with any JSON database (like Mongo). Let’s jump in!

Getting Started

First, we need to set up and connect to Firebase and get everything ready. We’ve even set up a demo database specifically for this walkthrough (the whole code is available here).

import { initializeApp } from "firebase/app";
import { getDatabase, ref, onValue } from "firebase/database";
import { createSkdb } from "skdb";

const firebaseConfig = {
  databaseURL: "https://skdb-demo-default-rtdb.firebaseio.com",
};

const app = initializeApp(firebaseConfig);

Data Ingestion

Let’s get the data we care about out of Firebase.

const db = getDatabase();
const dbRef = ref(db, '/');

let data = await new Promise((resolve, reject) =>
  onValue(dbRef, (snapshot) => resolve(snapshot.val()))
);

And ingest it into SKDB.

const skdb = await createSkdb();
await skdb.exec("CREATE TABLE json_products (v JSON);");
await skdb.insertMany('json_products',
  data.map(x => { return {v: JSON.stringify(x)}; })
);

Inspecting the Data

So far, we’ve dumped a bunch of JSON into an SQL table. Before we dive deeper, let’s see what our data looks like. Let’s run the classic SELECT * ... query.

await skdb.exec(`SELECT * FROM json_products LIMIT 10`)

The result looks like this:

  {
    v: '{"brand": "Al Munakh", "category": "fragrances", "description": "Original Al Munakh\\u00ae by Mahal Al Musk | Our Impression of Climate | 6ml Non-Alcoholic Concentrated Perfume Oil", "discountPercentage": 15.6, "id": "14", "images": ["https://cdn.dummyjson.com/product-images/14/1.jpg", "https://cdn.dummyjson.com/product-images/14/2.jpg", "https://cdn.dummyjson.com/product-images/14/3.jpg", "https://cdn.dummyjson.com/product-images/14/thumbnail.jpg"], "price": 120, "rating": 4.21, "stock": 114, "thumbnail": "https://cdn.dummyjson.com/product-images/14/thumbnail.jpg", "title": "Non-Alcoholic Concentrated Perfume Oil"}'
  },
  {
    v: '{"brand": "AmnaMart", "category": "furniture", "description": "Material: Stainless Steel and Fabric Item Size: 110 cm x 45 cm x 175 cm Package Contents: 1 Storage Wardrobe", "discountPercentage": 7.98, "id": "35", "images": ["https://cdn.dummyjson.com/product-images/35/1.jpg", "https://cdn.dummyjson.com/product-images/35/2.jpg", "https://cdn.dummyjson.com/product-images/35/3.jpg", "https://cdn.dummyjson.com/product-images/35/4.jpg", "https://cdn.dummyjson.com/product-images/35/thumbnail.jpg"], "price": 41, "rating": 4.06, "stock": 68, "thumbnail": "https://cdn.dummyjson.com/product-images/35/thumbnail.jpg", "title": "3 DOOR PORTABLE"}'
  },
...

It looks like a list of products, with their description. If you are like me, you have probably spent too many hours of your life staring at JSON, wondering what is actually in there. Fortunately, SKDB offers a solution. You can use json_infer_schema to infer the type of any JSON column (the function json_schema_pretty formats the result to be more human-readable).

(await skdb.exec(`
  SELECT json_schema_pretty(json_infer_schema(v)) AS schema
    FROM json_products
`)).scalarValue()

The result:

  {
    "brand": string,
    "category": string,
    "description": string,
    "discountPercentage": int | float,
    "id": string,
    "images": [string],
    "price": int,
    "rating": int | float,
    "stock": int,
    "thumbnail": string,
    "title": string
  }

Now, we get a clear view of our data, showing us details like brand, category, description, and more. Even though most of the data is straightforward, it does have some structure: the images field uses an array, which we will have to change if we want our data to be easily queryable in SQL.

Making JSON Data SQL-Friendly

Next, we transform this data into SQL using json_extract. json_extract, given a pattern, produces an SQL table with all the matching data. For example, {email<string>} will extract the content of the field email if it is a string. %.email: v will extract all fields called “email” regardless of their type or location in the input JSON’s structure. You can find the docs here.

Also, json_extract is always defined in a reactive view, one of the core concepts of SKDB. A reactive view is a special kind of view that is always maintained up-to-date by the database. In the following example, any change in the json_product table will be reflected in the view products.

With that in mind, let’s create a reactive view for our products.

await skdb.exec(`
  CREATE REACTIVE VIEW products AS
    json_extract(
      json_products,
      v,
      '{
         id<string>,
         brand<string>,
         title<string>,
         category<string>,
         price<int>,
         rating<num>
       }'
  );
`);

Past this point, we have an SQL table called products that we can query in SQL like any other table! Let’s add a couple of indexes to speed things up.

await skdb.exec("CREATE UNIQUE INDEX pk_products ON products(id);");
await skdb.exec("CREATE INDEX products_category ON products(category);");

For images, we will need a more complicated pattern. Since images are stored in arrays, we need to “flatten” them to make them SQL friendly. Concretely, we want to convert JSON like {id: 23, ["image1", "image2"]} into two separate SQL rows: 23, "image1" and 23, "image2". Fortunately, json_extract has a construction to do just that by appending [] to a field name.

await skdb.exec(`
  CREATE REACTIVE VIEW images AS 
    json_extract(
      json_products,
      v,
      '{
         id<string>,
         thumbnail<string>,
         images[]:image<string>
       }'
    )
`);

Just like that, we have two SQL views: products and images. We can search them like we would any other SQL table, or add indexes to them. SKDB’s reactive nature lets you create any number of reactive views directly in SQL: defining a client-side data layer has never been easier!

Client-side data layer

Reactive views are at the core of SKDB. You are going to use them whenever you want a specific view to be maintained up-to-date by the database. For example, let’s create a view for top-rated products:

await skdb.exec(`
  CREATE REACTIVE VIEW top_products AS
    SELECT * from products ORDER BY rating DESC LIMIT 10;
`);

Every time the underlying data changes, SKDB will update this view, and it will do so incrementally (without ever re-running the query from scratch). That completely changes how you should think about your data. You can create layers and layers of views that contain exactly what you need.

For example, imagine you want to be able to search for categories and sort the result by average product rating.

You would first create a reactive view:

await skdb.exec(`
  CREATE REACTIVE VIEW category_by_rating AS
    SELECT category, avg(rating) as average_rating
    FROM products
    ORDER BY average_rating
`);

You can now search categories very efficiently, with queries as simple as:

SELECT * FROM category_by_rating WHERE ...

Watching for Changes

When building reactive applications, it’s essential to respond to changing data in real-time. SKDB simplifies this process by providing a straightforward method to watch for changes in any SQL query. The watchChanges function allows you to define custom behavior for handling initial data loads, additions, and removals. This feature is particularly useful for applications that require real-time data updates, such as dashboards, live feeds, or any system that relies on the latest information.

Here’s an example of how to use skdb.watchChanges:

await skdb.watchChanges(
  "SELECT * FROM products",
  {},
  (initialRows) => {
    // Handle the initial set of rows
    console.log("Initial data:", initialRows);
  },
  (added, removed) => {
    // Efficiently manage additions
    added.forEach(row => {
      console.log("ADDED:", JSON.stringify(row));
    });

    // Handle removals with care
    removed.forEach(row => {
      console.log("REMOVED:", JSON.stringify(row));
    });
  }
);

This approach ensures that your application remains efficient and responsive, only reacting to changes that directly affect your dataset.

Simplifying Reactivity with useQuery

For developers working within the React ecosystem, managing state and reactivity can sometimes become cumbersome, especially when dealing with external data sources like databases. SKDB addresses this challenge with the skdb-react library and its useQuery hook, which is designed to seamlessly integrate SQL queries into your React components. This hook automatically refreshes your component whenever the underlying data changes, ensuring that your UI is always in sync.

Integrating useQuery is straightforward and significantly simplifies data management:

const products = useQuery("SELECT * FROM products LIMIT 10");
// The component automatically updates when one of the first 10 products changes

This pattern not only reduces boilerplate code but also encapsulates the complexity of data fetching and synchronization, allowing you to focus on building your application’s core features.

If you want examples on how to quickly get started, you can checkout this repo.

Tying it all together

Finally, we need to watch the changes coming from Firebase to have a real end-to-end reactive experience. You can use the onSnapshot primitive to listen for Firebase changes and have them reflected in SKDB. In the other direction, you can watch for local changes (using the primitive watchChanges) and communicate them to Firebase. Since this code is not very hard to write and somewhat application specific, we leave that as an exercise to the reader ;)

Conclusion

Leveraging the power of JSON databases like Firebase alongside a tool like SKDB can dramatically enhance your application’s data handling capabilities. By combining the flexibility of JSON with the robust querying capabilities of SQL, you can create a highly responsive, efficient, and dynamic application that stays in sync across all user interactions.

Embrace the future, embrace reactive programming and see how it transforms your application development process!

alt text

results matching ""

    No results matching ""