Backup Cloudflare D1 to SQL File in R2 with Sveltekit

Published: Sep 9, 2024

Introduction

In our previous post, we delved into setting up authentication in a SvelteKit application using GitHub OAuth, hosted on Cloudflare Pages with a D1 database. This setup provided a robust foundation for securing user access to your application, integrating a seamless authentication flow with a database-backed session management system.

However, as your application evolves and manages increasingly critical data, it’s essential to ensure that your database is not only functional but also securely backed up. Regular backups are a crucial part of maintaining data integrity and ensuring that your application can recover from unexpected incidents, such as data corruption or accidental deletions.

In this post, we’ll build upon the foundation we established earlier by implementing an automated and secure backup process for your SvelteKit application. We’ll be leveraging Cloudflare’s R2 storage to store our backups and GitHub Actions to automate the process. To ensure that these backups are triggered securely, we’ll employ TOTP (Time-based One-Time Password) authentication.

By the end of this guide, you’ll have a fully automated backup system in place, safeguarding your data with regular, secure backups stored in the cloud—giving you peace of mind as your application continues to grow.

The completed code can be viewed on github.

wrangler d1 time-travel my-database --before-timestamp=1683570504

Updated Project Structure

Here’s the updated project structure reflecting the current setup for this post:

my-sveltekit-app/
├── src/
│   ├── routes/
│   │   ├── api/
│   │   │   ├── backup/+server.ts   # Create
│   │   │   └── users/+server.ts
│   │   ├── profile/
│   │   │   ├── +page.svelte
│   │   │   └── +page.server.ts
│   │   ├── +page.svelte
│   │   ├── +layout.svelte
│   │   └── +layout.server.ts
│   ├── lib/
│   │   └── TOTP.js                 # Create
│   ├── app.d.ts
│   ├── app.html
│   ├── auth.ts                     # Update
│   └── hooks.server.ts
├── genTotp.js                      # Create
├── static/
├── package.json
├── svelte.config.js
├── tsconfig.json
├── wrangler.toml                   # Update
└── .github/
    └── workflows/
        └── backup.yml              # Create

Key Files Breakdown:

• src/routes/api/backup/+server.ts: The API route that handles database backups and interacts with Cloudflare R2 for storage.
• src/lib/TOTP.js: A custom utility file for handling TOTP (Time-based One-Time Password) verification, used for secure API access.
• genTotp.js: A file for generating a TOTP token. Relies on ‘TOTP.js’ file above, and TOTP_SECRET environment variable.
• .github/workflows/backup.yml: GitHub Actions workflow to automate backup operations.
• wrangler.toml: Configuration for Cloudflare Workers and Pages, including environment variables for your application and R2 storage.

This structure reflects the new features related to database backups, incremental backups, and chunking logic, as well as automated backup workflows with GitHub Actions.

Understanding the Requirements

When building a backup system for your SvelteKit app on Cloudflare, it’s crucial to consider various factors to ensure a robust, secure, and efficient solution. Let’s dive into the key requirements and considerations:

Security: Your First Line of Defense

Security should be at the forefront of your backup strategy. Here’s why:

1. Data Sensitivity: Backups often contain a goldmine of information. Imagine if a hacker got their hands on your entire database!
2. Regulatory Compliance: Depending on your industry, you may be legally required to protect user data, even in backups.
3. Trust: A security breach can severely damage your users’ trust in your application.

To address these concerns, we’ll implement:

• Time-Based One-Time Passwords (TOTP) for API authentication
• Encryption for data in transit and at rest
• Access controls on our Cloudflare R2 storage

Automation: Set It and (Almost) Forget It

While manual backups are better than no backups, automation is key to a reliable system. We’ll use Github Actions to schedule our backups.

Consider the following factors when setting up your backup schedule:

• Frequency: Daily? Weekly? It depends on how often your data changes and how much data loss you can tolerate.
• Timing: Run backups during off-peak hours to minimize impact on your app’s performance.
• Retention: How many backups do you keep? A common strategy is:
  • Daily backups for the past week
  • Weekly backups for the past month
  • Monthly backups for the past year

Note: In this tutorial, we’ll demonstrate a monthly backup schedule, leveraging Cloudflare D1’s Time Travel feature for more granular data recovery. While Time Travel provides a powerful tool for reverting to specific points in time, regular backups still serve as a valuable safety net and can be crucial for long-term data preservation.

Restoration: Hope for the Best, Prepare for the Worst

A backup is only as good as your ability to restore from it. Always have a well-documented, tested restoration process. Here’s a basic outline:

1. Retrieve the latest backup from R2
2. Decrypt the backup if it’s encrypted
3. Run the SQL commands to recreate your database structure
4. Import the data
5. Verify the restored data for integrity

Remember, practice makes perfect. Regularly test your restoration process to ensure it works when you need it most.

