path: root/2024/captions/emacsconf-2024-pgmacs--pgmacs-browsing-and-editing-postgresql-databases-from-emacs--eric-marsden--main.vtt

WEBVTT captioned by eric

NOTE Introduction

1
00:00:01.260 --> 00:00:03.980
Hi, this is a short presentation about PGmacs,

2
00:00:04.180 --> 00:00:07.100
which is a browsing and editing interface for

3
00:00:07.100 --> 00:00:08.360
Postgres databases.

4
00:00:08.920 --> 00:00:11.320
My name is Eric Marsden, and I'm the

5
00:00:11.320 --> 00:00:13.540
developer of this Emacs Lisp library.

6
00:00:14.800 --> 00:00:17.420
PGmacs was inspired by sqlite-mode, which is a

7
00:00:17.420 --> 00:00:20.600
great feature available from Emacs 29 onwards.

8
00:00:21.060 --> 00:00:24.560
It allows you to view and modify SQLite

9
00:00:24.560 --> 00:00:26.220
databases you might have lying around.

10
00:00:26.760 --> 00:00:28.140
It's a really great feature.

11
00:00:28.140 --> 00:00:30.840
It would be even better, however, if it

12
00:00:30.840 --> 00:00:32.900
were able to use a real database.

13
00:00:34.540 --> 00:00:36.200
And I happen to know that was possible

14
00:00:36.200 --> 00:00:38.120
because a few years ago, I wrote an

15
00:00:38.120 --> 00:00:42.920
Emacs Lisp library, pg.el, which implements the wire

16
00:00:42.920 --> 00:00:46.280
protocol used for communication over the network between

17
00:00:46.280 --> 00:00:49.600
a Postgres client and the Postgres backend, the

18
00:00:49.600 --> 00:00:50.440
Postgres server.

19
00:00:51.580 --> 00:00:53.560
I've included here an example of what it

20
00:00:53.560 --> 00:00:54.820
looks like to use this library.

21
00:00:54.820 --> 00:00:58.240
You connect to the database by specifying the

22
00:00:58.240 --> 00:01:01.520
database name, your username, your password, potentially the

23
00:01:01.520 --> 00:01:03.099
hostname that you're connecting to.

24
00:01:03.800 --> 00:01:06.680
Once you're connected, you can then execute SQL

25
00:01:06.680 --> 00:01:09.640
statements, for example, to create a new table,

26
00:01:10.180 --> 00:01:13.860
to insert values into that table, and to

27
00:01:13.860 --> 00:01:16.140
count the number of rows in a table.

28
00:01:17.100 --> 00:01:20.300
And pg.el does automatic type conversion to

29
00:01:20.300 --> 00:01:24.180
and from Emacs Lisp types and Postgres types for

30
00:01:24.180 --> 00:01:24.380
you.

NOTE Demo

31
00:01:26.710 --> 00:01:29.170
Probably the best is to jump straight into

32
00:01:29.170 --> 00:01:30.690
a demo of PGmacs.

33
00:01:31.210 --> 00:01:34.130
When we connect to a database, we see

34
00:01:34.130 --> 00:01:36.330
up the top of the buffer, some information

35
00:01:36.330 --> 00:01:39.070
about the backend that we're connected to, some

36
00:01:39.070 --> 00:01:42.010
version information and the total database size on

37
00:01:42.010 --> 00:01:42.230
disk.

38
00:01:43.090 --> 00:01:45.170
There are some shortcuts to some commands we

39
00:01:45.170 --> 00:01:47.370
can run on the database, and there's a

40
00:01:47.370 --> 00:01:49.490
list of tables that we have access to,

41
00:01:50.230 --> 00:01:52.650
with again, metainformation about their size on

42
00:01:52.650 --> 00:01:53.650
disk and their owner.

43
00:01:53.650 --> 00:01:56.490
Now, we can enter a table by pressing

44
00:01:56.490 --> 00:01:57.090
return.

