DEV Community: sharefun2023

PostgreSQL vs MySQL: 7 syntax differences that break migrations

sharefun2023 — Tue, 29 Sep 2026 07:49:53 +0000

Porting a working MySQL schema to PostgreSQL is rarely a find-and-replace. The SQL standard leaves enough room that the two engines disagree on quoting, types, upserts, and pagination — and most of the disagreements fail at runtime, not at parse time.

Here are the seven that cost me the most time.

1. Identifier quoting is not the same character

MySQL uses backticks, PostgreSQL uses double quotes (which is the ANSI standard):

SELECT `order` FROM orders;        -- MySQL
SELECT "order" FROM orders;        -- PostgreSQL

Two consequences people miss. First, backticks in PostgreSQL are a syntax error, so any generated query with backtick quoting must be rewritten. Second, double quotes in MySQL are legal but dangerous: with the default ANSI_QUOTES off, MySQL treats "order" as a string literal, not an identifier. So a query that reads fine in PostgreSQL can silently evaluate to a constant in MySQL.

2. String concatenation

SELECT first_name + ' ' + last_name FROM users;   -- SQL Server style, breaks in both
SELECT CONCAT(first_name, ' ', last_name) FROM users; -- works in both

PostgreSQL also supports the standard || operator. MySQL has || too, but by default it means logical OR — so this query is not an error in MySQL, it is a silent logic change:

SELECT first_name || ' ' || last_name FROM users;  -- MySQL: 0 or 1, not a string

CONCAT() is the portable choice. One more difference: PostgreSQL's || propagates NULL (the whole expression becomes NULL), while MySQL's CONCAT() also returns NULL if any argument is NULL — but CONCAT_WS() skips NULL arguments instead, which is usually what you want for address lines.

3. Auto-increment

-- MySQL
id INT AUTO_INCREMENT PRIMARY KEY

-- PostgreSQL, modern
id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- PostgreSQL, legacy but still everywhere
id SERIAL PRIMARY KEY

SERIAL is not a real type — it is shorthand that PostgreSQL expands into an integer column plus a sequence and a default. The practical trap: SERIAL creates an implicit sequence with its own name, and INSERT ... ON CONFLICT plus manual ID inserts can leave that sequence behind the actual max ID. Then the next insert fails with a duplicate key. GENERATED ... AS IDENTITY ties the sequence to the column properly and is what you should write in new schemas.

4. Upsert syntax is completely different

-- MySQL
INSERT INTO counters (id, n) VALUES (1, 1)
ON DUPLICATE KEY UPDATE n = n + 1;

-- PostgreSQL
INSERT INTO counters (id, n) VALUES (1, 1)
ON CONFLICT (id) DO UPDATE SET n = counters.n + 1;

Three things to watch. ON CONFLICT requires you to name the conflict target — without (id), PostgreSQL cannot tell which unique constraint you mean. The existing row is referenced through the table name, not VALUES(). And MySQL's INSERT IGNORE has no direct PostgreSQL equivalent; it silently becomes ON CONFLICT DO NOTHING, which is not the same statement — INSERT IGNORE also swallows type conversion and truncation problems, which DO NOTHING does not.

5. Pagination

SELECT * FROM orders ORDER BY id LIMIT 10 OFFSET 20;  -- both engines
SELECT * FROM orders ORDER BY id LIMIT 20, 10;        -- MySQL only

The comma form is a MySQL extension. In PostgreSQL LIMIT 20, 10 is a syntax error. PostgreSQL also accepts the standard OFFSET 20 ROWS FETCH FIRST 10 ROWS ONLY, which MySQL does not.

The deeper point applies to both engines: large OFFSET values force the database to generate and discard every skipped row. Past a few thousand, switch to keyset pagination — WHERE id > :last_seen_id ORDER BY id LIMIT 10 — which stays fast regardless of depth.

6. Booleans are not a type in MySQL

-- PostgreSQL
active boolean DEFAULT true

-- MySQL: BOOLEAN is an alias for TINYINT(1)
active BOOLEAN DEFAULT TRUE

MySQL stores it as a number, which is why WHERE active = 1 works there and WHERE active = true in PostgreSQL is correct while = 1 fails with an operator does not exist error. Porting conditionals between the two means touching every boolean comparison, not just the schema.

7. Case sensitivity of identifiers

PostgreSQL folds unquoted identifiers to lowercase, so SELECT * FROM Orders and FROM orders are the same table. MySQL's behaviour depends on the filesystem and lower_case_table_names: case-insensitive on Windows and macOS by default, case-sensitive on Linux. A migration where both engines run on Linux is fine, and then a teammate runs the MySQL copy on a Mac and half the queries break.

Practical rule: pick lowercase snake_case for every identifier and never rely on case-insensitive matching.

A note on string comparison

PostgreSQL's LIKE is case-sensitive; MySQL's LIKE follows the column collation, which is case-insensitive by default for utf8mb4_0900_ai_ci. The same filter returns different row counts. PostgreSQL's ILIKE gives you the case-insensitive behaviour explicitly.

Checklist before you migrate

Replace backticks with double quotes (and audit every existing double-quoted string in MySQL).
Replace || and + with CONCAT().
Rewrite AUTO_INCREMENT to GENERATED AS IDENTITY, then resync sequences on any table that received explicit IDs.
Convert ON DUPLICATE KEY UPDATE to ON CONFLICT (target) DO UPDATE and re-check anything using INSERT IGNORE.
Fix every LIMIT offset, count.
Type-check every boolean column and every boolean predicate.
Normalise identifier case before touching anything else.

If you want to see the two flavours side by side while rewriting, the SQL dialect converter shows the differences for a pasted query, and it runs entirely in the browser. The full comparison table, including date functions and interval arithmetic, is in PostgreSQL vs MySQL syntax differences.

The NOT IN trap: why your SQL query returns zero rows

sharefun2023 — Tue, 29 Sep 2026 07:49:26 +0000

A query that returns an empty result is usually a wrong WHERE clause. But there is one class of empty result that confuses even experienced people, because every row it filters on looks correct: NOT IN with a NULL in the subquery.

The failure

Two tables. Customers, and the orders they placed:

CREATE TABLE customers (id int, name text);
CREATE TABLE orders (customer_id int);

