Issue: Expose serialization options in sqlite_ext.JSONField (or use better defaults)
Author: zmwangx
State: closed
Created: 2019-02-05 07:52:52

Currently, `sqlite_ext.py` seems to use `json.dumps` without any options, which means the defaults `separators=(', ', ': ')` and `ensure_ascii=True` are assumed. These are a waste of space, and `ensure_ascii` also reduces human readability of non-ASCII text without real world benefit (since SQLite is Unicode-safe).

Compare the result of SQLite JSON1 extension's builtin `json` function:

```sql
INSERT INTO entry(doc) VALUES(json('{ "lang":  "中文" }'));
```

=> 

```sql
INSERT INTO entry VALUES(1,'{"lang":"中文"}');
```

and peewee:

```py
Entry.insert(doc=dict(lang='中文'))
```
=>

```sql
INSERT INTO entry VALUES(1,'{"lang": "\u4e2d\u6587"}');
```

It would be nice if serialization options are exposed, or just use `separators=(',', ':')` and `ensure_ascii=False`.

The `sqlite_ext.JSONField` relies on SQLite's `json1` extension. We convert Python data-types to JSON strings, but it's up to sqlite to parse, validate and then store the resulting JSON data. Sqlite efficiently stores this, so there's nothing to worry about:

```
sqlite> create table foo (data json);
sqlite> insert into foo (data) values (json('{ "foo": "bar" }'));
sqlite> select * from foo;
{"foo":"bar"}
```

Regarding the handling of unicode, I've added a test-case that shows that the library is doing the right thing:

eec3823e3d00a5ec52d0ff059f23a82a02512f36

Or,

```python
In [1]: from playhouse.sqlite_ext import *

In [2]: db = SqliteDatabase(':memory:')

In [3]: class KeyData(Model):
   ...:     key = TextField()
   ...:     data = JSONField()
   ...:     class Meta:
   ...:         database = db
   ...:         

In [4]: KeyData.create_table()

In [5]: unicode_str = '中文'

In [6]: data = {'foo': unicode_str}

In [7]: KeyData.create(key='k1', data=data)
Out[7]: <KeyData: 1>

In [8]: KeyData.get(KeyData.key == 'k1').data
Out[8]: {'foo': '中文'}
```

Sorry, either I wasn't clear or I'm doing something wrong. Adapting your example a little bit:

```py
#!/usr/bin/env python3

import logging

from playhouse.sqlite_ext import *

logger = logging.getLogger("peewee")
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

db = SqliteDatabase("/tmp/json1.db")


class KeyData(Model):
    key = TextField()
    data = JSONField()

    class Meta:
        database = db


KeyData.create_table()
unicode_str = "中文"
data = {"foo": unicode_str}
KeyData.create(key="k1", data=data)
```

Execution output:

```sql
('CREATE TABLE IF NOT EXISTS "keydata" ("id" INTEGER NOT NULL PRIMARY KEY, "key" TEXT NOT NULL, "data" JSON NOT NULL)', [])
('INSERT INTO "keydata" ("key", "data") VALUES (?, ?)', ['k1', '{"foo": "\\u4e2d\\u6587"}'])
```

Note that the value of `data` does NOT go through the `json` function. 

Now, dumping the database:

```sql
$ sqlite3 /tmp/json1.db .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "keydata" ("id" INTEGER NOT NULL PRIMARY KEY, "key" TEXT NOT NULL, "data" JSON NOT NULL);
INSERT INTO keydata VALUES(1,'k1','{"foo": "\u4e2d\u6587"}');
COMMIT;
```

Your raw SQL example is exactly what I have in mind (as what should be done)

```sql
insert into foo (data) values (json('{ "foo": "bar" }'));
```

emphasis on `json(...)`.

> the library is doing the right thing:

Definitely, there's no bug here, it's just that JSON is not efficiently stored.

Thanks for the clarification, I'll look into it some more.

By the way, while SQLite's `json` compresses whitespaces, it does preserve Unicode escape sequences, so `ensure_ascii=True` is still a problem.

I'm somewhat confused about `ensure_ascii`. I made a simple test using the interactive shell.

