How to fix error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint?

Question

Why is the error below occurring? I am aware of the db.session.add() method however I want to use raw SQL for learning sake. Shouldn't the UUID be auto-generating? Maybe I am missing something in the postgresql text or have the id not correct in the model?

error

Error: (psycopg2.errors.NotNullViolation) null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null, testuser, email@test.com, pASsWorD, null, 2021-01-10 16:13:23.270353-08).

models.py

from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy import DateTime
from sqlalchemy.sql import func

from .. import db

class Users(db.Model):
    __tablename__ = 'users'

    id = db.Column(
        UUID(as_uuid=True),
        primary_key=True,
        unique=True,
    )
    username = db.Column(
        db.String(120),
        unique=True,
        nullable=False
    )
    email = db.Column(
        db.String(120),
        unique=True,
        nullable=False
    )
    password = db.Column(
        db.String(120),
        nullable=False
    )
    updated_at = db.Column(
        DateTime(timezone=True),
        onupdate=func.now()
    )
    created_at = db.Column(
        DateTime(timezone=True),
        server_default=func.now()
    )

api.py

from flask import Blueprint, request, jsonify
from sqlalchemy import text, exc
from types import SimpleNamespace
from ...models.users import db, Users

bp = Blueprint('api', __name__, url_prefix='/api/v1')

@bp.route('/users', methods=['POST'])
def users():
    if request.method == 'POST':
        try:
            response = dict(request.get_json())
            values = SimpleNamespace(**response)

            if all(response.values()):
                sql_insert_one = text(f"INSERT INTO users(username, email, password) VALUES ('{values.username}', '{values.email}', '{values.password}');")
                db.session.execute(sql_insert_one)
                db.session.commit()
                message = f"successfully added new user: {values.username}"
            else:
                message = f"error adding new user, values submitted: {values}"

            return jsonify(response)
        except Exception as err:
            print(f'Error: {err}') 
            return jsonify(f'"error":"{err}"')

Answer 1

To answer your question "shouldn't the UUID be auto-generating?" No. From the postgresql docs: 'the core database does not include any function for generating UUIDs' ref.

You need to generate the UUID yourself, try uuid.

Or you can use db.Integer instead of db.UUID and SQLAlchemy will make it a sequence and it will auto-generate the id value.

Answer 2

In My case, everything is fine from the code level. But when I cross-checks the table DDL query(Structure), Autoincrement is not available in table level.

CREATE TABLE public.example_table (
id int4 NOT NULL,
table_name varchar(100) NULL,
created_at timestamptz(6) NOT NULL,
created_by_id int4 NULL,
CONSTRAINT "example_table_pkey" PRIMARY KEY (id)

);

It should be

CREATE TABLE public.example_table (
id serial NOT NULL,
table_name varchar(100) NULL,
created_at timestamptz(6) NOT NULL,
created_by_id int4 NULL,
CONSTRAINT "example_table_pkey" PRIMARY KEY (id)

);

Make sure your table structure. It may helpful for someone