Base64 Encoding in SQL: A Complete Guide

Every once in a while the database has to talk to the outside world, and the outside world does not always speak bytes. An API wants your logo inside a JSON string. A config export wants a secret that fits on one line of YAML without quotes or backslashes. A maintenance script wants to ship a file across a system that only carries text. That is the moment your data puts on a costume of letters, and the costume's name is base64.

The format itself is already covered on the home page (64 printable characters, every four of them standing for three bytes of input, up to two = signs padding the final group), so this article skips that lecture and goes straight to the machine work. Two things to hold onto: encoding is the direction where data gets bigger, so column widths and packet limits feel every byte of it, and the encoders in this SQL family disagree about the two things that are hardest to undo later: which bytes they read when your column holds text, and where they put the line breaks in what they write.

The Encoder Cheat Sheet

Who is on duty, what they eat, and where they break their output. The last two columns are the ones that bite, because a string full of uninvited line breaks and a string with a different alphabet are both perfectly valid base64 that your consumer will still reject:

Read the table left to right and the work falls into two decisions. First, how your bytes get there: the input type column is where character-set surprises are born, because "the same text" is different bytes under different collations. Second, what comes out on the other side: the wrap column decides whether your result is one flat line or a poem with a line break every 76 characters, and the URL-safe column decides whether you can put the result in a link at all.

First, Decide Which Bytes You Mean

An encoder packs bytes, but your column usually holds letters, and letters are only bytes if you say which alphabet of bytes. MySQL's TO_BASE64() reads its argument in the connection character set, which is a convenience until it is not: the same 'héllo' ships as different base64 under a latin1 client and a utf8mb4 client. When you mean the exact bytes as stored, freeze them with a binary cast first:

SELECT TO_BASE64('hello') AS from_text;
SELECT TO_BASE64(CAST('héllo' AS BINARY)) AS utf8_bytes;

The second row comes back as aMOpbGxv, the two hex digits C3 A9 in the middle being UTF-8's way of spelling é. PostgreSQL is stricter up front: encode() refuses to look at anything that is not bytea, so a text value must first name its encoding, while raw bytes can arrive as a hex literal:

SELECT encode(convert_to('héllo', 'UTF8'), 'base64') AS text_as_utf8;
SELECT encode('\x68656c6c6f'::bytea, 'base64') AS raw_bytes;

Every other dialect has its own front door to the same idea, and they all reduce to "get the bytes, then pack them":

The practical rule is the same as on the decoding side: decide the character set before you encode, write it into the query as a literal, and run one accented payload through the whole pipeline before you trust the column. One héllo catches every wrong collation, and it costs nothing.

Then, Watch What Comes Out

Once the bytes are packed, the encoders part ways over line breaks. Three of them (MySQL, PostgreSQL, the SQLite CLI) carry the e-mail habit of wrapping the output at 76 characters; the rest hand back one flat line no matter how long it gets. The difference is easy to miss and expensive to find, because a base64 field with hidden line breaks is a field that breaks a JSON parser halfway through:

SELECT LENGTH(TO_BASE64(REPEAT('x', 300))) AS wrapped,
      LENGTH(REPLACE(TO_BASE64(REPEAT('x', 300)), '\n', '')) AS flat;

Three hundred input bytes come back as 400 base64 characters, and the same call measures 405 because five line breaks rode along for the ride. The arithmetic behind it is small enough to keep in your head: the flat length is the input length divided by three, rounded up, times four. If your encoder wraps, add one line break for every full 76-character line, which is the flat length divided by 76 and rounded down. Three hundred bytes: 400 flat, 405 wrapped. One hundred eleven bytes: 148 flat, 149 wrapped. One more newline than you budgeted is how a VARCHAR(500) column starts silently truncating a VARCHAR(480) payload.

Two consequences worth writing down. Size text columns for the flat length plus a little slack if the writer might wrap, or ban the wrap in the writer and size for flat. And remember that the limit your result fights is the limit of the string, not the bytes: in MySQL the wrapped text counts against max_allowed_packet (64 MB by default in MySQL 8), so a 50-megabyte photo encoded to roughly 67 megabytes of letters does not fit the default packet even though the raw file would.

URL-safe Base64: The Traveling Alphabet