INSERT INTO customers VALUES (1,'Ada'), (2,'Grace'), (3,'Linus');
INSERT INTO orders VALUES (1), (NULL);

Customer 3 has never ordered. So this should return Linus:

SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

It returns nothing. Not Linus, not anybody. Zero rows.

Why

NOT IN is shorthand for a chain of AND-ed comparisons:

id <> 1 AND id <> NULL      -- for Linus: 3 <> 1 AND 3 <> NULL

And 3 <> NULL does not evaluate to true. It evaluates to UNKNOWN, because NULL means "unknown value" — and you cannot assert that 3 is different from a value you do not know.

SQL uses three-valued logic: every comparison is TRUE, FALSE, or UNKNOWN.

Expression	Result
`3 = 1`	`FALSE`
`3 <> 1`	`TRUE`
`3 = NULL`	`UNKNOWN`
`3 <> NULL`	`UNKNOWN`
`NULL = NULL`	`UNKNOWN`

WHERE keeps a row only when the condition is TRUE. TRUE AND UNKNOWN is UNKNOWN. So the single NULL in the orders table poisons the comparison for every customer, and the whole result collapses to empty.

Note that NULL = NULL is UNKNOWN too. NULL is not equal to itself — it is not a value, it is the absence of one.

Three ways to fix it

Fix 1 — filter the NULLs out of the subquery. Cheapest, and usually the right answer when the NULL is meaningless data:

SELECT name
FROM customers
WHERE id NOT IN (
  SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
);

Fix 2 — use NOT EXISTS. This is the version I reach for by default, because it is null-safe by construction: EXISTS returns a plain boolean about whether rows matched, and the comparison inside is evaluated per row rather than collapsed into a set:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Fix 3 — LEFT JOIN with an IS NULL check. The classic anti-join:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;

All three return Linus. Pick by readability and by what your optimizer likes — modern planners usually turn all three into the same anti-join.

The same trap in plain comparisons

Once you internalise that NULL is UNKNOWN, other surprises stop being surprising:

SELECT * FROM orders WHERE customer_id <> 1;   -- rows with NULL are excluded
SELECT * FROM orders WHERE customer_id = NULL; -- always empty
SELECT * FROM orders WHERE customer_id IS NULL; -- this is the one that works

COUNT(*) counts rows; COUNT(column) counts non-NULL values. For the orders above, COUNT(*) is 2 and COUNT(customer_id) is 1. That difference has broken more dashboards than any other SQL subtlety — an average computed as SUM(x)/COUNT(*) silently treats missing values as zeros.

What to take away

NOT IN against a subquery that can produce NULL returns zero rows. Always.
IS NULL / IS NOT NULL are the only operators that test for NULL directly.
Prefer NOT EXISTS for anti-joins; it removes an entire category of bug.
Aggregates ignore NULL, which changes denominators.

If you are porting queries between engines, note that COALESCE is standard while IFNULL (MySQL/SQLite), NVL (Oracle), and ISNULL (SQL Server) are dialect-specific — COALESCE works everywhere.

I wrote up the full set of NULL behaviours, including truth tables, NULLIF, and how each engine sorts NULLs, at SQL NULL handling: common pitfalls and fixes. And if you want to eyeball a query's structure before running it, the online SQL formatter is client-side — the text never leaves the browser.

ROW_NUMBER vs RANK vs DENSE_RANK: one of them silently duplicates your Top-N

sharefun2023 — Tue, 29 Sep 2026 07:49:19 +0000

I was reviewing a "top 3 products per category" report last week and found a bug that had been live for months. One category showed four products. Another showed the same product twice.

The query used RANK(). It should have used ROW_NUMBER().

They look interchangeable in a tutorial and behave completely differently the moment your data has ties.

The setup

Here is a small sales table with an obvious tie — two products in the same category sold 120 units:

CREATE TABLE product_sales (
  category   text,
  product    text,
  units_sold int
);

INSERT INTO product_sales VALUES
  ('tools', 'Hammer',   120),
  ('tools', 'Wrench',   120),
  ('tools', 'Saw',       95),
  ('tools', 'Drill',     80);

Two products are tied at 120, one at 95, one at 80. What does "top 3" mean here? That depends entirely on which ranking function you pick.

What each function actually returns

All three are window functions, so they need an OVER (PARTITION BY ... ORDER BY ...) clause. The difference is what they do with equal values:

Function	Ties get	Result on the data above
`ROW_NUMBER()`	arbitrary distinct numbers	1, 2, 3, 4
`RANK()`	the same rank, then it skips	1, 1, 3, 4
`DENSE_RANK()`	the same rank, then it does not skip	1, 1, 2, 3

SELECT
  product,
  units_sold,
  ROW_NUMBER() OVER (ORDER BY units_sold DESC) AS rn,
  RANK()       OVER (ORDER BY units_sold DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY units_sold DESC) AS drnk
FROM product_sales;

product | units_sold | rn | rnk | drnk
--------+------------+----+-----+-----
Hammer  |        120 |  1 |   1 |    1
Wrench  |        120 |  2 |   1 |    1
Saw     |         95 |  3 |   3 |    2
Drill   |         80 |  4 |   4 |    3

Notice RANK() jumps from 1 to 3. That gap is by design — it answers "how many rows are strictly better than this one, plus one". ROW_NUMBER() answers "give me a stable row index". Those are different questions.

How RANK() silently breaks "top N per group"

Now add the category column back and filter to the top 3:

SELECT category, product, units_sold
FROM (
  SELECT
    category, product, units_sold,
    RANK() OVER (PARTITION BY category ORDER BY units_sold DESC) AS rnk
  FROM product_sales
) t
WHERE rnk <= 3;

You get four rows for tools: Hammer, Wrench, Saw, Drill. Both tied products share rank 1, then Saw gets rank 3 and Drill also gets rank 3 — so rnk <= 3 keeps all four.

That is the bug. The report says "top 3" and emits 4 rows. If a downstream system paginates on the count, the totals are wrong. If a human reads it, they wonder why the list is one item longer.

Swap in ROW_NUMBER() and you get exactly 3 rows — but now which of Hammer/Wrench wins is undefined, because the ORDER BY has no tiebreaker.

The fix: ROW_NUMBER with an explicit tiebreaker

