MySQL Table Schemas

I need to generate the following structure of tables:

1. Sales
2.Sales
....

Currently if I use what the docs say regarding the configuration of schemas, when I run drizzle-kit generate:mysql I get the following sql file: CREATE DATABASE 127

;
--> statement-breakpoint
CREATE TABLE

127.sales

 int AUTO_INCREMENT NOT NULL,

double

 double DEFAULT 0,
    CONSTRAINT

sales_id PRIMARY KEY(id

)
);

What I'm looking for is not to create a database for each tenant but only create their set of tables. This is my schema definition:

import { mysqlTable, int, double, mysqlSchema } from "drizzle-orm/mysql-core";

export const mySchema = mysqlSchema("dynamic_value_here")

export const sales = mySchema.table("sales", {
  id: int("id").primaryKey().autoincrement(),
  total_price_dlrs: double("double").default(0),
});
export type Sale = typeof sales.$inferSelect;
export type NewSale = typeof sales.$inferInsert;

Could someone please help me and point me in the right direction? Thanks in advance

Angelelz•248d ago

Database and schema is the same thing in Mysql https://dev.mysql.com/doc/refman/8.0/en/show-databases.html

rfrancociavaldini•248d ago

How about only the prefix in the table name? Is that possible in drizzle?

Angelelz•248d ago

In the docs: https://orm.drizzle.team/docs/goodies#multi-project-schema

Goodies - DrizzleORM

Drizzle ORM | %s

rfrancociavaldini•247d ago

Is it possible to use that approach to generate tables using drizzle-kit generate command?

import { int, text, mysqlTableCreator } from 'drizzle-orm/mysql-core';
 
const mysqlTable = mysqlTableCreator((name) =>

1.${name}

);
const secondTable = mysqlTableCreator((name) =>

2.${name}

);

export const users = mysqlTable('users', {
  id: int('id').primaryKey(),
  name: text('name').notNull(),
});
export const secondUsers = secondTable('users', {
  id: int('id').primaryKey(),
  name: text('name').notNull(),
});

I'm able to create tables 1.users and 2.users but I need drizzle-kit to read them dynamically, like looping through an array or something. Is that possible?

Angelelz•247d ago

This is meant for multi project schema. Like using a only one database for different project and keep them separated I'm not sure I follow what you're trying to achieve

rfrancociavaldini•247d ago

Something similar, to have one database with multiple tables for multiple tenants (1. Users, 2.Users, etc) where 1,2...N are identifiers for these tenants.

Angelelz•247d ago

I'm not sure how feasible your strategy is going to be. The way I've seen multitenancy implemented is by having another table with all the tenant names, and all the children tables have a tenantId foreign key. You would always query with the foreign key for anything in your application. If you want to keep your strategy with drizzle, you'll probably need to create schemas for each tenant, and a different drizzle instance for each. I don't like that architecture at all. You basically would need to re-deply your application each time you have a new tenant

rfrancociavaldini•247d ago

Not re-deploy everytime, only would need to create a new set of tables. We currently use this method using Sequelize ORM and it has support for it out of the box, would love to be able to recreate this with Drizzle

Drizzle Team Join

The official Discord for all Drizzle related projects, such as Drizzle ORM, Drizzle Kit, Drizzle Studio and more!

7.4KMembers

View on Discord

Want results from more Discord servers?

Add your server

any way to automatically cast numeric types on retrieval?I have a numeric field in a video table: ``` export const VideoTable = pgTable('videos', { id: uui Query in drizzle doesn't work## Error: ```console DrizzleError: No fields selected for table "usersToGroups" ("users_groups") ``Cannot read properties of undefined (reading 'referencedTable')Seemingly getting this error out of nowhere. I believe it's related to the relations I defined, but Relational query builder in mysql?I cant find anything about how to implement this. Am I blind or is this impossible?TransactionRollbackErrorNeed some help understanding TransactionRollbackError... Is this error saying the `tx.rollback()` f PostgresError: null value in column "id" of relation "pairs" violates not-null constraintHello, I am fairly new to Drizzle and working with databases, so I am running into a problem that I OnConflictDoNothing with mysqlHi everyone, I want to use the on conflict do nothing method described here: https://orm.drizzle.tea drizzle-kit introspect reorder tablesHere is the case: - I have a remote database. I made a backup and restore it locally - I ran: drizz Migration custom loggerHey there, I'm want to run migration as part of my CI/CD, and would have liked the output of the `mi why do snapshots not have the same name as the migrations?Hey, loving dirzzle so far. Hit a snag recently. Me and my friend are collaborating on a project. Help with improving database queryHello, I am seeking advice on how to improve a database query I am currently working with. My object Typescript types for relational queries with neon dbHey, I am having an issue with typescript types for relational queries with drizzle and neon db. Det .onUpdateNow() for postgresql?is that possible?Migrating in JS & JSON (no filesytem)👋 we're big fans (and sponsors) of Drizzle at Val Town. We use it internally and love it. We just onDuplicateKeyUpdatei'm having problems using `onDuplicateKeyUpdate`, doing it like ```js // values is an array of e Unable to generate uuid as primary keyHey everyone! I've searched through the help posts with similar titles but I could not get the answe [Has Solution] Drizzle Studio says it's running on the server http://0.0.0.0Idk what might be the issue. But when I run `pnpm drizzle-kit studio`. I am receiving the message: `Error using relational queries but not with core querieswhen i do ``` const invitesData = await db.select().from(invites).innerJoin(members, eq(invites.in Is it good to use UUID as primary key ?Hi, i want to define manually an id for my entry to be able to create at tame time a folder with sam What exactly i am doing wrong.. Prepared Query```json [{ "error": { "json": { "message": "prepared statement \"preparedCon

Gaming

Programming

MySQL Table Schemas