Use PostgreSQL instead of MongoDB (#4572)

* wip

* Update note.ts

* Update timeline.ts

* Update core.ts

* wip

* Update generate-visibility-query.ts

* wip

* wip

* wip

* wip

* wip

* Update global-timeline.ts

* wip

* wip

* wip

* Update vote.ts

* wip

* wip

* Update create.ts

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* Update files.ts

* wip

* wip

* Update CONTRIBUTING.md

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* Update read-notification.ts

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* Update cancel.ts

* wip

* wip

* wip

* Update show.ts

* wip

* wip

* Update gen-id.ts

* Update create.ts

* Update id.ts

* wip

* wip

* wip

* wip

* wip

* wip

* wip

* Docker: Update files about Docker (#4599)

* Docker: Use cache if files used by `yarn install` was not updated

This patch reduces the number of times to installing node_modules.
For example, `yarn install` step will be skipped when only ".config/default.yml" is updated.

* Docker: Migrate MongoDB to Postgresql

Misskey uses Postgresql as a database instead of Mongodb since version 11.

* Docker: Uncomment about data persistence

This patch will save a lot of databases.

* wip

* wip

* wip

* Update activitypub.ts

* wip

* wip

* wip

* Update logs.ts

* wip

* Update drive-file.ts

* Update register.ts

* wip

* wip

* Update mentions.ts

* wip

* wip

* wip

* Update recommendation.ts

* wip

* Update index.ts

* wip

* Update recommendation.ts

* Doc: Update docker.ja.md and docker.en.md (#1) (#4608)

Update how to set up misskey.

* wip

* :v:

* wip

* Update note.ts

* Update postgre.ts

* wip

* wip

* wip

* wip

* Update add-file.ts

* wip

* wip

* wip

* Clean up

* Update logs.ts

* wip

* :pizza:

* wip

* Ad notes

* wip

* Update api-visibility.ts

* Update note.ts

* Update add-file.ts

* tests

* tests

* Update postgre.ts

* Update utils.ts

* wip

* wip

* Refactor

* wip

* Refactor

* wip

* wip

* Update show-users.ts

* Update update-instance.ts

* wip

* Update feed.ts

* Update outbox.ts

* Update outbox.ts

* Update user.ts

* wip

* Update list.ts

* Update update-hashtag.ts

* wip

* Update update-hashtag.ts

* Refactor

* Update update.ts

* wip

* wip

* :v:

* clean up

* docs

* Update push.ts

* wip

* Update api.ts

* wip

* :v:

* Update make-pagination-query.ts

* :v:

* Delete hashtags.ts

* Update instances.ts

* Update instances.ts

* Update create.ts

* Update search.ts

* Update reversi-game.ts

* Update signup.ts

* Update user.ts

* id

* Update example.yml

* :art:

* objectid

* fix

* reversi

* reversi

* Fix bug of chart engine

* Add test of chart engine

* Improve test

* Better testing

* Improve chart engine

* Refactor

* Add test of chart engine

* Refactor

* Add chart test

* Fix bug

* コミットし忘れ

* Refactoring

* :v:

* Add tests

* Add test

* Extarct note tests

* Refactor

* 存在しないユーザーにメンションできなくなっていた問題を修正

* Fix bug

* Update update-meta.ts

* Fix bug

* Update mention.vue

* Fix bug

* Update meta.ts

* Update CONTRIBUTING.md

* Fix bug

* Fix bug

* Fix bug

* Clean up

* Clean up

* Update notification.ts

* Clean up

* Add mute tests

* Add test

* Refactor

* Add test

* Fix test

* Refactor

* Refactor

* Add tests

* Update utils.ts

* Update utils.ts

* Fix test

* Update package.json

* Update update.ts

* Update manifest.ts

* Fix bug

* Fix bug

* Add test

* :art:

* Update endpoint permissions

* Updaye permisison

* Update person.ts

#4299

* データベースと同期しないように

* Fix bug

* Fix bug

* Update reversi-game.ts

* Use a feature of Node v11.7.0 to extract a public key (#4644)

* wip

* wip

* :v:

* Refactoring

#1540

* test

* test

* test

* test

* test

* test

* test

* Fix bug

* Fix test

* :sushi:

* wip

* #4471

* Add test for #4335

* Refactor

* Fix test

* Add tests

* :clock4:

* Fix bug

* Add test

* Add test

* rename

* Fix bug

2 files changed, 137 insertions, 39 deletions

diff --git a/src/db/mongodb.ts b/src/db/mongodb.ts
deleted file mode 100644
index f82ced1765..0000000000
--- a/src/db/mongodb.ts
+++ /dev/null
@@ -1,39 +0,0 @@
-import config from '../config';
-
-const u = config.mongodb.user ? encodeURIComponent(config.mongodb.user) : null;
-const p = config.mongodb.pass ? encodeURIComponent(config.mongodb.pass) : null;
-
-const uri = `mongodb://${u && p ? `${u}:${p}@` : ''}${config.mongodb.host}:${config.mongodb.port}/${config.mongodb.db}`;
-
-/**
- * monk
- */
-import mongo from 'monk';
-
-const db = mongo(uri);
-
-export default db;
-
-/**
- * MongoDB native module (officialy)
- */
-import * as mongodb from 'mongodb';
-
-let mdb: mongodb.Db;
-
-const nativeDbConn = async (): Promise<mongodb.Db> => {
-	if (mdb) return mdb;
-
-	const db = await ((): Promise<mongodb.Db> => new Promise((resolve, reject) => {
-		mongodb.MongoClient.connect(uri, { useNewUrlParser: true }, (e: Error, client: any) => {
-			if (e) return reject(e);
-			resolve(client.db(config.mongodb.db));
-		});
-	}))();
-
-	mdb = db;
-
-	return db;
-};
-
-export { nativeDbConn };
diff --git a/src/db/postgre.ts b/src/db/postgre.ts
new file mode 100644
index 0000000000..bc5ee4ce8c
--- /dev/null
+++ b/src/db/postgre.ts
@@ -0,0 +1,137 @@
+import { createConnection, Logger, getConnection } from 'typeorm';
+import config from '../config';
+import { entities as charts } from '../services/chart/entities';
+import { dbLogger } from './logger';
+import * as highlight from 'cli-highlight';
+
+import { Log } from '../models/entities/log';
+import { User } from '../models/entities/user';
+import { DriveFile } from '../models/entities/drive-file';
+import { DriveFolder } from '../models/entities/drive-folder';
+import { AccessToken } from '../models/entities/access-token';
+import { App } from '../models/entities/app';
+import { PollVote } from '../models/entities/poll-vote';
+import { Note } from '../models/entities/note';
+import { NoteReaction } from '../models/entities/note-reaction';
+import { NoteWatching } from '../models/entities/note-watching';
+import { NoteUnread } from '../models/entities/note-unread';
+import { Notification } from '../models/entities/notification';
+import { Meta } from '../models/entities/meta';
+import { Following } from '../models/entities/following';
+import { Instance } from '../models/entities/instance';
+import { Muting } from '../models/entities/muting';
+import { SwSubscription } from '../models/entities/sw-subscription';
+import { Blocking } from '../models/entities/blocking';
+import { UserList } from '../models/entities/user-list';
+import { UserListJoining } from '../models/entities/user-list-joining';
+import { Hashtag } from '../models/entities/hashtag';
+import { NoteFavorite } from '../models/entities/note-favorite';
+import { AbuseUserReport } from '../models/entities/abuse-user-report';
+import { RegistrationTicket } from '../models/entities/registration-tickets';
+import { MessagingMessage } from '../models/entities/messaging-message';
+import { Signin } from '../models/entities/signin';
+import { AuthSession } from '../models/entities/auth-session';
+import { FollowRequest } from '../models/entities/follow-request';
+import { Emoji } from '../models/entities/emoji';
+import { ReversiGame } from '../models/entities/games/reversi/game';
+import { ReversiMatching } from '../models/entities/games/reversi/matching';
+import { UserNotePining } from '../models/entities/user-note-pinings';
+import { UserServiceLinking } from '../models/entities/user-service-linking';
+import { Poll } from '../models/entities/poll';
+import { UserKeypair } from '../models/entities/user-keypair';
+import { UserPublickey } from '../models/entities/user-publickey';
+
+const sqlLogger = dbLogger.createSubLogger('sql', 'white', false);
+
+class MyCustomLogger implements Logger {
+	private highlight(sql: string) {
+		return highlight.highlight(sql, {
+			language: 'sql', ignoreIllegals: true,
+		});
+	}
+
+	public logQuery(query: string, parameters?: any[]) {
+		sqlLogger.info(this.highlight(query));
+	}
+
+	public logQueryError(error: string, query: string, parameters?: any[]) {
+		sqlLogger.error(this.highlight(query));
+	}
+
+	public logQuerySlow(time: number, query: string, parameters?: any[]) {
+		sqlLogger.warn(this.highlight(query));
+	}
+
+	public logSchemaBuild(message: string) {
+		sqlLogger.info(message);
+	}
+
+	public log(message: string) {
+		sqlLogger.info(message);
+	}
+
+	public logMigration(message: string) {
+		sqlLogger.info(message);
+	}
+}
+
+export function initDb(justBorrow = false, sync = false, log = false) {
+	const enableLogging = log || !['production', 'test'].includes(process.env.NODE_ENV);
+
+	try {
+		const conn = getConnection();
+		return Promise.resolve(conn);
+	} catch (e) {}
+
+	return createConnection({
+		type: 'postgres',
+		host: config.db.host,
+		port: config.db.port,
+		username: config.db.user,
+		password: config.db.pass,
+		database: config.db.db,
+		synchronize: process.env.NODE_ENV === 'test' || sync,
+		dropSchema: process.env.NODE_ENV === 'test' && !justBorrow,
+		logging: enableLogging,
+		logger: enableLogging ? new MyCustomLogger() : null,
+		entities: [
+			Meta,
+			Instance,
+			App,
+			AuthSession,
+			AccessToken,
+			User,
+			UserKeypair,
+			UserPublickey,
+			UserList,
+			UserListJoining,
+			UserNotePining,
+			UserServiceLinking,
+			Following,
+			FollowRequest,
+			Muting,
+			Blocking,
+			Note,
+			NoteFavorite,
+			NoteReaction,
+			NoteWatching,
+			NoteUnread,
+			Log,
+			DriveFile,
+			DriveFolder,
+			Poll,
+			PollVote,
+			Notification,
+			Emoji,
+			Hashtag,
+			SwSubscription,
+			AbuseUserReport,
+			RegistrationTicket,
+			MessagingMessage,
+			Signin,
+			ReversiGame,
+			ReversiMatching,
+			...charts as any
+		]
+	});
+}