Database Testing For Front-End Developers: Checklist, Tips & Code (2022)

Olivia Rhye
11 Jan 2022
5 min read

Database security is not a given: a third of all businesses will experience a data breach in the next two years. And with a data breach taking 191 days on average to be discovered, it is no surprise that security risks can potentially cost you millions of dollars! This is why database testing is key to fix potential leaks early before they blow up your entire infrastructure.

Our job at Rowy is to make working with databases easy, so you will find in the following article how to integrate the best practices we use to protect our customers’ data from your front-end interface. Don’t wait too long to add them to your roadmap!

What Is Database Testing

Database testing is a set of practices making sure your database behaves as it is expected to:

  • Data and metadata are stored without information loss.
  • Data manipulation doesn’t result in unintended information loss.
  • Client-facing accesses are secured.
  • Connectivity problems are handled.

4 Reasons To Test Your Database

  • The only purpose of software systems is to pass information from one human to another: there is no app without data! Data accuracy ensured by regular tests is key for businesses and individuals because it’s a prerequisite to making better decisions. You simply can’t get work done when your data is compromised.
  • Security risks are a huge source of stress. Imagine years of hard work going down the drain because a complete stranger got access your administration dashboard! Database testing brings peace of mind because it proposes a holistic approach leaving no angle for hackers to utilize.
  • A data breach is catastrophic. It will not only cost you time and money, but also reputation. You can’t just walk away from a security breach and keep going as if nothing happened―there are legal consequences (e.g GDPR protection)! Receive a data breach notification from a company you trusted with an important part of your life is terrible user experience.
  • Tests improve the quality of your development workflow. Finding out hidden issues ahead of a release in production will naturally help improve the overall user experience and save you from headaches down the road.

Database Testing Checklist: 6 Best Practices

To make it easier to build a database, we are going to use Rowy. Set up a database and create a table called Blog with a single column named title. This Rowy table is connected to a live Firestore database collection and ready to use.

We can now jump into a Javascript repository in your local machine and get started.

1. Database Authentication & Authorization

First, you need to make sure your database access is protected. You don’t want anyone to be able to access private data they aren’t supposed to look at. Authentication and authorization solve this.

Authentication verifies the identity of a user. When you log in your favorite social media platform, you need to provide your email and a password: that’s authentication. By default, only you can access the database as an administrator. Let’s make sure of that first. In Javascript, you can use the mocha library to write simple unit tests in any repository:

import assert from 'assert'
import { initializeApp } from "firebase/app"

describe('Database', () => {
  describe('#auth()', () => {
		let firebaseConfig = {
	    # ...
			apiKey: "FIREBASE_API_KEY",
			# ...
	  }

    it('connecting to the database requires the right API key', () => {
		  assert.doesNotThrow(() => {
        initializeApp(firebaseConfig)
      });
    })

		it('the wrong API key triggers an error', () => {
        firebaseConfig.apiKey = "wrong API key"

        assert.throws(() => {
          initializeApp(firebaseConfig)
        });
    })
  })
})

Just run npm run mocha from the command line and you'll obtain your test results:

$ npm run mocha

Database
  #auth()
    ✔ connecting to the database requires the right API key
    ✔ the wrong API key triggers an error

2 passing (9ms)

Done in 0.28s.

A database isn’t useful if you can’t have non-admin users interact with it. You can use an auth provider like Firebase Authentication or Auth0 to remedy that.

At this point, you can authenticate any type of users against your database. But you also need to take care of authorization―making sure user accounts have the correct parameters to access and manage their own data. In Firebase, you can set up security rules to do just that.

For example, we can tell Firebase to prevent access to any document unless the user is authenticated. This is useful for an app where you need to login first before being able to use it. In the Firebase console:

service cloud.firestore {
  match /databases/{database]/documents {
    match /{document=**} {
      allow read, write: if request.auth != null;
    }
  }
}

And the test:

import assert from 'assert'
import { initializeApp } from "firebase/app"
import { getFirestore, collection, query, getDocs } from "firebase/firestore"

describe('Database', () => {
  describe('#auth()', () => {
		it("can't access collection because of authorization rule", async () => {
			let firebaseConfig = {
		    # ...
				apiKey: "FIREBASE_API_KEY",
				# ...
		  }
	
			const app = initializeApp(firebaseConfig)
			const db = getFirestore(app) 
			
			const blogRef = collection(db, "Blog")
			const blogs = query(blogRef)
			
			await assert.rejects((async () => {
			  const res = await getDocs(blogs)
			})())
		})
	})
})

2. Functional Testing

The second thing you need to test is if you can actually do what you want to do with the database. If the database transactions do not work as expected, your feature is broken. Functional testing makes sure you can provide the service your users expect.

You want to be at least able to create, read, update, and delete (CRUD) database documents, preferably using a randomized dataset while respecting specified constraints. Let’s try to create a new blog post:

await setDoc(doc(db, "Blog", "a random id"), {
  title: "a new blog post"
});

We can always check in Rowy if we are tired of testing the result with code:

Adding a document to Rowy

Functional testing also validates the data―making sure you have the right data types, or **** testing for SQL injection patterns, for example. ****Whatever kind of database you’re using, you’ll need your data to follow a certain structure: structural testing makes sure the documents follow the design described by your business requirements. You want to test things like collection names, properties, and indexes, to make sure they match your schema specification.