Never let the database decide ties for you. Add a deterministic second sort key:

SELECT category, product, units_sold
FROM (
  SELECT
    category, product, units_sold,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY units_sold DESC, product ASC   -- tiebreaker
    ) AS rn
  FROM product_sales
) t
WHERE rn <= 3;

Now the result is stable and reproducible: the same query returns the same three rows every time, in every environment. When someone asks "why is Wrench above Hammer?", the answer is a rule you wrote down, not whatever order the storage engine happened to return.

If ties genuinely deserve equal standing and you want all of them, then RANK() is correct — just be aware you are asking for "at least N".

When DENSE_RANK is what you actually want

DENSE_RANK() is for "which distinct tier is this value in", where you do not want gaps. Ranking price bands, severity levels, or score buckets:

SELECT
  product,
  units_sold,
  DENSE_RANK() OVER (ORDER BY units_sold DESC) AS tier
FROM product_sales;

You get tiers 1, 1, 2, 3 — consecutive, no holes. That reads correctly as "there are three distinct performance levels here". If you used RANK(), the output would say tier 3 for a value that is only the second-best level, which is misleading in a label.

Three things that bite people

1. ROW_NUMBER without a tiebreaker is non-deterministic. The database is free to return either tied row first, and it can change between runs, after an ANALYZE, or after a version upgrade. If the result feeds anything with a count or a diff, you will eventually see a phantom change.

2. Window functions need the filter outside the query. You cannot write WHERE ROW_NUMBER() OVER (...) <= 3 — window functions are evaluated after WHERE. The subquery (or a CTE) is required, not stylistic.

3. Version support. Window functions landed in MySQL 8.0, PostgreSQL 8.4, SQL Server 2005, and SQLite 3.25. On MySQL 5.7 there is no ROW_NUMBER() at all, and the usual workaround is a correlated subquery counting better rows — which is also the clearest way to understand what the rank means:

SELECT p1.category, p1.product, p1.units_sold
FROM product_sales p1
WHERE (
  SELECT COUNT(*) FROM product_sales p2
  WHERE p2.category = p1.category
    AND p2.units_sold > p1.units_sold
) < 3;

That counts how many rows beat this one, which is exactly what RANK() computes — and it has the same tie behaviour, including the extra rows.

The takeaway

Reach for ROW_NUMBER() when you mean "give me N rows". Reach for RANK() when you mean "give me everything at least as good as the Nth". Reach for DENSE_RANK() when the number is a tier label rather than a position.

And whenever you use ROW_NUMBER() for a Top-N, put a tiebreaker in the ORDER BY. It costs nothing and it makes the query reproducible.

If you want to check how a slow or deeply nested query looks after cleanup, the free online SQL formatter runs entirely in your browser — no query text leaves your machine. I wrote up the full window function reference, including running totals and LAG/LEAD comparisons, at SQL window functions explained.

Your PNG quality slider does nothing — I measured canvas.toBlob() for PNG, JPEG and WebP

sharefun2023 — Sat, 26 Sep 2026 12:38:20 +0000

Every image tool on the web has a "quality" slider. Almost nobody tells you which formats actually obey it.

I build client-side image tools, so the slider isn't cosmetic for me — it's the whole feature. I sat down with a headless Chrome and measured what canvas.toBlob() really does with that argument, format by format. Here are the raw numbers, and the two places where the mental model everyone has is simply wrong.

The setup

One 1200×800 canvas, deliberately "photo-like" so a lossy encoder has something to chew on:

a three-stop linear gradient across the whole frame,
6,000 semi-transparent noise rectangles (4×4 px),
72 px monospace text on top.

Then I encoded the same pixels over and over with different quality values. Chrome 149 headless, Linux, nothing exotic. Everything below is canvas.toBlob(callback, type, quality) on identical source pixels — no file I/O, no libraries.

Format / quality	Output bytes	vs. the best case
PNG, q = 0.1	1,345,909	—
PNG, q = 1.0	1,345,909	0.0% change
JPEG, q = 1.0	962,887	baseline
JPEG, q = 0.8	96,345	−90.0%
JPEG, q = 0.6	57,971	−94.0%
JPEG, q = 0.1	12,124	−98.7%
WebP, q = 1.0	477,822	baseline
WebP, q = 0.8	68,194	−85.7%
WebP, q = 0.3	26,504	−94.5%

Finding 1: PNG ignores the quality argument completely

Not "a little", not "with a small effect". Byte-identical. 1,345,909 bytes at q = 0.1 and at q = 1.0, and a second 200×200 canvas gave 17,279 bytes at both settings too.

The reason is boring once you see it: PNG is a lossless format. The ninth argument of the WebP/JPEG encoder is a quantisation target; PNG has no such concept. Chrome's PNG encoder takes the quality value and throws it away.

Which means every product page claiming "PNG compression: set quality to 80% and save 50%" is describing something no browser can do. If you see a PNG quality slider, it is decoration.

What actually makes a PNG smaller:

fewer pixels — downscaling is the only lever that always works,
fewer colours — converting to a palette and quantising (that needs a real PNG encoder such as UPNG/ImageQuant, not toBlob),
a different format — for photographic content WebP or AVIF wins outright, and both keep transparency.

Finding 2: WebP beat JPEG at every quality setting I tried

Look at the table again. Same pixels, same machine, same call:

q = 1.0 → JPEG 962,887 vs WebP 477,822 (WebP is half the size),
q = 0.8 → JPEG 96,345 vs WebP 68,194.

Chrome's image/jpeg encoder at quality 1.0 is still a lossy encode — it just targets a very high fidelity and stops caring about size. WebP's q = 1.0 is also lossy, but its ceiling is far cheaper.

The practical takeaway: if the browser can encode WebP, "I'll use JPEG because WebP might be bigger" is not a good default any more. Check it per image if you care, but on photographic pixels the ordering held for me across the whole range.

Two caveats worth writing down, because someone will hit them:

The curve is source-dependent. My synthetic gradient plus noise is a compression-friendly worst case for JPEG (smooth areas plus fine detail). Flat vector-ish artwork compresses very differently and the percentages shrink a lot. Never quote a compression percentage without saying what image it came from.
q = 1.0 JPEG is still lossy. There is no "lossless JPEG" in canvas. If you need bit-accuracy, the format is PNG or WebP (lossless) — not a higher quality number.