45
00:01:58.510 --> 00:02:01.230
Here we again see some metainformation about

46
00:02:01.230 --> 00:02:04.210
the table, such as the list of columns,

47
00:02:05.510 --> 00:02:08.490
the SQL type of each column, any defaults

48
00:02:08.490 --> 00:02:11.170
that might be present and any SQL constraints.

49
00:02:11.970 --> 00:02:14.370
We see any indexes that might be present

50
00:02:14.370 --> 00:02:16.790
on this table, and then we see the

51
00:02:16.790 --> 00:02:19.070
list of rows of data in the table.

52
00:02:19.830 --> 00:02:22.870
If we see any information which is incorrect

53
00:02:22.870 --> 00:02:26.710
or incomplete, we can easily fix it by

54
00:02:26.710 --> 00:02:36.300
pressing enter again, and this updates the information

55
00:02:36.300 --> 00:02:37.900
present in the database.

56
00:02:38.200 --> 00:02:39.840
PGmacs shows us this in the minibuffer

57
00:02:39.840 --> 00:02:42.480
it has updated one row.

58
00:02:43.540 --> 00:02:45.440
We can look at help for the key

59
00:02:45.440 --> 00:02:47.440
bindings that are present in this type of

60
00:02:47.440 --> 00:02:47.780
buffer.

61
00:02:48.120 --> 00:02:49.580
If we scroll down, we can see that

62
00:02:49.580 --> 00:02:51.580
we can upcase the value of a cell

63
00:02:51.580 --> 00:02:52.680
with M-u.

64
00:02:52.920 --> 00:02:53.920
Let's try that out.

65
00:02:56.430 --> 00:02:58.950
Okay, here we have updated the value of

66
00:02:58.950 --> 00:03:00.670
the cell in the database.

67
00:03:00.830 --> 00:03:03.330
We can now lowercase it, and we can

68
00:03:03.330 --> 00:03:06.510
put it back to the initial

69
00:03:06.510 --> 00:03:07.930
title case of the value.

70
00:03:09.010 --> 00:03:11.550
We can also run a shell command on

71
00:03:11.550 --> 00:03:14.570
a cell value, to count the number of

72
00:03:14.570 --> 00:03:15.670
characters, for example.

73
00:03:17.570 --> 00:03:20.390
We can run a shell command with a

74
00:03:20.390 --> 00:03:22.990
prefix argument, which updates the value in the

75
00:03:22.990 --> 00:03:25.610
database with the output from the shell command.

76
00:03:27.470 --> 00:03:29.790
So that has updated the database.

77
00:03:30.270 --> 00:03:32.790
If we do that again, that will reverse

78
00:03:32.790 --> 00:03:39.750
the value. If we come back

79
00:03:39.750 --> 00:03:41.590
to the list of tables, we see

80
00:03:41.590 --> 00:03:43.010
that there is a table which is called

81
00:03:43.010 --> 00:03:43.590
deleteme.

82
00:03:43.890 --> 00:03:50.140
We can try renaming this table with R,

83
00:03:50.380 --> 00:03:52.880
we can look at what is in this

84
00:03:52.880 --> 00:03:53.280
table.

NOTE Deletion

85
00:03:53.960 --> 00:03:56.600
Okay, there is only one row of information

86
00:03:56.600 --> 00:03:59.280
that doesn't seem very important, so let's delete

87
00:03:59.280 --> 00:03:59.680
that.

88
00:04:02.160 --> 00:04:05.600
Let's now delete the table with DEL.

89
00:04:07.980 --> 00:04:11.000
PGmacs asks for confirmation, and we've deleted the

90
00:04:11.000 --> 00:04:11.240
table.

91
00:04:13.390 --> 00:04:15.470
Now when we have a big table with

92
00:04:15.470 --> 00:04:18.250
a lot of data, PGmacs is going to

93
00:04:18.250 --> 00:04:21.150
show us the results paginated, so we can

94
00:04:21.150 --> 00:04:23.350
go chunk by chunk through the table.

