v1

Question

v1

fdarian opened this issue a year ago · 24 comments

Answer 1 · 2023-12-20T18:15:05.000Z

Following this 👀 Thanks for your work.

Answer 2 · 2023-12-26T09:00:18.000Z

Amazing work! Looking forward to the SQLite support.

Answer 3 · 2023-12-31T06:47:28.000Z

@octalpixel I've just added the SQLite adapter on v0.5.0, feel free to check out and let me know if there's a problem

Answer 4 · 2023-12-31T18:21:13.000Z

@octalpixel I've just added the SQLite adapter on v0.5.0, feel free to check out and let me know if there's a problem

Thanks for the ping. Will check this out later today.

Answer 5 · 2024-01-01T19:41:39.000Z

@octalpixel I've just added the SQLite adapter on v0.5.0, feel free to check out and let me know if there's a problem

Thanks for the ping. Will check this out later today.

Hey tried this now on a quick sample project with two tables and it works well! Thanks for this! Will do a bit more a bigger schema.

Answer 6 · 2024-01-02T04:07:11.000Z

I know this a bit unrela

@octalpixel I've just added the SQLite adapter on v0.5.0, feel free to check out and let me know if there's a problem

Thanks for the ping. Will check this out later today.

Hey tried this now on a quick sample project with two tables and it works well! Thanks for this! Will do a bit more a bigger schema.

Ik this a bit unrelated but I tried this on React Native Project, and there is require cycle.

This because categories and transaction import each other for relationships.

How about an optional flag to define all the relationship in another file ?

Answer 7 · 2024-01-02T04:31:36.000Z

I know this a bit unrela

@octalpixel I've just added the SQLite adapter on v0.5.0, feel free to check out and let me know if there's a problem

Thanks for the ping. Will check this out later today.

Hey tried this now on a quick sample project with two tables and it works well! Thanks for this! Will do a bit more a bigger schema.

Ik this a bit unrelated but I tried this on React Native Project, and there is require cycle.

This because categories and transaction import each other for relationships.

How about an optional flag to define all the relationship in another file ?

Thanks for noticing! Just released the patch v0.5.2 where you can add relationalQuery = false

generator drizzle {
  provider        = "prisma-generator-drizzle"
  relationalQuery = false
}

A proper fix would be to simply separate the definition to a separate file later

Answer 8 · 2024-01-02T04:43:24.000Z

I know this a bit unrela

@octalpixel I've just added the SQLite adapter on v0.5.0, feel free to check out and let me know if there's a problem

Thanks for the ping. Will check this out later today.

Hey tried this now on a quick sample project with two tables and it works well! Thanks for this! Will do a bit more a bigger schema.

Ik this a bit unrelated but I tried this on React Native Project, and there is require cycle.
This because categories and transaction import each other for relationships.
How about an optional flag to define all the relationship in another file ?

Thanks for noticing! Just released the patch v0.5.2 where you can add relationalQuery = false
generator drizzle {
  provider        = "prisma-generator-drizzle"
  relationalQuery = false
}
A proper fix would be to simply separate the definition to a separate file later

Thanks! I believe this is one way of solving it. Also just looking at your patch at the moment and train of thought.Maybe not the best, but here is a thought dump

Have a flag to output everything to one file. (Fixes the require cycle for React Native projects)
Have a flag to output relationship to another file
Flag to only generate relationships (This can actually be a good starting point to learn about how to make relationships with Drizzle as well)

Answer 9 · 2024-01-03T04:33:51.000Z

It would be great if this lib also properly handled @ignore and @@ignore. Basically don't pull these columns and/or tables into the generated schema(s).

Answer 10 · 2024-01-03T04:35:13.000Z

It would be great if this lib also properly handled @ignore and @@ignore. Basically don't pull these columns and/or tables into the generated schema(s).

Yeah that seems simple.

Answer 11 · 2024-01-03T05:42:23.000Z

So just wanted to report an experiment that I did. After trying out your lib, an idea sparked in my head. WHAT IF we introspect the database using drizzlekit to actually pull in the schema. I tried this with a local sqlite db, and db pushed the schema to it using prisma and introspected it with drizzlekit. This is what I got.

PS: Disregard the table names being title case :D

From introspect