By carefully considering these requirements, we’re setting ourselves up for success in creating a robust backup system for our SvelteKit app on Cloudflare.

Setting Up R2 and Environment Variables

Before our backup system can work, we need to set up Cloudflare R2 and configure some environment variables. Here’s how:

1. Set up R2 at Cloudflare:

   • Log into your Cloudflare account and go to the R2 section.
   • Click “Create bucket” and give it a name, like “my-app-backups”.
   • Note down the bucket name, you’ll need it later.

2. Update your wrangler.toml file: Add these lines to your wrangler.toml:

   
   

   [[r2_buckets]]
   binding = "MY_BUCKET"
   bucket_name = "my-app-backups"

   
   

   Replace “my-app-backups” with your actual bucket name, and fill in your database details.

3. Generate a TOTP secret: Run this Node.js command in your terminal:

   
   

   node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

   
   

   This will generate a random string to use as your TOTP secret.

4. Set up environment variables:

   • Go to your Cloudflare Pages project settings.
   • Find the “Environment variables” section.
   • Add a new variable named TOTP_SECRET and paste in the secret you generated.

Setting up local testing

Now, you might be wondering, “How do I set this up on my local machine for testing?” Great question! Here’s how:

1. Create a .env file in your project root (if you haven’t already).
2. Add this line to your .env file:

   TOTP_SECRET=your_generated_secret_here

   Replace your_generated_secret_here with the TOTP secret we just generated.
3. Ensure your .env file is added to your .gitignore file. We don’t want to accidentally share our secrets!

Implementing TOTP Authentication

In the world of API security, Time-Based One-Time Passwords stand out as a robust method to protect sensitive operations. Let’s dive into how we can implement TOTP for our backup system.

What is TOTP and Why Use It?

TOTP is a dynamic password system that generates short-lived codes based on:

1. A shared secret
2. The current time

Here’s why it’s perfect for our backup API:

• Short-lived: Codes typically last 30 seconds, minimizing the window for attacks.
• No password storage: Unlike API keys, we don’t need to store passwords on the server.
• Easy to use: Many authenticator apps support TOTP.

Setting Up the TOTP Library

To secure our backups with Time-based One-Time Passwords, we’ll be using a custom TOTP class. This class will handle token generation and verification, ensuring that only authorized users can trigger the backup process. Here’s the start of our TOTP.js file:

export class TOTP {
  constructor(secret, digits = 6, step = 30) {
    this.secret = secret;
    this.digits = digits;
    this.step = step;
  }

  async generateTOTP(time) {
    // Implement
  }

  base32ToUint8Array(base32) {
    // Implement
  }

  async verify(token, time = Date.now()) {
    // Implement
  }
}

The constructor initializes the class with three key parameters:

• secret: The shared secret between the server and client.
• digits: The length of the token (default is 6 digits).
• step: The time step interval in seconds (default is 30 seconds).

Generating TOTP Tokens

The generateTOTP method creates a one-time password based on the current time and the shared secret. Each token is valid for a single time interval, usually 30 seconds, ensuring that tokens expire quickly and reduce security risks.

export class TOTP {
    constructor(secret, digits = 6, step = 30) {
        // remains the same
    }
    async generateTOTP(time) {
      let counter = Math.floor(time / this.step);
      const data = new Uint8Array(8);
      for (let i = 7; i >= 0; i--) {
        data[i] = counter & 0xff;
        counter >>= 8;
      }
  
      const key = await crypto.subtle.importKey(
        'raw',
        this.base32ToUint8Array(this.secret),
        { name: 'HMAC', hash: 'SHA-1' },
        false,
        ['sign']
      );
  
      const signature = await crypto.subtle.sign('HMAC', key, data);
      const dataView = new DataView(signature);
      const offset = dataView.getUint8(19) & 0xf;
      const otp = dataView.getUint32(offset) & 0x7fffffff;
  
      return (otp % Math.pow(10, this.digits)).toString().padStart(this.digits, '0');
    }

  base32ToUint8Array(base32) {
    // Implement
  }

  async verify(token, time = Date.now()) {
    // Implement
  }
}

Handling Base32 Secrets

TOTP secrets are often encoded in Base32 format, especially when they’re shared via QR codes for 2FA. We need to convert this Base32-encoded string into a binary format (Uint8Array) that we can use with cryptographic functions.

Here’s the base32ToUint8Array method:

export class TOTP {
    constructor(secret, digits = 6, step = 30) {
        // remains the same
    }
    async generateTOTP(time) {
        // remains the same
    }

    base32ToUint8Array(base32) {
        const alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';
        let bits = 0;
        let value = 0;
        let index = 0;
        const output = new Uint8Array(Math.ceil(base32.length * 5 / 8));

        for (let i = 0; i < base32.length; i++) {
            value = (value << 5) | alphabet.indexOf(base32[i]);
            bits += 5;
            if (bits >= 8) {
                output[index++] = (value >>> (bits - 8)) & 255;
                bits -= 8;
            }
        }

        return output;
    }