95
00:04:24.370 --> 00:04:28.070
We can implement a where filter on the

96
00:04:28.070 --> 00:04:30.570
rows to only show the rows that match

97
00:04:30.570 --> 00:04:32.390
a certain SQL clause.

98
00:04:33.110 --> 00:04:35.450
So for example, here we have some temperature

99
00:04:35.450 --> 00:04:35.950
measurements.

100
00:04:36.490 --> 00:04:38.690
We want to focus on measurements that are

101
00:04:38.690 --> 00:04:41.270
more than 40 degrees Celsius, for example.

102
00:04:45.980 --> 00:04:48.120
Okay, we have now filtered on a certain

103
00:04:48.120 --> 00:04:49.240
number of rows.

104
00:04:49.540 --> 00:04:51.240
The filter is shown to us just up

105
00:04:51.240 --> 00:04:53.540
here, and we see here that we have

106
00:04:53.540 --> 00:04:55.960
some values that look anomalous.

107
00:04:56.500 --> 00:04:59.120
There's one, 140, and one is 61.

108
00:04:59.840 --> 00:05:02.980
We probably want to delete them, they represent

109
00:05:02.980 --> 00:05:03.540
errors.

110
00:05:04.080 --> 00:05:06.080
We can mark them for deletion with d,

111
00:05:06.560 --> 00:05:09.680
and then really delete them, expunge them, as

112
00:05:09.680 --> 00:05:11.160
in Dired, with x.

NOTE Export

113
00:05:12.880 --> 00:05:16.120
We can export this table in CSV format,

114
00:05:16.480 --> 00:05:17.800
thanks to this little button up here.

115
00:05:19.860 --> 00:05:22.760
Here we have our table conveniently formatted as

116
00:05:22.760 --> 00:05:23.400
CSV.

117
00:05:23.400 --> 00:05:28.480
We can also export a particular row to

118
00:05:28.480 --> 00:05:32.080
JSON by typing j, let's look at

119
00:05:32.080 --> 00:05:36.270
what that looks like.

120
00:05:37.810 --> 00:05:40.230
Okay, here's the JSON for one of the

121
00:05:40.230 --> 00:05:41.190
rows in the table.

NOTE HStore

122
00:05:42.250 --> 00:05:44.270
Now let's look at a table that contains

123
00:05:44.270 --> 00:05:46.350
a column of type HStore.

124
00:05:46.430 --> 00:05:48.850
This is a Postgres-specific key-value map.

125
00:05:49.110 --> 00:05:51.170
Then the attributes column is of type

126
00:05:51.170 --> 00:05:54.750
JSON, which can be stored natively in Postgres.

127
00:05:54.750 --> 00:05:56.910
It's possible to edit these values using a

128
00:05:56.910 --> 00:06:00.490
widget-based interface by typing w, and here

129
00:06:00.490 --> 00:06:03.630
we have a convenient interface for modifying these

130
00:06:03.630 --> 00:06:04.450
types of values.

131
00:06:05.410 --> 00:06:08.390
Likewise, for the JSON type parameter, we can

132
00:06:08.390 --> 00:06:10.790
update using a widget-based interface.

NOTE Connecting to a different database

133
00:06:11.510 --> 00:06:14.270
Let's connect to a different database.

134
00:06:14.490 --> 00:06:18.910
We do that with a M-x pgmacs, and

135
00:06:18.910 --> 00:06:26.670
then enter our username and password.

136
00:06:27.310 --> 00:06:29.390
Here we have the list of tables present

137
00:06:29.390 --> 00:06:30.390
in this database.

NOTE SchemaSpy

138
00:06:31.110 --> 00:06:33.690
PGmacs has some support for running the SchemaSpy

139
00:06:33.690 --> 00:06:37.950
utility, which generates a graphical representation of the

140
00:06:37.950 --> 00:06:41.710
relationships between tables in this database, which can

141
00:06:41.710 --> 00:06:44.470
be useful when you're discovering the database.