export const transaction = sqliteTable("Transaction", {
	id: integer("id").primaryKey({ autoIncrement: true }).notNull(),
	amount: real("amount").notNull(),
	date: numeric("date").notNull(),
	description: text("description"),
	createdAt: numeric("createdAt").default(sql`(CURRENT_TIMESTAMP)`).notNull(),
	updatedAt: numeric("updatedAt").notNull(),
	transCategoryId: integer("transCategoryId").references(() => transCategory.id, { onDelete: "set null", onUpdate: "cascade" } ),
	categoryId: integer("categoryId").references(() => category.id, { onDelete: "set null", onUpdate: "cascade" } ),
});

export const transCategory = sqliteTable("TransCategory", {
	id: integer("id").primaryKey({ autoIncrement: true }).notNull(),
	name: text("name").notNull(),
},
(table) => {
	return {
		nameKey: uniqueIndex("TransCategory_name_key").on(table.name),
	}
});

From the generator

export const transactions = sqliteTable('Transaction', {
  id: integer('id', { mode: 'number' }).primaryKey(),
  amount: real('amount').notNull(),
  date: integer('date', { mode: 'timestamp' }).notNull(),
  description: text('description'),
  createdAt: integer('createdAt', { mode: 'timestamp' }).notNull(),
  updatedAt: integer('updatedAt', { mode: 'timestamp' }).notNull(),
  transCategoryId: integer('transCategoryId', { mode: 'number' }),
  categoryId: integer('categoryId', { mode: 'number' }),
});

export const transactionsRelations = relations(transactions, (helpers) => ({
  TransCategory: helpers.one(transCategories, {
    relationName: 'TransCategoryToTransaction',
    fields: [transactions.transCategoryId],
    references: [transCategories.id],
  }),
  Category: helpers.one(categories, {
    relationName: 'CategoryToTransaction',
    fields: [transactions.categoryId],
    references: [categories.id],
  }),
}));

Off scope for you: One thing I noticed that prisma doesn't play well with enum of sqlite (Probably something you can look into). If i remember correctly Sqlite enums are done using CHECKS in sql so in one way doesnt make sense ?

Also drizzle-team/drizzle-orm#636 seems to be solved with incorrect relationships. So I guess in theory the introspect should work ?

Answer 12 · 2024-01-03T06:01:07.000Z

So just wanted to report an experiment that I did. After trying out your lib, an idea sparked in my head. WHAT IF we introspect the database using drizzlekit to actually pull in the schema. I tried this with a local sqlite db, and db pushed the schema to it using prisma and introspected it with drizzlekit. This is what I got.

PS: Disregard the table names being title case :D

From introspect
export const transaction = sqliteTable("Transaction", {
	id: integer("id").primaryKey({ autoIncrement: true }).notNull(),
	amount: real("amount").notNull(),
	date: numeric("date").notNull(),
	description: text("description"),
	createdAt: numeric("createdAt").default(sql`(CURRENT_TIMESTAMP)`).notNull(),
	updatedAt: numeric("updatedAt").notNull(),
	transCategoryId: integer("transCategoryId").references(() => transCategory.id, { onDelete: "set null", onUpdate: "cascade" } ),
	categoryId: integer("categoryId").references(() => category.id, { onDelete: "set null", onUpdate: "cascade" } ),
});

export const transCategory = sqliteTable("TransCategory", {
	id: integer("id").primaryKey({ autoIncrement: true }).notNull(),
	name: text("name").notNull(),
},
(table) => {
	return {
		nameKey: uniqueIndex("TransCategory_name_key").on(table.name),
	}
});
From the generator
export const transactions = sqliteTable('Transaction', {
  id: integer('id', { mode: 'number' }).primaryKey(),
  amount: real('amount').notNull(),
  date: integer('date', { mode: 'timestamp' }).notNull(),
  description: text('description'),
  createdAt: integer('createdAt', { mode: 'timestamp' }).notNull(),
  updatedAt: integer('updatedAt', { mode: 'timestamp' }).notNull(),
  transCategoryId: integer('transCategoryId', { mode: 'number' }),
  categoryId: integer('categoryId', { mode: 'number' }),
});

export const transactionsRelations = relations(transactions, (helpers) => ({
  TransCategory: helpers.one(transCategories, {
    relationName: 'TransCategoryToTransaction',
    fields: [transactions.transCategoryId],
    references: [transCategories.id],
  }),
  Category: helpers.one(categories, {
    relationName: 'CategoryToTransaction',
    fields: [transactions.categoryId],
    references: [categories.id],
  }),
}));
Off scope for you: One thing I noticed that prisma doesn't play well with enum of sqlite (Probably something you can look into). If i remember correctly Sqlite enums are done using CHECKS in sql so in one way doesnt make sense ?

