1 import { QueryTypes, Transaction } from 'sequelize'
2 import { Sequelize as SequelizeTypescript } from 'sequelize-typescript'
3 import { ActorCustomPageModel } from '@server/models/account/actor-custom-page'
4 import { RunnerModel } from '@server/models/runner/runner'
5 import { RunnerJobModel } from '@server/models/runner/runner-job'
6 import { RunnerRegistrationTokenModel } from '@server/models/runner/runner-registration-token'
7 import { TrackerModel } from '@server/models/server/tracker'
8 import { VideoTrackerModel } from '@server/models/server/video-tracker'
9 import { UserModel } from '@server/models/user/user'
10 import { UserNotificationModel } from '@server/models/user/user-notification'
11 import { UserRegistrationModel } from '@server/models/user/user-registration'
12 import { UserVideoHistoryModel } from '@server/models/user/user-video-history'
13 import { VideoChannelSyncModel } from '@server/models/video/video-channel-sync'
14 import { VideoJobInfoModel } from '@server/models/video/video-job-info'
15 import { VideoLiveReplaySettingModel } from '@server/models/video/video-live-replay-setting'
16 import { VideoLiveSessionModel } from '@server/models/video/video-live-session'
17 import { VideoSourceModel } from '@server/models/video/video-source'
18 import { LocalVideoViewerModel } from '@server/models/view/local-video-viewer'
19 import { LocalVideoViewerWatchSectionModel } from '@server/models/view/local-video-viewer-watch-section'
20 import { isTestOrDevInstance } from '../helpers/core-utils'
21 import { logger } from '../helpers/logger'
22 import { AbuseModel } from '../models/abuse/abuse'
23 import { AbuseMessageModel } from '../models/abuse/abuse-message'
24 import { VideoAbuseModel } from '../models/abuse/video-abuse'
25 import { VideoCommentAbuseModel } from '../models/abuse/video-comment-abuse'
26 import { AccountModel } from '../models/account/account'
27 import { AccountBlocklistModel } from '../models/account/account-blocklist'
28 import { AccountVideoRateModel } from '../models/account/account-video-rate'
29 import { ActorModel } from '../models/actor/actor'
30 import { ActorFollowModel } from '../models/actor/actor-follow'
31 import { ActorImageModel } from '../models/actor/actor-image'
32 import { ApplicationModel } from '../models/application/application'
33 import { OAuthClientModel } from '../models/oauth/oauth-client'
34 import { OAuthTokenModel } from '../models/oauth/oauth-token'
35 import { VideoRedundancyModel } from '../models/redundancy/video-redundancy'
36 import { PluginModel } from '../models/server/plugin'
37 import { ServerModel } from '../models/server/server'
38 import { ServerBlocklistModel } from '../models/server/server-blocklist'
39 import { UserNotificationSettingModel } from '../models/user/user-notification-setting'
40 import { ScheduleVideoUpdateModel } from '../models/video/schedule-video-update'
41 import { TagModel } from '../models/video/tag'
42 import { ThumbnailModel } from '../models/video/thumbnail'
43 import { VideoModel } from '../models/video/video'
44 import { VideoBlacklistModel } from '../models/video/video-blacklist'
45 import { VideoCaptionModel } from '../models/video/video-caption'
46 import { VideoChangeOwnershipModel } from '../models/video/video-change-ownership'
47 import { VideoChannelModel } from '../models/video/video-channel'
48 import { VideoCommentModel } from '../models/video/video-comment'
49 import { VideoFileModel } from '../models/video/video-file'
50 import { VideoImportModel } from '../models/video/video-import'
51 import { VideoLiveModel } from '../models/video/video-live'
52 import { VideoPlaylistModel } from '../models/video/video-playlist'
53 import { VideoPlaylistElementModel } from '../models/video/video-playlist-element'
54 import { VideoShareModel } from '../models/video/video-share'
55 import { VideoStreamingPlaylistModel } from '../models/video/video-streaming-playlist'
56 import { VideoTagModel } from '../models/video/video-tag'
57 import { VideoViewModel } from '../models/view/video-view'
58 import { CONFIG } from './config'
60 require('pg').defaults.parseInt8 = true // Avoid BIGINT to be converted to string
62 const dbname = CONFIG.DATABASE.DBNAME
63 const username = CONFIG.DATABASE.USERNAME
64 const password = CONFIG.DATABASE.PASSWORD
65 const host = CONFIG.DATABASE.HOSTNAME
66 const port = CONFIG.DATABASE.PORT
67 const poolMax = CONFIG.DATABASE.POOL.MAX
69 let dialectOptions: any = {}
71 if (CONFIG.DATABASE.SSL) {
74 rejectUnauthorized: false
79 const sequelizeTypescript = new SequelizeTypescript({
90 benchmark: isTestOrDevInstance(),
91 isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE,
92 logging: (message: string, benchmark: number) => {
93 if (process.env.NODE_DB_LOG === 'false') return
95 let newMessage = 'Executed SQL request'
96 if (isTestOrDevInstance() === true && benchmark !== undefined) {
97 newMessage += ' in ' + benchmark + 'ms'
100 logger.debug(newMessage, { sql: message, tags: [ 'sql' ] })
104 function checkDatabaseConnectionOrDie () {
105 sequelizeTypescript.authenticate()
106 .then(() => logger.debug('Connection to PostgreSQL has been established successfully.'))
109 logger.error('Unable to connect to PostgreSQL database.', { err })
114 async function initDatabaseModels (silent: boolean) {
115 sequelizeTypescript.addModels([
125 AccountVideoRateModel,
129 VideoCommentAbuseModel,
132 VideoChangeOwnershipModel,
141 ScheduleVideoUpdateModel,
144 VideoRedundancyModel,
145 UserVideoHistoryModel,
147 VideoLiveSessionModel,
148 VideoLiveReplaySettingModel,
149 AccountBlocklistModel,
150 ServerBlocklistModel,
151 UserNotificationModel,
152 UserNotificationSettingModel,
153 VideoStreamingPlaylistModel,
155 VideoPlaylistElementModel,
156 LocalVideoViewerModel,
157 LocalVideoViewerWatchSectionModel,
162 ActorCustomPageModel,
164 VideoChannelSyncModel,
165 UserRegistrationModel,
166 RunnerRegistrationTokenModel,
171 // Check extensions exist in the database
172 await checkPostgresExtensions()
174 // Create custom PostgreSQL functions
175 await createFunctions()
177 if (!silent) logger.info('Database %s is ready.', dbname)
180 // ---------------------------------------------------------------------------
184 checkDatabaseConnectionOrDie,
188 // ---------------------------------------------------------------------------
190 async function checkPostgresExtensions () {
192 checkPostgresExtension('pg_trgm'),
193 checkPostgresExtension('unaccent')
196 return Promise.all(promises)
199 async function checkPostgresExtension (extension: string) {
200 const query = `SELECT 1 FROM pg_available_extensions WHERE name = '${extension}' AND installed_version IS NOT NULL;`
202 type: QueryTypes.SELECT as QueryTypes.SELECT,
206 const res = await sequelizeTypescript.query<object>(query, options)
208 if (!res || res.length === 0) {
209 // Try to create the extension ourselves
211 await sequelizeTypescript.query(`CREATE EXTENSION ${extension};`, { raw: true })
214 const errorMessage = `You need to enable ${extension} extension in PostgreSQL. ` +
215 `You can do so by running 'CREATE EXTENSION ${extension};' as a PostgreSQL super user in ${CONFIG.DATABASE.DBNAME} database.`
216 throw new Error(errorMessage)
221 function createFunctions () {
222 const query = `CREATE OR REPLACE FUNCTION immutable_unaccent(text)
225 SELECT public.unaccent('public.unaccent', $1::text)
226 $func$ LANGUAGE sql IMMUTABLE;`
228 return sequelizeTypescript.query(query, { raw: true })