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 { UserVideoHistoryModel } from '@server/models/user/user-video-history'
9 import { VideoJobInfoModel } from '@server/models/video/video-job-info'
10 import { LocalVideoViewerModel } from '@server/models/view/local-video-viewer'
11 import { LocalVideoViewerWatchSectionModel } from '@server/models/view/local-video-viewer-watch-section'
12 import { isTestInstance } from '../helpers/core-utils'
13 import { logger } from '../helpers/logger'
14 import { AbuseModel } from '../models/abuse/abuse'
15 import { AbuseMessageModel } from '../models/abuse/abuse-message'
16 import { VideoAbuseModel } from '../models/abuse/video-abuse'
17 import { VideoCommentAbuseModel } from '../models/abuse/video-comment-abuse'
18 import { AccountModel } from '../models/account/account'
19 import { AccountBlocklistModel } from '../models/account/account-blocklist'
20 import { AccountVideoRateModel } from '../models/account/account-video-rate'
21 import { ActorModel } from '../models/actor/actor'
22 import { ActorFollowModel } from '../models/actor/actor-follow'
23 import { ActorImageModel } from '../models/actor/actor-image'
24 import { ApplicationModel } from '../models/application/application'
25 import { OAuthClientModel } from '../models/oauth/oauth-client'
26 import { OAuthTokenModel } from '../models/oauth/oauth-token'
27 import { VideoRedundancyModel } from '../models/redundancy/video-redundancy'
28 import { PluginModel } from '../models/server/plugin'
29 import { ServerModel } from '../models/server/server'
30 import { ServerBlocklistModel } from '../models/server/server-blocklist'
31 import { UserNotificationSettingModel } from '../models/user/user-notification-setting'
32 import { ScheduleVideoUpdateModel } from '../models/video/schedule-video-update'
33 import { TagModel } from '../models/video/tag'
34 import { ThumbnailModel } from '../models/video/thumbnail'
35 import { VideoModel } from '../models/video/video'
36 import { VideoBlacklistModel } from '../models/video/video-blacklist'
37 import { VideoCaptionModel } from '../models/video/video-caption'
38 import { VideoChangeOwnershipModel } from '../models/video/video-change-ownership'
39 import { VideoChannelModel } from '../models/video/video-channel'
40 import { VideoCommentModel } from '../models/video/video-comment'
41 import { VideoFileModel } from '../models/video/video-file'
42 import { VideoImportModel } from '../models/video/video-import'
43 import { VideoLiveModel } from '../models/video/video-live'
44 import { VideoPlaylistModel } from '../models/video/video-playlist'
45 import { VideoPlaylistElementModel } from '../models/video/video-playlist-element'
46 import { VideoShareModel } from '../models/video/video-share'
47 import { VideoStreamingPlaylistModel } from '../models/video/video-streaming-playlist'
48 import { VideoTagModel } from '../models/video/video-tag'
49 import { VideoViewModel } from '../models/view/video-view'
50 import { CONFIG } from './config'
52 require('pg').defaults.parseInt8 = true // Avoid BIGINT to be converted to string
54 const dbname = CONFIG.DATABASE.DBNAME
55 const username = CONFIG.DATABASE.USERNAME
56 const password = CONFIG.DATABASE.PASSWORD
57 const host = CONFIG.DATABASE.HOSTNAME
58 const port = CONFIG.DATABASE.PORT
59 const poolMax = CONFIG.DATABASE.POOL.MAX
61 let dialectOptions: any = {}
63 if (CONFIG.DATABASE.SSL) {
66 rejectUnauthorized: false
71 const sequelizeTypescript = new SequelizeTypescript({
82 benchmark: isTestInstance(),
83 isolationLevel: Transaction.ISOLATION_LEVELS.SERIALIZABLE,
84 logging: (message: string, benchmark: number) => {
85 if (process.env.NODE_DB_LOG === 'false') return
87 let newMessage = 'Executed SQL request'
88 if (isTestInstance() === true && benchmark !== undefined) {
89 newMessage += ' in ' + benchmark + 'ms'
92 logger.debug(newMessage, { sql: message, tags: [ 'sql' ] })
96 function checkDatabaseConnectionOrDie () {
97 sequelizeTypescript.authenticate()
98 .then(() => logger.debug('Connection to PostgreSQL has been established successfully.'))
101 logger.error('Unable to connect to PostgreSQL database.', { err })
106 async function initDatabaseModels (silent: boolean) {
107 sequelizeTypescript.addModels([
117 AccountVideoRateModel,
121 VideoCommentAbuseModel,
124 VideoChangeOwnershipModel,
132 ScheduleVideoUpdateModel,
135 VideoRedundancyModel,
136 UserVideoHistoryModel,
138 AccountBlocklistModel,
139 ServerBlocklistModel,
140 UserNotificationModel,
141 UserNotificationSettingModel,
142 VideoStreamingPlaylistModel,
144 VideoPlaylistElementModel,
145 LocalVideoViewerModel,
146 LocalVideoViewerWatchSectionModel,
151 ActorCustomPageModel,
155 // Check extensions exist in the database
156 await checkPostgresExtensions()
158 // Create custom PostgreSQL functions
159 await createFunctions()
161 if (!silent) logger.info('Database %s is ready.', dbname)
164 // ---------------------------------------------------------------------------
168 checkDatabaseConnectionOrDie,
172 // ---------------------------------------------------------------------------
174 async function checkPostgresExtensions () {
176 checkPostgresExtension('pg_trgm'),
177 checkPostgresExtension('unaccent')
180 return Promise.all(promises)
183 async function checkPostgresExtension (extension: string) {
184 const query = `SELECT 1 FROM pg_available_extensions WHERE name = '${extension}' AND installed_version IS NOT NULL;`
186 type: QueryTypes.SELECT as QueryTypes.SELECT,
190 const res = await sequelizeTypescript.query<object>(query, options)
192 if (!res || res.length === 0) {
193 // Try to create the extension ourselves
195 await sequelizeTypescript.query(`CREATE EXTENSION ${extension};`, { raw: true })
198 const errorMessage = `You need to enable ${extension} extension in PostgreSQL. ` +
199 `You can do so by running 'CREATE EXTENSION ${extension};' as a PostgreSQL super user in ${CONFIG.DATABASE.DBNAME} database.`
200 throw new Error(errorMessage)
205 function createFunctions () {
206 const query = `CREATE OR REPLACE FUNCTION immutable_unaccent(text)
209 SELECT public.unaccent('public.unaccent', $1::text)
210 $func$ LANGUAGE sql IMMUTABLE;`
212 return sequelizeTypescript.query(query, { raw: true })