142
00:06:45.170 --> 00:06:47.870
Here is the SVG that's output by this

143
00:06:47.870 --> 00:06:48.470
utility.

144
00:06:50.860 --> 00:06:53.840
This is a test database containing information about

145
00:06:53.840 --> 00:06:55.460
a digital media store.

146
00:06:55.880 --> 00:06:58.160
It has a table with information about the

147
00:06:58.160 --> 00:06:59.300
tracks that are available.

148
00:07:00.960 --> 00:07:03.480
If we look at this table, we

149
00:07:03.480 --> 00:07:04.780
see that we have the name of the

150
00:07:04.780 --> 00:07:08.980
track, a reference to the album, album ID.

151
00:07:09.240 --> 00:07:11.240
This is a reference to a foreign key,

152
00:07:11.660 --> 00:07:13.520
a key in the table which is called

153
00:07:13.520 --> 00:07:13.940
album.

154
00:07:14.820 --> 00:07:16.840
Now we can follow this foreign key reference

155
00:07:16.840 --> 00:07:20.580
simply by pressing Enter, and here we find

156
00:07:20.580 --> 00:07:23.100
it's the album called For Those About To

157
00:07:23.100 --> 00:07:23.340
Rock.

158
00:07:24.260 --> 00:07:26.420
And in the same way, here we have

159
00:07:26.420 --> 00:07:28.780
a foreign key reference to the relevant artist

160
00:07:28.780 --> 00:07:29.680
which we can follow.

161
00:07:30.080 --> 00:07:31.800
That's, of course, AC/DC.

NOTE Convenience queries

162
00:07:32.620 --> 00:07:35.720
And finally, PGmacs has some convenience queries that

163
00:07:35.720 --> 00:07:38.280
allows us to list the procedures which are

164
00:07:38.280 --> 00:07:39.820
defined in this database.

165
00:07:40.260 --> 00:07:42.820
Here we have the built-in procedures, mostly

166
00:07:42.820 --> 00:07:45.300
used by the PostGIS extension.

167
00:07:48.430 --> 00:07:52.110
We can also display some more information about

168
00:07:52.110 --> 00:07:52.690
our backend.

169
00:07:53.090 --> 00:07:56.450
Here we have the list of extensions which

170
00:07:56.450 --> 00:07:56.990
are available.

171
00:07:57.470 --> 00:08:00.250
We can load one of these extensions if

172
00:08:00.250 --> 00:08:01.570
we have the rights to do that.

173
00:08:02.350 --> 00:08:05.290
Here we've loaded the relevant extension.

174
00:08:06.210 --> 00:08:08.850
We can show some information about the Postgres

175
00:08:08.850 --> 00:08:16.650
settings also, and update them if we have

176
00:08:16.650 --> 00:08:17.790
the rights to do that.

NOTE Emacs as an application development platform

177
00:08:18.850 --> 00:08:21.650
Now I'm preaching to the converted, but Emacs

178
00:08:21.650 --> 00:08:24.870
is a really great application development platform.

179
00:08:25.510 --> 00:08:29.130
PGmacs is currently around 3000 lines of code.

180
00:08:29.630 --> 00:08:32.510
A first point of comparison, PGCLI, which is

181
00:08:32.510 --> 00:08:36.409
a text user interface for accessing Postgres implemented

182
00:08:36.409 --> 00:08:39.289
in Python, is 17,000 lines of code.

183
00:08:40.390 --> 00:08:43.190
PGAdmin4, which is a well-known Python GUI

184
00:08:43.190 --> 00:08:47.010
for administrating Postgres databases, is almost half a

185
00:08:47.010 --> 00:08:48.050
million lines of code.

186
00:08:48.410 --> 00:08:51.450
And DBeaver, implemented in Java, is almost a

187
00:08:51.450 --> 00:08:52.490
million lines of code.

188
00:08:53.130 --> 00:08:56.030
Developing things on Emacs, you get for

