Introduction Migrations

 Introduction Migrations

Introduction Migrations



What are migrations?

- A migration is a file that keeps track of changes to our database schema (structure of our database).

- Offers version control on our schema.

- Migrations deal with how we manage modifications to our data schema, over time.


Benefits of migrations in our app:

- Mistakes to our database schema are very expensive to make. 

- The entire app can go down, so we want to quickly roll back changes, and test changes before we make them.

- Encapsulate a set of changes to our database schema, made over time.

- Are uniquely named.

- Are usually stored as local files in our project repo, e.g. a migrations/ folder.

- There should be a one-to-one mapping between the changes made to our database, and the migration files that exist in our migrations/ folder.

- Our migrations files set up the tables for our database.

- All changes made to our db should exist physically as part of migration files in our repository.



Upgrades and rollbacks:

- Migrations stack together in order to form the latest version of our database schema.

- We can upgrade our database schema by applying migrations. 

- We can roll back our database schema to a former version by reverting the migrations that we applied. 


Migration command-line scripts:

There are generally 3 scripts needed, for:

- migrate: creating a migration script template to fill out; generating a migration file based on changes to be made

- upgrade: applying migrations that hadn't been applied yet ("upgrading" our database)

- downgrade: rolling back applied migrations that were problematic ("downgrading" our database)


Why use migrations?

Without migrations:

- We do heavy-handed work, creating and recreating the same tables in our database even for minor changes.

- We can lose existing data in older tables we dropped.


With migrations:

- Auto-detects changes from the old version & new version of the SQLAlchemy models. 

- Creates a migration script that resolves differences between the old & new versions. 

- Gives fine-grain control to change existing tables.


This is much better, because:

- We can keep existing schema structures, only modifying what needs to be modified. 

- We can keep existing data.

- We isolate units of change in migration scripts that we can roll back to a “safe” db state. 


Flask-Migrate-cheat-sheet


Flask-Migrate commands:

pip install Flask-Migrate
flask db --help


flask db init 

- Create initial migrations directory structure. 


flask db migrate 

- Detects the model changes to be made and creates a migration file with an upgrade and downgrade logic set up. 

(replaces use of db.create_all()).


flask db upgrade 

- Runs the upgrade command in the migration file, to apply the migration. 


flask db downgrade

- Runs the downgrade command in the migration file, to roll back in migration.  



Comments

Post a Comment

Popular posts from this blog

SQLAlchemy Model.query cheat-sheet

Image
SQLAlchemy  Model  query-cheat-sheet Query Methods Here are some useful query methods to get to know.  - Select records all() MyModel.query.all() Fetches all records from a table. Returns list of objects. first() MyModel.query.first() Fetches just the first result. Returns object or None. - Filtering filter_by() MyModel.query.filter_by(attr='some value') Similar to SELECT * from ... WHERE filter() Examples: MyModel.query.filter(MyOtherModel.some_attr='some value') OrderItem.query.filter(Product.id=3) Similar to filter_by , but instead, you specify attributes on a given Model.-- -- equals: query.filter(User.name == 'ed') -- not equals: query.filter(User.name != 'ed') LIKE : query.filter(User.name.like('%ed%')) ILIKE (case-insensitive LIKE): query.filter(User.name.ilike('%ed%')) IN: query.filter(User.name.in_(['ed', 'wendy', 'jack'])) NOT IN: query.filter(~User.name.in_(['ed', 'wendy', 'jack...
Read more

What is a DBAPI ?

Image
 DB-API DB-API definition: - We will sometimes want to interact with our database and use its results in a specific programming language.   - To build web applications or data pipelines in a specific language like (Ruby, Python, Javascript, etc.). - That's where DBAPIs come in. A DB-API:  - A DB-API provides a standard for one programming language (like python) to talk to a relational database server. - Is a low-level library for writing SQL statements that connect to a database.  - Also known as database adapters. - Different DBAPIs exist for every server framework or language + database system. - Database adapters define a standard for using a database (with SQL) and using the results of database queries as input data in the given language. -- Turn a selected (SELECT * from some_table;) list of rows into an array of objects in Javascript for say a NodeJS adapter; or a list of tuples in Python for a Python adapter. Examples across languages and server frameworks: - ...
Read more

Connections and Sessions in TCP/IP

Image
 Connections and Sessions in TCP/IP TCP/IP is a connection-based protocol: - To start any communication a connection must be arranged. - Open a connection to start communications. - Close a connection to end communications.  - Meaning all communications between parties are arranged over a connection. - A connection is established before any data transmission begins. - Over TCP/IP , we'll always need to establish a connection between clients and servers in order to enable communications.  Moreover: Deliveries over the connection are error-checked, If packets arrive damaged or lost, then they are resent (known as retransmission). It's really important that we continually monitor what connections are opened and closed. between a recipient and sender because the more connection that we have open The more performance-based issues we may run into.  We will always need to establish a connection from a client application to a Postgres server Or MySQL server over TCP/IP in o...
Read more