Tag: react

SQL Hammer (everything is a nail)

~ Sean Nolan

Update: SQL Hammer has been updated somewhat extensively since this post was written. It’s still a great introduction and contains good detail, but if you just want to use and understand the latest version of the app, start here: https://github.com/seanno/shutdownhook/tree/main/dss#readme.

Everyone loves to talk about code, but it’s really databases that run the world. And not nerd-chic graph databases, or vector databases, or object stores, or whatever hyped-up new hoohah that Meta created this month. I’m talking about old school, relational, SQL databases. Every (every) (every!) company uses them. And for good reason — tables and columns and relationships do an excellent job of modeling things and processes in the real world.

To be fair, there are solid specialized use cases for the hoohas too. But SQL is and will remain ubiquitous because it does its job. I’m actually of the opinion that everyone should learn SQL, not just because it’s a work superpower (I’m looking at you, Tracy) but because normalization is a useful strategy for thinking about all kinds of problems. Actually, that’s another article I should write — but not right now.

The “Direct SQL” Problem

Today, I want to share a little tool that fills a technology gap I’ve found at every company I’ve ever been at — an access-controlled, auditable way to directly execute ad hoc SQL queries for data analysis, troubleshooting, workflow support and data repair.

See, in the “normal” course of business SQL is an infrastructure component — not something folks interact with directly. Enterprise applications provide higher-level end-user and system interfaces that use SQL “under the covers” as a way to store and model data. These applications add important controls on top of the raw data store: making sure that access restrictions are enforced, applying business rules to keep things consistent, generating logs to support auditing requirements, and so on. All good stuff.

But inevitably, something happens that requires people to venture under those covers. Maybe the application just has a bug. Or more commonly, something needs to happen in the real world that the application wasn’t designed to handle — updating the shipping address for an order after it was placed, reprocessing a job after a network interruption, that kind of thing. Perhaps somebody needs metrics for a presentation and the data warehouse doesn’t have the right data. There’s always something.

Easy enough for somebody who knows SQL. Open up a database client or your SQL-aware IDE and you’re good to go. But there’s a problem! Every one of the guardrails provided by application logic are gone. No access control, no auditing, no double-checking that your query doesn’t (oops) accidentally delete a bunch of records. Not only that, but direct database connections typically require access to a machine running directly in a production network — another opportunity for things to go wrong.

It’s easy to say that this “shouldn’t” happen, but that’s just naïve, wishful thinking. Best to just acknowledge reality and at least put some controls in place that minimize risk and maximize capability. That’s exactly what SQL Hammer does.

Try it live!

Before we install anything, let’s take a look at SQL Hammer running on my trusty server at https://shutdownapps.duckdns.org:7083/. I’m assuming you know the basics of SQL syntax. If not, this app probably ain’t much use to you anyways. Log in with any GitHub account and you’ll see two entries in the “Connection” dropdown: “EV Counts in Washington State” and “Scratch DB for Demos etc.” First choose the EV connection, which contains information about EV sales in Washington State (sourced from data.wa.gov) in a single table “evs.”

Running Queries

Your account is granted read-only access to this connection, so you can’t run or save arbitrary SQL statements — you can only run queries that others have created and marked as “shared.” This feature makes direct SQL data safely available to less technical folks in your organization. Click on “EV counts by model year,” then the Run button. Magic!

The “Open as URL” button creates a link that can be bookmarked or shared to provide direct (but still authenticated) access to this query. “Save as CSV” downloads the data for further processing in a spreadsheet app like Microsoft Excel.

Queries can also be more dynamic, requiring user-provided input at runtime. Select and run “EV counts by make in year;” you’ll see that the target model year is listed as a parameter in the results area and applied to the query results. The default is 2023, but you can change this and “Refresh” to look at another year. Parameters are super-powerful, especially for shared queries.

Creating Queries

Now choose the “Scratch DB” connection from the dropdown at the top. In this account you’ve been granted full SQL access, which is a little scary for me, because TTP is a real phenomenon. Please behave yourself! Also, the database resets itself overnight so don’t expect your changes to stick around. Anyhoo.

