1"use strict";(self.webpackChunkdocs=self.webpackChunkdocs||[]).push([["35806"],{75834(e,n,a){a.r(n),a.d(n,{metadata:()=>t,default:()=>u,frontMatter:()=>l,contentTitle:()=>o,toc:()=>h,assets:()=>d});var t=JSON.parse('{"id":"multiple-schemas","title":"Using Multiple Schemas","description":"In MySQL, PostgreSQL, and SQLite (via ATTACH DATABASE) it is possible to define your entities in multiple schemas. In MySQL terminology, it is called database, but from an implementation point of view, it is a schema.","source":"@site/versioned_docs/version-7.1/multiple-schemas.md","sourceDirName":".","slug":"/multiple-schemas","permalink":"/docs/7.1/multiple-schemas","draft":false,"unlisted":false,"editUrl":"https://github.com/mikro-orm/mikro-orm/edit/master/docs/versioned_docs/version-7.1/multiple-schemas.md","tags":[],"version":"7.1","lastUpdatedBy":"Martin Ad\xe1mek","lastUpdatedAt":1779264166000,"frontMatter":{"title":"Using Multiple Schemas"},"sidebar":"docs","previous":{"title":"Naming Strategy","permalink":"/docs/7.1/naming-strategy"},"next":{"title":"Virtual Entities","permalink":"/docs/7.1/virtual-entities"}}'),s=a(74848),i=a(28453),r=a(50773),c=a(57250);let l={title:"Using Multiple Schemas"},o,d={},h=[{value:"Default schema on <code>EntityManager</code>
1",id:"default-schema-on-entitymanager",level:2},{value:"Wildcard Schema",id:"wildcard-schema",level:2},{value:"Note about migrations",id:"note-about-migrations",level:3},{value:"SQLite ATTACH DATABASE",id:"sqlite-attach-database",level:2},{value:"Configuration",id:"configuration",level:3},{value:"Entity Definition",id:"entity-definition",level:3},{value:"Schema Generator Support",id:"schema-generator-support",level:3},{value:"Limitations",id:"limitations",level:3}];function m(e){let n={a:"a",blockquote:"blockquote",code:"code",h2:"h2",h3:"h3",li:"li",p:"p",pre:"pre",strong:"strong",ul:"ul",...(0,i.R)(),...e.components};return(0,s.jsxs)(s.Fragment,{children:[(0,s.jsx)(n.p,{children:"In MySQL, PostgreSQL, and SQLite (via ATTACH DATABASE) it is possible to define your entities in multiple schemas. In MySQL terminology, it is called database, but from an implementation point of view, it is a schema."}),"\n",(0,s.jsxs)(n.blockquote,{children:["\n",(0,s.jsx)(n.p,{children:"To use multiple schemas, your connection needs to have access to all of them (multiple connections are not supported in a single MikroORM instance)."}),"\n"]}),"\n",(0,s.jsxs)(n.p,{children:["All you need to do is simply define the schema name via ",(0,s.jsx)(n.code,{children:"schema"})," options, or table name including schema name in ",(0,s.jsx)(n.code,{children:"tableName"})," option:"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"@Entity({ schema: 'first_schema' })\nexport class Foo { ... }\n\n// or alternatively we can specify it inside custom table name\n@Entity({ tableName: 'second_schema.bar' })\nexport class Bar { ... }\n"})}),"\n",(0,s.jsxs)(n.p,{children:["Then use those entities as usual. Resulting SQL queries will use this ",(0,s.jsx)(n.code,{children:"tableName"})," value as a table name so as long as your connection has access to given schema, everything should work as expected."]}),"\n",(0,s.jsxs)(n.p,{children:["You can also query for entity in specific schema via ",(0,s.jsx)(n.code,{children:"EntityManager"}),", ",(0,s.jsx)(n.code,{children:"EntityRepository"})," or ",(0,s.jsx)(n.code,{children:"QueryBuilder"}),":"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"const user = await em.findOne(User, { ... }, { schema: 'client-123' });\n"})}),"\n",(0,s.jsxs)(n.p,{children:["To create entity in specific schema, you will need to use ",(0,s.jsx)(n.code,{children:"QueryBuilder"}),":"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"const qb = em.createQueryBuilder(User);\nawait qb.insert({ email: '[email protected]' }).withSchema('client-123');\n"})}),"\n",(0,s.jsxs)(n.h2,{id:"default-schema-on-entitymanager",children:["Default schema on ",(0,s.jsx)(n.code,{children:"EntityManager"})]}),"\n",(0,s.jsxs)(n.p,{children:["Instead of defining schema per entity or operation it's possible to ",(0,s.jsx)(n.code,{children:".fork()"})," EntityManger and define a default schema that will be used with wildcard schemas."]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"const fork = em.fork({ schema: 'client-123' });\nawait fork.findOne(User, { ... });\n\n// Will yield the same result as\nconst user = await em.findOne(User, { ... }, { schema: 'client-123' });\n"})}),"\n",(0,s.jsx)(n.p,{children:"When creating an entity the fork will set default schema"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"const fork = em.fork({ schema: 'client-123' });\nconst user = new User();\nuser.email = '[email protected]';\nawait fork.persist(user).flush();\n\n// Will yield the same result as\nconst qb = em.createQueryBuilder(User);\nawait qb.insert({ email: '[email protected]' }).withSchema('client-123');\n"})}),"\n",(0,s.jsx)(n.p,{children:"You can also set or clear schema"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"em.schema = 'client-123';\nconst fork = em.fork({ schema: 'client-1234' });\nfork.schema = null;\n"})}),"\n",(0,s.jsxs)(n.p,{children:[(0,s.jsx)(n.code,{children:"EntityManager.schema"})," Respects the context, so global EM will give you the contextual schema if executed inside ",(0,s.jsx)(n.a,{href:"https://mikro-orm.io/docs/identity-map#-requestcontext-helper",children:"request context handler"})]}),"\n",(0,s.jsx)(n.h2,{id:"wildcard-schema",children:"Wildcard Schema"}),"\n",(0,s.jsx)(n.p,{children:"MikroORM also supports defining entities that can exist in multiple schemas. To do that, you just specify wildcard schema:"}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"@Entity({ schema: '*' })\nexport class Book {\n\n @PrimaryKey()\n id!: number;\n\n @Property({ nullable: true })\n name?: string;\n\n @ManyToOne(() => Author, { nullable: true, deleteRule: 'cascade' })\n author?: Author;\n\n @ManyToOne(() => Book, { nullable: true })\n basedOn?: Book;\n\n}\n"})}),"\n",(0,s.jsxs)(n.p,{children:["Entities like this will be by default ignored when using ",(0,s.jsx)(n.code,{children:"SchemaGenerator"}),", as you need to specify which schema to use. For that you need to use the ",(0,s.jsx)(n.code,{children:"schema"})," option of the ",(0,s.jsx)(n.code,{children:"create/update/drop"})," methods or the ",(0,s.jsx)(n.code,{children:"--schema"})," CLI parameter."]}),"\n",(0,s.jsxs)(n.p,{children:["On runtime, the wildcard schema will be replaced with either ",(0,s.jsx)(n.code,{children:"FindOptions.schema"}),", ",(0,s.jsx)(n.code,{children:"EntityManager.schema"})," or with the ",(0,s.jsx)(n.code,{children:"schema"})," option from the ORM config."]}),"\n",(0,s.jsx)(n.h3,{id:"note-about-migrations",children:"Note about migrations"}),"\n",(0,s.jsxs)(n.p,{children:["By default, ",(0,s.jsx)(n.code,{children:"migration:create"})," ignores wildcard-schema entities \u2014 you would need to fall back to ",(0,s.jsx)(n.code,{children:"SchemaGenerator"})," (e.g. in an API endpoint that creates new tenants) or write the dynamic schema queries by hand."]}),"\n",(0,s.jsxs)(n.p,{children:["For multi-tenant fan-out where every tenant shares the same schema shape, opt the wildcard entities into ",(0,s.jsx)(n.code,{children:"migration:create"})," with ",(0,s.jsx)(n.code,{children:"migrations.includeWildcardSchema: true"}),". The emitted DDL is unqualified, so the same migration file can be applied against any schema at runtime via ",(0,s.jsx)(n.code,{children:"migrator.up({ schema })"}),". See ",(0,s.jsx)(n.a,{href:"/docs/7.1/migrations#runtime-schema-context",children:"Runtime schema context"})," for the full flow."]}),"\n",(0,s.jsx)(n.h2,{id:"sqlite-attach-database",children:"SQLite ATTACH DATABASE"}),"\n",(0,s.jsxs)(n.p,{children:["SQLite supports multiple schemas via the ",(0,s.jsx)(n.code,{children:"ATTACH DATABASE"})," command, which allows attaching additional database files to a single connection. Each attached database acts as a separate schema, and tables are accessed using the ",(0,s.jsx)(n.code,{children:"schema.table_name"})," syntax."]}),"\n",(0,s.jsx)(n.h3,{id:"configuration",children:"Configuration"}),"\n",(0,s.jsxs)(n.p,{children:["Use the ",(0,s.jsx)(n.code,{children:"attachDatabases"})," option to specify databases to attach on connection:"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"import { MikroORM } from '@mikro-orm/sqlite'; // or @mikro-orm/libsql\n\nconst orm = await MikroORM.init({\n dbName: './main.db',\n entities: [Author, Book, UserProfile, LogEntry],\n attachDatabases: [\n { name: 'users_db', path: './users.db' },\n { name: 'logs_db', path: '/var/data/logs.db' },\n ],\n});\n"})}),"\n",(0,s.jsxs)(n.p,{children:["Relative paths are resolved from the ",(0,s.jsx)(n.code,{children:"baseDir"})," option (or current working directory if not set)."]}),"\n",(0,s.jsx)(n.h3,{id:"entity-definition",children:"Entity Definition"}),"\n",(0,s.jsxs)(n.p,{children:["Reference attached databases using the ",(0,s.jsx)(n.code,{children:"schema"})," option:"]}),"\n",(0,s.jsxs)(r.A,{groupId:"entity-def",defaultValue:"define-entity-class",values:[{label:"defineEntity + class",value:"define-entity-class"},{label:"defineEntity",value:"define-entity"},{label:"reflect-metadata",value:"reflect-metadata"},{label:"ts-morph",value:"ts-morph"}],children:[(0,s.jsx)(c.A,{value:"define-entity-class",children:(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"import { defineEntity, p } from '@mikro-orm/core';\n\n// Entity in the main database (schema is optional for main)\nconst AuthorSchema = defineEntity({\n name: 'Author',\n schema: 'main',\n properties: {\n id: p.number().primary(),\n name: p.string(),\n },\n});\n\nexport class Author extends AuthorSchema.class {}\nAuthorSchema.setClass(Author);\n\n// Entity in an attached database\nconst UserProfileSchema = defineEntity({\n name: 'UserProfile',\n schema: 'users_db',\n properties: {\n id: p.number().primary(),\n username: p.string(),\n },\n});\n\nexport class UserProfile extends UserProfileSchema.class {}\nUserProfileSchema.setClass(UserProfile);\n"})})}),(0,s.jsx)(c.A,{value:"define-entity",children:(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"import { defineEntity, p } from '@mikro-orm/core';\n\n// Entity in the main database (schema is optional for main)\nexport const Author = defineEntity({\n name: 'Author',\n schema: 'main',\n properties: {\n id: p.number().primary(),\n name: p.string(),\n },\n});\n\n// Entity in an attached database\nexport const UserProfile = defineEntity({\n name: 'UserProfile',\n schema: 'users_db',\n properties: {\n id: p.number().primary(),\n username: p.string(),\n },\n});\n"})})}),(0,s.jsx)(c.A,{value:"reflect-metadata",children:(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"// Entity in the main database (schema is optional for main)\n@Entity({ schema: 'main' })\nclass Author {\n\n @PrimaryKey()\n id!: number;\n\n @Property()\n name!: string;\n\n}\n\n// Entity in an attached database\n@Entity({ schema: 'users_db' })\nclass UserProfile {\n\n @PrimaryKey()\n id!: number;\n\n @Property()\n username!: string;\n\n}\n"})})}),(0,s.jsx)(c.A,{value:"ts-morph",children:(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"// Entity in the main database (schema is optional for main)\n@Entity({ schema: 'main' })\nclass Author {\n\n @PrimaryKey()\n id!: number;\n\n @Property()\n name!: string;\n\n}\n\n// Entity in an attached database\n@Entity({ schema: 'users_db' })\nclass UserProfile {\n\n @PrimaryKey()\n id!: number;\n\n @Property()\n username!: string;\n\n}\n"})})})]}),"\n",(0,s.jsx)(n.h3,{id:"schema-generator-support",children:"Schema Generator Support"}),"\n",(0,s.jsx)(n.p,{children:"The schema generator fully supports attached databases. It will:"}),"\n",(0,s.jsxs)(n.ul,{children:["\n",(0,s.jsxs)(n.li,{children:["Create tables in the correct attached database based on the entity's ",(0,s.jsx)(n.code,{children:"schema"})," option"]}),"\n",(0,s.jsx)(n.li,{children:"Detect and diff tables across all attached databases"}),"\n",(0,s.jsx)(n.li,{children:"Generate proper migration SQL for each database"}),"\n"]}),"\n",(0,s.jsx)(n.pre,{children:(0,s.jsx)(n.code,{className:"language-ts",children:"// Creates tables in all databases (main and attached)\nawait orm.schema.create();\n\n// Updates schema across all databases\nawait orm.schema.update();\n"})}),"\n",(0,s.jsx)(n.h3,{id:"limitations",children:"Limitations"}),"\n",(0,s.jsxs)(n.ul,{children:["\n",(0,s.jsxs)(n.li,{children:[(0,s.jsx)(n.strong,{children:"libSQL remote connections"}),": ATTACH DATABASE is not supported when using remote libSQL URLs (",(0,s.jsx)(n.code,{children:"libsql://"}),", ",(0,s.jsx)(n.code,{children:"https://"}),"). Only local file-based databases can be attached."]}),"\n",(0,s.jsxs)(n.li,{children:[(0,s.jsx)(n.strong,{children:"Cross-database foreign keys"}),": While SQLite allows foreign keys between attached databases within the same connection, the referenced table name in the SQL syntax cannot include a schema prefix. MikroORM handles this automatically."]}),"\n",(0,s.jsxs)(n.li,{children:[(0,s.jsx)(n.strong,{children:"Transactions"}),": All attached databases share the same transaction scope within a connection."]}),"\n"]})]})}function u(e={}){let{wrapper:n}={...(0,i.R)(),...e.components};return n?(0,s.jsx)(n,{...e,children:(0,s.jsx)(m,{...e})}):m(e)}}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.