A web SQL interface to your Stripe account using Datasette.

Overview

Datasette, Stripe and tdog

Or: Stripe Sigma Alternative

  • Datasette is a web GUI for exploring SQLite datasets.
  • Stripe handles online payments. Sigma is their SQL analytics web GUI.
  • Table Dog is a CLI that converts your Stripe account to a SQLite database file.

This repo is a web app that runs Datasette and the tdog CLI in a single container to give you a fast real-time SQL interface to your Stripe account.

Demo: https://datasette-stripe.fly.dev/stripe.

Notable features

  • Full text search across all of your Stripe data.
    • Hold s when clicking ID's to open in the Stripe web UI.
  • Real time.
    • Polls /events, applies changes to your SQLite database.
  • Sharable URL's.
    • Share queries as URLs with your team.
    • Export tables to Excel.
  • Snapshot your SQLite database.
    • Download with curl and HTTP basic auth.
    • Save to file based storage (Dropbox etc) so that you can reproduce query results.
    • Use a local DB GUI.
    • Query using Python or another language.
  • Health checks.
  • GraphQL and JSON API's included.
    • Datasette allows you to use curl to get any SQL or GraphQL result set as JSON.
    • This allows you to do complex joins very efficiently as they are powered by SQLite underneath.
    • These APIs can be used from a backend server to query Stripe state without having to handle webhooks or SQL servers and schemas.
  • Mirrors your Stripe API key.
    • Sign in to the Datasette instance with HTTP basic auth username={STRIPE_SECRET_KEY}, password=``.
    • Revoke your key to block all HTTP access to the instance.
  • Very responsive.
    • The combination of SQLite and a Fly.io instance at the network "edge" minimize any network hops.
  • Single Docker container.
    • Can be run on your local laptop or other Docker environment.

Security

HTTPS/TLS and HTTP basic auth protects the Datasette instance (and the Stripe/SQLite data loaded into it). See the proxy readme.md for more details.

Docker Run

Run a local copy with these commands:

  • git clone https://github.com/tabledog/datasette-stripe && cd datasette-stripe
  • docker build . -t datasette-stripe
  • docker run -it -e NODE_ENV='development' -e STRIPE_SECRET_KEY='rk_test...' -v /host/data:/data -p 3000:3000 datasette-stripe
  • You will now have:
    • A Datasette instance running at http://127.0.0.1:3000 (use HTTPS in production).
    • A SQLite database containing your Stripe account located at /host/data/stripe.sqlite.
  • Note: The env var TDOG_LICENSE should be set to your tdog license for production Stripe accounts.

Run this web app for free with Fly.io

Fly.io is like Heroku for Docker containers. They deploy your web app to the closest region to you, manage HTTPS/TLS certificates and OS updates. You give them a single Docker container (this repo), and they handle the rest.

1. Create a Fly.io account

2. Create Fly app.

  • Your app name will become your URL name: https://{your_app_name}.fly.dev/
  • git clone this repo, cd to it in your terminal.
  • Change app name in fly.toml to {your_app_name} (must be globally unique).
  • fly apps create
    • View current app with fly status.
    • The current app will be the one named in fly.toml. Commands operate on this app.

3. Create volume.

  • Run fly volumes create volume_tdog --region lhr --size 1;
  • This is where the stripe.sqlite database and logs are stored. These persist between VM reboots.

4. Set secrets.

  • Create a new restricted key with read-only for everything https://dashboard.stripe.com/apikeys.
  • fly secrets set STRIPE_SECRET_KEY=rk_test_...
  • fly secrets set TDOG_LICENSE=... tdog license.
  • API keys are never hard coded into Docker images or logs.
  • Remember to clear your shell history after setting this.

5. Deploy instance.

  • fly deploy
  • fly open
    • Opens URL to app in your browser.
    • The HTTP basic auth username is the same as the STRIPE_SECRET_KEY, leave the password blank. This is identical to Stripes API authentication.
    • The tdog CLI will do a full download on first boot which could take a while, view the progress via the logs (link in the top right burger menu of Datasette).
  • fly logs
    • Fly.io VM provisioning logs - any issues will be shown here.
  • fly ssh console
    • htop to see processes.