I create "bstr" (a bytestring) and "ustr" (unicode) -- bstr contains the utf8 representation of the two characters in your example. ustr, being unicode, consists of the unicode code-points corresponding to those characters.

Here is the behavior with Python 2. The unicode version results in the unicode code-point escapes *regardless* of `ensure_ascii`. It is only by serializing the UTF8-encoded `bytes` value with `ensure_ascii=False` that we get the equivalent of the "raw" characters:

```python
In [2]: bstr = '中文'

In [3]: bstr
Out[3]: '\xe4\xb8\xad\xe6\x96\x87'

In [4]: ustr = bstr.decode('utf8')

In [5]: print ustr
中文

In [6]: json.dumps(bstr)
Out[6]: '"\\u4e2d\\u6587"'

In [7]: json.dumps(bstr, ensure_ascii=False)
Out[7]: '"\xe4\xb8\xad\xe6\x96\x87"'

In [8]: json.dumps(ustr)
Out[8]: '"\\u4e2d\\u6587"'

In [9]: json.dumps(ustr, ensure_ascii=False)
Out[9]: u'"\u4e2d\u6587"'
```

Here is the behavior with Python 3 -- note that we cannot serialize bytestrings so we'll be dealing with the unicode only:

```python
In [1]: import json

In [2]: ustr = '中文'

In [3]: json.dumps(ustr)
Out[3]: '"\\u4e2d\\u6587"'

In [4]: json.dumps(ustr, ensure_ascii=False)
Out[4]: '"中文"'
```

-------------------------------

Under-the-hood, we're still talking about UTF8-encoded bytestrings. So in Python 2, the only way to avoid storing the code-points is to serialize the value as an already-encoded bytestring, because when using unicode we get the escapes regardless.

Anyways...the end-result is potentially 2 or 3 extra bytes for these code-points. Is this the crux of the issue? The extra bytes?

See above patch for changes. You can now specify a custom json_dumps or json_loads when declaring your JSONField.

Cool, I'll give it a spin shortly. Regarding this:

> Here is the behavior with Python 2. The unicode version results in the unicode code-point escapes _regardless_ of `ensure_ascii`. It is only by serializing the UTF8-encoded `bytes` value with `ensure_ascii=False` that we get the equivalent of the "raw" characters:

Actually no, you seem to be tricked by py27's confusing return types and the repr representations that look similar...

> ```py
> In [9]: json.dumps(ustr, ensure_ascii=False)
> Out[9]: u'"\u4e2d\u6587"'
> ```

These are the unescaped Unicode code points U+4E2D and U+6587 (equivalent to the Python 3 `ensure_ascii=False` version), escaped for `repr`. In py27, `json.dumps` on a `str` gives you a `str`, and `json.dumps` on a `unicode` gives you a `unicode`.

> Anyways...the end-result is potentially 2 or 3 extra bytes for these code-points. Is this the crux of the issue? The extra bytes?

That's one problem. The other problem is when you look at JSON values directly without `json_extract` (e.g. in a command line shell, or in a GUI like sqlitebrowser), escaped codepoints are not readable.

> In py27, `json.dumps` on a `str` gives you a `str`, and `json.dumps` on a `unicode` gives you a `unicode`.

Actually that's also false... It seems you only get a `unicode` when `ensure_ascii=False`. Anyway, very confusing. Glad I went all in on Python 3 years ago...

Ooof, thanks for pointing that out, yeah I moved over to Py3 as well but I still continue to support Py2 for my libraries, at least until Py2 is officially dead.

Execuse me. 
Postgresql extension seems have same problem.
The JSONField() in playhouse.postgres_ext did not have json_dumps and json_loads options.

Is it possible to add this feature to postgres same as sqlite? 

You can provide a custom json serialization handler to the Postgres JSONField already:

https://github.com/coleifer/peewee/blob/55ef182840f869f63ea973421e3a572a117236c8/playhouse/postgres_ext.py#L293-L296

WOW, thanks for the fast reply. I didn't realize that.
BinaryJSONField seems work fine, even without custom dumps function.

If for some reason someone ends up here for the `Your version of psycopg2 does not support JSON.` exception then try to run `pip install --upgrade psycopg2` and try again. Hope this helps someone even tho it looks like a dumb or obvious solution.