  async verify(token, time = Date.now()) {
    // Implement
  }
}

Verifying the Token

The verify method checks whether the provided token matches the one generated by the server. To account for slight time discrepancies between the client and server, it checks the tokens for the current time as well as for the previous and next time steps.

export class TOTP {
    constructor(secret, digits = 6, step = 30) {
        // remains the same
    }
    async generateTOTP(time) {
        // remains the same
    }

    base32ToUint8Array(base32) {
        // remains the same
    }

    async verify(token, time = Date.now()) {
        const currentTime = Math.floor(time / 1000);
        for (let i = -1; i <= 1; i++) {
            const currentToken = await this.generateTOTP(currentTime + i * this.step);
            if (token === currentToken) return true;
        }
        return false;
    }
}

Testing the TOTP Class

Now that we’ve implemented the TOTP class, it’s important to test its functionality to ensure that the tokens are being generated and verified correctly.

Here’s how you can write a test script for the TOTP class:

import { TOTP } from './src/lib/TOTP.js';

const secret = 'JBSWY3DPEHPK3PXP';  // Example base32-encoded secret
const totp = new TOTP(secret);

async function runTests() {
  // Test token generation
  const testTime = 1624044672;
  const generatedToken = await totp.generateTOTP(testTime);
  console.log(`Generated token at time ${testTime}:`, generatedToken);

  // Test valid token verification
  const validToken = await totp.generateTOTP(Math.floor(Date.now() / 1000));  // Generate a token based on current time
  const isValid = await totp.verify(validToken);
  console.log(`Verification result for token ${validToken}:`, isValid);

  // Test invalid token verification
  const invalidToken = '123456';
  const isInvalidValid = await totp.verify(invalidToken);
  console.log(`Verification result for invalid token ${invalidToken}:`, isInvalidValid);
}

runTests();

Running the Test

To run this test:

1. Make sure Node.js is installed on your machine.
2. Save the script in the applications base directory.
3. Run the test script with Node.js:

node TOTPTest.js

export class TOTP {
  constructor(secret, digits = 6, step = 30) {
    this.secret = secret;
    this.digits = digits;
    this.step = step;
  }

  async generateTOTP(time) {
    let counter = Math.floor(time / this.step);
    const data = new Uint8Array(8);
    for (let i = 7; i >= 0; i--) {
      data[i] = counter & 0xff;
      counter >>= 8;
    }

    const key = await crypto.subtle.importKey(
      'raw',
      this.base32ToUint8Array(this.secret),
      { name: 'HMAC', hash: 'SHA-1' },
      false,
      ['sign']
    );

    const signature = await crypto.subtle.sign('HMAC', key, data);
    const dataView = new DataView(signature);
    const offset = dataView.getUint8(19) & 0xf;
    const otp = dataView.getUint32(offset) & 0x7fffffff;

    return (otp % Math.pow(10, this.digits)).toString().padStart(this.digits, '0');
  }

  base32ToUint8Array(base32) {
    const alphabet = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ234567';
    let bits = 0;
    let value = 0;
    let index = 0;
    const output = new Uint8Array(Math.ceil(base32.length * 5 / 8));

    for (let i = 0; i < base32.length; i++) {
      value = (value << 5) | alphabet.indexOf(base32[i]);
      bits += 5;
      if (bits >= 8) {
        output[index++] = (value >>> (bits - 8)) & 255;
        bits -= 8;
      }
    }

    return output;
  }

  async verify(token, time = Date.now()) {
    const currentTime = Math.floor(time / 1000);
    for (let i = -1; i <= 1; i++) {
      const currentToken = await this.generateTOTP(currentTime + i * this.step);
      if (token === currentToken) return true;
    }
    return false;
  }
}

Creating the Backup API Endpoint

Now that we have our TOTP authentication in place, let’s create the backbone of our backup system: the API endpoint. We’ll build this in SvelteKit, leveraging its powerful server-side capabilities.

Adding a Variable to Session

To determine whether we’re running in a development environment, we’ll update our src/auth.ts file. This allows us to tailor behavior based on the deployment context.

Here’s the breakdown:

// ... imports ...

export const { handle, signIn, signOut } = SvelteKitAuth(async (event) => {

    // ... existing logic for environment variable retrieval ...

    const authOptions = {
        // ... previous options remain the same ...
        callbacks: {
            async signIn({ user, account, profile }) {
                // ... remains the same ...
            },
            async session({ session, token }) {
                if (token?.sub) {
                    session.user.id = token.sub;
                }

                // Check for Cloudflare Pages environment
                session.dev = event.platform?.env.CF_PAGES;

                return session;
            }
        }
    };

    return authOptions;
});