Also drizzle-team/drizzle-orm#636 seems to be solved with incorrect relationships. So I guess in theory the introspect should work ?

That's actually also my initial hack a while ago, and it is a good one! (depends on the stability of db pull tho)

However the downside for this solution is that it requires an active database connection, meaning it requires another setup and it became more complex when managing with multiple branches. Therefore since Prisma already have their DMMF helper, as well as other packages like the nexus, zod, typegraphql, pothos, etc.. uses this technique to map Prisma with their features, it'd be safer use the proven pipeline like the others.

Off scope for you: One thing I noticed that prisma doesn't play well with enum of sqlite (Probably something you can look into). If i remember correctly Sqlite enums are done using CHECKS in sql so in one way doesnt make sense ?

Fyi I'm not really familiar for this. Currently the goal of this library is to provide all features that Prisma has — excluding unsupported features like the ENUMs in sqlite. Once Prisma has the support, we could reliably map the feature to drizzle.

So if you'd like to use one that drizzle has already supported, I think you could generate and commit the result. However it requires you to maintain the modified schema everytime the script generates the schema, I think this could be a feature that we could look in the future.

Also I think this is where the db pull solution comes in, it's more of a subsidiary rather than complementary.

Answer 13 · 2024-01-03T06:18:37.000Z

So just wanted to report an experiment that I did. After trying out your lib, an idea sparked in my head. WHAT IF we introspect the database using drizzlekit to actually pull in the schema. I tried this with a local sqlite db, and db pushed the schema to it using prisma and introspected it with drizzlekit. This is what I got.
PS: Disregard the table names being title case :D
From introspect
export const transaction = sqliteTable("Transaction", {
	id: integer("id").primaryKey({ autoIncrement: true }).notNull(),
	amount: real("amount").notNull(),
	date: numeric("date").notNull(),
	description: text("description"),
	createdAt: numeric("createdAt").default(sql`(CURRENT_TIMESTAMP)`).notNull(),
	updatedAt: numeric("updatedAt").notNull(),
	transCategoryId: integer("transCategoryId").references(() => transCategory.id, { onDelete: "set null", onUpdate: "cascade" } ),
	categoryId: integer("categoryId").references(() => category.id, { onDelete: "set null", onUpdate: "cascade" } ),
});

export const transCategory = sqliteTable("TransCategory", {
	id: integer("id").primaryKey({ autoIncrement: true }).notNull(),
	name: text("name").notNull(),
},
(table) => {
	return {
		nameKey: uniqueIndex("TransCategory_name_key").on(table.name),
	}
});
From the generator
export const transactions = sqliteTable('Transaction', {
  id: integer('id', { mode: 'number' }).primaryKey(),
  amount: real('amount').notNull(),
  date: integer('date', { mode: 'timestamp' }).notNull(),
  description: text('description'),
  createdAt: integer('createdAt', { mode: 'timestamp' }).notNull(),
  updatedAt: integer('updatedAt', { mode: 'timestamp' }).notNull(),
  transCategoryId: integer('transCategoryId', { mode: 'number' }),
  categoryId: integer('categoryId', { mode: 'number' }),
});

export const transactionsRelations = relations(transactions, (helpers) => ({
  TransCategory: helpers.one(transCategories, {
    relationName: 'TransCategoryToTransaction',
    fields: [transactions.transCategoryId],
    references: [transCategories.id],
  }),
  Category: helpers.one(categories, {
    relationName: 'CategoryToTransaction',
    fields: [transactions.categoryId],
    references: [categories.id],
  }),
}));
Off scope for you: One thing I noticed that prisma doesn't play well with enum of sqlite (Probably something you can look into). If i remember correctly Sqlite enums are done using CHECKS in sql so in one way doesnt make sense ?
Also drizzle-team/drizzle-orm#636 seems to be solved with incorrect relationships. So I guess in theory the introspect should work ?
That's actually also my initial hack a while ago, and it is a good one! (depends on the stability of db pull tho)

However the downside for this solution is that it requires an active database connection, meaning it requires another setup and it became more complex when managing with multiple branches. Therefore since Prisma already have their DMMF helper, as well as other packages like the nexus, zod, typegraphql, pothos, etc.. uses this technique to map Prisma with their features, it'd be safer use the proven pipeline like the others.

