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 (
id 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 advance8 Replies
Database and schema is the same thing in Mysql
https://dev.mysql.com/doc/refman/8.0/en/show-databases.html
How about only the prefix in the table name? Is that possible in drizzle?
Goodies - DrizzleORM
Drizzle ORM | %s
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?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
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.
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 tenantNot 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