The new line session.dev = event.platform?.env.CF_PAGES; checks if the event.platform object exists (provided by SvelteKit). If it does, it accesses the environment variable named CF_PAGES. This variable is typically present when deployed on Cloudflare Pages. By setting session.dev to true in such cases, we can distinguish the development environment from others.

Setting Up the API Route

In SvelteKit, API routes are created as server-side endpoints. Let’s create our backup endpoint at src/routes/api/backup/+server.ts:

import { json } from '@sveltejs/kit';
import { TOTP_SECRET } from '$env/static/private';
import { TOTP } from '$lib/TOTP';

export async function POST({ request, platform, locals }) {
    // We'll fill this in step by step
}
async function backupDatabase(env) {
    // Implement
}
async function storeInR2(env, filename, content) {
    // Implement
}
function convertToSQLInsertStatements(tableName, data) {
    // Implement
}

This sets up a POST endpoint at /api/backup. We’re importing necessary modules, including our TOTP class.

Handling POST Requests

Let’s flesh out our POST handler:

export async function POST({ request, platform, locals }) {
  const session = await locals.getSession();
  const isCloudflarePages = session?.dev === 'true';

  // Use the appropriate way to access the secret based on the environment
  const secret = isCloudflarePages ? platform.env.TOTP_SECRET : TOTP_SECRET;

  if (!platform) {
    return json({ error: 'Not running on Cloudflare Pages' }, { status: 400 });
  }

  // We'll add authentication and backup logic here
}

This code:

1. Checks if we’re running on Cloudflare Pages
2. Retrieves the TOTP secret appropriately
3. Ensures we’re running in the correct environment

Implementing TOTP Verification

Now, let’s add our TOTP authentication:

export async function POST({ request, platform, locals }) {
    // previously added section remains the same

    const authHeader = request.headers.get('authorization');
    if (!authHeader) {
        return json({ error: 'Unauthorized' }, { status: 401 });
    }
    const token = authHeader.split(' ')[1];

    const totp = new TOTP(secret);
    const isValid = await totp.verify(token);

    if (!isValid) {
        return json({ error: 'Unauthorized: Invalid token' }, { status: 401 });
    }
}

This code:

1. Extracts the TOTP token from the Authorization header
2. Verifies the token using our TOTP class
3. Returns an error if the token is invalid

Triggering the Backup

If the authentication passes, we can trigger our backup:

export async function POST({ request, platform, locals }) {
    // previously added section remains the same

    try {
        await backupDatabase(platform.env);
        return json({ success: true });
    } catch (error) {
        console.error('Backup failed:', error);
        return json({ error: 'Backup failed' }, { status: 500 });
    }
}

import { json } from '@sveltejs/kit';
import { TOTP_SECRET } from '$env/static/private';
import { TOTP } from '$lib/TOTP';

export async function POST({ request, platform, locals }) {
  const session = await locals.getSession();
  const isCloudflarePages = session?.dev === 'true';
  const secret = isCloudflarePages ? platform.env.TOTP_SECRET : TOTP_SECRET;

  if (!platform) {
    return json({ error: 'Not running on Cloudflare Pages' }, { status: 400 });
  }

  const authHeader = request.headers.get('authorization');
  if (!authHeader) {
    return json({ error: 'Unauthorized' }, { status: 401 });
  }
  const token = authHeader.split(' ')[1];

  const totp = new TOTP(secret);
  const isValid = await totp.verify(token);

  if (!isValid) {
    return json({ error: 'Unauthorized: Invalid token' }, { status: 401 });
  }

  try {
    await backupDatabase(platform.env);
    return json({ success: true });
  } catch (error) {
    console.error('Backup failed:', error);
    return json({ error: 'Backup failed' }, { status: 500 });
  }
}

With this endpoint in place, we’ve created a secure gateway for our backup system. In the next section, we’ll dive into the backupDatabase function to see how we actually create our database backups.

Building the Database Backup Function

Now that we have our secure API endpoint, let’s dive into the heart of our backup system: the backupDatabase function. This function will interact with Cloudflare D1 to extract our data and prepare it for storage in R2.

Connecting to the Cloudflare D1 Database

First, let’s define our backupDatabase function after the POST function in src/routes/api/backup/+server.ts:

async function backupDatabase(env) {
  const tables = [
    'accounts', 'sessions', 'users', 'verification_tokens'
  ];

  let backupContent = '';

  for (const table of tables) {
    try {
      // We'll fill this in step by step
    } catch (error) {
      console.error(`Error backing up table ${table}:`, error);
      throw error;
    }
  }

  // We'll add R2 storage logic here
}

This function takes an env parameter, which gives us access to our Cloudflare environment, including the D1 database.

