The lack of support for JSON field in Django ORM + MariaDB is one of the reasons we want to migrate the SQuaSH API to Flask + SQLAlchemy.
See issue:
https://github.com/adamchainz/django-mysql/issues/342
The result of this investigation should demonstrate that we can use or not JSON data type in SQLAlchemy + MariaDB.
CONCLUSION:
SQLAlchmey 1.2 doesn't support JSON data type on MariaDB 10.3+ yet.
http://docs.sqlalchemy.org/en/latest/dialects/mysql.html#sqlalchemy.dialects.mysql.JSON
The code snippet for in models.py to create a JSON field looks like
from sqlalchemy.dialects.mysql import JSON
|
|
class MeasurementModel(db.Model):
|
__tablename__ = 'measurements'
|
|
id = db.Column(db.Integer, primary_key=True
)
|
value = db.Column(db.Float())
|
data = db.Column(JSON())
|
|
it creates a field with longtext type instead, see below.
So basically SQLAlchemy and Djando ORM are in the same situation right now with
respect to JSON support in MariaDB.
Server version: 5.5.5-10.3.2-MariaDB-10.3.2+maria~jessie mariadb.org binary distribution
|
|
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
|
|
Oracle is a registered trademark of Oracle Corporation and/or its
|
affiliates. Other names may be trademarks of their respective
|
owners.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
mysql> show columns in measurements;
|
+-----------+-------------+------+-----+---------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-----------+-------------+------+-----+---------+----------------+
|
| id | int(11) | NO | PRI | NULL | auto_increment |
|
| value | float | YES | | NULL | |
|
| data | longtext | YES | | NULL | |
|
| metric_id | int(11) | YES | MUL | NULL | |
|
+-----------+-------------+------+-----+---------+----------------+
|
5 rows in set (0.05 sec)
|
|
|
On MySQL 5.7 we benefit from the JSON support, and from the fact that we can use CloudSQL in our GKE deployment. So this seems the way to go for now.
mysql: [Warning] Using a password on the command line interface can be insecure.
|
Welcome to the MySQL monitor. Commands end with ; or \g.
|
Your MySQL connection id is 186
|
Server version: 5.7.14-google-log (Google)
|
|
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
|
|
Oracle is a registered trademark of Oracle Corporation and/or its
|
affiliates. Other names may be trademarks of their respective
|
owners.
|
|
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
|
|
mysql> use qadb
|
Reading table information for completion of table and column names
|
You can turn off this feature to get a quicker startup with -A
|
|
Database changed
|
mysql> show columns in measurements;
|
+-----------+-------------+------+-----+---------+----------------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-----------+-------------+------+-----+---------+----------------+
|
| id | int(11) | NO | PRI | NULL | auto_increment |
|
| value | float | YES | | NULL | |
|
| data | json | YES | | NULL | |
|
| metric_id | int(11) | YES | MUL | NULL | |
|
+-----------+-------------+------+-----+---------+----------------+
|
5 rows in set (0.05 sec)
|
|
|
Updated list of packages and flask extensions:
flask-restful
flask-sqlalchemy
flask-migrate # installs alembic
marshmallow
marshmallow-sqlalchemy
flask-marshmallow
flast-jwt
pymysql