The part that isn't about quality at all

toBlob() does not copy your file. It encodes the RGBA pixels currently on the canvas. Three consequences people trip over:

Metadata is gone. The canvas holds pixels, not a container. EXIF, GPS, XMP and the ICC profile do not survive drawImage → toBlob. Sometimes that's the feature (stripping location data before publishing); sometimes it silently breaks a colour-managed workflow. Either way it is a side effect, not a setting.

Alpha survives for PNG and WebP, and cannot survive JPEG. JPEG has no alpha channel. Transparent regions come out black unless you paint something first:

const ctx = canvas.getContext('2d');
ctx.fillStyle = '#ffffff';
ctx.fillRect(0, 0, canvas.width, canvas.height);
ctx.drawImage(img, 0, 0, canvas.width, canvas.height);

If you're exporting SVG to JPEG and your logo's transparent background turned into a black box, this line is the fix.

Animation is flattened. Canvas is a single raster. Draw an animated GIF, APNG or animated WebP into it and you get one frame — whatever frame the image happens to be showing. There is no frame count in toBlob.

Where this bites hardest: SVG → PNG

This is the exact path an SVG-to-PNG converter takes:

serialise the SVG and load it into an <img>,
drawImage() it onto a canvas at the pixel size you want,
canvas.toBlob(cb, 'image/png') and hand the result to the user.

Step 3 is the PNG row of the table above. The quality argument is quietly discarded.

So when you're looking for a "higher quality" PNG export of a vector file, the quality field is the wrong control — it does nothing, in any browser. The only lever that changes the output is how many pixels you rasterise to. A 24×24 icon exported at 512×512 is 512×512 real pixels; the SVG's own width/height attributes don't cap it, and a file with only a viewBox has no intrinsic size at all (browsers fall back to 300×150 when they must).

I keep a browser-only converter at imgloft.com/svg-to-png where the only input that matters is the output size, precisely because that's the only one that does anything. If you want the long version of the SVG rasterisation pitfalls — external resources silently skipped, webfonts that never load, currentColor resolving to black — they're written up in the SVG to PNG guide.

Reproduce it in twenty lines

Paste this in any browser console. It rebuilds the test canvas and prints the same table:

const c = document.createElement('canvas');
c.width = 1200; c.height = 800;
const ctx = c.getContext('2d');
const g = ctx.createLinearGradient(0, 0, 1200, 800);
g.addColorStop(0, '#1b3a6b');
g.addColorStop(0.5, '#c94f2a');
g.addColorStop(1, '#f2e6c9');
ctx.fillStyle = g; ctx.fillRect(0, 0, 1200, 800);
for (let i = 0; i < 6000; i++) {
  ctx.fillStyle = `rgba(${Math.random()*255|0},${Math.random()*255|0},${Math.random()*255|0},0.35)`;
  ctx.fillRect(Math.random()*1190, Math.random()*790, 4, 4);
}
ctx.fillStyle = '#111'; ctx.font = '72px monospace'; ctx.fillText('MEASURED', 60, 200);

const enc = (type, q) => new Promise(r => c.toBlob(b => r(b.size), type, q));
(async () => {
  console.log('png  0.1', await enc('image/png',  0.1));
  console.log('png  1.0', await enc('image/png',  1.0));
  console.log('jpeg 0.8', await enc('image/jpeg', 0.8));
  console.log('webp 0.8', await enc('image/webp', 0.8));
})();

The two PNG numbers will match. They always match.

One gotcha if you wire this to a button

My first attempt at measuring this was wrong, and the mistake is easy to make. The convert button in a typical pipeline is disabled while the async encode is in flight:

btn.disabled = true;
canvas.toBlob(async (blob) => { /* ... */ btn.disabled = false; }, type, quality);

A disabled button does not dispatch click. So a script that clicks, changes the quality, and clicks again gets the first result twice — and you conclude the quality setting is ignored. I nearly published "WebP ignores quality too" on that basis. Reload the page between measurements, or poll until btn.disabled === false before clicking again.

Same trap catches users, not just testers: double-clicking a convert button mid-encode does nothing at all, which is usually the desired behaviour and occasionally a silent dropped job.

The short version

PNG: quality is ignored — byte-identical output. Resize, quantise, or switch format.
JPEG: quality works, hard. Watch out for the transparent-to-black default and for q = 1.0 still being lossy.
WebP: quality works, and it beat JPEG at every setting on my test pixels, alpha included.
Everything: toBlob re-encodes pixels, so metadata is dropped, animation is flattened, and the only "more quality" lever for vector sources is more pixels.

If you build tools like these, measure the numbers yourself before you write them on a landing page. A quality slider that does nothing is a much worse look than no slider at all.

Base64 decoding: padding, UTF-8, and the atob() trap

sharefun2023 — Mon, 21 Sep 2026 12:06:14 +0000

Most Base64 bugs I have debugged were not crypto bugs. They were text-encoding bugs wearing a Base64 costume — or decoders being far more permissive than anyone assumed. Here are the five that keep showing up, each one measured rather than remembered.

1. `atob()` does not return text

Base64 encodes bytes, not characters. atob() returns a "binary string": one character per byte, char codes 0–255. Non-ASCII text (i.e. anything UTF-8) comes back as mojibake:

atob('5paH5Lu2');   // "æä»¶"  ← not what you meant

The fix is one extra step, and it is always the same step:

const bytes = Uint8Array.from(atob(b64), c => c.charCodeAt(0));
const text  = new TextDecoder('utf-8').decode(bytes);   // "文件"

Newer engines are getting a native helper (Uint8Array.fromBase64()), but the split is real: Chrome (v153) has it and Node 24 does not — I checked both on this machine, side by side. Until every runtime you target agrees, TextDecoder is the form that works everywhere.

2. Padding is structure, not payload

Base64 packs 3 bytes into 4 characters. When the byte count is not divisible by 3, = fills out the last group. Two useful consequences:

Browsers accept missing padding: atob('aGVsbG8') returns 'hello' without complaint.
length % 4 === 1 is impossible. If you compute that, the string was truncated somewhere upstream — not "recovered" by adding =. atob('a') throws InvalidCharacterError, which is the correct behaviour being politely demonstrated.

