Django + MySQL, How to port your web application from SQLite to MySQL

Django’s Object Relational Mapping Pattern

A model is the single, definitive source of data about your data. It contains the essential fields and behaviors of the data you’re storing. Generally, each model maps to a single database table.

we’ve learnt from the official docs that models.py in Django’s folder structure in your web application is the source of data. it contains everything you want to store in your database. we generally define tables, pre and post save methods etc., in models

we want a table with the following requirements, table should contains name, count, and timestamp of the metric. we’ll create a table in the following way.

SimpleMetricTable

Elephant in the Room

After few months we’ve realised that the sqlite database which is the default when creating the project is not scaling. when you have multiple sources which create and modify your DataObjects from your SimpleMetricTable we need to move on.

MySQL to the rescue

django.db.backends.sqlite3' this is how we tell django to use sqlite as backend db. we’ll configure mysql database backend first and then tell django to use django.db.backends.mysql.

the proper configuration would in the following form

this can be done in settings.py

login to your mysql database and create a database like metrics using CREATE DATABASE metrics; we’ll define this database in mysql.conf

polls/configs/mysql.conf

But hey 👋🏻 what about the data I’ve collected so far ? how do we port the old data from sqlite.db to our new shiny MySQL.

Dumping db to JSON in Django

simply run the following command

Note that this should be done before changing the database from sqlite to MySQL in settings.py , after you’ve changed the database from sqlite we can simply run

After a bunch of IntegrityErrors and a couple of google searches using flags like --exclude auth.permission --exclude contenttypes while dumping data we’ve successfully ported our application to MySQL.

The Problem

you’ve patted yourself in the back making a good day at work, started packing up for the day while watching your api output in the log. 👁 your application starts throwing 500 from some of the write requests. you check the code and everything looks fine. some of the requests are fine but those 500 makes you sit again.

Somewhere at the back of your mind there is an itch that this is due to the MySQL change you’ve done today. you start checking what type of data is being stored and written into your database. a thousand google searches follow.

Then you realise the mistake, the SimpleMetricTable you’ve ported from sqlite to MySQL has latin character sets and is not accepting utf-8 in MySQL

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "metrics"
  AND T.table_name = "metrics";

ALTER TABLE metrics CONVERT TO CHARACTER SET utf8;

and you are good to go.

footnotes:
this was actually written a while ago in medium
medium link

Comments

[Dev Prakash]

Thanks a lot