Skip to content
This repository was archived by the owner on Aug 23, 2025. It is now read-only.

Commit 04f845e

Browse files
rickbergfalkrickbergfalk
authored
Implement configurable session store (#807)
* Implement memory session storage * Implement database session storage * Implement redis session storage * Run redis for GitHub tests * Rename filesystem to file (system) * Use memory session store for test default
1 parent e7a3141 commit 04f845e

15 files changed

Lines changed: 635 additions & 86 deletions

File tree

‎.github/workflows/test.yml‎

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -38,7 +38,7 @@ jobs:
3838
strategy:
3939
fail-fast: false # if one job fails, others will not be aborted
4040
matrix:
41-
backendtype: [ '', 'mssql','mysql', 'mariadb', 'postgres' ]
41+
backendtype: ['', 'mssql', 'mysql', 'mariadb', 'postgres']
4242
node-version: [12.x]
4343

4444
steps:
@@ -49,6 +49,9 @@ jobs:
4949
with:
5050
node-version: ${{ matrix.node-version }}
5151

52+
- name: Start redis
53+
run: docker-compose -f server/docker-compose.yml up -d redis &
54+
5255
- name: Start service for backend server
5356
if: ${{ matrix.backendtype }}
5457
run: docker-compose -f server/docker-compose.yml up -d ${{ matrix.backendtype }} &

‎CHANGELOG.md‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -47,7 +47,7 @@ Version 5 contains many infrastructure and API changes, as well as migrations th
4747

4848
This release finishes the migration from an embedded JSON database to an ORM and SQLite (used by default). For new instances of SQLPad, the following alternative backend databases may be used: SQL Server, MySQL, MariaDB, or PostgreSQL via `SQLPAD_BACKEND_DB_URI`.
4949

50-
Providing a value for `SQLPAD_DB_PATH`/`dbPath` is still required, as the filesystem is still used for sessions and storage of query results. This requirement will be removed in a later 5.x release.
50+
Providing a value for `SQLPAD_DB_PATH`/`dbPath` is still required, as the file system is still used for sessions and storage of query results. This requirement will be removed in a later 5.x release.
5151

5252
Migrations will be run on SQLPad start up. To disable this behavior, set `SQLPAD_DB_AUTOMIGRATE` to `false`. Migrations may be run manually via `node server.js --config path/to/file.ext --migrate`. When using `--migrate` the process will exit after applying migrations.
5353

@@ -205,12 +205,12 @@ Special thanks to @eladeyal-intel, @bruth, @yorek, @dengc367, @murphyke, and @Wi
205205
- Add GitHub Release builds via build pipeline (#550)
206206
- Add `dbInMemory` setting (#553)
207207

208-
`dbInMemory` will run the embedded SQLPad db in memory without logging to disk. Enabling this does not remove the need for `dbPath` at this time, as filesystem access is still required for result caches and express session support. (`dbPath` to become optional in future release)
208+
`dbInMemory` will run the embedded SQLPad db in memory without logging to disk. Enabling this does not remove the need for `dbPath` at this time, as file system access is still required for result caches and express session support. (`dbPath` to become optional in future release)
209209

210210
### Fixes
211211

212212
- Fix TypeError: Do not know how to serialize a BigInt (#522)
213-
- Fix tests for non-utc timezones (#524)
213+
- Fix tests for non-utc time zones (#524)
214214

215215
### Maintenance
216216

‎docs/api-batches.md‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@ Prior to v5, queries were run in SQLPad with an HTTP `POST`, with the query resu
1010

1111
While nice and simple, it has some downsides:
1212

13-
- long queries require long HTTP timeout configurations (not ideal, requires additional configuration to load balancers, proxy, etc)
13+
- long queries require long HTTP timeout configurations (not ideal, requires additional configuration to load balancers, proxy, etc.)
1414
- An execution with multiple statements/queries would require all queries to finish before results are sent back. Results would have to be in single response (might be too big)
1515

1616
As of v5 new restful APIs have replaced the existing query-result API.
@@ -21,9 +21,9 @@ These newly created objects are returned as soon as they are created, each conta
2121

2222
Immediately following creation, the batch is executed, each statement sequentially, under the same connection if the database driver supports it. On error, further statements in the batch are stopped, and the statement and batch in question is marked as status `error`.
2323

24-
On success, the batch and statement are marked with status `finished`. Query results are written to the filesystem as a JSON file under the `results` directory inside the directory specified by the `dbPath` configuration variabled.
24+
On success, the batch and statement are marked with status `finished`. Query results are written to the file system as a JSON file under the `results` directory inside the directory specified by the `dbPath` configuration variable.
2525

26-
These results on the filesystem are kept according to the `queryHistoryRetentionTimeInDays` setting, which defaults to 30 days.
26+
These results on the file system are kept according to the `queryHistoryRetentionTimeInDays` setting, which defaults to 30 days.
2727

2828
## Creating a Batch
2929

‎docs/configuration.md‎

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,14 @@ SQLPAD_TIMEOUT_SECONDS = 300
4747
# Minutes to keep a session active. Session will be extended by this amount each request.
4848
SQLPAD_SESSION_MINUTES = 60
4949

50+
# Store to use for user session
51+
# Valid values are `file` (default), `database`, `redis`, `memory`
52+
# `file` uses files in the sessions directory under SQLPAD_DB_PATH
53+
# `memory` may be used for single sqlpad instances, and works well for no-auth setups
54+
# `redis` offers best performance and is most commonly used. SQLPAD_REDIS_URI must also be set.
55+
# `database` will use whatever backend database is used (or SQLite if SQLPAD_DB_PATH is set)
56+
SQLPAD_SESSION_STORE = "file"
57+
5058
# Name used for cookie. If running multiple SQLPads on same domain, set to different values.
5159
SQLPAD_COOKIE_NAME = "sqlpad.sid"
5260

@@ -90,6 +98,17 @@ SQLPAD_QUERY_HISTORY_RESULT_MAX_ROWS = 1000
9098
SQLPAD_DEFAULT_CONNECTION_ID = ""
9199
```
92100

101+
## Redis
102+
103+
Redis may be used for session storage as of `5.3.0`.
104+
105+
```bash
106+
# URI for redis instance to use for user sessions if configured
107+
# Format should be [redis[s]:]//[[user][:password@]][host][:port][/db-number][?db=db-number[&password=bar[&option=value]]]
108+
# More info at http://www.iana.org/assignments/uri-schemes/prov/redis
109+
SQLPAD_REDIS_URI = ""
110+
```
111+
93112
## Backend Database Management
94113

95114
SQLPad may be configured to use SQLite, PostgreSQL, MySQL, MariaDB, or SQL Server as a backing database.

‎docs/getting-started.md‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -4,7 +4,7 @@
44

55
There are 2 options to run SQLPad: Install [Node.js](https://nodejs.org/) and [build and run SQLPad from the git repository](https://github.com/rickbergfalk/sqlpad/blob/master/DEVELOPER-GUIDE.md), or use the [docker images on Docker Hub](https://hub.docker.com/r/sqlpad/sqlpad/).
66

7-
SQLPad does not require any additional servers other than its own self. By default it uses SQLite and the filesystem for storing queries, query results, and web sessions. SQLite may be replaced with an external database using the `SQLPAD_BACKEND_DB_URI` environment variable as of v5.
7+
SQLPad does not require any additional servers other than its own self. By default it uses SQLite and the file system for storing queries, query results, and web sessions. SQLite may be replaced with an external database using the `SQLPAD_BACKEND_DB_URI` environment variable as of v5.
88

99
The `SQLPAD_DB_PATH` must still be provided however, as it is used for sessions and query result storage. This requirement will be removed in a later 5.x release.
1010

‎server/app.js‎

Lines changed: 57 additions & 13 deletions
Original file line numberDiff line numberDiff line change
@@ -3,8 +3,12 @@ const path = require('path');
33
const express = require('express');
44
const helmet = require('helmet');
55
const pino = require('pino');
6+
const redis = require('redis');
67
const session = require('express-session');
78
const FileStore = require('session-file-store')(session);
9+
const MemoryStore = require('memorystore')(session);
10+
const SequelizeStore = require('connect-session-sequelize')(session.Store);
11+
const RedisStore = require('connect-redis')(session);
812
const appLog = require('./lib/app-log');
913
const Webhooks = require('./lib/webhooks.js');
1014
const bodyParser = require('body-parser');
@@ -99,22 +103,62 @@ async function makeApp(config, models) {
99103
);
100104

101105
const cookieMaxAgeMs = parseInt(config.get('sessionMinutes'), 10) * 60 * 1000;
102-
const sessionPath = path.join(config.get('dbPath'), '/sessions');
103106

104-
app.use(
105-
session({
106-
store: new FileStore({
107+
const sessionOptions = {
108+
saveUninitialized: false,
109+
resave: true,
110+
rolling: true,
111+
cookie: { maxAge: cookieMaxAgeMs },
112+
secret: config.get('cookieSecret'),
113+
name: config.get('cookieName'),
114+
};
115+
const sessionStore = config.get('sessionStore').toLowerCase();
116+
117+
switch (sessionStore) {
118+
case 'file': {
119+
const sessionPath = path.join(config.get('dbPath'), '/sessions');
120+
sessionOptions.store = new FileStore({
107121
path: sessionPath,
108122
logFn: () => {},
109-
}),
110-
saveUninitialized: false,
111-
resave: true,
112-
rolling: true,
113-
cookie: { maxAge: cookieMaxAgeMs },
114-
secret: config.get('cookieSecret'),
115-
name: config.get('cookieName'),
116-
})
117-
);
123+
});
124+
break;
125+
}
126+
case 'memory': {
127+
sessionOptions.store = new MemoryStore({
128+
checkPeriod: cookieMaxAgeMs,
129+
});
130+
break;
131+
}
132+
case 'database': {
133+
sessionOptions.store = new SequelizeStore({
134+
db: models.sequelizeDb.sequelize,
135+
table: 'Sessions',
136+
});
137+
// SequelizeStore supports the touch method so per the express-session docs this should be set to false
138+
sessionOptions.resave = false;
139+
// SequelizeStore docs mention setting this to true if SSL is done outside of Node
140+
// Not sure we have any way of knowing based on current config
141+
// sessionOptions.proxy = true;
142+
break;
143+
}
144+
case 'redis': {
145+
const redisUri = config.get('redisUri');
146+
if (!redisUri) {
147+
throw new Error(
148+
`Redis session store requires SQLPAD_REDIS_URI to be set`
149+
);
150+
}
151+
const redisClient = redis.createClient(redisUri);
152+
sessionOptions.store = new RedisStore({ client: redisClient });
153+
sessionOptions.resave = false;
154+
break;
155+
}
156+
default: {
157+
throw new Error(`Invalid session store ${sessionStore}`);
158+
}
159+
}
160+
161+
app.use(session(sessionOptions));
118162

119163
const baseUrl = config.get('baseUrl');
120164

‎server/docker-compose.yml‎

Lines changed: 31 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,10 @@
11
version: '3'
22
services:
3+
redis:
4+
image: redis:6-alpine
5+
ports:
6+
- 6379:6379
7+
38
mssql:
49
image: 'mcr.microsoft.com/mssql/server:2017-latest'
510
hostname: 'mssql'
@@ -10,10 +15,22 @@ services:
1015
- MSSQL_SA_PASSWORD=SuperP4ssw0rd!
1116
- MSSQL_PID=Express
1217
healthcheck:
13-
test: ["CMD", "/opt/mssql-tools/bin/sqlcmd", "-S", "localhost", "-U", "sa", "-P", "SuperP4ssw0rd!", "-Q", "SELECT 1" ]
14-
interval: 3s
15-
timeout: 3s
16-
retries: 10
18+
test:
19+
[
20+
'CMD',
21+
'/opt/mssql-tools/bin/sqlcmd',
22+
'-S',
23+
'localhost',
24+
'-U',
25+
'sa',
26+
'-P',
27+
'SuperP4ssw0rd!',
28+
'-Q',
29+
'SELECT 1',
30+
]
31+
interval: 3s
32+
timeout: 3s
33+
retries: 10
1734

1835
postgres:
1936
image: postgres:9.6-alpine
@@ -23,9 +40,9 @@ services:
2340
POSTGRES_DB: sqlpad
2441
ports:
2542
- '5432:5432'
26-
healthcheck:
27-
test: ["CMD", "pg_isready"]
28-
interval: 5s
43+
healthcheck:
44+
test: ['CMD', 'pg_isready']
45+
interval: 5s
2946
timeout: 2s
3047
retries: 10
3148

@@ -38,12 +55,12 @@ services:
3855
MYSQL_PASSWORD: password
3956
MYSQL_DATABASE: db
4057
MYSQL_ROOT_PASSWORD: password
41-
healthcheck:
42-
test: ["CMD", "mysqladmin", "status", "-uroot", "-ppassword"]
43-
interval: 5s
58+
healthcheck:
59+
test: ['CMD', 'mysqladmin', 'status', '-uroot', '-ppassword']
60+
interval: 5s
4461
timeout: 2s
4562
retries: 10
46-
63+
4764
mysql:
4865
image: mysql:8
4966
ports:
@@ -53,9 +70,8 @@ services:
5370
MYSQL_PASSWORD: password
5471
MYSQL_DATABASE: db2
5572
MYSQL_ROOT_PASSWORD: root
56-
healthcheck:
57-
test: ["CMD", "mysql", "-uuser", "-ppassword", "-e", "select 1"]
58-
interval: 5s
73+
healthcheck:
74+
test: ['CMD', 'mysql', '-uuser', '-ppassword', '-e', 'select 1']
75+
interval: 5s
5976
timeout: 2s
6077
retries: 10
61-

‎server/lib/config/config-items.js‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -29,6 +29,11 @@ const configItems = [
2929
envVar: 'SQLPAD_SESSION_MINUTES',
3030
default: 60,
3131
},
32+
{
33+
key: 'sessionStore',
34+
envVar: 'SQLPAD_SESSION_STORE',
35+
default: 'file', // database, redis, memory
36+
},
3237
{
3338
key: 'timeoutSeconds',
3439
envVar: 'SQLPAD_TIMEOUT_SECONDS',
@@ -428,6 +433,11 @@ const configItems = [
428433
envVar: 'SQLPAD_BACKEND_DB_URI',
429434
default: '',
430435
},
436+
{
437+
key: 'redisUri',
438+
envVar: 'SQLPAD_REDIS_URI',
439+
default: '',
440+
},
431441
{
432442
key: 'seedDataPath',
433443
envVar: 'SQLPAD_SEED_DATA_PATH',

‎server/migrations/05-00100-sessions.js‎

Lines changed: 36 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,36 @@
1+
const Sequelize = require('sequelize');
2+
3+
/**
4+
* @param {import('sequelize').QueryInterface} queryInterface
5+
* @param {import('../lib/config')} config
6+
* @param {import('../lib/logger')} appLog
7+
* @param {object} nedb - collection of nedb objects created in /lib/db.js
8+
*/
9+
// eslint-disable-next-line no-unused-vars
10+
async function up(queryInterface, config, appLog, nedb) {
11+
/**
12+
* sessions table is used for web user sessions
13+
*/
14+
await queryInterface.createTable('sessions', {
15+
sid: {
16+
type: Sequelize.STRING(36),
17+
primaryKey: true,
18+
},
19+
expires: {
20+
type: Sequelize.DATE,
21+
},
22+
data: {
23+
type: Sequelize.TEXT,
24+
},
25+
created_at: {
26+
type: Sequelize.DATE,
27+
},
28+
updated_at: {
29+
type: Sequelize.DATE,
30+
},
31+
});
32+
}
33+
34+
module.exports = {
35+
up,
36+
};

0 commit comments

Comments
 (0)