3. URL-safe Base64 is a different alphabet

JWT segments, S3 presigned URLs, anything passed through a query string: + becomes -, / becomes _, and the = padding is usually stripped. atob() rejects those two characters outright:

atob('a-b_');   // InvalidCharacterError

So convert the alphabet before decoding, and re-add the padding:

function b64urlToText(s) {
  s = s.replace(/-/g, '+').replace(/_/g, '/');
  s += '='.repeat((4 - (s.length % 4)) % 4);
  return new TextDecoder('utf-8')
    .decode(Uint8Array.from(atob(s), c => c.charCodeAt(0)));
}

4. Node's decoder never complains, and that is the trap

Buffer.from(s, 'base64') is deliberately lenient. It ignores characters outside the alphabet, it ignores truncation, and it never throws. Measured on Node 24:

Buffer.from('aGVs*bG8=', 'base64').toString();  // 'hello'  ← the * was silently dropped
Buffer.from('aGVsb',    'base64').toString();   // 'hel'    ← truncated input, no error
Buffer.from('a-b_',     'base64');              // 'k\ufffd\ufffd' ← URL-safe chars, decoded as garbage

Which means "it decoded fine in Node" proves nothing at all: you can be handed a mangled string, get a plausible-looking result, and only find out much later. If the input can come from anywhere you do not control, validate before decoding:

if (!/^[A-Za-z0-9+/]*={0,2}$/.test(s) || s.length % 4 === 1) {
  throw new Error('not valid Base64');
}

5. Whitespace is a non-issue (PEM, MIME, emails)

Both browser atob() and Node strip ASCII whitespace, so line-wrapped PEM bodies and MIME blobs decode as-is: atob('aGVs\n bG8=') → 'hello'. The classic failure is never whitespace — it is everything in section 4.

Where the "free online decoder" pattern actually leaks

Look at what gets pasted into a Base64 decoder. It is rarely a toy string: it is a session cookie, a signed token, an auth header copied out of a log line. If it is a JWT, the payload is a live credential until it expires, and it has just been POSTed to someone's server. That is also why token decoders make an attractive target — their request logs are full of credentials that still work.

Browser devtools will decode for you, but the fiddly cases (URL-safe input, invalid bytes, known-truncation, a hex view of the decoded bytes) are what made me keep a decoder open in a browser tab. Mine is at 23232322.xyz/base64-decode — client-side only, nothing uploaded, and it explains why an input is rejected instead of just failing. The encoder pair is at base64-encode, and if you are pasting actual tokens, the one that verifies signatures (HS256/384/512 secrets and RS/ES256–512 public keys) is the JWT decoder & debugger.

TL;DR

Symptom	Cause	Fix
`æä»¶` instead of real text	`atob()` returns bytes-as-chars	`new TextDecoder('utf-8').decode(Uint8Array.from(atob(s), c => c.charCodeAt(0)))`
`InvalidCharacterError` on `-` / `_`	URL-safe alphabet	swap `-_` → `+/`, re-pad
Throws on a 5-char segment	`length % 4 === 1`	input is truncated; fix upstream
Node decodes garbage without error	`Buffer.from` silently drops junk	regex-validate first
Line-wrapped PEM block	—	nothing to do, whitespace is ignored

I Counted How Many Free Dev Tools Quietly Upload Your Data. Then I Built 80 That Don't.

sharefun2023 — Sun, 13 Sep 2026 12:05:30 +0000

Paste a JWT into a "free online JWT debugger." Paste a config file into a "free JSON formatter."

Then open DevTools, hit the Network tab, and watch it POST your payload to api.something-saas.com/v1/parse.

I got annoyed enough to check. Of the first dozen "free" tools I tried, eight shipped data off the device. Some had a privacy policy. Some had a privacy policy that said "we may share data with partners." Your production tokens, in a partner's S3 bucket.

Most of them weren't even doing it to be evil. They just built a Next.js app, needed the logic somewhere, and the server was already there. Formatter on the server. Base64 decode on the server. For a 3-line JSON.stringify.

The part that actually costs you

It's not just privacy theater. Server-side tooling has real costs you pay every day:

Latency. Round-trip to a region that isn't yours, on a connection that isn't yours. My regex tester took ~400ms to say "no match."
Rate limits and paywalls. Free tier hits you at 10 operations. The tool that was "free" is now a $9/mo subscription for btoa.
Availability. Their server goes down, your tool goes down. Great during an incident, when you actually need to decode something.
Offline. On a plane, on a locked-down corporate network, the tool is gone.

Every one of those problems disappears if the code runs in the browser tab you already have open.

What client-side actually means

There is no API. There is no fetch to a backend. Network stays empty except for the static assets. Concretely:

// Base64, no server. 2 lines and no data leaves the tab.
const encode = (s) => btoa(String.fromCharCode(...new TextEncoder().encode(s)));
const decode = (s) => new TextDecoder().decode(Uint8Array.from(atob(s), c => c.charCodeAt(0)));

// JWT verify with a real signature check, in-page.
// WebCrypto handles HS256/384/512 and RS/ES via importKey.
const [h, p, s] = jwt.split(".");
const key = await crypto.subtle.importKey(
  "raw", new TextEncoder().encode(secret),
  { name: "HMAC", hash: "SHA-256" }, false, ["verify"]
);
const sig = Uint8Array.from(
  atob(s.replace(/-/g, "+").replace(/_/g, "/")), c => c.charCodeAt(0)
);
const ok = await crypto.subtle.verify(
  "HMAC", key, sig, new TextEncoder().encode(`${h}.${p}`)
);

That's the whole "backend." The <input> is the API.

The interesting cases are where people assume you need a server:

Task	Why people think it needs a server	How it's actually done client-side
SQL formatting	"Parsing is hard"	Tokenizer + formatter in a Web Worker — keeps the main thread at 60fps
Image conversion	"Encoding is server work"	Canvas + `toBlob('image/webp')`
SVG to PNG	"Rasterization needs a service"	Draw the SVG into a canvas via a data-URI image
Regex testing	"Sandboxing"	It's a regex. `RegExp` has been in the engine forever
Hashing (SHA/MD5)	"Performance"	`crypto.subtle`, native speed