Section 5 of RFC 4648 defined a second alphabet for base64 because the original one has two characters with jobs in URL syntax. The plus sign adds query parameters, the slash separates path segments, and the padding equals sign gets percent-encoded the moment it meets a query string. The URL-safe variant swaps + for - and / for _, and the JWT spec on top of that drops the padding entirely, so a token can sit in a link, a path segment or a file name without a single percent sign.

Only one dialect in this family ships the switch natively. SQL Server 2025's BASE64_ENCODE() takes an optional second argument, and with it on the result uses - and _ and skips the padding:

SELECT BASE64_ENCODE(0xCAFECAFE) AS standard;
SELECT BASE64_ENCODE(0xCAFECAFE, TRUE) AS url_safe;

The same two bytes come back as yv7K/g== and yv7K_g. ClickHouse keeps the variants as separate functions, and its URL-safe form also drops the padding:

SELECT base64URLEncode('https://clickhouse.com') AS url_safe;

which arrives as aHR0cHM6Ly9jbGlja2hvdXNlLmNvbQ, the last equals sign of the standard form trimmed away. Everywhere else the recipe is two character translations and a trim, and it is worth writing once as a database function because every token pipeline needs it. In PostgreSQL it reads like this:

SELECT rtrim(replace(replace(
        encode(convert_to('https://clickhouse.com', 'UTF8'), 'base64'),
        '+', '-'),
      '/', '_'),
      '=') AS url_safe;

Translate + to -, translate / to _, trim the trailing padding, done. One warning for the SQL Server crowd: the url_safe output is not what the server's own XML and JSON base64 decoders expect, so a column packed in URL-safe form for the outside world will not unpack inside the database with the built-ins. Keep the audience in mind before you pick the alphabet.

JWTs: Stamping Tokens from the Database

The most interesting thing you can build with the encoder is a Json Web Token, because a JWT is nothing but three base64 pieces in a row: a header and a payload, both JSON objects packed URL-safe without padding, and a signature computed over the first two. When a batch job needs to mint tokens (seeding a test environment, regenerating expired API credentials, building an audit feed), the whole ceremony fits in one PostgreSQL query if you accept pgcrypto for the HMAC (enable it once with CREATE EXTENSION IF NOT EXISTS pgcrypto;):

WITH head AS (
  SELECT encode(convert_to('{"alg":"HS256","typ":"JWT"}', 'UTF8'), 'base64') AS h
),
body AS (
  SELECT encode(convert_to('{"sub":"1234567890","name":"Dev User"}', 'UTF8'), 'base64') AS p
),
joined AS (
  SELECT rtrim(replace(replace(h, '+', '-'), '/', '_'), '=') AS h64u,
         rtrim(replace(replace(p, '+', '-'), '/', '_'), '=') AS p64u
  FROM head, body
)
SELECT h64u || '.' || p64u || '.' ||
       rtrim(replace(replace(
         encode(hmac((h64u || '.' || p64u)::bytea, 'sql-secret-key'::bytea, 'sha256'), 'base64'),
         '+', '-'),
         '/', '_'),
       '=') AS token
FROM joined;

Each step is one of the moves this article has already shown: pack the JSON as base64, reshape it into the URL-safe no-padding alphabet, then sign the first two pieces and reshape the signature the same way. For the JSON above and the secret sql-secret-key, the result is eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJzdWIiOiIxMjM0NTY3ODkwIiwibmFtZSI6IkRldiBVc2VyIn0.7_3VdmM8vH0L2bRBNqXXQXzZIR1l8T4_S4QxPPNBEFM, a token that any HS256 inspector accepts. The caveats deserve as much airtime as the trick: it only covers HMAC algorithms (HS256, HS384, HS512), it places a shared secret inside a database statement, and it is built for batch and audit work, not for a production token service. The verification side, where you prove that token against its secret, is a job for the application layer or for the decoding article's signature check.

Images and Files in a Text Column

The most common reason to encode in SQL is a file that has to travel as text: an API that inlines the image instead of referencing it, an export for a system that carries no binary, a seed script that recreates a database on a new server. DuckDB makes the round trip nearly trivial, because it reads files into BLOBs through a table function that accepts glob patterns, and the encoder flattens whatever arrives:

