I want to update my database tables in heroku. However, when I check the status of my tables using heroku pg:info DATABASE, I realize there are no tables created, yet they are defined in models.py as seen at the bottom of the question.
=== DATABASE_URL
Plan: Hobby-dev
Status: Available
Connections: 0/20
PG Version: 12.4
Created: 2020-10-13 23:10 UTC
Data Size: 8.2 MB
Tables: 0 # <---------------------------no tables
Rows: 0/10000 (In compliance)
Fork/Follow: Unsupported
Rollback: Unsupported
Continuous Protection: Off
Add-on: postgresql-closed-00235
This is how my Python Flask app is set up:
config.py
import os
basedir = os.path.abspath(os.path.dirname(__file__))
class Config(object):
SECRET_KEY = os.environ.get('SECRET_KEY') or 'you-will-never-guess'
SQLALCHEMY_DATABASE_URI = os.environ.get('DATABASE_URL') or \
'sqlite:///' + os.path.join(basedir, 'app.db')
SQLALCHEMY_TRACK_MODIFICATIONS = False
MAIL_SERVER='smtp.gmail.com'
MAIL_PORT=587
MAIL_USE_TLS=1
MAIL_USERNAME='my-username'
MAIL_PASSWORD='my-password'
ADMINS=['admin-email']
POSTS_PER_PAGE=10
STRIPE_PUBLISHABLE_KEY='<stripe-publishable-key>'
STRIPE_SECRET_KEY='<stripe-secret-key>'
STRIPE_ENDPOINT_SECRET='<stripe-endpoint-secret>'
LOG_TO_STDOUT = os.environ.get('LOG_TO_STDOUT')
__init__.py
from flask import Flask
from config import Config
from flask_bootstrap import Bootstrap
from flask_sqlalchemy import SQLAlchemy
from flask_migrate import Migrate
from flask_login import LoginManager
import logging
from logging.handlers import SMTPHandler, RotatingFileHandler
import os
from flask_mail import Mail
from flask_moment import Moment
import stripe
app = Flask(__name__)
app.config.from_object(Config)
db = SQLAlchemy(app)
migrate = Migrate(app, db)
login = LoginManager(app)
login.login_view = 'login'
bootstrap = Bootstrap(app)
mail = Mail(app)
moment = Moment(app)
stripe_keys = {
'secret_key': app.config['STRIPE_SECRET_KEY'],
'publishable_key': app.config['STRIPE_PUBLISHABLE_KEY'],
'endpoint_secret': app.config['STRIPE_ENDPOINT_SECRET']
}
stripe.api_key = stripe_keys['secret_key']
if not app.debug:
if app.config['MAIL_SERVER']:
auth = None
if app.config['MAIL_USERNAME'] or app.config['MAIL_PASSWORD']:
auth = (app.config['MAIL_USERNAME'], app.config['MAIL_PASSWORD'])
secure = None
if app.config['MAIL_USE_TLS']:
secure = ()
mailhandler = SMTPHandler(
mailhost=(app.config['MAIL_SERVER'], app.config['MAIL_PORT']),
fromaddr='noreply@' + app.config['MAIL_SERVER'],
toaddrs=app.config['ADMINS'],
subject='Somasoma: Error',
credentials=auth, secure=secure
)
mailhandler.setLevel(logging.ERROR)
app.logger.addHandler(mailhandler)
if app.config['LOG_TO_STDOUT']:
stream_handler = logging.StreamHandler()
stream_handler.setLevel(logging.INFO)
app.logger.addHandler(stream_handler)
else:
if not os.path.exists('logs'):
os.mkdir('logs')
file_handler = RotatingFileHandler('logs/somasoma.log', maxBytes=10240, backupCount=10)
file_handler.setFormatter(logging.Formatter(
'%(asctime)s %(levelname)s: %(message)s [in %(pathname)s:%(lineno)d] '
))
file_handler.setLevel(logging.INFO)
app.logger.addHandler(file_handler)
app.logger.setLevel(logging.INFO)
app.logger.info('Somasoma Blog')
from app import routes, models, errors
blog_app.py
from app import app, db
from app.models import User, Post
@app.shell_context_processor
def make_shell_context():
return {'db':db, 'User': User, 'Post': Post}
Procfile
web: flask db upgrade; gunicorn blog_app:app
Ideally, to update my database, I have run heroku run flask db upgrade command. This instead throws me this error:
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.DuplicateTable) relation "user" already exists
[SQL:
CREATE TABLE "user" (
id SERIAL NOT NULL,
username VARCHAR(64),
email VARCHAR(120),
password_hash VARCHAR(128),
about_me VARCHAR(140),
last_seen TIMESTAMP WITHOUT TIME ZONE,
PRIMARY KEY (id)
)
The thing is when I try to log into my deployed up on heroku, I cannot sign up or sign in because of non-existent 'user' as seen in the stdout heroku log:
psycopg2.errors.UndefinedTable: relation "user" does not exist
2020-10-13T23:27:13.018103+00:00 app[web.1]: LINE 2: FROM "user"
SQL: SELECT "user".id AS user_id, "user".username AS user_username, "user".email AS user_email, "user".password_hash AS user_password_hash
2020-10-13T23:27:13.018113+00:00 app[web.1]: FROM "user"
2020-10-13T23:27:13.018114+00:00 app[web.1]: WHERE "user".username = %(username_1)s
2020-10-13T23:27:13.018114+00:00 app[web.1]: LIMIT %(param_1)s]
2020-10-13T23:27:13.018114+00:00 app[web.1]: [parameters: {'username_1': 'harry', 'param_1': 1}]
2020-10-13T23:27:13.018114+00:00 app[web.1]: (Background on this error at: http://sqlalche.me/e/f405)
This error is a direct result of the database tables being empty. I have looked at several example solutions such as this and this but I am not quite able to solve the database issue I am facing.
Personally, I thought that the inclusion of the argument db while instantiating the migrate variable in __init__.py would solve that. I mean:
Instead of:
migrate = Migrate(app)
Then this should fix it:
migrate = Migrate(app, db)
However, with that addition, I am still not able to fix this error.
What could I be missing out on? Any lead or direction is much appreciated.
Edited:
I have a user and post model defined for my database:
models.py
class User(UserMixin, db.Model):
id = db.Column(db.Integer, primary_key = True)
username = db.Column(db.String(64), index = True, unique = True)
email = db.Column(db.String(120), index = True, unique = True)
password_hash = db.Column(db.String(128))
posts = db.relationship('Post', backref = 'author', lazy = 'dynamic')
def __repr__(self):
return '<User {}>'.format(self.username)
class Post(db.Model):
id = db.Column(db.Integer, primary_key = True)
body = db.Column(db.String(140))
timestamp = db.Column(db.DateTime, index = True, default = datetime.utcnow)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'))
def __repr__(self):
return 'Post <>'.format(self.body)
@login.user_loader
def load_user(id):
return User.query.get(int(id))