Off scope for you: One thing I noticed that prisma doesn't play well with enum of sqlite (Probably something you can look into). If i remember correctly Sqlite enums are done using CHECKS in sql so in one way doesnt make sense ?

Fyi I'm not really familiar for this. Currently the goal of this library is to provide all features that Prisma has — excluding unsupported features like the ENUMs in sqlite. Once Prisma has the support, we could reliably map the feature to drizzle.

So if you'd like to use one that drizzle has already supported, I think you could generate and commit the result. However it requires you to maintain the modified schema everytime the script generates the schema, I think this could be a feature that we could look in the future.

Also I think this is where the db pull solution comes in, it's more of a subsidiary rather than complementary.

Definitely! Thanks for your insights on this! I empathize with the fact that extra setup is needed for a case like such. My thought process if purely SQLite based. Thats where your libs value prop lies for me. Really love what Prisma Schema has to offer for the DX when setting up the database. Looking to use Prisma as the schema generator, more sort of like where some devs use Prisma + Kysely.

Answer 14 · 2024-01-03T06:22:59.000Z

Definitely! Thanks for your insights on this! I empathize with the fact that extra setup is needed for a case like such. My thought process if purely SQLite based. Thats where your libs value prop lies for me. Really love what Prisma Schema has to offer for the DX when setting up the database. Looking to use Prisma as the schema generator, more sort of like where some devs use Prisma + Kysely.

Thanks also for the suggestion! keep it coming

Answer 15 · 2024-01-03T07:13:24.000Z

Definitely! Thanks for your insights on this! I empathize with the fact that extra setup is needed for a case like such. My thought process if purely SQLite based. Thats where your libs value prop lies for me. Really love what Prisma Schema has to offer for the DX when setting up the database. Looking to use Prisma as the schema generator, more sort of like where some devs use Prisma + Kysely.

Thanks also for the suggestion! keep it coming

Also! I forgot to mention you have another value prop. You generate the relationships as well where the introspect does not drizzle-team/drizzle-orm#3358

Answer 16 · 2024-01-18T18:51:15.000Z

Hi! Are default values currently in development? I'd want to use the package, but unfortunately default values of identifiers aren't set properly, so typescript wants me to set ID on every insert, kinda annoying!

Answer 17 · 2024-01-19T04:15:18.000Z

Hi! Are default values currently in development? I'd want to use the package, but unfortunately default values of identifiers aren't set properly, so typescript wants me to set ID on every insert, kinda annoying!

On it #22

Answer 18 · 2024-01-19T16:08:23.000Z

Hey @farreldarian with the inclusion of defaults support, could you push a new version?

Answer 19 · 2024-01-20T07:27:01.000Z

I'm planning to address #18 and add bytes support for the 0.6.0, but I've just published a canary release If you'd like to try out

Answer 20 · 2024-01-20T14:25:00.000Z

Hi, this works great with general default values, but unfourtenately this didn't solve the id issues.
Ids are generated like this based on my postgres db:
id String @id @default(dbgenerated("uuid_generate_v4()")) @db.Uuid
Current implementation doesn't really work in this case, only with autoincrement. Is there something you could do? @farreldarian

Answer 21 · 2024-01-20T16:21:14.000Z

Hi, this works great with general default values, but unfourtenately this didn't solve the id issues. Ids are generated like this based on my postgres db: id String @id @default(dbgenerated("uuid_generate_v4()")) @db.Uuid Current implementation doesn't really work in this case, only with autoincrement. Is there something you could do? @farreldarian

Defining dbgenerated explicitly will work, I'm working on the PR right know. But I couldn't find in the prisma docs defining the mapping for uuid() function in SQL for each provider.

In this case I recommend using the drizzle.default directive to generate via javascript, similar to what Prisma does with cuid().

UPDATE
It's all generated by the prisma engine, including uuid().

Answer 22 · 2024-01-20T16:54:56.000Z

Looks great! Could you provide a canary version for now to test it out? Thanks!

Answer 23 · 2024-01-20T17:06:33.000Z

Looks great! Could you provide a canary version for now to test it out? Thanks!

Just released v0.6.0 🚀

Answer 24 · 2024-06-14T23:01:42.000Z

Nice work ! keep the updates...

v1

Planned Features

Models

Helpers

Config

Unplanned