189
00:08:56.030 --> 00:08:58.270
free portability between different platforms.

190
00:08:58.470 --> 00:09:00.130
As I'm showing you, it works great on

191
00:09:00.130 --> 00:09:02.730
Linux, which is where I developed this library.

192
00:09:03.070 --> 00:09:06.530
It also works perfectly in the terminal, except

193
00:09:06.530 --> 00:09:08.530
for some of the functionality I showed, which

194
00:09:08.530 --> 00:09:10.190
requires SVG support.

195
00:09:10.970 --> 00:09:12.630
It also works well, if you're into that

196
00:09:12.630 --> 00:09:15.230
kind of thing, on MacOS and on Windows.

197
00:09:15.830 --> 00:09:19.130
And it even works perfectly on Haiku, which

198
00:09:19.130 --> 00:09:20.770
is a free BeOS clone.

199
00:09:21.300 --> 00:09:23.870
Emacs is actually really pretty on this operating

200
00:09:23.870 --> 00:09:26.590
system, so congratulations to the people who did

201
00:09:26.590 --> 00:09:27.330
the port there.

202
00:09:27.530 --> 00:09:29.350
Now, of course, the main advantage of building

203
00:09:29.350 --> 00:09:32.430
on the Emacs development platform, is that the

204
00:09:32.430 --> 00:09:35.590
application is easy for the user to extend.

NOTE Extending pgmacs

205
00:09:36.250 --> 00:09:38.690
To illustrate that, previously we were looking at

206
00:09:38.690 --> 00:09:40.650
a table of temperature measurements.

207
00:09:42.070 --> 00:09:44.510
Imagine we want to highlight rows in this

208
00:09:44.510 --> 00:09:48.430
table, which look anomalous, where the value looks

209
00:09:48.430 --> 00:09:49.450
a bit extreme.

210
00:09:50.090 --> 00:09:52.150
That's quite easy to do with a bit

211
00:09:52.150 --> 00:09:53.130
of Emacs Lisp.

212
00:09:54.610 --> 00:09:58.410
We define a function, which, if the cell

213
00:09:58.410 --> 00:10:01.570
value is bigger than 40, is going to

214
00:10:01.570 --> 00:10:03.850
display it in a face which has a

215
00:10:03.850 --> 00:10:04.950
dark red foreground.

216
00:10:05.950 --> 00:10:08.890
We can now register this display function for

217
00:10:08.890 --> 00:10:11.590
the measurement column in the temperatures table.

218
00:10:12.190 --> 00:10:16.490
And if we reopen the table now, we

219
00:10:16.490 --> 00:10:19.950
see that the anomalous measurements are indeed highlighted

220
00:10:19.950 --> 00:10:20.810
in red.

221
00:10:24.100 --> 00:10:27.320
Another example, imagine we have a table which

222
00:10:27.320 --> 00:10:28.880
contains image data.

223
00:10:29.200 --> 00:10:31.740
There's a column which is of BYTEA type,

224
00:10:31.740 --> 00:10:35.020
which contains images in binary form.

225
00:10:35.340 --> 00:10:38.920
We can display these inline as follows.

226
00:10:40.340 --> 00:10:44.900
We create an inline image display function, using

227
00:10:44.900 --> 00:10:47.080
Emacs' image support.

228
00:10:47.080 --> 00:10:49.540
And then we'd register this function to display

229
00:10:49.540 --> 00:10:52.460
the image column in the inline image table.

230
00:10:52.780 --> 00:10:55.540
If we now reopen the inline image table,

231
00:10:56.060 --> 00:10:59.140
we see the images are displayed inline.

232
00:10:59.660 --> 00:11:03.160
And as a final example of customisation, here's

233
00:11:03.160 --> 00:11:05.440
how to bind a key to a specific

234
00:11:05.440 --> 00:11:07.860
function, in the row-list buffer.

235
00:11:08.800 --> 00:11:12.080
Let's define a function that does a DuckDuckGo