Retrieving Table Schemas

For each table, we first want to get its schema. This will allow us to recreate the table structure during restoration:

for (const table of tables) {
    try {
        const schemaResult = await env.DB.prepare(`SELECT sql FROM sqlite_master WHERE type='table' AND name=?`).bind(table).all();
        if (schemaResult.results.length > 0) {
            const createTableStatement = schemaResult.results[0].sql;
            backupContent += `DROP TABLE IF EXISTS ${table};\n`;
            backupContent += `${createTableStatement};\n\n`;
        }

        // next we'll fetch table data
    } catch (error) {
        console.error(`Error backing up table ${table}:`, error);
        throw error;
    }
}

This code:

1. Queries the sqlite_master table to get the CREATE TABLE statement
2. Adds a DROP TABLE statement to our backup (useful for clean restores)
3. Adds the CREATE TABLE statement to our backup

Fetching Table Data

Next, let’s fetch the actual data from each table:

for (const table of tables) {
    try {
        // retrieve table schema remains the same

        const data = await env.DB.prepare(`SELECT * FROM ${table}`).all();
        if (data.results.length > 0) {
            backupContent += convertToSQLInsertStatements(table, data.results);
            backupContent += '\n';
        }
    } catch (error) {
        console.error(`Error backing up table ${table}:`, error);
        throw error;
    }
}

Here, we’re selecting all data from the table and converting it to SQL INSERT statements.

Generating SQL Statements for Backup

Let’s implement the convertToSQLInsertStatements function:

function convertToSQLInsertStatements(tableName, data) {
  let sqlStatements = '';
  for (const row of data) {
    const columns = Object.keys(row).join(', ');
    const values = Object.values(row).map(val => 
      val === null ? 'NULL' : `'${String(val).replace(/'/g, "''")}'`
    ).join(', ');
    sqlStatements += `INSERT INTO ${tableName} (${columns}) VALUES (${values});\n`;
  }
  return sqlStatements;
}

This function:

1. Generates an INSERT statement for each row of data
2. Handles NULL values and escapes single quotes in string values

Error Handling and Logging

Notice how we wrap each table backup in a try-catch block:

try {
  // Backup logic here
} catch (error) {
  console.error(`Error backing up table ${table}:`, error);
  throw error;
}

This allows us to log specific table errors while still allowing the overall backup process to fail if any table backup fails.

async function backupDatabase(env) {
  const tables = [
    'accounts', 'sessions', 'users', 'verification_tokens'
  ];

  let backupContent = '';

  for (const table of tables) {
    try {
      const schemaResult = await env.DB.prepare(`SELECT sql FROM sqlite_master WHERE type='table' AND name=?`).bind(table).all();
      if (schemaResult.results.length > 0) {
        const createTableStatement = schemaResult.results[0].sql;
        backupContent += `DROP TABLE IF EXISTS ${table};\n`;
        backupContent += `${createTableStatement};\n\n`;
      }

      const data = await env.DB.prepare(`SELECT * FROM ${table}`).all();
      if (data.results.length > 0) {
        backupContent += convertToSQLInsertStatements(table, data.results);
        backupContent += '\n';
      }
    } catch (error) {
      console.error(`Error backing up table ${table}:`, error);
      throw error;
    }
  }

  // Todo: Store in R2 (we'll implement this in the next section)
}

function convertToSQLInsertStatements(tableName, data) {
  let sqlStatements = '';
  for (const row of data) {
    const columns = Object.keys(row).join(', ');
    const values = Object.values(row).map(val => 
      val === null ? 'NULL' : `'${String(val).replace(/'/g, "''")}'`
    ).join(', ');
    sqlStatements += `INSERT INTO ${tableName} (${columns}) VALUES (${values});\n`;
  }
  return sqlStatements;
}

In the next section, we’ll implement the storeInR2 function to save our backup file securely in Cloudflare R2 storage.

Storing Backups in Cloudflare R2

Now that we’ve generated our backup content, it’s time to securely store it. Cloudflare R2 provides an excellent solution for this, offering object storage that’s both cost-effective and easily integrated with our Cloudflare Pages setup.

Implementing the storeInR2 Function

Let’s implement our storeInR2 function after the backupDatabase function in src/routes/api/backup/+server.ts:

async function storeInR2(env, filename, content) {
  try {
    await env.MY_BUCKET.put(filename, content);
    console.log(`Backup stored successfully: ${filename}`);
  } catch (error) {
    console.error(`Error storing file ${filename} in R2:`, error);
    throw error;
  }
}

This function:

1. Takes the environment, filename, and content as parameters
2. Uses the R2 bucket binding (env.MY_BUCKET) to store the file
3. Logs success or throws an error if the storage fails

Naming Conventions for Backup Files

We’re using a timestamp-based naming convention for our backup files:

const filename = `full_backup_${new Date().toISOString()}.sql`;

This ensures that each backup has a unique name and allows us to easily identify when each backup was created.

Integrating R2 Storage into Our Backup Process

Now, let’s update our backupDatabase function to use R2 storage:

async function backupDatabase(env) {
  const tables = [
    'accounts', 'sessions', 'users', 'verification_tokens'
  ];

  let backupContent = '';

  for (const table of tables) {
    try {
      // ... (previous table backup logic)
    } catch (error) {
      console.error(`Error backing up table ${table}:`, error);
      throw error;
    }
  }

  const filename = `full_backup_${new Date().toISOString()}.sql`;
  await storeInR2(env, filename, backupContent);
}

With these implementations, we’ve now created a robust system for generating database backups and securely storing them in Cloudflare R2. This setup provides us with easily accessible, secure off-site backups of our entire database.

Testing the Backup System

Now that we’ve implemented our backup system, it’s crucial to thoroughly test it to ensure it’s working correctly. We’ll use the existing user data from our authentication system to verify our backup and restore processes.

Setting Up a Test Environment

Assuming you’ve followed along from the previous post, you should already have your SvelteKit app set up with Cloudflare Pages, D1, and authentication. Let’s make sure we have some test data to work with:

1. Register a few test users through your application’s signup process.
2. Log in and out a few times with a user to add to the session table.

Executing a Backup

Let’s simulate a backup scenario! To follow along, open two separate tabs or terminal windows. In the first window, navigate to your project’s root directory and run npm run dev:full to start the development server.

Navigate to your project’s root directory in the second window. For this window we’ll need to grab that TOTP secret we generated earlier.

Remember that TOTP secret?

Copy and paste the following command, replacing the-totp-secret-we-generated-earlier with your actual secret:

export TOTP_SECRET=the-totp-secret-we-generated-earlier

Next, we’ll need a handy script to generate a fresh TOTP token. Here’s the script you can save as genTotp.js:

// genTotp.js
import {TOTP} from './src/lib/TOTP.js';
const secret = process.env.TOTP_SECRET;
const totp = new TOTP(secret);

totp.generateTOTP(Math.floor(Date.now() / 1000)).then(token => {
  console.log(token);
});

Time is of the essence!

Since TOTP tokens expire quickly, it’s recommended to copy and execute all the following commands in rapid succession. This ensures the secret and token remain valid for the entire process.

Ready to trigger a backup? Here’s the magic spell:

export TOKEN=$(node genTotp.js)
curl -X POST https://your-app.pages.dev/api/backup \
     -H "Content-Type: application/json" \
     -H "Authorization: Bearer $TOKEN" \
     -d '{"action": "backup"}'

This command performs three key actions:

1. It generates a fresh TOTP token using genTotp.js.
2. It sends a POST request to the /api/backup endpoint on your deployed application.
3. It includes the generated token in the Authorization header to authenticate the request.

If everything works as planned, you should see a successful response from the server!

Testing Locally?

No worries, we’ve got you covered! Use this command sequence instead:

export TOKEN=$(node genTotp.js)
curl -X POST http://localhost:5173/api/backup \
     -H "Content-Type: application/json" \
     -H "Authorization: Bearer $TOKEN" \
     -d '{"action": "backup"}'

This approach achieves the same functionality, just targeting your local development environment.

Verifying the Backup File in R2

A Quick Visual Check:

For a fast and easy confirmation of a test from cloudflare pages, log into your Cloudflare dashboard and navigate to the Workers section. In the “Storage” area, locate the R2 bucket dedicated to your backups. A quick glance at the bucket’s contents will reveal any newly uploaded backup files, indicating a successful transfer. If you need to download the files for further inspection or verification, you can do so directly from the dashboard.

Local Testing Tip:

If you’re working locally, you can verify backup creation by checking the .wrangler/state/v3/r2/[r2-name-binding]/blobs folder. New backup files will be stored here, providing a convenient way to confirm their existence and inspect their contents.

To programatically ensure our backup was stored correctly in R2, let’s add a simple retrieval function to our API:

// In src/routes/api/backup/+server.ts

export async function GET({ request, platform, locals }) {
  // Add authentication here similar to the POST handler

  const backups = await listBackups(platform.env);
  return json({ backups });
}

async function listBackups(env) {
  const list = await env.MY_BUCKET.list();
  return list.objects.map(obj => obj.key);
}

Now with this command you can list your backups:

export TOKEN=$(node genTotp.js)
curl -X GET https://your-app.pages.dev/api/backup \
     -H "Authorization: Bearer $TOKEN"

Or with this command if you are testing locally:

export TOKEN=$(node genTotp.js)
curl -X GET http://localhost:5173/api/backup \
     -H "Authorization: Bearer $TOKEN"