First click the “New” button, then enter and run a query like “select * from stuff.” Give the query a name and click “Save” so you can use it again later. The query will only be visible to you unless you check the “Shared” box, in which case it can be run (but not edited) by anyone with access to the connection.

Add a parameter to your query by inserting a ? in the query itself, and providing a name in the parameters box — for example, “select * from stuff where label like ?” and a parameter “search”. Run the query again and you’ll be prompted to enter a search string — try something like “yo%” to see wildcard matching live and in color. Woot!

The parameters list should be comma-separated, and each entry in that list must have a corresponding ? marker in the query itself. SQL Hammer doesn’t do a lot of verification on this, so it’s easy to create syntax errors if you’re not careful. A parameters entry can have a default value by adding a colon to the name followed by the value. For example, try “insert into stuff values (?,?)” with a parameter list like “label:mylabel,num:0”.

More than just SELECT

You may have noticed that the query above was actually an insert statement, not a select. This is fine! Assuming you’re configured for write access to a connection, you’re free to execute DML or even DDL statements. You can share these queries too, which can be a great way to capture data from users when combined with parameter lists.

Auditing

With most direct SQL solutions, figuring out who ran what query, when, with what parameters, can be nearly (or actually) impossible. Not only is this just a practical business problem, in many cases it can be a legal one — especially for folks working in regulated environments like healthcare or banking. SQL Hammer to the rescue! If a connection has its “log_queries” column set to 1, every query statement and parameters is logged to disk, together with the user email that executed it. Preserve this logfile and you’ll have no problem satisfying your auditors.

Of course, these logs may now include highly sensitive information — so you’ll have to put processes in place to protect them from prying eyes or tampering. A pain, but well-worth the benefits.

Installing SQL Hammer

SQL Hammer is 100% open source; the code is on GitHub and building it yourself is pretty straightforward. I’ll talk more about that later, but first let’s walk through a binary installation. I’ll be using Linux, but it’s all pure Java so no reason you can’t use Windows or a Mac if you prefer (do let me know if you run into any problems).

First make sure you’ve got a JRE v11 or above. You can check this by running “java -version” at the command line. Next, download sqlhammer-v1.zip from GitHub and unzip it to a directory on a machine that has network access to the database(s) you care about.

Execute “run.sh” in this directory, and point a browser on the same machine at https://localhost:3001. You’ll have to approve the self-signed certificate, but from there you should be prompted for a GitHub login and be on your way! Logs will be written to nohup.out in the same directory; that’s a good place to look for errors if something doesn’t work.

Very cool — but to run “for real” you’ll want to be sure your configuration is correct and secure, so read on. Don’t skip this!

SSL Certificates

The default config.json uses a self-signed localhost certificate for HTTPS. You’ll need to update the SSLCertificateX values to point at your own certificate and key files, using the same same format as Apache’s SSLCertificateFile and SSLCertificateKeyFile. If you really, really want to run without HTTPS you can just delete these values, but that’s probably a bad idea. Remember to set the “Port” value as desired as well.

OAuth2 Provider

As you’ve seen, the default is set up to authenticate users with GitHub accounts, using a stub OAuth2 application registered to my account (seanno). For obvious reasons, you’ll want to pick your own provider and application! If you want to use a social login like Google, Facebook, Amazon or GitHub you’ll find instructions in my recent blog post.

If you’d rather use your enterprise login (likely), you’ll have to figure out how to configure an OAuth2 / OpenID Connection application. Set the “Provider” in config.json to “other,” then provide values for ClientID, ClientSecret, AuthURL, TokenURL and possibly Scope. Most providers make this pretty easy; e.g., for Azure AD / Entra there are instructions here and a config.json fragment will look something like this:

"OAuth2": {
  "Provider": "other",
  "ClientId": "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx",
  "ClientSecret": "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy",
  "AuthURL": "https://login.microsoftonline.com/zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz/oauth2/v2.0/authorize",
  "TokenURL": "https://login.microsoftonline.com/ zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz /oauth2/v2.0/token"
}