236
00:11:12.080 --> 00:11:15.500
lookup for a particular value in

237
00:11:15.500 --> 00:11:16.700
the Emacs web browser.

238
00:11:18.080 --> 00:11:22.140
We can define a function which does a

239
00:11:22.140 --> 00:11:24.400
funcall on the cell value for this

240
00:11:24.400 --> 00:11:26.200
DuckDuckGo lookup function.

241
00:11:27.100 --> 00:11:30.060
And finally we can define a key, the

242
00:11:30.060 --> 00:11:33.360
capital D key, in the row-list map,

243
00:11:33.540 --> 00:11:35.480
which calls this function that does a

244
00:11:35.480 --> 00:11:36.900
DuckDuckGo lookup.

245
00:11:37.240 --> 00:11:39.600
And now if I'm browsing information in a

246
00:11:39.600 --> 00:11:41.580
table that I want to do a web

247
00:11:41.580 --> 00:11:45.380
search on, I can use my D shortcut

248
00:11:45.380 --> 00:11:48.580
and see some web results concerning the cell.

NOTE Conclusion

249
00:11:49.400 --> 00:11:53.880
So to conclude, the source and installation instructions

250
00:11:53.880 --> 00:11:56.520
for PGmacs are available on GitHub.

251
00:11:56.680 --> 00:11:58.780
It requires Emacs 29.

252
00:11:59.260 --> 00:12:01.620
There is a prebuilt container image which you

253
00:12:01.620 --> 00:12:02.820
can use for testing.

254
00:12:02.980 --> 00:12:04.100
It's a Docker image.

255
00:12:04.580 --> 00:12:06.160
The Docker image only works in terminal mode.

256
00:12:06.160 --> 00:12:08.380
And of course that's a recommended way of

257
00:12:08.380 --> 00:12:11.020
testing Emacs Lisp code that you load

258
00:12:11.020 --> 00:12:13.100
into your Emacs before having read it.

259
00:12:13.360 --> 00:12:15.720
It works with any recent version of Postgres

260
00:12:15.720 --> 00:12:18.580
over the past 10 years, including the latest

261
00:12:18.580 --> 00:12:19.900
release, Postgres 17.

262
00:12:20.440 --> 00:12:22.700
It does work with a certain number of

263
00:12:22.700 --> 00:12:26.440
databases that are compatible with Postgres, such as

264
00:12:26.440 --> 00:12:30.360
ParadeDB, TimescaleDB and IvorySQL.

265
00:12:30.940 --> 00:12:33.560
However, it doesn't work with all databases that

266
00:12:33.560 --> 00:12:35.340
claim to be Postgres compatible.

267
00:12:35.340 --> 00:12:37.840
It doesn't work, for example, with CrateDB or

268
00:12:37.840 --> 00:12:39.960
with CockroachDB or some others that you see

269
00:12:39.960 --> 00:12:40.640
listed here.

270
00:12:40.760 --> 00:12:42.660
The reason for that is that these databases

271
00:12:42.660 --> 00:12:46.440
don't implement the system tables that PGmacs queries

272
00:12:46.440 --> 00:12:49.600
to obtain information about the columns present in

273
00:12:49.600 --> 00:12:52.220
a table, the size on disk, the ownership

274
00:12:52.220 --> 00:12:53.560
of tables, etc.

275
00:12:54.600 --> 00:12:58.180
PGmacs supports TLS encrypted connections to the database,

276
00:12:58.380 --> 00:13:01.400
as well as local Unix socket connections.

277
00:13:02.040 --> 00:13:04.600
And in terms of stability, I would classify

278
00:13:04.600 --> 00:13:06.160
it as beta status.

279
00:13:06.680 --> 00:13:09.200
I do use it myself in production on

280
00:13:09.200 --> 00:13:11.160
some not very important data.

281
00:13:11.560 --> 00:13:13.060
Really, it works quite well.

282
00:13:14.300 --> 00:13:15.420
Thanks for your attention.