This should return a list of backup files stored in R2.

Analyzing Backup Contents

To verify the contents of a backup, let’s add another endpoint to retrieve a specific backup file:

// In src/routes/api/backup/[filename]/+server.ts
import { json } from '@sveltejs/kit';

export async function GET({ params, platform, locals }) {
  // Add authentication here

  const { filename } = params;
  const fileContent = await retrieveFromR2(platform.env, filename);

  if (!fileContent) {
    return json({ error: 'Backup file not found' }, { status: 404 });
  }

  return json({ content: fileContent });
}

async function retrieveFromR2(env, filename) {
  const object = await env.MY_BUCKET.get(filename);
  if (object === null) {
    return null;
  }
  return await object.text();
}

Now you can retrieve and inspect a specific backup file:

export TOKEN=$(node genTotp.js)
curl -X GET https://your-app.pages.dev/api/backup/your_backup_filename.sql \
     -H "Authorization: Bearer $TOKEN"

Or with these commands if running locally:

export TOKEN=$(node genTotp.js)
curl -X GET http://localhost:5173/api/backup/your_backup_filename.sql \
     -H "Authorization: Bearer $TOKEN"

Verify that the backup contains:

1. The correct CREATE TABLE statements for your schema
2. INSERT statements for your test users and any other data

Testing Restore Functionality

While we haven’t implemented a restore function in this post, you can manually test the restoration process:

1. Create a new D1 database for testing.
2. Copy the content of your backup file.
3. Execute the SQL statements in your new D1 database.
4. Query the restored data to ensure it matches your original database.

By thoroughly testing your backup system, you can be confident that it’s working correctly and will be there when you need it. Remember to periodically review your backups and test the restore process to ensure your data recovery strategy remains solid.

Automating Backups

While manual backups are useful, automating the process ensures regular, consistent backups without human intervention. We’ll use GitHub Actions to set up a scheduled backup system that triggers our backup API endpoint.

Setting Up GitHub Actions

First, let’s create a GitHub Actions workflow file. In your repository, create a new file at .github/workflows/backup.yml:

name: Monthly Backup

on:
  schedule:
    - cron: '0 0 1 * *'  # Runs at midnight on the 1st of every month
  workflow_dispatch:  # Allows manual triggering

jobs:
  backup:
    runs-on: ubuntu-latest
    steps:
      - name: Checkout repository
        uses: actions/checkout@v3

      - name: Setup Node.js
        uses: actions/setup-node@v3
        with:
          node-version: '20.13.1'

      - name: Generate TOTP and Trigger Backup
        env:
          TOTP_SECRET: ${{ secrets.TOTP_SECRET }}
        run: |
          TOKEN=$(node genTotp.js)
          curl -X POST https://your-app.pages.dev/api/backup \
          -H "Content-Type: application/json" \
          -H "Authorization: Bearer $TOKEN" \
          -d '{"action": "backup"}'

This workflow does the following:

1. Runs monthly at midnight on the 1st of every month.
2. Allows manual triggering via the GitHub Actions UI.
3. Checks out your repository to access the genTotp.js file.
4. Sets up Node.js.
5. Generates a TOTP token using your genTotp.js script.
6. Triggers the backup API endpoint using curl (don’t forget to update the website url).

Securing the TOTP Secret

To keep your TOTP secret secure, we’re using GitHub Secrets. To set this up:

1. Go to your GitHub repository.
2. Navigate to Settings > Secrets and variables > Actions.
3. Click “New repository secret”.
4. Name it TOTP_SECRET and paste your secret value.

Testing the Automated Backup

To test your automated backup:

1. Push these changes to your GitHub repository.
2. Go to the “Actions” tab in your GitHub repository.
3. Select the “Monthly Backup” workflow.
4. Click “Run workflow” and choose the branch to run it on.
5. Monitor the workflow run to ensure it completes successfully.

By implementing this automated backup system, you’ve significantly improved your data protection strategy. Your SvelteKit app on Cloudflare now has regular, automated backups, ensuring you always have recent data to fall back on if needed.

Security Best Practices and Next Steps

When it comes to backups, security is paramount. Your backups contain sensitive data, and protecting them is just as important as protecting your live database. Let’s explore some key security best practices for your backup system.

Protecting the Backup API Endpoint

1. TOTP Authentication: We’re already using Time-Based One-Time Passwords for API authentication. This is an excellent start, as it provides a secure, time-limited access mechanism.

2. HTTPS: Ensure your API endpoint is only accessible via HTTPS. Cloudflare Pages automatically provides HTTPS, so you’re covered here.

3. API Rate Limiting: Implement rate limiting to prevent brute-force attacks. Utilize Cloudflare’s built-in rate limiting rules. By configuring these rules, you can set specific thresholds for API requests to protect your application from excessive traffic and potential abuse.