SELECT filename, to_base64(content) AS b64
FROM read_blob('/data/pics/*.png');

One row per file, a flat base64 string per row, no wrapping to strip and no padding to apologise for. Write the result into a text column and the images are portable through any channel that moves text. Then have the cost conversation honestly: a 1-megapixel photo arrives as roughly 1.37 megabytes of letters, and from then on every scan, sort and index entry pays that price. If you control the schema, the better design is a BLOB column plus an encode at the API boundary, where only the bytes that actually leave the building get dressed up.

HTTP, JSON and API Traffic

Headers and payloads are where base64 does its quiet everyday work. A Basic auth header is the literal prefix Basic followed by the base64 of username:password, and constructing one in SQL is a concatenation plus one encode:

SELECT 'Basic ' || encode(convert_to('alice' || ':' || 's3cret', 'UTF8'), 'base64') AS header;

which builds Basic YWxpY2U6czNjcmV0, the exact header a client would send. Use it to generate the fixtures your integration tests compare against, or to normalise a column of stored headers before you audit them. On the JSON side, MySQL can pack a field and file it inside a document in a single expression, no application code in the loop:

SELECT JSON_OBJECT('img', TO_BASE64(CAST('hello file' AS BINARY))) AS doc;

The result is {"img": "aGVsbG8gZmlsZQ=="}, a ready-to-send payload. The same shape works for certificates, public keys and any other file your API decided to inline, and it is the direction that matters when you are the one producing the traffic, not decoding someone else's.

Config Files, Secrets and Environment Variables

One export habit deserves its own paragraph because it is everywhere: the secret stored as base64 in a config table. Kubernetes kept the habit alive, where secret values are base64 at rest so they fit on one line of YAML with no quotes, no newlines and no backslashes, and every in-house config system that ever met a Kubernetes pipeline picked it up. The packing direction is one encode per value, with the binary cast doing the character-set work so the exported text is exactly the stored bytes:

SELECT name, TO_BASE64(CAST(value AS BINARY)) AS for_config
FROM app_config
WHERE name LIKE '%_secret%';

Each value comes out as a flat, paste-ready string that the new environment can drop into a YAML file and decode back on the other side. Treat the result with the care it deserves: you just turned a column of stored secrets into a column of stored secrets that any human can read in about ten seconds, and the base64-in-config habit gets a second look the moment you are standing this close to the plaintext. Base64 is a transport, not a vault. If the environment has a real secret store, the base64 column is a migration away from one.

Email and the 76-Character Habit

The 76-character wrap is older than every database on this page. MIME, the set of standards that lets e-mail carry binary attachments (RFC 2045, section 6.8, 1995), wraps base64 output at 76 characters and ends each line with a carriage return and a line feed, because the old e-mail network could not be trusted with longer lines. Three encoders here inherited the wrap as their default (MySQL, PostgreSQL, the SQLite CLI), which is a gift for anything that ended up in an e-mail and a trap for anything that did not. And they inherited it half-finished: PostgreSQL ends its lines with a lone newline, not the carriage return and newline the MIME standard specifies, so output that should paste into a real e-mail attachment needs one more pass:

WITH t AS (
 SELECT replace(encode(attachment::bytea, 'base64'), chr(10), '') AS s
 FROM email_outbox
)
SELECT regexp_replace(s, '(.{1,76})', '\1' || chr(13) || chr(10), 'g') AS mime_ready
FROM t;

Strip the newlines PostgreSQL already added, then re-wrap at 76 with a full CRLF after every line, and the string is MIME-correct in one expression. Run three hundred bytes through it and the 400 characters you packed become 412: six wrapped lines, six CRLF pairs, twelve characters of transport ceremony. The same shape of problem shows up with PEM blocks, which wrap at 64 instead of 76, and with the APIs that want no wrap at all because their JSON parser will not meet a newline in the middle of a field. The rule for all of them: find out which contract the consumer signed before you encode, because re-wrapping a column of stored base64 is a migration, not a query.

Pitfalls: Where Encoders Lie to You

Every trap on this list is one that a specific dialect sets, not one that base64 sets, and every one of them has at least one codebase that found it in production:

• The unasked-for wrap. MySQL, PostgreSQL and the SQLite CLI wrap their output at 76 by default, and nobody in your query asked for it. The base64 field in your JSON now contains line breaks, and the consumer that accepts the format in the spec rejects it in the wild. The flat form is one REPLACE() over the newline character, applied on the writer side so the column stores what the reader wants.
• The character-set slip. TO_BASE64('héllo') without the binary cast encodes whatever the connection character set believes the letters are, and a latin1 client and a utf8mb4 client believe different things. The same query text, two different base64 results, and the wrong one decodes into mojibake that nobody traces back to the encoder. The binary cast, or an explicit convert_to(), is the only honest version of the query.
• The BLOB-only door. DuckDB's to_base64() will not look at a string; feed it text and the query fails with a type error. The string has to pass through encode() first to become a BLOB. It is the strictest front door in the family, and it is the reason the examples on this page always show the pair to_base64(encode(...)) for text input.
• The padding flip. ClickHouse's base64Encode() went from no padding to padding when the 26.x line landed, and the old base64EncodeWithPadding() companion was removed along the way. A script written against 2023 ClickHouse and rerun on 2026 ClickHouse produces a different string for the same input, and a script that called the old companion dies with UNKNOWN_FUNCTION. Version-check the server before you diff the output.
• The 6000-byte boundary. SQL Server's BASE64_ENCODE() returns varchar(8000) for inputs of 6000 bytes or fewer and varchar(max) above that, so a column sized for the small case changes its type at the boundary. The same function's url_safe output, meanwhile, is not readable by the server's own XML and JSON base64 decoders, which expect the standard alphabet with padding. Pick the variant for the audience that will read it.
• The 2000-byte RAW. In Oracle, a RAW value in a plain SQL statement tops out at 2000 bytes. Since the encoder takes RAW and returns RAW, a single-statement encode can only accept about 1500 bytes of input (its 2000 characters of output would fit, any more would not), and a single-statement decode can only accept 2000 characters of base64. Larger payloads need the PL/SQL chunk loop, and the loop is the standard answer because the limit is from the 1990s and has never moved.
• The packet tax. MySQL counts the encoded string against max_allowed_packet, not the raw bytes. A photo that fits the table by a mile can overflow the packet once it is 33 percent larger and wrapped, and the failure mode is a truncated value or a NULL that looks like data corruption. Check the limit in the same breath as the column width.
• The trailing newline. The SQLite CLI's base64() ends its last line with a newline, the 76-character habit applied to the final line as well. Paste the shell output into a JSON field and you have shipped a base64 string with a newline in it, the unasked-for wrap wearing a different hat.
• The alphabet assumption. A consumer built for the standard alphabet meets your URL-safe output (or vice versa) and sees characters it does not know. Most decoders fail loudly on the underscore; a few fail silently by skipping it. Document the alphabet of every base64 column in the schema comment, because the next developer will not remember which token pipeline wrote the row.

When Size Actually Matters

The size math is the flat length plus whatever wrapping your encoder adds, and the home page does the full derivation of the ratio. What is worth doing here is walking through where the number stops being a curiosity. A VARCHAR column sized to the input's byte count silently truncates the output the first time the payload is long enough to need a third character of headroom, because three bytes of input cost four characters. An index over a base64 text column pays the tax twice: once in storage and again in every comparison, because the index entries are the wrapped letters, not the bytes. MySQL's max_allowed_packet and PostgreSQL's 1-GB bytea ceiling are the two walls most people hit first, and both are checked against the text, which is the bigger side of the exchange. The design answer is rarely about choosing a different encoder (there is only one base64); it is about choosing where the encoding happens. BLOB column, hash column for lookups, encode at the boundary: the base64 exists only in the traffic, where it belongs.

Security: What Base64 Is Not

Base64 is not encryption, and the one habit that needs saying out loud is the config-table one from earlier: a secret stored as base64 is a secret stored in a different font. The transformation is a bijection with no key, reversible by every programming language on earth in one function call, and its only real effect is keeping the value on one line of YAML. If the threat model includes another user of this database, another service that reads the export, or a log that captured the row, base64 contributes exactly zero to the defense. It obfuscates from the human eye for a few seconds, which is why it feels like protection in a code review and why it fails in an incident. Encrypt what must be secret, encrypt it with a key someone can actually keep secret, and let base64 do the job it is good at: moving bytes through a channel that only carries text.

