2024-11-07 Building a CRUD Webapp with PostgresSQL and MySQL

Jing-Ying Chen

Jing-Ying Chen

— 3 min read
In the CRUD webapp example I built previously, SQLAlchemy is used to interact with a SQLite database. Here I want to see how to make the app work with both PostgreSQL and MySQL without too much trouble.

PostgreSQL

Searching the web using "SQLAlchemy" and "PostgreSQL", we will find a lot of information. Since I want to have a quick testing with the database, I'll run a PostgreSQL database as a docker container.

$ docker run --name noter_psdb -e POSTGRES_PASSWORD=mypass -p 5432:5432 -d postgres

Unable to find image 'postgres:latest' locally
latest: Pulling from library/postgres
...

$ docker ps
CONTAINER ID   IMAGE                             COMMAND                  CREATED         STATUS                PORTS                                                                                  NAMES
15af98338033   postgres                          "docker-entrypoint.s…"   7 seconds ago   Up 4 seconds          0.0.0.0:5432->5432/tcp, :::5432->5432/tcp

We named the container noter_psdb, so let's shell into the container:

$ docker exec -it noter_psdb bash
root@15af98338033:/#

Then we can use psql to interact with the database:

root@15af98338033:/# su - postgres
postgres@15af98338033:~$ psql
psql (17.0 (Debian 17.0-1.pgdg120+1))
Type "help" for help.

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/var/run/postgresql" at port "5432".
postgres=# \q
root@15af98338033:/#
^D

Based on the information so far, we can proceed by updating our existing program accordingly. For example, our app.py is changed to:

...
app = Flask(__name__)
app.config['SECRET_KEY'] = '123454321'
app.config["SQLALCHEMY_DATABASE_URI"] = "postgresql://postgresql:mypass@192.168.x.x/noterdb"

db.init_app(app)
...

The format for the connection string is based on SQLAlchemy's official site. The code above indicates that the docker host is at 192.168.x.x`, and we will connect to the PostgreSQL server using default port 5432, with the database instance noterdb.

If we attempt to initialize the database tables like before using

python app.py

there will be some errors to fix. First we need to install psycopg2 :

pip install psycopg2

We also need to create a new database noterdb in PostgreSQL:

$ docker exec -it noter_psdb bash
root@15af98338033:/# su - postgres
postgres@15af98338033:~$ createdb noterdb
postgres@15af98338033:~$ ^D

Now we can run python app.py again, and launch the server:

python app.py
flask run --debug

See if you can create new notes now:

MySQL

We may guess that in this case we can simply change the connection string to mysql://root:mypass@192.168.x.x/noterdb") in app.py and follow a similar procedure. That's right. First create a MySQL container:

docker run --name noter_mydb -e MYSQL_ROOT_PASSWORD=mypass -p 3306:3306 -d mysql

We can shell into the container too:

$ docker exec -it noter_mydb bash
bash-5.1# mysql -h localhost -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
...

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ^D

Again change app.py to:

...
app = Flask(__name__)
app.config['SECRET_KEY'] = '123454321'
app.config["SQLALCHEMY_DATABASE_URI"] = "mysql://root:mypass@192.168.x.x/noterdb"

db.init_app(app)
...

Of course trying to initialize the database by running python app.py at this stage will result in errors. So let's fix them. Since my local machine is a Mac, according to here I need to:

brew install mysql-client pkg-config
export PKG_CONFIG_PATH="$(brew --prefix)/opt/mysql-client/lib/pkgconfig"
pip install mysqlclient

You need to follow the instructions based on your OS.

To create the noterdb database in MySQL, shell into the container:

$ docker exec -it noter_mydb bash
bash-5.1# mysql -h localhost -u root -p
...
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE noterdb;
Query OK, 1 row affected (0.16 sec)

mysql> ^D

But when running python app.py, we get one last error:

...
raise exc.CompileError(
sqlalchemy.exc.CompileError: (in table 'note', column 'text'): VARCHAR requires a length on dialect mysql

It seems in our code it is not a good idea to use String() for text with unknown size. So I change the models.py file to use Text instead :

from flask_sqlalchemy import SQLAlchemy

from sqlalchemy import String, Text
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column

class Base(DeclarativeBase):
  pass

db = SQLAlchemy(model_class=Base)

class Note(db.Model):
    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(100))
    text: Mapped[str] = mapped_column(Text)

With the change, the app works again.

Remarks

Note that my goal is to quickly check if it is straightforward to adjust the webapp for different databases using SQLAlchemy. It is confirmed. But in real-world situation when dealing with databases one should be careful about how to setup the user accounts, passwords, among other things. Furthermore, it is certain that the data models will continue to be modified during development (or between different releases), and the existing data in the database need to be "migrated" carefully. This is an important topic when working with databases.

Read more