WebCrypto also gives you something the SaaS tools don't: the key never crosses the wire. A server-side JWT verifier has to receive your signing secret to check a signature. That's a much bigger deal than the payload leak.

What I built

23232322.xyz — 80+ tools, one page each, no build step. JSON formatter and validator, Base64 encode/decode, regex tester, JWT debugger with real HS256/384/512 and RS/ES256-512 verification, hash generators, UUID, URL encode/decode, diff checker, HTML minifier, and a bunch more.

No account. No upload. No analytics on your input. Source for each page is plain HTML + vanilla JS, so you can read it and confirm nothing is being sent anywhere.

How to check any tool yourself in 20 seconds

Before you trust any "free online X":

Open DevTools → Network.
Filter to Fetch/XHR.
Use the tool with obvious dummy data ({"test":1}).
If a request appears with your input in the body — that data left your machine.

Do that to my tools too. It's the point.

If a tool can't be written this way, it's because it needs a real backend. Most of them don't. They just needed somewhere to put the code.

The whole set is at 23232322.xyz.

Why Your SVG-to-PNG Conversion Looks Wrong (And How to Fix It in the Browser)

sharefun2023 — Thu, 10 Sep 2026 12:34:26 +0000

Why Your SVG-to-PNG Conversion Looks Wrong (And How to Fix It in the Browser)

You export an icon to PNG and it comes back with the wrong font, a black background instead of transparency, or soft, blurry edges. Nine times out of ten the bug is not in your export code — it's in the assumption that a browser rasterizes SVG the same way a design tool does.

Converting SVG to PNG in the browser is really just three steps: turn the markup into an image, draw that image onto a canvas, and read the canvas back as a PNG. The canvas is doing all the work. Everything that looks wrong comes from what the browser refuses to load while doing it.

Here is the full pipeline plus the seven failure modes I hit while building a client-side converter with no server, no uploads, and no dependencies.

The pipeline

async function svgToPng(svgText, { scale = 2, background = null } = {}) {
  // 1. Parse and normalise the SVG
  const doc = new DOMParser().parseFromString(svgText, 'image/svg+xml');
  const svg = doc.documentElement;
  if (svg.querySelector('parsererror')) throw new Error('Invalid SVG');

  // 2. Resolve the intrinsic size
  const vb = svg.getAttribute('viewBox');
  let [, , w, h] = (vb || '0 0 300 150').split(/[\s,]+/).map(Number);
  w = w || Number(svg.getAttribute('width')) || 300;
  h = h || Number(svg.getAttribute('height')) || 150;

  // Force explicit pixel dimensions (see pitfall 3)
  svg.setAttribute('width', w);
  svg.setAttribute('height', h);
  svg.setAttribute('xmlns', 'http://www.w3.org/2000/svg');

  // 3. Serialise to a blob URL the <img> can load
  const blob = new Blob([new XMLSerializer().serializeToString(svg)], {
    type: 'image/svg+xml;charset=utf-8',
  });
  const url = URL.createObjectURL(blob);

  try {
    const img = await loadImage(url);

    // 4. Draw at scale for a crisp result
    const canvas = document.createElement('canvas');
    canvas.width = Math.round(w * scale);
    canvas.height = Math.round(h * scale);
    const ctx = canvas.getContext('2d');

    if (background) {
      ctx.fillStyle = background;
      ctx.fillRect(0, 0, canvas.width, canvas.height);
    }
    ctx.drawImage(img, 0, 0, canvas.width, canvas.height);

    // 5. Export
    return await new Promise((resolve, reject) =>
      canvas.toBlob(
        (b) => (b ? resolve(b) : reject(new Error('Canvas is tainted'))),
        'image/png'
      )
    );
  } finally {
    URL.revokeObjectURL(url);
  }
}

function loadImage(src) {
  return new Promise((resolve, reject) => {
    const img = new Image();
    img.onload = () => resolve(img);
    img.onerror = () => reject(new Error('SVG failed to rasterise'));
    img.src = src;
  });
}

Roughly 40 lines, and it works — for a subset of SVG. The rest of this post is about that subset.

Pitfall 1: external resources are silently blocked

This is the big one. When an SVG is loaded through an <img>, the browser treats it as an image, not a document. Images do not get to fetch subresources. So these all fail silently:

<image href="logo.png"> pointing at a file or a URL
@font-face rules inside a <style> block
@import of a stylesheet
anything behind url(#…) that resolves to a network request

There is no error. No console warning. The element just renders as nothing, and you get a PNG with a hole in it. The fix is to inline everything first — convert referenced images to data: URIs, and turn text into paths before export if the font matters (see next pitfall).

Pitfall 2: fonts fall back to whatever is installed

Same root cause, different symptom. Your SVG says font-family: "Inter", the browser rasterizing it has never heard of Inter, and you get Helvetica or DejaVu instead. Text reflows, line breaks move, and a logo turns into something slightly wrong that nobody can name.

Two reliable options:

Convert text to paths in the source file. The output is then font-independent everywhere, forever. This is what text-to-path / object-to-path does in your editor.
Use only fonts you can guarantee — generic families (sans-serif, serif, monospace) or system stacks. They render, just never the exact file you designed with.

If you keep live text, expect the browser's metrics, not your design tool's metrics.

Pitfall 3: no width/height, no pixels

An SVG with only a viewBox has no intrinsic size:

<svg viewBox="0 0 24 24" xmlns="http://www.w3.org/2000/svg">

That's perfectly valid SVG, and Chrome will happily display it when sized by CSS. But when you load the same markup into an <img>, several browsers report naturalWidth as 0 — and drawImage with a zero-sized source throws or draws nothing. The export "succeeds" and produces an empty or 1×1 PNG.

Fix: always set explicit width and height attributes on the root <svg> before serialising, derived from the viewBox. That's the svg.setAttribute('width', w) line above, and it's the single most common cause of "my converter only works on some files."

Pitfall 4: blurry output on retina

A canvas has two sizes: its CSS size and its backing-store size. Only the backing store defines how many real pixels you get. If you set the canvas to 300×300 and draw a 300×300 icon into it, you get a 300×300 PNG — which is correct, and looks soft on a 2× display because there simply aren't enough pixels.

Set the backing store bigger and scale the drawing:

canvas.width = Math.round(w * scale);   // scale = 2 for 2x
canvas.height = Math.round(h * scale);
ctx.drawImage(img, 0, 0, canvas.width, canvas.height);

Because SVG is resolution-independent, you can go as high as you like — scale = 4 on a 24px icon gives you 96×96 real pixels. Just remember the dimensions you multiply here are the output pixel dimensions; a 4000px export of a complex icon is a 4000px PNG, not a free lunch.

Pitfall 5: "transparent" isn't

canvas starts fully transparent, so toBlob('image/png') gives you real alpha by default. The surprise is the other direction: designers export SVGs with a white <rect> covering the artboard, so your "transparent" PNG arrives with a white box painted over the background. Nothing to fix in code — it's in the source file.

If you do want a flat background (say for a JPG or for email), paint it before drawing, since drawImage composites rather than replaces:

if (background) {
  ctx.fillStyle = background;
  ctx.fillRect(0, 0, canvas.width, canvas.height);
}
ctx.drawImage(img, 0, 0, canvas.width, canvas.height);

Pitfall 6: filters and `foreignObject` are renderer-specific

feGaussianBlur, feTurbulence, mix-blend-mode, and <foreignObject> are the parts of SVG that every engine implements a little differently. A blur that looks right in Chrome can look banded in Firefox and glossier in Safari. <foreignObject> — the trick for rendering HTML inside SVG — is the least portable feature in the spec, and it's also the fastest way to get a canvas that won't export (next pitfall).

If your target is a PNG that looks identical for everyone, flatten these effects in the source file or accept per-browser drift.

Pitfall 7: the tainted canvas

canvas.toBlob() throws SecurityError when the canvas is "tainted" — meaning something cross-origin was drawn into it. With pure SVG-as-image this is mostly moot because external resources are blocked anyway, but it comes straight back when you compose a design: draw a user's cross-origin photo onto the canvas, then try to export, and the export dies even though the pixels are right there on screen.

Two ways out:

Serve the images with permissive CORS headers and set img.crossOrigin = 'anonymous' before setting src. (Order matters; setting it after triggers a second, uncached request or an outright failure.)
Do the compositing server-side, which is exactly the dependency a client-side tool is trying to avoid.

This is also why the classic "paste a URL and convert" trick fails: the remote SVG has to be fetched with CORS enabled, or the browser blocklists it before a single pixel is drawn.

The checklist

Before you blame your code, check the source file:

Everything inlined — images as data: URIs, fonts as paths or generic families.
Explicit width and height on the root <svg>, matching the viewBox.
xmlns="http://www.w3.org/2000/svg" present after serialisation.
No currentColor and no var(--…) — outside a page context there is nothing to inherit from, so they resolve to defaults (usually black).
No page CSS assumptions — styles must live inside the SVG's own <style> or as attributes.

And in your code:

Backing store scaled up for retina, not just CSS size.
URL.revokeObjectURL in a finally block, or you leak the blob on every conversion.
Paint a background before drawImage if the format has no alpha channel.

I built one of these

These are the exact edge cases I hit while building imgloft.com — a free browser-side image toolset for SVG→PNG, PNG compression, resizing and cropping. It does the rasterisation on your device with the pipeline above, so nothing is uploaded and there's no queue to wait in. If you just want to convert a file rather than debug one:

SVG to PNG converter — scale 1×–4×, transparent or flat background
The longer guide version of this post — including a comparison table of browser-side vs server-side conversion

If you've hit a rasterisation bug that isn't on this list, I want to hear it — drop it in the comments and I'll reproduce it.

How I Built a Free SVG to PNG Converter in 1 Hour

sharefun2023 — Tue, 04 Aug 2026 08:05:54 +0000

I needed a quick way to convert SVG icons to PNG for a project. Every online converter I found required uploading files to a server — slow, privacy-invasive, and overkill for a 2KB icon.

So I built imgloft.com — a free, client-side SVG to PNG converter that works entirely in your browser. No uploads, no server, 100% private.

The Problem

Most online SVG-to-PNG tools follow the same pattern:

Upload your SVG to their server
Server runs a conversion library (ImageMagick, librsvg, etc.)
Download the result

This is fine for occasional use, but it breaks down when:

You're working with sensitive design assets
The file is large and upload takes forever
You're offline or behind a restrictive firewall
You just want to convert a tiny icon without waiting

The 1-Hour Solution

The entire converter is built with three browser APIs:

// 1. Read the SVG file
const reader = new FileReader();
reader.onload = (e) => {
  const svgText = e.target.result;

  // 2. Draw it on a canvas at the desired resolution
  const img = new Image();
  const svgBlob = new Blob([svgText], {type: 'image/svg+xml'});
  img.src = URL.createObjectURL(svgBlob);

  img.onload = () => {
    const canvas = document.createElement('canvas');
    canvas.width = img.width;
    canvas.height = img.height;
    const ctx = canvas.getContext('2d');
    ctx.drawImage(img, 0, 0);

    // 3. Export as PNG
    canvas.toBlob((blob) => {
      const url = URL.createObjectURL(blob);
      // Trigger download
    }, 'image/png');
  };
};
reader.readAsText(file);

That's it. Three APIs, zero dependencies, zero server cost.

Why It Only Took 1 Hour

No backend. The entire tool runs in the browser using the Canvas API. No Express, no ImageMagick, no Docker container. Just a static HTML file served from Cloudflare Pages.

No auth. Users don't need accounts — they come, convert, and leave. No database, no user management, no session tokens.

No framework. Plain HTML, vanilla JavaScript, and a few lines of CSS. No React, no build step, no node_modules.

What I Learned

Client-side is underrated. The browser can do way more than we give it credit for. SVG rendering, image encoding, even basic image manipulation — all built in.

Ship the MVP. I could have spent weeks adding batch conversion, format options, color filters. Instead I shipped the core feature in an afternoon and let users tell me what they need.

Free tools attract real users. Within days of going live, Google started indexing the site. People are searching for "svg to png converter" — 40,000 times a month. The demand is real.

Try It Yourself

imgloft.com — drag any SVG, get a PNG instantly. Also includes compress, resize, and crop tools.

If you're thinking about building a micro-tool site, just do it. Ship the simplest version first. The browser is your backend.

Building a Client-Side SQL Formatter with Vanilla JS and Web Workers

sharefun2023 — Mon, 20 Jul 2026 15:35:46 +0000

Most SQL formatters process queries server-side. That means every time you click "Format", your SQL gets sent to a random backend — with no guarantee of what happens to it.

I wanted something different: a formatter where your data never leaves your browser.

The Stack

sqlformat.io is built with:

Vanilla JavaScript — no framework, no build step
sql-formatter — the formatting engine
Web Workers — formatting runs off the main thread so the UI stays snappy even on 1000+ line queries
Cloudflare Pages — zero-cost hosting with global edge caching

Architecture

User pastes SQL
  ↓
Main thread sends to Web Worker
  ↓
Worker calls sql-formatter with dialect-specific options
  ↓
Formatted SQL sent back to main thread
  ↓
Rendered with syntax highlighting (no library needed — just regex + <span>)

Key insight: the sql-formatter library alone is ~50KB gzipped. Running it in a Web Worker means the main thread never blocks, and the user can keep typing while large queries format in the background.

Why No Server?

Privacy — zero bytes leave the user browser
Offline — works after the page loads, no internet needed
Scale — no server costs, no rate limits, no API keys
Speed — no round trip, instant formatting

Lessons Learned

Web Workers are underrated — they turn a potentially laggy text processor into a buttery-smooth UX
SQL dialects are deceptively complex — MySQL vs PostgreSQL vs BigQuery all have different quoting rules, and the formatter needs to handle inline comments and dollar-quoted strings that span multiple lines
Regex-based syntax highlighting works surprisingly well for SQL — you do not need a parser, just a few well-placed regex patterns for keywords, strings, numbers, and comments

Try it: sqlformat.io

Source: github.com/sharefun2023/sqlformat.io

I Got Tired of Slow SQL Formatters That Upload My Queries — So I Built a Free One

sharefun2023 — Mon, 20 Jul 2026 15:35:08 +0000

I write a lot of SQL every day. And every online formatter I tried had the same problems:

Uploads queries to their server — privacy nightmare when you are working with production schema
Tiny text area — cannot handle 500+ line queries without lag
3 ads before you even see the formatted output

So I built sqlformat.io — a no-BS SQL formatter.

What It Does

Client-side only — everything runs in YOUR browser via Web Worker. Zero bytes leave your device
15+ SQL dialects — MySQL, PostgreSQL, SQLite, BigQuery, Snowflake, T-SQL, and more
No signup, no ads, no upload — ever
Instant — paste, click Format, done

Why I Built It

I got tired of copy-pasting production queries into random websites and wondering where they ended up. With sqlformat.io, you can disconnect your internet after the page loads and keep formatting offline.

Extra Tools Included

It is not just a formatter — the site has several free SQL tools:

Diff — compare two SQL queries side by side
Minify/Compress — strip whitespace for embedding in code
Validate — syntax check across dialects
ER Diagram — visualize table relationships
SQL to JSON — convert query results
Data Generator — generate test data
Explain Visualizer — MySQL/PostgreSQL EXPLAIN output as a tree

Tech Stack

Vanilla JS + sql-formatter library, hosted on Cloudflare Pages. All formatting runs in a Web Worker so the UI stays responsive even on huge queries.

Open source: github.com/sharefun2023/sqlformat.io

Would love feedback from folks who format SQL daily!

DEV Community: sharefun2023

PostgreSQL vs MySQL: 7 syntax differences that break migrations

1. Identifier quoting is not the same character

2. String concatenation

3. Auto-increment

4. Upsert syntax is completely different

5. Pagination

6. Booleans are not a type in MySQL

7. Case sensitivity of identifiers

A note on string comparison

Checklist before you migrate

The NOT IN trap: why your SQL query returns zero rows

The failure

Why

Three ways to fix it

The same trap in plain comparisons

What to take away

ROW_NUMBER vs RANK vs DENSE_RANK: one of them silently duplicates your Top-N

The setup

What each function actually returns

How RANK() silently breaks "top N per group"

The fix: ROW_NUMBER with an explicit tiebreaker

When DENSE_RANK is what you actually want

Three things that bite people

The takeaway

Your PNG quality slider does nothing — I measured canvas.toBlob() for PNG, JPEG and WebP

The setup

Finding 1: PNG ignores the quality argument completely

Finding 2: WebP beat JPEG at every quality setting I tried

The part that isn't about quality at all

Where this bites hardest: SVG → PNG

Reproduce it in twenty lines

One gotcha if you wire this to a button

The short version

Base64 decoding: padding, UTF-8, and the atob() trap

1. atob() does not return text

2. Padding is structure, not payload

3. URL-safe Base64 is a different alphabet

4. Node's decoder never complains, and that is the trap

5. Whitespace is a non-issue (PEM, MIME, emails)

Where the "free online decoder" pattern actually leaks

TL;DR

I Counted How Many Free Dev Tools Quietly Upload Your Data. Then I Built 80 That Don't.

The part that actually costs you

What client-side actually means

What I built

How to check any tool yourself in 20 seconds

Why Your SVG-to-PNG Conversion Looks Wrong (And How to Fix It in the Browser)

Why Your SVG-to-PNG Conversion Looks Wrong (And How to Fix It in the Browser)

The pipeline

Pitfall 1: external resources are silently blocked

Pitfall 2: fonts fall back to whatever is installed

Pitfall 3: no width/height, no pixels

Pitfall 4: blurry output on retina

Pitfall 5: "transparent" isn't

Pitfall 6: filters and foreignObject are renderer-specific

Pitfall 7: the tainted canvas

The checklist

I built one of these

How I Built a Free SVG to PNG Converter in 1 Hour

The Problem

The 1-Hour Solution

Why It Only Took 1 Hour

What I Learned

Try It Yourself

Building a Client-Side SQL Formatter with Vanilla JS and Web Workers

The Stack

Architecture

Why No Server?

Lessons Learned

I Got Tired of Slow SQL Formatters That Upload My Queries — So I Built a Free One

What It Does

Why I Built It

Extra Tools Included

Tech Stack

1. `atob()` does not return text

Pitfall 6: filters and `foreignObject` are renderer-specific