If you have trouble with this, first take a closer look at my Social Login post, and feel free to ping me if it’s still not working — happy to help if I can.

An important note on this: When SQL Hammer starts up the first time, the first person to log in is given full write access to the Metadata store. This will have happened when you first logged with GitHub above. If your chosen provider is associated with the same email address you use on GitHub, all good. If not, you’ll want to start with a fresh metadata store — just delete the dss.sql file (by default in /tmp) after configuring your provider, then log in to recreate these tables.

Cookie Encryption

SQL Hammer uses the config.json values under “CookieEncrypt” to protect it’s login cookie. Since the default value is public on GitHub, anybody with access to your server could theoretically whip up a fake cookie to get access to your stuff. Not good.

In your installation directory, run “java -cp dss-server-1.0-SNAPSHOT.jar com.shutdownhook.toolbox.Encrypt keygen”. This will output a randomly-generated key; put into the “CookieEncrypt.Key” field and you’re good to go.

Metadata Store Location

The default setup stores the DSS database in /tmp; edit the Sql.ConnectionString value if you want it somewhere else.

(Maybe) add JDBC drivers

By default, SQL Hammer includes JDBC drivers for SQLite, mySQL and PostgreSQL databases. If you use a database like Microsoft SQL Server, Azure SQL or Oracle, you’ll need to download those driver(s) to the same directory where you installed SQL Hammer. Then load the JAR(s) by adding them to the “cp” argument in run.sh, separated with colons. Here’s an example doing this for Azure SQL (mssql-jdbc-12.4.2.jre11.jar):

nohup java \
  -cp dss-server-1.0-SNAPSHOT.jar:mssql-jdbc-12.4.2.jre11.jar \
  com.shutdownhook.dss.server.App \
  config.json &

Finding the right JDBC driver is pretty easy; just search “XYZ jdbc driver download” on Google and you’ll get there in no time.

99% of JDBC drivers now self-register by including a META-INF/services/java.sql.Driver file in their JAR. If for some crazy reason yours does not, you’ll need to add its fully-qualified class name to config.json under Sql.PreloadDrivers, which should be an array of string values.

Managing Connections and Access

I’m particularly fond of this — admin tasks are performed not through a pre-built UX, but by using SQL itself. The first user to log into an installation is set up as the administrator, with access to the “DSS Metadata Store” connection. A few pre-built queries are added as well, but honestly the SQL is pretty simple; three tables drive it all. Just click the “Schema” button to see how they’re put together.

connections contains one row for each configured database connection. “name” is the primary key, just a short unique label. “description” is what shows up in the dropdown, and setting “log_queries” to 1 will cause query text and parameters to be logged for audit purposes. The star is “connection_string,” which contains everything necessary to connect to and authorize a specific database connection. SQL Hammer connection strings typically contain login information and are very sensitive — be careful to restrict access to the metadata store!

You can add connections using the “connections: add new” query or just with a simple insert statement. The harder part is figuring out a connection string that works; a great place to start is this article at Baeldung. A few examples can also go a long way; so here you go:

  • SQLite: jdbc:sqlite:/PATH/TO/FILE
  • mySQL: jdbc:mysql://SERVER:PORT/DATABASE?user=USER&password=PASSWORD (port is usually 3306)
  • PostgreSQL: jdbc:postgresql://SERVER:PORT/DATABASE?user=USER&password=PASSWORD (port is usually 5432)

access holds (not surprisingly) rules about who can access which connections. The “user” field can either be a specific user (i.e., logged-in email address) or a wildcard pattern matched with the like operator. Some useful practical applications of this are “%” for matching any user, and “%@xyz.com” to match all users with email addresses at xyz.com.

In order to execute or save arbitrary queries, a user must match an access row for the relevant connection in which the “can_create” column has the value 1. Without this flag, users can only run existing queries associated with the connection that are marked “shared” (and can’t see the underlying SQL statements).

The built-in queries “connections: grant access” and “connections: remove access” can be used to manipulate the table; they’re just parameterized versions of insert and delete.