Say you have a view counter for each article, you should test it’s a number before updating its value in the database:

assert.equal(isNaN(blog_post.view_count), false)

3. Database Server Testing & Monitoring

Since a database is a software like any other, you need a server to host it and make it available online for other services to use. And if the server goes down, so does your database! You can avoid that by following 3 best practices:

  • Leverage environment separations - use separate databases for development, testing, and production
  • Monitor your servers - use HTTP requests to know when your database is down
  • Enable database logs - make sure your database tells you when something goes wrong and why

Running separate coding environments for different stages of your product lifecycle is key to efficiently catching and fixing bugs. You’ll need at least a development environment to interact with new features on a local machine and a production environment where people buy your services. With Firebase, you can set your Security Rules for a given Firestore instance to use Test mode:

// Allow read/write access to all users under any conditions
// Warning: **NEVER** use this rule set in production; it allows
// anyone to overwrite your entire database.
service cloud.firestore {
  match /databases/{database}/documents {
    match /{document=**} {
      allow read, write: if true;
    }
  }
}

With a managed database service like Rowy built on top of Firebase, you’ll set up one project per environment. It’ll only take a click to switch between the two:

Rowy projects

And integrating changes from one environment to another is as simple as copy/pasting:

Duplicating tables across projects

Once your database is available in production, you want to make sure it is available and performing well at all times. That’s where monitoring tools come in. A monitoring service like Firebase’s usage dashboard will check your database server activity and report in real-time:

Performance monitoring

4. Load Testing

A single database can only handle a limited number of concurrent connections, so you’ll need to test how much load your infrastructure can handle to avoid down-times. Load testing measures the resistance of your database against crashes and traffic spikes, allowing you to scale your database infrastructure ahead of time.

Let’s say you have a web page that query rows from a database. You can use a Javascript library like the k6 package to ping this live webpage before remaining idle for a second:

//load-test.js

import http from 'k6/http';
import { sleep } from 'k6';

export default function () {
  http.get('https://my-cool-website.com');
  sleep(1);
}

And then we use k6’s CLI to simulate 100 users connecting to this webpage in the span of 10 seconds:

k6 run --vus 100 --duration 10s load-test.js

If you use a cloud provider like Firebase, make sure to use a development environment, or you might cause a denial of service attack without noticing. Also, don’t play too much with it unless you want your Firebase bill to explode!

Speaking of DoS attacks, they are the most common type of cyber attacks nowadays―78,558 DDoS attacks in Q2 2022 alone!―so make sure to have a way to monitor whether your database can handle the scale or not. In production, you might want to look into installing a load balancer (a reverse proxy that distributes traffic among different databases―complex to configure) and having your database cached data served over a Content Deliverivery Network (like Cloudflare’s).

One of the main advantages of database providers like Rowy / Firebase is the ability to scale to infinity without any additional work, which is welcome if you aren’t a database expert. Thanks to the Firebase Web Performance Monitoring dashboard, you can monitor what happens with your database performance in real-time:

Firebase Performance Monitoring

5. Backup Monitoring

You need to account for the worst case scenario where your database would be corrupted by a black swan event, resulting in a loss of data. Having a backup system ready is mandatory to mitigate data loss, but you’ll also need to monitor the backup system in case it breaks down. 3 steps to implement a robust database backup process:

  • Run the backup command - Every database engine has its own way to backup data. You’ll need to look up the documentation to find it.
  • Create redundant backup copies in different locations - Servers can also go down. The more places you can store backups, the less likely you are to lose them.
  • Schedule your backup job at least once a day - Depending on how fast your data changes, you’ll to find a sweet spot between security and performance: the more often you backup your database, the less risk you’ll have to lose any change, but if each backup takes an hour because of how much data you have, you might neither have the space nor the time to save each change.

In Firestore, you can export / import data any time from the Google Cloud Platform Console:

Google Cloud data import/export

Combined with a scheduled data export using a Cloud function and a Cloud Scheduler job, you can automate your database backup completely:

Google Cloud scheduler

6. Automate Your Tests

Lastly, tests aren’t any good if you don’t actually run them on each breaking change you make to your database: integrate your tests in your CI/CD pipeline! Chances are you are using devops tools like Github Actions to deliver your code to a production environment. Don’t hesitate to copy/paste all the tests above and run them on each commit!

Let’s say we want to run our tests on each git push―this is the workflow you’ll need to run your test suite:

name: Database Testing a React app

on:
  push:
    branches: [ main ]

jobs:
  build:
    runs-on: ubuntu-latest

    strategy:
      matrix:
        node-version: [10.x, 12.x, 14.x, 15.x]

    steps:
      - uses: actions/checkout@v3
      - name: Use Node.js ${{ matrix.node-version }}
        uses: actions/setup-node@v3
        with:
          node-version: ${{ matrix.node-version }}
      - run: npm install
      - run: npm run mocha

Use Rowy To Manage Your Database

Rowy offers all the benefits of a feature-complete database management system, with a low learning curve and built-in best practices for database testing! It’s the perfect way to minimize your costs of switching to a secure database.

The best part? We’ve got plenty of templates to get you up and running in 2 minutes! So don’t hesitate and try Rowy for free.

Olivia Rhye
11 Jan 2022
5 min read
Get started with Rowy in minutes

Continue reading

Browse all