✒️
Start your developer blog today!
✉️
Questions?
Email me

What Stripe Data Do I Store in My Database?

Published May 18, 2021

I’ve been exploring the intracacies of Stripe over the last few months and learning how to properly integrate it into a SaaS application.

The Stripe API and documentation have been fairly intuitive, so I was able to get it all up and running pretty quickly (handling payments, creating checkout sessions, generating customer portals, adding credit cards, etc.).

The one question I had was: what Stripe data should I store in my database?

Do I store just the customer Stripe ID or the entire, exact JSON response from Stripe?

How do I handle keeping my database up-to-date with Stripe’s database of my customers?

In general, we can store anything that is returned in the Stripe API response. But what should we store?

Customers

The Bare Minimum

All the data we need regarding a specific customer is accessible by their customer.id, or Stripe ID.

If a user, or customer, logs into my application, I can make a request to Stripe’s API for whatever data I need (i.e. charges, subscriptions, billing information, etc.) if I know the Stripe ID.

We have two options here:

  1. We make several requests with Stripe’s API upon login to obtain all relevant customer data, store it in the session state, and use it whenever needed, OR
  2. We can make one-off API requests each time the our application needs some Stripe customer data

Simple for us. The great thing about this is that there’s nothing we, as the developers, need to maintain. All we need to do is store the customer’s Stripe ID when the customer is created. From there, we can pass it in during checkout sessions, and query by this Stripe ID to get all the customer information we need.

And Safe. It’s perfectly safe to store a customer’s Stripe ID. Even if an attacker were to get a hold of our database, they would not be able to do anything with it without our Secret API key (sk_live_xxx). Just be sure we’re not storing any API keys in our database. That then becomes a security risk.

The Issue with the Bare Minimum

The issue with storing just a customer’s Stripe ID is that we are forced to call the Stripe API many times per session.

For instance, to get the name of a product a customer is subscribed to, we would need to make 3 calls.

const { customerId } = await queryMyDatabase(userId); // 1
const customer = await stripe.customers.retrieve(customerId, {
  expand: ["subscriptions"], // 2
});
const productId = customer.subscriptions.data[0].plan.product;
const product = await stripe.products.retrieve(productId); // 3
console.log(product.name);

It’s apparent how this can become tiresome over time, especially when we will realistically need more than just a product name in our application. We’ll likely need price information, invoices, payment intents, and possibly much more data.

The Hybrid Approach

Instead of just storing a customer’s Stripe ID or storing the entire JSON response from Stripe, we can define our own model with information relevant to our application.

The goal is to decrease the number of calls we need to make to an external API.

If I frequently query for product metadata, I could store a productId.

const { productId } = await queryMyDatabase(userId);
const product = await stripe.products.retrieve(productId);
console.log(product.name);

We can take this a step further and store the product metadata we need directly in our database.

const { product } = await queryMyDatabase(userId);
console.log(product.name);

For instance, in my specific use case, I don’t plan on showing card information. I don’t need the brand, exp_month, exp_year, or last4, so I just won’t store it. This may be drastically different for other developers and other use cases.

The Issue with any Database Approach

We know now that a hybrid model provides the benefit of not having to make an excessive number of calls to the Stripe API.

However, with any database approach, we need to be wary of a couple things.

Handling an ever-changing format. The format of Stripe’s response may change in the future and therefore may require migrations of our database models. Be sure to keep track of API releases and the version we’re specifically using.

Updating our database. Our Stripe data will be constantly changing, especially when it comes to customer information. Our database will often become out-of-date (i.e. customer subscribes to a new product), and it’s up to us to keep it consistent with what’s in Stripe’s database. We can use event webhooks to handle this. Conveniently, I wrote an article detailing how to incorporate Stripe CLI with a dockerized Next.js application.

Duplicating data in this way should bring up some concerns for developers, but when we’re dealing with money, we generally want to record more than less. There is inherent risk in relying on a third party to store information related to a customer’s transaction history.

Products and Prices

Now, how should we handle products and prices?

Should we pull this data from Stripe every time a user accesses a Pricing or a Products and Services page on our site? Or should we just have a local copy of this data?

Store local copies of static data. If we’re frequently accessing data that isn’t changing often, then it makes sense to store a local copy of the data and make our queries against our local data.

This is ideal for production environments (generally, the services we offer to our customers won’t change every day).

Now, if the products and prices are changing frequently because we’re developing, then it might make sense to hold off on storing product and price information until we are confident that the metadata is correct.

And, of course, we can always configure webhooks to upsert products and prices into our database based on webhook events (product.created, product.updated, price.deleted, etc.)

Credit Card Information

Just don’t. Similarly to how many have transitioned to OAuth providers to remove the need to store passwords (and for many other reasons), we want to avoid storing card information as much as possible. We want to avoid storing any sensitive information in our database (i.e. passwords, credit card information, secret keys, API keys, etc.). As a result, we should always be working with tokens and ids. As interesting as it is to learn about AES encryption, database permissions, and other security concerns, there are standard security practices in place that Stripe handles for us.

Handling PCI-compliance. The greatest benefit of using a payment processing service like Stripe is that they handle the issue of being PCI compliant. If we start storing customer card information, then our system also needs to be PCI-compliant, which requires being externally audited for compliance with local laws and security practices. If we are not storing it in a PCI-compliant way but still use Stripe’s API to charge the card, then we will be violating Stripe’s TOS and will likely get banned.

Reasses your needs. There are few scenarios in which customer card information needs to be stored. I would encourage anyone who is seriously considering this to reevaluate their goals with this. There is likely a solution available through the Stripe API.

Summary

The idea here is that we want to create a data model that contains only the data we care about (and hopefully that doesn’t include credit card information).

I recommend maintaining a local cache of anything you’ll use frequently. In my application, this schema below is what I started out using to produce a multi-tiered (basic vs. premium), subscription-based (monthly vs. yearly) product.

The majority of the information we store is meant for the frontend (Pricing, Products and Services, Billing, Settings, etc.). Other than that, the application itself only uses the customer’s product tier to determine what services to allow and/or limit.

  • Customer
    • id: customer ID
    • name/email: any human identifier for the customer
    • subscriptionId: references Subscription.id
  • Subscription
    • id: subscription ID
    • current_period_end: timestamp of customer’s next bill
    • cancel_at_period_end: indicates whether customer canceled a subscription (services will be halted at the next billing cycle if true)
    • status: subscription status (i.e. active, canceled, past_due)
    • priceId: references Price.id
  • Price
    • id: price ID
    • recurring.interval: monthly vs yearly billing
    • unit_amount: price of product
    • currency: three-letter ISO currency code
    • productId: references Product.id
  • Product
    • id: product ID
    • name: product name
    • description: product description
    • tier: custom field to differentiate between a basic and premium product

By no means is this comprehensive, but I hope this provides a solid starting point for anyone looking to integrate Stripe into their application.


More SQL Articles