• https://taylor.town/pg-email-token

• https://taylor.town/pg-task

Here's a condensed version of how I do login/signup:

  app.post("/signup", async c => {
    const body = await c.req.parseBody();
    const usr = {
      email: body.email.toString(),
      password: sql`crypt(${body.password.toString()}, gen_salt('bf', 8))`,
    } as Record<string, unknown>;
    const [{ usr_id, email, token }] = await sql`
      with usr_ as (insert into usr ${sql(usr)} returning *)
      select usr_id, email, email_token(now(), email) as token from usr_
    `;
    await sendVerificationEmail(email, token);
    await setSignedCookie(c, "usr_id", usr_id, cookieSecret);
    return c.redirect("/u");
  });
  
  app.post("/login", async c => {
    const { email, password } = await c.req.parseBody();
    const [usr] = await sql`
      select *, password = crypt(${password.toString()}, password) AS is_password_correct
      from usr where email = ${email.toString()}
    `;
    if (!usr || !usr.is_password_correct) return c.html(<p>Your password was incorrect.</p>);
    if (!usr.email_verified_at) await sendVerificationEmail(usr.email, usr.token);
    await setSignedCookie(c, "usr_id", usr.usr_id, cookieSecret);
    return c.redirect("/u");
  });

This is typescript, which is interesting because we have more confidence the JS "works."

But, it is also embedding SQL directly into your typescript.

Isn't that a contradiction in code? I'm NOT suggesting you refactor to use an ORM or something!

When I was an Android developer, I thought it was really fascinating to see how Room worked. Each SQL statement you created was actually compiled and validated at compile time not runtime, and it meant the SQL was sanity checked and sanitized.

Nothing like that is happening here, right? You cannot validate this SQL at compile/build time? If not, aren't you YOLO riding the motorcycle but telling mom you are wearing your helmet?

Correct! It's a total pain haha.

I loathe SQL, but it's the best tool for most modern web apps.

All of flashcasts.com is 1800 lines. I'd guess 30% is HTML and 30% is SQL.

The SQL does the vast majority of the heavy lifting if you think about moving bits over distances.

So SQL is really important, but why don't I have any safeguards? Well, in my experience, the available safeguards are medicine more painful than the poison.

For example, any of the queries in my app I can copy-and-paste into psql and start an interactive debugging session. Can't do that with ORMs.

In my app, I dynamically aggregate and build all the podcast XML for every feed in a single Postgres query. It's super performant and surprisingly little SQL. Can't imagine doing that through an ORM.

SQL queries will throw an error if they're not well formatted, so you just have to write a test suite that somehow hits every query exactly once. You can do this by separating out your SQL, or integration tests, or a handful of other methods, none of which are pleasant.

Anyway, I'm tired of SQL, sol I'm working on an embedded query language in scrapscript. Stay tuned for details:

[1] https://scrapscript.org

  sql`select ${"hello"}, ${sql`world`}`

  select $1, world

My favorite truth in that site: "Anyway, instead of learning biochem and spanish I went and built this stupid website." I don't know what you are talking about at all.