Introducing fsql - an interactive cli for querying Forge SQL databases via web triggers

Forge
,
1

Overview

fsql is a tool designed to feel like a mysql or psql session for your Forge SQL database.

It makes it easy to explore your data, insert test records, work on queries, etc.

Install it into a Forge development deployment by running fsql-setup from the project root. It will setup a webtrigger that fsql will use to run the sql commands.

Get Started
Github repo

Demo

fsql usage demo

Feedback

Create an issue on github (see link above) or post a message here with your thoughts and any issues encountered. Thank you!

12 Likes
2

Hi, Nice tool :+1:

One important thing to be careful about: it should be restricted to the dev environment only, or at least clearly documented as dangerous for production use .

Otherwise, this web trigger becomes a direct ingress point to the database, and since web triggers can be called without authorization, it may expose your Forge SQL instance in production.

My recommendation is to explicitly block execution in production right at the beginning of the web trigger, for example:

import { getAppContext } from "@forge/api";

const environmentType = getAppContext()?.environmentType;
if (environmentType === "PRODUCTION") {
  return {
    statusCode: 500,
    body: "This trigger is disabled in production",
  };
}

Alternatively, adding strong authorization on top of the trigger would also work.

4 Likes
3

Thanks @vzakharchenko. :+1: I will look at adding something for that.

Yes maybe a hard block for production and a clear warning otherwise.

Authorization would be a nice add for a staging or qa environment adding a layer of protection on top the URI whilst still allowing the tool to be used there … :thinking:

2 Likes
4

Released a new version that blocks production access as discussed above:

The upgrade process is now documented in README.md so follow that to pick up the changes:

image
image869Ă—231 14.8 KB

Related: fql-setup works against the default environment ONLY. So a production (or staging) deploy would require manual steps to get going anyway. But with this change if a production deploy was achieved the function is going to block requests anyway.

A staging deployment of fsql could be quite handy for some. So I’ll look at adding the ability to do that to fsql-setup sometime. Maybe a -e <environment> that accepts anything BUT production. Let me know if that’s something anyone is interested in.

4 Likes
5

I just released a new version (1.3.0) that adds a few commands:

image

.indexes shows indexes for all tables
.migrations is a shortcut to select * from __migrations;
.database shows the database name (there is only one for the forge app)

Also improved the error message when the configuration is not found to give a little guidance of how to proceed.

Release notes:

2 Likes
6

Hi @ChrisHatch

You could add a really handy “export schema” feature for early-stage development:

  • .export-table → runs SHOW CREATE TABLE "<table>" and prints the DDL (optionally normalized to CREATE TABLE IF NOT EXISTS ...).
  • .export-schema (or .export-all) → iterates all tables and prints DDL for each (skipping Forge system tables like __migrations), optionally wrapped with:
    SET foreign_key_checks = 0; ... SET foreign_key_checks = 1;

    • This is super useful right before the first production deploy to “snapshot” the current dev schema into proper forge migrations.

    Another nice side effect: it makes it easy to recreate the Forge SQL schema on a local MySQL instance. I use that to generate Drizzle models via introspection from the real schema.

    You can take a look here:

3 Likes
7

sounds great @vzakharchenko :+1:

I will look at adding that soon.

2 Likes
8

Released a new version (1.4.0):

  • new fsql-export command that dumps schema DDL and table data to a file
  • autocomplete table and column names in the fsql console
  • command history in the fsql console

Links:

fsql-export

The script will look for a migrations.ts to extract DDL but it will go to the database if --live-schema is given.

Data is dumped by default so use --schema-only if you don’t want that or have a huge database!

@vzakharchenko I took a slightly different approach to what we discussed above in using a separate command for the export. But I like the idea of commands like .export inside the repl so I’ll look at adding those at some point. :+1:

Security

NOTE: the same security constraints apply to fsql-export that were described for fsql above. Namely it only works in a development environment . Both scripts share the same webtrigger which will reject requests in a production environment.

3 Likes