Deleting.

  • fly apps destroy {your_app_name}
    • This will destroy the volume and secrets that belong to this app.

Related

  • Fly billing page - Keep an eye on usage here. A single VM can be left on for free under the "free tier".
  • If you like the demo but are non-technical, I can set up a Datasette instance for your Stripe account.
You might also like...

Next-generation ORM for Node.js & TypeScript | PostgreSQL, MySQL, MariaDB, SQL Server & SQLite

Next-generation ORM for Node.js & TypeScript | PostgreSQL, MySQL, MariaDB, SQL Server & SQLite

Prisma Quickstart • Website • Docs • Examples • Blog • Slack • Twitter • Prisma 1 What is Prisma? Prisma is a next-generation ORM that consists of the

Jan 2, 2023

An adapter-based ORM for Node.js with support for mysql, mongo, postgres, mssql (SQL Server), and more

An adapter-based ORM for Node.js  with support for mysql, mongo, postgres, mssql (SQL Server), and more

Waterline is a next-generation storage and retrieval engine, and the default ORM used in the Sails framework. It provides a uniform API for accessing

Jan 4, 2023

Microsoft SQL Server client for Node.js

node-mssql Microsoft SQL Server client for Node.js Supported TDS drivers: Tedious (pure JavaScript - Windows/macOS/Linux, default) Microsoft / Contrib

Jan 4, 2023

TypeScript clients for databases that prevent SQL Injection

Safe From HTML Injection Using tagged template literals for queries, e.g. db.query(sql`SELECT * FROM users WHERE id=${userID}`); makes it virtually im

Dec 21, 2022

Conjure SQL from GraphQL queries 🧙🔮✨

Conjure SQL from GraphQL queries 🧙🔮✨

Sqlmancer Conjure SQL from your GraphQL queries 🧙 🔮 ✨ ⚠️ This project is currently on hiatus. I am hoping to resume working on Sqlmancer once I have

Oct 30, 2022

Postgres SQL RCE.js

Postgres-SQLi-RCE.js PostgreSQL Large Objects and UDF (User Defined Functions) RCE exploit re-written in javascript, for easy chaining with XSS Methdo

Jan 20, 2022

Free Introduction to SQL eBook

Free Introduction to SQL eBook

This is an open-source introduction to SQL guide that will help you to learn the basics of SQL and start using relational databases for your SysOps, DevOps, and Dev projects. No matter if you are a DevOps/SysOps engineer, developer, or just a Linux enthusiast, you will most likely have to use SQL at some point in your career.

Jan 2, 2023

Run SPARQL/SQL queries directly on Virtuoso database with connection pool support.

🔗 virtuoso-connector Package that allows you to create a direct connection to the Virtuoso database and run queries on it. Connection can be used to

Nov 15, 2022

Database driven code generation for ts-sql-query

ts-sql-codegen This utility generates table mapper classes for ts-sql-query by inspecting a database through tbls. While it is possible to use ts-sql-

