[Bug]: Table without sequence fails

[adsharma]

What happened?

The following test case passes with sqlite, but fails with duckdb:

from sqlalchemy import Column, Integer, Sequence, String, create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm.session import Session

Base = declarative_base()


class FakeModel(Base):  # type: ignore
    __tablename__ = "fake"

    id = Column(Integer, primary_key=True)
    name = Column(String)


#eng = create_engine("duckdb:///test.db")
eng = create_engine("sqlite:///test.db")
Base.metadata.create_all(eng)
session = Session(bind=eng)

session.add(FakeModel(id=1, name="Frank"))
session.commit()

frank = session.query(FakeModel).one()

assert frank.name == "Frank"

Here's the error I got:

DuckDB Engine Version

0.14.0

DuckDB Version

1.1.3

SQLAlchemy Version

No response

Relevant log output

sqlalchemy.exc.ProgrammingError: (duckdb.duckdb.CatalogException) Catalog Error: Type with name SERIAL does not exist!
Did you mean "JSON"?
[SQL: 
CREATE TABLE fake (
	id SERIAL NOT NULL, 
	name VARCHAR, 
	PRIMARY KEY (id)
)

### Code of Conduct

- [X] I agree to follow this project's Code of Conduct

[adsharma]

This is the same test case from README.md slightly modified:

-    id = Column(Integer, Sequence("fakemodel_id_sequence"), primary_key=True)
+    id = Column(Integer, primary_key=True)

[Alex-Monahan]

Hello! I believe that the workaround in the Readme might help! https://github.com/Mause/duckdb_engine?tab=readme-ov-file#auto-incrementing-id-columns

[adsharma]

Thanks. One of those workarounds might help me. But I still don't understand why isn't this a problem for sqlite:

2025-01-08 16:02:23,363 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-01-08 16:02:23,364 INFO sqlalchemy.engine.Engine
CREATE TABLE fake (
        id INTEGER NOT NULL,
        name VARCHAR,
        PRIMARY KEY (id)
)


2025-01-08 16:02:23,364 INFO sqlalchemy.engine.Engine [no key 0.00026s] ()
2025-01-08 16:02:23,382 INFO sqlalchemy.engine.Engine COMMIT
2025-01-08 16:02:23,383 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-01-08 16:02:23,385 INFO sqlalchemy.engine.Engine INSERT INTO fake (id, name) VALUES (?, ?)
2025-01-08 16:02:23,385 INFO sqlalchemy.engine.Engine [generated in 0.00021s] (1, 'Frank')
2025-01-08 16:02:23,386 INFO sqlalchemy.engine.Engine COMMIT

Looks like the issue is that duckdb-engine is trying to use: sqlalchemy.dialects.postgresql.base, but then doesn't support all of of PostgreSQL feature set, leading to breakages for a very simple test case.

Does it make sense to create a duckdb dialect?

[adsharma]

After this commit, duckdb works and sqlite breaks. I've switched to the duckdb-engine now.

https://github.com/adsharma/fquery/commit/b2e35ebc2519b73fcd6549df9654dbe03159ee67