When Each Dialect Learned to Wrap

The release notes tell the same story as the decoding side did, just with the letters going the other way, and the schedule says something about each engine:

2005. PostgreSQL 7.4 lists base64 as a first-class format of encode() and decode(), the oldest base64 machinery in this family by a wide margin. A database with a real binary type and a format argument got there early, because the answer was one enum value away.

Early 2000s. Oracle's UTL_ENCODE package ships in the 9i era with BASE64_ENCODE() next to its MIME header, quoted-printable and uuecode siblings. RAW in, RAW out, and three decades later the package has not changed its mind.

2013. MySQL 5.6 adds TO_BASE64() and FROM_BASE64() as a matched pair, and MariaDB 10.0 inherits both. The pair's contract has not moved since: 76-character lines on the way out, whitespace tolerance on the way in.

2019. ClickHouse 18.16 ships base64Encode() and base64Decode() together with the MySQL-style alias, because the columnar world was importing workloads whose log schemas already carried base64 in them.

2023. SQLite 3.41.0 adds base64() to the command-line shell as an application-defined function. The core library gets nothing, as is its way; the shell, where humans actually poke at SQLite files, gets the tool.

2025. SQL Server, generally available in November 2025, finally ships BASE64_ENCODE() and BASE64_DECODE(), twenty-seven years after the product launched and a generation after its users learned the XML workaround by heart.

The pattern is the same one the decoding article ends with, mirrored: the engines with a real binary type and a format argument got base64 the day the need was obvious, and the engines where everything is a string scheduled it for later.

Oddities Worth Knowing

• The SQLite CLI's base64() is the family's shape-shifter, and in the encoding direction it shows the trick best: hand it a BLOB and it hands back wrapped text with a trailing newline, hand it text and it hands back a BLOB. One name, two jobs, chosen by the type of the argument, and no other encoder in this family will do it.
• ClickHouse's base64Encode() changed its padding behavior when the 26.x line arrived and deleted the old base64EncodeWithPadding() in the same breath, so the same query returns a different string on the same server a year apart. The function did not break; it grew up, which is somehow harder to debug.
• PostgreSQL wraps at 76 characters exactly like the 1995 MIME standard, except it ends the lines with a lone newline instead of the standard's carriage return and newline. Twenty-plus years after the spec, one fewer character per line, and the rebellion is invisible unless you diff the bytes.
• In the mysql client, the bytes you encode print fine as base64 text, but the moment you look at the raw column with CAST(... AS BINARY) the client switches to hex display (binary-as-hex), and a perfectly good hello arrives on screen as 68656C6C6F. The setting has convinced thousands of developers their encoder is broken.
• Snowflake displays BINARY values as hex in every result set, so a TO_BINARY() input column in your encode query reads like a checksum even when everything worked. Two dialects, two hex displays, one identical feeling of unease.
• Oracle's SQL-level RAW caps at 2000 bytes, so a 3-kilobyte certificate cannot be pasted into a SQL statement as a RAW literal at all. The encode has to happen in PL/SQL, in chunks, with a loop, and the loop is still the recommended answer because the limit dates from the 1990s.
• One line of MIME base64 is 76 characters, which is 57 raw bytes, because four characters carry three. The number 76 that appears in three encoders' defaults is not a limit so much as a packing density: every wrapped line you see in an old e-mail attachment carried exactly 57 bytes of your data.

The Other Direction

This article has been about putting the costume on: deciding which bytes you mean, watching what comes out, picking the alphabet for the audience, and doing the size math before the column truncates. Taking the costume off is a different temperament entirely, with silent NULLs where one dialect shrugs, hard errors where another raises its voice, and a URL-safe alphabet that half the family does not know at all. All of that, from FROM_BASE64() to decode() to BASE64_DECODE(), is covered in depth in the related Base64 decoding article for SQL, linked from this page's article list. Encode here, decode there, and the whole round trip fits inside one afternoon.

Last updated: 2026-08-30

Related article: Base64 Decoding in SQL: A Complete Guide