Dec 12, 2022
Comments
  • Suggestion for

    Suggestion for "Docker Run" section of README

    This is an awesome project, thanks for creating it!

    Summary

    I ran into some small confusion when trying to run it locally and wanted to share my solutions and maybe add a few sentences to the documentation.

    • First, when I ran the the command in the Docker Run section it threw an error.
    • Secondly, I totally missed the "Sign in to the Datasette instance..." part under the "Features" section, so when I got the image up and running I was confused about what to enter in the login.
    • Third, once the container was up & running, I noticed that the datasette instance was basically empty and I initially didn't understand whether the tdog process had started automatically or if I needed to start it somehow.

    Below I'll discuss the details of the docker command issue. I'll also push a PR that makes these little changes to the README. If you're not taking contributions right now I totally understand, just figured I'd throw it out there in case it's useful. Again, this project is awesome!

    Details of the docker run cmd issue

    Here's the message I got when entering that docker cmd:

    datasette-stripe on  master [!?]
    ❯ docker run -it -e NODE_ENV='development' -e STRIPE_SECRET_KEY='rk_test...' -v /host/data:/data -p 3000:3000 datasette-stripe
    docker: Error response from daemon: Mounts denied:
    The path /host/data is not shared from the host and is not known to Docker.
    You can configure shared paths from Docker -> Preferences... -> Resources -> File Sharing.
    See https://docs.docker.com/desktop/mac for more info.
    

    I tried adding the host & host/data folders in File Sharing, as suggested by the error message, but that didn't resolve. Eventually I figured out that passing an absolute path to the -v flag resolved the issue. So the new version of the command that I propose is: docker run -it -e NODE_ENV='development' -e STRIPE_SECRET_KEY='rk_test...' -v `pwd`/host/data:/data -p 3000:3000 datasette-stripe. This expands pwd and results in an absolute path.

    opened by qbatten 1
Owner
Table Dog
Download your Stripe account to a SQL database.
Table Dog
Connect to private Google Cloud SQL instance through Cloud SQL Auth Proxy running in Kubernetes.

⛅ google-cloud-sql A CLI app which establishes a connection to a private Google Cloud SQL instance and port-forwards it to a local machine. Connection

Dinko Osrecki 10 Oct 16, 2022
Lovefield is a relational database for web apps. Written in JavaScript, works cross-browser. Provides SQL-like APIs that are fast, safe, and easy to use.

Lovefield Lovefield is a relational database written in pure JavaScript. It provides SQL-like syntax and works cross-browser (currently supporting Chr

Google 6.8k Jan 3, 2023
Database manager for MySQL, PostgreSQL, SQL Server, MongoDB, SQLite and others. Runs under Windows, Linux, Mac or as web application

Database manager for MySQL, PostgreSQL, SQL Server, MongoDB, SQLite and others. Runs under Windows, Linux, Mac or as web application

DbGate 2k Dec 30, 2022
A simple url shorter API built with nodejs running on Kubernetes in Google Cloud, using PostgreSQL for storage and cloud sql proxy.

Simple URL Shorter - Google Cloud - Kubernetes A simple url shorter API built with nodejs running on Kubernetes in Google Cloud, using PostgreSQL for

null 3 Nov 25, 2021
A Gmail Clone which built with ReactJS and Redux. You can sign in with your Google Account, compose a new e-mail and send realtime emails to the project.

Gmail Clone with ReactJS A Gmail Clone that you can sign in with your Google Account, compose a new e-mail and send realtime emails to the project. Cl

Özge Coşkun Gürsucu 49 Nov 14, 2022
An easy-to-use multi SQL dialect ORM tool for Node.js

Sequelize Sequelize is a promise-based Node.js ORM tool for Postgres, MySQL, MariaDB, SQLite and Microsoft SQL Server. It features solid transaction s

Sequelize 27.3k Jan 4, 2023
ORM for TypeScript and JavaScript (ES7, ES6, ES5). Supports MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server, Oracle, SAP Hana, WebSQL databases. Works in NodeJS, Browser, Ionic, Cordova and Electron platforms.

TypeORM is an ORM that can run in NodeJS, Browser, Cordova, PhoneGap, Ionic, React Native, NativeScript, Expo, and Electron platforms and can be used

null 30.1k Jan 3, 2023
An SQL-friendly ORM for Node.js

Objection.js Objection.js is an ORM for Node.js that aims to stay out of your way and make it as easy as possible to use the full power of SQL and the

Vincit 6.9k Jan 5, 2023
AlaSQL.js - JavaScript SQL database for browser and Node.js. Handles both traditional relational tables and nested JSON data (NoSQL). Export, store, and import data from localStorage, IndexedDB, or Excel.

Please use version 1.x as prior versions has a security flaw if you use user generated data to concat your SQL strings instead of providing them as a

Andrey Gershun 6.1k Jan 9, 2023