4. IP Whitelisting: If possible, restrict access to your backup API to known IP addresses. This can be done through Cloudflare’s firewall rules.

5. Double Authentication Dance: Consider adding another layer of authentication, like API keys or additional OAuth scopes. It’s like making someone know both the secret handshake AND the password to get into your treehouse.

Securing TOTP Secrets

1. Environment Variables: Always store your TOTP secret as an environment variable, never in your codebase.

2. Secret Rotation: Regularly rotate your TOTP secret.

3. Secure Storage: When storing the TOTP secret in Cloudflare, use encrypted environment variables.

Access Control

1. Principle of Least Privilege: Ensure that the Cloudflare Worker running your backup system has only the permissions it needs. Use separate R2 buckets for backups and other data if necessary.

2. Regular Audits: Periodically review who has access to your Cloudflare account, GitHub repository, and any other systems involved in your backup process.

Monitoring and Alerting

1. Logging: Implement comprehensive logging for all backup operations. Store these logs securely and separately from your backups.

   
   

   async function logBackupOperation(env, operation, status, details) {
     await env.MY_LOGS_BUCKET.put(`backup_log_${Date.now()}.json`, JSON.stringify({
       operation,
       status,
       details,
       timestamp: new Date().toISOString()
     }));
   }

   
   

2. Real-time Alerts: Set up alerts for any unusual activity, such as multiple failed backup attempts or access from unexpected IP addresses. You can use Cloudflare Workers to send alerts to your team:

   
   

   async function sendAlert(message) {
     await fetch('https://your-alert-endpoint.com', {
       method: 'POST',
       body: JSON.stringify({ message }),
       headers: { 'Content-Type': 'application/json' }
     });
   }

Regular Security Reviews

1. Penetration Testing: Regularly attempt to breach your own backup system (in a controlled, ethical manner) to identify vulnerabilities.

2. Code Reviews: Whenever you make changes to your backup system, have them reviewed by another developer with a focus on security.

3. Dependency Audits: Regularly update and audit your dependencies for known vulnerabilities:

   
   

   npm audit

   By implementing these security best practices, you’re not just creating backups; you’re creating a secure, robust system that protects your data at every step of the process. Remember, security is an ongoing process. Regularly review and update your security measures to stay ahead of potential threats.

Conclusion

Throughout this guide, we’ve embarked on a comprehensive journey to build a robust, secure, and efficient backup system for your SvelteKit application on Cloudflare. We’ve covered a wide range of topics, from the basics of setting up a backup system to advanced techniques for optimization and compliance.

Let’s recap the key points we’ve explored:

1. We implemented a secure backup API using Time-Based One-Time Passwords (TOTP) for authentication.
2. We created a comprehensive backup function that interacts with Cloudflare D1 to extract and format our data.
3. We leveraged Cloudflare R2 for secure and efficient storage of our backups.
4. We automated our backup process using GitHub Actions, ensuring regular and consistent backups.

Building a robust backup system is a critical step in ensuring the reliability and trustworthiness of your SvelteKit application. By implementing the strategies and best practices outlined in this guide, you’re not just protecting your data – you’re safeguarding your users’ trust and your application’s future.

We encourage you to adapt these concepts to your specific needs and to stay curious about emerging best practices in data protection and backup strategies. The world of web development is ever-evolving, and staying informed is key to maintaining a secure and efficient application.

Additional Resources

To further your knowledge and stay up-to-date with the technologies and concepts we’ve discussed, here are some valuable resources:

Cloudflare Documentation

• Cloudflare D1 Documentation: Comprehensive guide to working with D1 databases.
• Cloudflare R2 Documentation: Detailed information on using R2 for object storage.
• Cloudflare Workers Documentation: Learn more about leveraging Workers for serverless computing.

SvelteKit Resources

• SvelteKit Documentation: Official documentation for SvelteKit.
• SvelteKit on Cloudflare: Guide on deploying SvelteKit applications on Cloudflare Pages.

Security and Authentication

• TOTP RFC: The official RFC for Time-Based One-Time Password Algorithm.
• OWASP Cheat Sheet Series: Excellent resource for web application security best practices.

Data Protection and Compliance

• General Data Protection Regulation (GDPR): Official website for GDPR information.

GitHub Actions

• GitHub Actions Documentation: Learn more about automating your workflows with GitHub Actions.

Community and Forums

• Cloudflare Community: Engage with other developers using Cloudflare services.
• Svelte Discord: Join the Svelte community for discussions and support.

Remember, the field of web development and data management is constantly evolving. Stay curious, keep learning, and don’t hesitate to engage with the community. Your questions and experiences can be invaluable to others on similar journeys.

We hope this guide has been helpful in setting up a secure and efficient backup system for your SvelteKit application on Cloudflare. Happy coding, and may your data always be safe and recoverable!