Issue: Some troubles with SQLite autoincrementing primary key.
Author: JoanSoConfused
State: closed
Created: 2016-01-03 03:41:12

I am new to the peewee and I am using peewee 2.7.4 with Python 3.5.0 and with SQLite and I am very confused about SQLite autoincrementing primary keys on tables.

The peewee documentations says "peewee will automatically add an auto-incrementing integer primary key field named id."

I am using code based on some documentation sample code:

```
#!/usr/bin/env python3.5

from peewee import *

db = SqliteDatabase('people.db')

class Person(Model):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()

    class Meta:
        database = db # This model uses the "people.db" database.

db.connect()

db.create_tables([Person], True)

person = Person.create(name='Joan', birthday='1970-01-01', is_relative=False)
person.save(force_insert=True)
```

So class Person has no primary key field so peewee should add one I think.

When I run the code it dies: "sqlite3.IntegrityError: UNIQUE constraint failed: person.id"

When I inspect the person table I see the id field that peewee added does not have autoincrement!

```
sqlite> .schema person
CREATE TABLE "person" ("id" INTEGER NOT NULL PRIMARY KEY, "name" VARCHAR(255) NOT NULL, "birthday" DATE NOT NULL, "is_relative" SMALLINT NOT NULL);
sqlite> 
```

I do not know what is bug here! Should id column have autoincrement? Or should documentations be clearer about specialistic SQLite behaviors?

Your friend,
Joan


You don't need the `force_insert=True` since you are using an auto-incrementing primary key. That might be the first problem.

> When I inspect the person table I see the id field that peewee added does not have autoincrement!

SQLite and autoincrement is more involved than you might think. There's a reason peewee doesn't specify `AUTOINCREMENT` with SQLite, described here:

https://www.sqlite.org/autoinc.html

Peewee instead will use the unique `rowid` which uses the algorithm MAX(rowid) + 1 to generate new rowids, rather than ensuring they are unique across deletions, etc.


I remove the force_insert=True and it does work! Many thank yous!

I think there are still bugs here even if some of them are just bugs with the documentations.
1. I do not see why using force_insert=True should cause the code to fail with such an unobvious error. The error should be more obvious or force_insert=True should be ok to use all the time. I was doing an insert so being able to force it is something I should be able to do, no? The documentations say "If force_insert=True an INSERT will be issued regardless of whether or not the primary key exists." And I do not see the documentations warning me that I should not use it or not use it if I am using SQLite! So I think the documentations are very misleading and maybe even wrong in this case.
2. The reason for there being no autoincrement for the SQLite would be good note in documentation so we know what going on! It is very confusing when the documentation say "Because we have not specified a primary key, peewee will automatically add an auto-incrementing integer primary key field named id." but there is no warning that SQLite has special handlings!

Right now the documentations are very misleading and the actual behaviors are very surprising. The documentations say it is ok to do things that it is not ok to do apparently, like use force_insert=True. They also do not explain very unexpected behaviors with SQLite like there being no autoincrement.

I would contributes the documentation changes by my written English is not so good! But the documentations should be there to help us not confuse us with things that are misleading or wrong or if they do not say we should beware of surprises that would surprise us.

I think it be very helpful if the documentations are fixed to be clear about how SQLite is different from what would be expected by long time SQLite users and SQLite users who have used other ORM with SQLite that works in ways different from peewee.


1. Why are you calling `force_insert=True`? The docs indicate that parameter is only needed if you have a non-auto-incrementing primary key, or if you explicitly want to set the primary key to the value in the model instance.
2. I don't see how I can document every little quirk of each db engine. Know your database, simple as that.

I disagree that the docs are misleading. peewee has been around for 5 years and nobody's been that confused by the way `force_insert` works.


1. I was a user of force_insert=True because I was creating an object for the first time and when I was looked at the table that peewee itself created with its default id primary key column it was like `"id" INTEGER NOT NULL PRIMARY KEY` with no auto-increment! So I did exactly what you and the documentation said to do: used force_insert=True because when I look at table schema it has a, as you said, "a non-auto-incrementing primary key"! I see not auto-increment in the column definition so it is by definition not non-auto-incrementing primary key! Now I know this wrong apparently in peewee land but the documentations are not clear at all so I ask for clarification.
2. This is not "little quirk". It is peewee specific. In other ORM and hand written code they and we just use SQLite autoincrement directly. The deceiving part is peewee documents saying "Because we have not specified a primary key, peewee will automatically add an auto-incrementing integer primary key field named id." but then on SQLite not actually using the built in autoincrement and instead doing its own peewee specific magic. I'm not saying peewee approach is unjustified but it is unusual and that is why it should be documented so that long time SQLite users who know how SQLite is typically work know that peewee is doing things so differently! Primary keys are not a "little" part of relational databases. Primary keys are very important!

I must state that this my first experiences with peewee and I not impressed! I do not think I being unreasonable for asking for documentations to be clearer. And I probably am not first person to experience this. There probably others but I am first to speak up. Other people probably do what I am thinking of doing and moving to other Python ORM that has documentations that are clear and not misleading like peewee documentations current are. I want to be a user of peewee so bad but peewee is supposed to save me time and me having to deal with peewee-specific quirk only wastes time! I do not understand why you are not willing to add but like 2 sentences to documentations to help people now and in future. peewee work in unusual way and documentation should say so!


Peewee does create an auto-incrementing primary key field named "id" automatically. In order to save CPU and RAM with SQLite, Peewee uses the native `rowid` field rather than specifically requiring globally unique auto-incrementing ids with SQLite. The docs are correct, you just misinterpreted the behavior of SQLite and Peewee in this case. I could care less which ORM you use, peewee would have saved you time if you had just used it like it is used in the docs.