3 min de leitura ·

[Dúvida] Padrões de criação de scripts SQL para FKs: como vocês fazem?

Tenho um script em Node.js que cria as FKs, mas, por enquanto, só funciona para MariaDB. Como ele faz isso? Ele recebe um arquivo .sql, lê e mapeia as tabelas, depois mapeia as colunas, faz comparações com as PKs e, se uma PK existir em outra tabela, cria a relação correspondente. Vou deixar um exemplo abaixo das tabelas, e das FKs geradas.

Tenho uma dúvida

como vocês criam declaram o nome da tabela e IDs?
Eu sigo um padrão de formatação e nomenclatura.
Para as tabelas declaro o nome do database e depois o nome da tabela, para as PKs delcaro Id_ e o nome da key, para o script funcionar corretamente como no exemplo no final, mas sei que nem todo mundo segue o mesmo padrão.

Por que criei?

Bom, um dos únicos softwares que encontrei para fazer isso custa quase 99 doletas. Eu precisava de algo rápido e customizável, porque, toda vez que fazia uma mudança, tinha que alterar manualmente as duas tabelas e a relação entre elas. Muitas vezes errava o nome da FK, e isso gerava erros quando tentava rodar o script sql.
Com ORM (PrismaJS), achei mais viável ele ler o banco de dados e fazer o map pra criar os types, do que escrever as tabelas nele, já que ainda seria necessário fazer essas declarações manualmente.

No começo, tentei fazer uma primeira versão com a ajuda do ChatGPT, mas ficou horrível. Quando começa a ficar mais complexo, ele se perde. Então resolvi fazer tudo manualmente com TS. Já faz um bom tempo que uso esse script. tenho um script sql mais de 1k linhas e cria mais de 400 relações automaticamente. assim não preciso mais escrever todas essas linhas manualmente para declarar as FKs.

Agora, estou trabalhando em uma versão que será um SaaS, suportando múltiplos bancos de dados: MySQL, PostgreSQL, SQLite, SQL Server e Oracle.

Como irei criar e disponibilizar

estou pensando em criar uma versão gratuita com algumas limitações para que os devs possam testar seus scripts SQL e avaliar se vale a pena adquirir uma assinatura.
também estou considerando desenvolver uma versão para uso via API.

Exemplos de tabelas:

CREATE TABLE IF NOT EXISTS
    `Test_App`.`User_Email` (
        `Id_User_Email` INT NOT NULL AUTO_INCREMENT,
        `Exposed_Id_Email` VARCHAR(256) NULL,
        `Id_Email_Status` INT NULL,
         PRIMARY KEY (`Id_User_Email`)
    )  ;

CREATE TABLE IF NOT EXISTS
    `Test_App`.`Email_Status` (
        `Id_Email_Status` INT NOT NULL AUTO_INCREMENT,
        `Id_User` INT NOT NULL,
        PRIMARY KEY (`Id_Email_Status`)
    )  ;

CREATE TABLE IF NOT EXISTS
    `Test_App`.`User` (
        `Id_User` INT NOT NULL AUTO_INCREMENT,
        `Id_User_Email` INT NOT NULL,
        PRIMARY KEY (`Id_User`)
    ) ;

exemplo das FKs geradas com o script:

ALTER TABLE `User_Email` ADD CONSTRAINT `Fk_User_Email_Email_Status` FOREIGN KEY (`Id_Email_Status`) REFERENCES `Email_Status` (`Id_Email_Status`) ON DELETE CASCADE;

ALTER TABLE `Email_Status` ADD CONSTRAINT `Fk_Email_Status_User` FOREIGN KEY (`Id_User`) REFERENCES `User` (`Id_User`) ON DELETE CASCADE;

ALTER TABLE `User` ADD CONSTRAINT `Fk_User_User_Email` FOREIGN KEY (`Id_User_Email`) REFERENCES `User_Email` (`Id_User_Email`) ON DELETE CASCADE;

