Any tips for using Drizzle without the schema declared via Drizzle syntax?
We are using Atlas for DB migrations, and therefore don't have the schema declared via Drizzle. Introspecting the DB has not been too accurate unfortunately.
Some issues:
BOOLEAN inferred correctly as numeric, could this be inferred as integer mode: "boolean"? (I think Prisma does this)
Named foreign keys getting dropped. (not too important?)
DEFAULT native => default("sql(native)").
UNIQUE constraints getting dropped (not too important?)
15 Replies
Sorry to ping you @Andrew Sherman but I have no idea for this 😅
For more context, our BE is in Go and FE in Typescript. Drizzle works great when schema is declared in TS but I'm not sure how realistic it would be to maintain this in the long run, manually updating the drizzle schema
Oh, your are using Drizzle like a type generator?
Well we are using Drizzle in our Next.js app, to perform queries etc. But the actual DB is declared via an Atlas
hcl schema. We interact with the DB through the Next.js app (route handlers, server components etc), but we also have another Go backend depending on the same DBI see. The easiest way would be to use Drizzle has the main source of truth (maintaining the schema and applying migrations).
Or we need a thing like drizzle prisma but for atlas (https://orm.drizzle.team/docs/prisma).
External Integrations | Atlas | Manage your database schema as code
Atlas allows loading the desired state of the database schema from external programs or ORMs, regardless of the
Writing the schema w/ Drizzle is not realistic unfortunately, we have checks + triggers etc.
Fair 😄
So we can forget about the external loader (If I understand it correctly it enables Atlas to load a schema maintained by an other orm).
I am not expert enough to know what would be the best solution here.
If I summarize: this is mostly a
introspect issue but you don't plan to use Drizzle to manage migrations.
For your initial question, I don't think it is an issue if some things are "badly" introspected since you will not produce and apply migrations.
BOOLEAN inferred correctly as numeric, could this be inferred as integer mode: "boolean"? (I think Prisma does this)
integer('id', { mode: 'boolean' }) is possible for SQLite (but you have to manually edit your schema if it is not what introspect gives you)
Named foreign keys getting dropped. (not too important?)I think it's ok (not used)
DEFAULT native => default("sql(native)")Seems "ok" because
default is used to generate the default value for a column (not used at runtime) . Only $default() and $defaultFn() are used at runtime
UNIQUE constraints getting dropped (not too important?)Ok too
The main issue so far is working with
BOOLEAN it gets introspected to numeric() which can accept any string. This is correct, but I'm not sure if it's desired considering Drizzle's mode: "boolean". IIRC pushing with mode: boolean results in INTEGER, but introspecting BOOLEAN results in numeric()
Maybe best to just stick with INTEGERYou are in SQLite right? https://orm.drizzle.team/docs/column-types/sqlite#boolean
Boolean are only integer in SQLite.
numeric is a Postgres type 🧐Drizzle ORM - SQLite column types
Drizzle ORM is a lightweight and performant TypeScript ORM with developer experience in mind.
Yeah SQLite, but in SQLite
BOOLEAN actually has affinity of numeric, it's weird.
https://www.sqlite.org/datatype3.html#affinity_name_examplesoh right, it is not in the drizzle doc 😦
Hey @lordbinbash!
All of those are bugs, and we will definitely fix them. So, the most important thing for you is the mode: boolean, right?
I would need to check if we can somehow retrieve from SQLite whether this column is actually boolean and not numeric. If we can, we can definitely improve our introspection.
For now, you would need to manually add this mode, which is not ideal, of course, but it's a workaround
Yeah, thanks a lot, SQLite datatypes are a bit weird so not sure how possible this would even be 🤔
Boolean is not really a real thing - https://www.sqlite.org/datatype3.html#boolean_datatype
yeah, I know, that is a reason behind us choosing such introspect modes
to make sure it will work for any cases and then you can change it if needed