WARNING Donโ€™t try this at home work: just because you can do it, it does not mean you should do it. Also donโ€™t take the post too seriously.

Yesterday I had a doubt: can you use Emoji in SQL? No, I donโ€™t mean storing Emoji in a TEXT column, I mean using Emoji for naming databases, tables, columns. Turns out you can (at least on SQL implementations I tried, SQLite and PostgreSQL 9.6) and it works very well.

It is not magic, Emoji are Unicode characters and you can use Unicode characters inside SQL names.

CREATE TABLE ๐Ÿ‘ค

I decided to re-implement the classic โ€œPublic Libraryโ€ database we all built when learning SQL. Yes, that one database we have practiced on at school, storing users, books, authors and keeping tracks of Books read by users.

First of all letโ€™s create the database, since we are working with a library we will call it ๐Ÿ“š.

CREATE DATABASE ๐Ÿ“š;
USE ๐Ÿ“š; -- \c ๐Ÿ“š in PostgreSQL

Then we begin by creating the users table:

CREATE TABLE ๐Ÿ‘ค(
	๐Ÿ”‘ INTEGER PRIMARY KEY,
	๐Ÿ—ฃ varchar(64), -- name
	๐Ÿ—“ DATE -- date of registration
);

As you can see using Emoji solves the classical naming problem you have when programming: waste no more time on โ€œShould I call the table User or Users?โ€, just use ๐Ÿ‘ค.

Letโ€™s keep build the database by adding the table for the authors:

CREATE TABLE โœ(
	๐Ÿ”‘ INTEGER PRIMARY KEY,
	๐Ÿ—ฃ varchar(64)
);

And a table for the books:

CREATE TABLE ๐Ÿ“•(
	๐Ÿ”‘ INTEGER PRIMARY KEY,
	๐Ÿ’ฌ varchar(64), -- title
	๐Ÿ”– varchar(64), -- genre/tag
	โœ INTEGER REFERENCES โœ(๐Ÿ”‘) -- who wrote the book?
);

Finally letโ€™s create a table for storing which books were read by which user.

We will use the instantly recognizable name ๐Ÿ‘ค๐Ÿ ๐Ÿ“• for this table, which as we all know stands for โ€œUser took the Book homeโ€: it is that easy.

CREATE TABLE ๐Ÿ‘ค๐Ÿ ๐Ÿ“•(
	๐Ÿ‘ค INTEGER REFERENCES ๐Ÿ‘ค(๐Ÿ”‘),
	๐Ÿ“• INTEGER REFERENCES ๐Ÿ“•(๐Ÿ”‘)
);

Populating the database

This is the boring bit, just the classic INSERT INTOs.

INSERT INTO ๐Ÿ‘ค VALUES
	(1, 'Jeff', CURRENT_DATE),
	(2, 'Annie', CURRENT_DATE);

INSERT INTO โœ VALUES
	(1, 'Herman Melville'),
	(2, 'Lewis Carroll');

INSERT INTO ๐Ÿ“• VALUES
	(1, 'Alice in Wonderland', '๐Ÿ”ฎ', 2), -- genre is fantasy
	(2, 'Moby Dick', '๐Ÿ“–', 1), -- genre is novel
	(3, 'Through the Looking-Glass', '๐Ÿ”ฎ', 2); -- genre is fantasy

INSERT INTO ๐Ÿ‘ค๐Ÿ ๐Ÿ“• VALUES
	(1, 1), -- Jeff took home 'Alice in Wonderland'
	(1, 2), -- Jeff took home 'Moby Dick'
	(2, 2);	-- Annie took home 'Moby Dick'

Queries

One common task when dealing with this database is finding out which books were read by which users. The query by itself is simple but using Emoji is even simpler: you visualize concepts not names.

-- get the book taken home by each people
SELECT ๐Ÿ‘ค.๐Ÿ—ฃ AS ๐Ÿ‘ค, ๐Ÿ“•.๐Ÿ’ฌ AS ๐Ÿ“•
FROM ๐Ÿ‘ค JOIN ๐Ÿ‘ค๐Ÿ ๐Ÿ“• ON ๐Ÿ‘ค.๐Ÿ”‘ = ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.๐Ÿ‘ค
	JOIN ๐Ÿ“• ON ๐Ÿ“•.๐Ÿ”‘ = ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.๐Ÿ“•;

which results in:

   ๐Ÿ‘ค  |         ๐Ÿ“•
-------+---------------------
 Jeff  | Alice in Wonderland
 Jeff  | Moby Dick
 Annie | Moby Dick

Extending the concept

We want to extend our ๐Ÿ“• table to also store how long each book is. The naive solution would be to use an INTEGER field to solve the problem, but sometimes numbers are confusing, in these times Emoji are here to help.

To help us simplify the concept of book length we will use a custom TYPE (n.d.r: custom TYPEs are not available in SQLite).

CREATE TYPE ๐Ÿ“„ AS ENUM ('๐Ÿ“„', '๐Ÿ“„๐Ÿ“„', '๐Ÿ“„๐Ÿ“„๐Ÿ“„', '๐Ÿ“„๐Ÿ“„๐Ÿ“„๐Ÿ“„');