Debug do relacionamento na CLI:

relation generateFK: {
  foreignKey: [ 'Id_Email_Status' ],
  relateTo: [ 'Email_Status' ],
  currentTable: 'User_Email'
}
relation: {
  foreignKey: [ 'Id_Email_Status' ],
  relateTo: [ 'Email_Status' ],
  currentTable: 'User_Email'
}
relation generateFK: {
  foreignKey: [ 'Id_User' ],
  relateTo: [ 'User' ],
  currentTable: 'Email_Status'
}
relation: {
  foreignKey: [ 'Id_User' ],
  relateTo: [ 'User' ],
  currentTable: 'Email_Status'
}
relation generateFK: {
  foreignKey: [ 'Id_User_Email' ],
  relateTo: [ 'User_Email' ],
  currentTable: 'User'
}
relation: {
  foreignKey: [ 'Id_User_Email' ],
  relateTo: [ 'User_Email' ],
  currentTable: 'User'
}

Se quiserem da qualquer dica fiquem a vontade
Obrigado pela atenção 👍

samuelcdias

13 dias atrás

Não entendi muito bem seu objetivo.
Pq eu precisaria de recriar a FK de um banco que já existe?
Ou ainda se eu já tenho um código construído com code first pq iria alterar a estrutura das tabelas exceto por mudança de regra de negócio?
Verificou se não é problema na geração de código do ORM?

Mas voltando a sua dúvida aqui na empresa onde trabalho as tabelas tem PK como Id e FK como _Id.
Trabalho com csharp porém quando utilizava typescript as FK eram tipadas com o tipo do ID da PK, assim qualquer mudança na PK estoura erro na fase de transpilação.
Segue exemplo:

type User = {
    Id: string;
    FirstName: string;
    scope: Role;
}
type Role = 'Admin' | 'Customer' | 'Manager';
type Adress = {
   CustomerId = User['Id'];
   Value = string;
}

Simplifiquei os campos mas veja que ao tipar CustomerId eu forço um acoplamento. Se Id for eliminado de User vai estourar erro em toda aplicação.

Fora que o meu domínio não precisa de ser exatamente o que está no banco, posso incluir uma camada para fazer o de/para.

fernandodev

Autor

13 dias atrás

Não é recriar a FKs e sim cria-las, no primeiro exemplo do script sql ele não tem relacionamento entre si pq precisa ser declarado, no segundo ALTER TABLE... é o retorno do script com o relacionamento das tabelas depois de lido as tabelas e feito o mapeamento e assim criando o relaciomanento entre elas.
nesse exemplo pra tabela User ter o relaciomaneto com User_Email ela precisaria ser declarada assim:

CREATE TABLE IF NOT EXISTS `Test_App`.`User` (
    `Id_User` INT NOT NULL AUTO_INCREMENT,
    `Id_User_Email` INT NOT NULL,
    PRIMARY KEY (`Id_User`),
    -- criando relaciomaneto com a user_email
    CONSTRAINT `fk_user_email` FOREIGN KEY (`Id_User_Email`) 
    REFERENCES `Test_App`.`User_Email` (`Id_User_Email`)
);

adicionado a CONSTRAINT na tabela, e isso que meu script faz, ele cria as CONSTRAINT, evitando essa duas linhas ser declarada nas tabelas

No orm ainda preciso declarar as FKs, exemplo:

ORM PrismaJS

model User {
  id_user       Int        @id @default(autoincrement()) @map("Id_User")
  id_user_email Int        @map("Id_User_Email")
  user_email    User_Email @relation(fields: [id_user_email], references: [id_user_email])

  @@map("User")
}

no ORM ainda teria que declara essa linha user_email User_Email... pra criar o relaciomaneto entre elas no BD

e meu script não atenderia pessoas que criar as tabelas diretamente por algum ORM, somente pessoas que lida com script sql