You may find it useful to create multiple “connection” rows that reference the same database, associating different queries with each and granting access to different users. For example, you may have some users that are interested in queries related to financial data, while others are focused on user behavior. By segmenting these query sets by connection, you can reduce confusion and better conform to the principle of minimum access. Since folks only see the connections they have access to, navigation is no problem.

queries holds the actual statements for saved queries, including the “is_shared” flag that marks whether users other than the creator can execute them. Mostly this table is managed through the user interface, but there’s no reason you can’t manipulate it directly as well.

Building from source

The SQL Hammer frontend is an SPA built with React; the backend is a set of Java handlers that run in the context of my custom classes on top of the built-in HttpServer. All of this is packaged into a single uber-JAR that runs as a standalone process starting with com.shutdownhook.dss.server.App.

Building the package requires a git client, Java JDK v11+, maven, node and npm, most of which you probably having hanging around anyways:

git clone https://github.com/seanno/shutdownhook.git
cd shutdownhook/dss
./fullbuild.sh

The fullbuild.sh script first builds the React bundle in the client directory (I had to increase node memory with set NODE_OPTIONS=--max-old-space-size=4096) and copies the resulting files into a zip resource under server/src/main/resources. It then builds and locally installs the utility and server classes under ../toolbox, then the server components, and then finally assembles the uber-JAR which lands in server/target/dss-server-1.0-SNAPSHOT.jar. Use this JAR in place of the one in the release directory to run your own build.

If you want to actively play with the user interface, it’s possible to run the React SPA separately from the server. This is particularly nice because it gives you hot-reload. The details are a bit grotty to include here; drop me a note and I’ll get you started. If there’s any kind of broad interest I’ll write it up; just too lazy to do it now!

Quirks and Futures

This is most definitely a “v1” release. It works and works pretty well, at least for my purposes. But the UX is definitely awkward in places — the interplay between “Run” in the editing pane and “Refresh” in the run pane trips me up regularly. I’d like to have more ready-access to the schema information while running queries. The log file isn’t as configurable as it ought to be. “Setup” is a joke. And on and on.

But you have to start somewhere — and I’m hoping that the app will find its way to a few like-minded folks that have shared my pain and acknowledge the need for something like SQL Hammer. Direct SQL is not just inevitable, it’s super-powerful. It can even be an asset to agility and compliance when managed responsibly. I’d love to hear what you think!

Diceball, a Glowforge++ Project

~ Sean Nolan

Baseball, specifically Red Sox Baseball, was a Big Deal for kids growing up in the 1970s in suburban Boston. We all collected and traded cards, prayed to be put on the Red Sox for little league, kept score watching on channel 38, played home run derby on the neighborhood tennis court, and established infinitely complex rules to mange “ghost runners” for wiffleball games with only four players. I can neither confirm nor deny reports of ritual burnings of Yankee cards.

Anyways, there was a dice-based baseball game we used to play when we couldn’t be outside. Not the exquisitely-complicated version created by some kid in Quebec in 1979 (I was 10 and that one was a little mind-numbing), but a really simple version that just associates rolls with outcomes. I’d totally forgotten about this until it popped up on my Pinterest feed a couple of months ago. Seemed like a fun and nostalgic Glowforge project, so I started playing with designs in Inkscape. It took awhile, but I’m quite pleased with the end result, which included not just standard cutting and etching but some cool magnets and an online component as well.

Note: You may notice that the engravings are all Mariners images, not the Red Sox. My kids grew up Mariners fans, and over the last 30 years that’s made me one too. Julio!!!

The game consists of a playing field inside a finger-joint box, magnetic bases and tokens, dice (purchased!) and a mobile scoreboard app. Most of the pieces are quarter-inch two-sided white oak veneer MDF from Craft Closet (a great source BTW, they even recommend GF settings for their materials). A QR code on the pitchers mound opens the scoreboard app on a phone, which sits in landscape mode in center field. Each roll of the dice corresponds to one at-bat, according to rules etched into the bottom of the lid.

Tokens are used to represent the batter and baserunners. Outs and runs are recorded via touches on the mobile app, and honestly that’s about it. The game is simple, 100% luck-based, yet kind of entertaining. And a ton of fun to put together; I love projects that combine multiple techniques.

