Replacing S3 File Storage with SQLite Cloud

S3 is the flexible, scalable, and high-performance object storage service offered by AWS - a true staple of cloud computing.

But have you ever wanted to write a simple SQL query against your metadata? Or, maybe you’d like to ditch yet another data storage solution to simplify your stack?

Thanks to the power of SQLite, you can store your files and media assets right next to your transactional data in SQLite Cloud. As an added benefit, you can write SQL against your metadata for advanced searching, filtering, and access patterns.

Why use SQLite Cloud?

SQLite (and by extension, SQLite Cloud) offers low overhead for file I/O, especially compared to other relational databases. SQLite Cloud serves data on the edge, bringing files closer to users for reduced latency. And, with the upcoming launch of local-first, file changes will seamlessly synchronize across devices.

Sample Implementation

In our example TypeScript repo, we’ve built a sample interface you can use over your SQLite Cloud database driver.

We designed our interface to be familiar to users of the v3 S3 SDK, so it should look pretty familiar.

The sample code features an ObjectClient class with an init method and a send method. The init method creates a table to store objects and their metadata, including a bucket attribute for categorization. The send method takes in a command object and calls the command’s “execute” method.

// Client class
class ObjectClient {
  db: Database;

  constructor(connStr: string) {
    this.db = new Database(connStr);
  }

  async init() {
    try {
      const { error, message } = await initializeObjectsTable(this.db);
      if (error) {
        throw new Error(message);
      }
      console.log(message);
    } catch (err) {
      console.error(err);
    }
  }

  async send(command: any) {
    return command.execute(this.db);
  }
}

// Get object command
class GetObjectCommand implements GetObjectRequest, Command {
  bucket: string;
  key: string;

  constructor(params: GetObjectRequest) {
    const validation = fieldCheck(params);
    if (validation.error) {
      throw new Error(validation.message);
    }
    this.bucket = params.bucket;
    this.key = params.key;
  }

async execute(db: Database): Promise<CommandResponse> {
  try {
    const response =
      await db.sql` SELECT * FROM objects WHERE bucket=${this.bucket} AND key=${this.key}`;
    return {
      error: null,
      result: response,
      message: "Successfully retrieved object",
      }
    } catch (error) {
      return { 
        error, 
        result: null, 
        message: "Error getting object" 
      }
    }
  }
}

We also added a ListObjectsByDateCommand class, allowing users to query objects in a bucket by date range. This class accepts either a timestamp or a date in the format YYYY-MM-DD as an argument.

class ListObjectsFromDateCommand implements ListObjectsFromDateRequest, Command {
  bucket: string;
  from: string;
  to?: string;

  constructor(params: ListObjectsFromDateRequest) {
    this.bucket = params.bucket;
    this.from = params.from;
    this.to = params.to;
  }

  async execute(db: Database): Promise<CommandResponse> {
    try {
      if (!this.to) {
        let toDate = new Date(this.from);
        toDate.setUTCHours(0, 0, 0, 0);
        const startOfDayString = toDate.toISOString();
        let fromDate = toDate;
        fromDate.setUTCDate(fromDate.getUTCDate() + 1);
        fromDate.setUTCHours(0, 0, 0, 0);
        const startOfNextDayString = fromDate.toISOString();
        const response =
          await db.sql`SELECT key, created_at, modified_at FROM objects WHERE bucket = ${this.bucket} AND created_at BETWEEN ${startOfDayString} AND ${startOfNextDayString}`;
        return {
          error: null,
          result: response,
          message: "Successfully listed objects by date",
        };
      } else {
        let toDate;
        if (this.to.length === 10) {
          toDate = new Date(this.to);
          toDate.setUTCDate(toDate.getUTCDate() + 1);
          toDate.setUTCHours(0, 0, 0, 0);
          toDate = toDate.toISOString();
        }
        const response =
          await db.sql`SELECT key, created_at, modified_at FROM objects WHERE bucket = ${
            this.bucket
          } AND created_at BETWEEN ${this.from} AND ${
            !toDate ? this.to : toDate
          }`;
        return {
          error: null,
          result: response,
          message: "Successfully listed objects by date",
        };
      }
    } catch (error) {
      return { error, result: null, message: "Error listing objects by date" };
    }
  }
}

Conclusion

We all appreciate S3's consistent performance, availability, and virtually unlimited capacity. However, it's not uncommon to face challenges when performing advanced searches for objects in a bucket.

SQLite Cloud makes a compelling alternative to S3, as it offers high speed read/write over file storage, and comes with all the functionality of SQL out-of-the-box.

To check out the SQLite Cloud beta, sign up for free today.

Until next time!