Schema for the MySQL Tables

[MaheshkumarSundaram]

Hi @neonexus,

Do you mind sharing the MySQL schema of the tables through create statement?

Thanks.

[neonexus]

CREATE DATABASE `myapp` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci */;

CREATE TABLE `apitoken` (
  `id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `data` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `token` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `user` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `log` (
  `id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `description` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `data` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createdAt` datetime(3) DEFAULT NULL,
  `user` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `request` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `otp` (
  `id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `isEnabled` tinyint(1) DEFAULT NULL,
  `secret` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `backupTokens` text COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `user` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `requestlog` (
  `id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `direction` varchar(8) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `method` varchar(6) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `host` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `path` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `headers` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `getParams` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `body` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `responseCode` int(4) unsigned DEFAULT NULL,
  `responseBody` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `responseHeaders` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `responseTime` varchar(15) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createdAt` datetime(3) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `session` (
  `id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `data` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `csrfSecret` varchar(105) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `expiresAt` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `user` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user` (
  `id` varchar(36) COLLATE utf8mb4_unicode_ci NOT NULL,
  `role` varchar(7) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `firstName` varchar(70) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `lastName` varchar(70) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `password` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `verificationKey` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `avatar` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `isGravatar` tinyint(1) DEFAULT NULL,
  `deletedAt` datetime DEFAULT NULL,
  `createdAt` datetime DEFAULT NULL,
  `updatedAt` datetime DEFAULT NULL,
  `deletedBy` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `createdBy` varchar(36) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

[MaheshkumarSundaram]

@neonexus, Thank you. Much appreciated.

[neonexus]

It should be noted, that the order of the columns doesn't matter as far as the software is concerned; but can very much affect indexing speeds. Generally speaking, one would want to keep id first, then any remote pointers, like to a user, then all other data. This all goes mainly for PROD, as Sails will just change order n DEV.

[MaheshkumarSundaram]

@neonexus

Could you please explain when exactly the tables log and apitoken would be used?

As I see, apitoken would be used if we are not using sessions. Will this come into play when using tunnels!? I am not sure about its exact usecase.

Thanks

[neonexus]

The log table is good for storing logs of issues with the API; especially with load-balanced instances. So, if a callback returns an err, it would be good to use sails.helpers.createLog. Then I used to have a CRON job run through, and check if there were any new issues that popped up since last check, and email me if there was.

As for the apiToken; this is good for 3rd party API access. So, if you had a service, where one of your users wanted to get access to the API, they would create a token, and use that instead of user/password. It was also good for testing the API back before things like Postman existed.

[MaheshkumarSundaram]

@neonexus,

Thank you for the clear explanation. Appreciated!

[MaheshkumarSundaram]

Hi @neonexus,

Just a follow-up on the schema, user column in tables - session, log, otp and apitoken refer to the id of the table user. Is there any reason why id of the table user is not made as foreign key for the others directly?

Thanks

[neonexus]

@MaheshkumarSundaram because they get wiped on DEV, and I pulled that schema dump from a DEV instance, not a PROD one. On PROD you'll have to setup the keys manually, just like the rest of the schema, because Sails won't modify schema on PROD.

Ultimately, you don't NEED them; and while they can improve performance on queries, I don't believe it will matter with the way Waterline handles JOINs (since you can JOIN a table on a completely different datastore, like MySQL and some NoSQL store).

[MaheshkumarSundaram]

Ah makes sense @neonexus. Thanks for the brief.