The Field & Tokens

The field is 10×10 inches and comprised of a few different insets — cut separately but all together in this SVG file. Creating the infield shape took forever, intersecting and unioning arcs and lines and circles in Inkscape. I am (at best) an Inkscape hack, but am continually amazed at what a stellar job it does with really complex (for me) stuff. Of course each inset needed to deal with kerf width, which I described here and won’t belabor again.

I pre-stained a piece for the outfield grass using “Dragon’s Belly Green” Unicorn Spit, and used the natural white oak for the infield. The bases and pitchers mound are 1/8” Glowforge Draftboard, which took white acrylic paint well, and since it was half the depth of the oak, left a hidden space underneath for the magnets. For those I used some amazingly strong small rare earth magnets I found on Amazon — the same ones I used for the lid of the chess board I made last year.

Unfortunately the magnets didn’t quite fit into the holes underneath first, second and third bases. I was able to snip off enough using a wire cutter, but the material is really brittle and I wrecked quite a few before I was finished. Luckily they’re cheap and, since they were hidden under the board, looks didn’t matter. A few dabs of two-part epoxy held them in place great. The pitchers mound didn’t have magnets underneath, so I padded the extra space with a bit of cork sheet I had lying around.

The tokens are simple circles cut from more white oak for the home team (34, 24, 11 and 51) and mahogany for the visitors (33, 15, 10 and 20). I’ll leave it as an exercise for you to figure out which numbers correspond to which of our favorite Mariners and Red Sox players. 😉 I used the drill press to very very carefully create a recess for magnets in the bottom of each one — careful to get the polarity right so that the tokens stick to the bases rather than jumping away from them!

The Box

The field is glued into a lidded box, which is handy for storage and keeps the dice from bouncing off the table during play. Having never built a laser-cut box before, I tried the “boxes.py” SVG generator and really can’t say enough good things about it. Choose your style, set your measurements and you’re ready to go. And because its output is a clean SVG file, it was super-easy to add engravings for the top and the dice combos.

Once again our old friend kerf is super-important to ensure a good fit, and it was a little tough to get right with thicker pieces. But my second try was a success and didn’t require a lick of glue to stay solid (the first attempt is now holding my supply of Unicorn Spit). The inset lid even snugs perfectly into place. My box was pretty simple, but there are tons of options to choose from. What a stellar resource.

Three coats of a clear satin spray polyurethane to protect the surfaces and the physical game was good go to. Now, on to the virtual!

The App

Some versions of the game use a cribbage-like setup with pegs to keep track of runs and outs. I played with that, and with manually-operated counter wheels, but really didn’t like either one. Instead I decided to build a mobile website, optimized for a landscape-mode phone, that could sit right in the box in center field. I added a little brace behind second base that should fit pretty much any phone. The pitchers mound has a QR code (I have QR Codes on the brain these days) that opens up the scoreboard app, so there’s no stupid URL to remember or lose. Just scan the code and place the phone into its nook. Works great!

The code for the app is up on github; if you have any cause to use it please feel free! It’s a ReactJS site, really nice for simple little apps like this that can be all client-side. I set things up using create-react-app, and don’t get me started on that. I literally just started with React three months ago, and CRA was (and basically still is) the default in every single “getting started” tutorial out there. But Javascript tools have the lifespan of a mayfly, and suddenly it’s deprecated. Something has got to give so that we can get developers off this new-tool treadmill, it’s just inane. Anyways.

There’s not too much to say about the app itself. Diceball.js holds the game state and logic, which is passed down to three controls: Scoreboard.js drives the aggregate and per-inning run display (tracking extra innings if necessary), OutsDisplay.js shows current outs in the inning, and ButtonBar.js handles game updates. Game state is persisted into local storage so you can resume games in progress, and a full undo chain lets you fix touch errors like double-tapping the “out” button by mistake. Because there’s no server-side processing, I was able to host it in my family Azure account simply by copying the files up to a storage account with web access enabled. Nice.

And that’s it! A lot of fun to make and to share. Until next time!