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.