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