The longer the book the more ๐Ÿ“„ it will have.

Letโ€™s update the ๐Ÿ“• table.

ALTER TABLE ๐Ÿ“•
	ADD COLUMN ๐Ÿ”ข๐Ÿ“„ ๐Ÿ“„ DEFAULT '๐Ÿ“„';

INSERT INTO ๐Ÿ“• VALUES
	(4, 'Example Book', '๐Ÿ”ฎ', 1, '๐Ÿ“„๐Ÿ“„๐Ÿ“„');

It is pretty easy to select long books (You may ask โ€œWhat is the definition of long?โ€, the answer is simple, more than 2 ๐Ÿ“„):

SELECT * FROM ๐Ÿ“• WHERE ๐Ÿ“•.๐Ÿ”ข๐Ÿ“„ > '๐Ÿ“„๐Ÿ“„';

Resulting in:

๐Ÿ”‘ |      ๐Ÿ’ฌ       | ๐Ÿ”– | โœ | ๐Ÿ”ข๐Ÿ“„
---+---------------+----+---+-------
 4 | Example Book  | ๐Ÿ“– | 1 | ๐Ÿ“„๐Ÿ“„๐Ÿ“„

Rating

We also want the possibility for the users to rate books.

Ideally we would want to implement a rating scale: for example a number from 1 to 10. But we all know that you canโ€™t really communicate how you feel about a book with just a number, thatโ€™s why we need Emoji.

CREATE TYPE โญ AS ENUM ('๐Ÿคข', 'โ˜น', '๐Ÿ˜', '๐Ÿ™‚', '๐Ÿ˜');

ALTER TABLE ๐Ÿ‘ค๐Ÿ ๐Ÿ“•
	ADD COLUMN โญ โญ;

We then add some fake data:

INSERT INTO ๐Ÿ‘ค๐Ÿ ๐Ÿ“• VALUES
	(1, 4, '๐Ÿ˜'), -- Jeff took home 'Example Book' and rated it ๐Ÿ˜
	(2, 3, '๐Ÿ™‚'), -- Annie took home 'Through the Looking-Glass' and rated it ๐Ÿ™‚
	(2, 4, '๐Ÿ˜'), -- Annie took home 'Example Book' and rated it ๐Ÿ˜
	(2, 4, '๐Ÿคข'); -- Annie took home 'Example Book' (yes again) and rated it ๐Ÿคข

And we can finally get some meaningful info.

-- select the ratings for 'Example Book'
SELECT ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.โญ
FROM ๐Ÿ‘ค๐Ÿ ๐Ÿ“• JOIN ๐Ÿ“• ON ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.๐Ÿ“• = ๐Ÿ“•.๐Ÿ”‘
WHERE ๐Ÿ“•.๐Ÿ’ฌ = 'Example Book';
 โญ
---
 ๐Ÿ˜
 ๐Ÿ˜
 ๐Ÿคข

This next query is useful for the frontend, just display the Emoji instead of some over complicated visualization logic for the rating:

-- select the most common rating for each book
-- MODE() is another function exclusive to PostgreSQL
--- https://wiki.postgresql.org/wiki/Aggregate_Mode
SELECT ๐Ÿ“•.๐Ÿ’ฌ, MODE() WITHIN GROUP (ORDER BY ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.โญ) AS MostCommonRating
FROM ๐Ÿ‘ค๐Ÿ ๐Ÿ“• JOIN ๐Ÿ“• ON ๐Ÿ‘ค๐Ÿ ๐Ÿ“•.๐Ÿ“• = ๐Ÿ“•.๐Ÿ”‘
GROUP BY  ๐Ÿ“•.๐Ÿ”‘;
             ๐Ÿ’ฌ            | mostcommonrating
---------------------------+------------------
 Alice in Wonderland       | NULL
 Moby Dick                 | NULL
 Through the Looking-Glass | ๐Ÿ™‚
 Example Book              | ๐Ÿ˜

Whatโ€™s next?

It would be even cooler if we could write queries in this format, but I donโ€™t see this as a possibility without the cooperation from the major SQL DBMS.

๐Ÿ”Ž * 			-- SELECT *
โžก๏ธ ๐Ÿ“• 			-- FROM Book
โ“ ๐Ÿ“•.๐Ÿ’ฌ = "Moby Dick"  -- WHERE Book.Title = "Moby Dick"

On a serious note, I had the idea to write a comparator function that given two modified/composite Emoji (such as ๐ŸŽ…๐Ÿฟ and ๐ŸŽ…๐Ÿป) would return TRUE if the two Emoji belong to the same unmodified Emoji: I did not want to waste much more time on this so I skipped it.

I think it would be pretty trivial to do, since a modified/composite Emoji is just two Unicode characters one after another, so getting the first character from both Emoji and checking equality should be enough.

If someone implements this please let me know.

Summing up

Please donโ€™t take this post seriously. I had fun writing it and I hope you had fun too reading it. The SQL code is available here: use it wisely.