В предыдущей статье мы рассмотрели настройку образа докера Postgres и взаимодействие с ним с помощью файлов sql и интерактивного терминала psql. Эта статья будет продолжением этого, в ней будет более подробно рассказано о том, как мы можем искать в наших таблицах и извлекать значимую информацию, а также представлено введение в отношения между таблицами.
Вывод данных
Хотя наша таблица базы данных довольно проста и мала, я хотел бы сейчас представить концепцию дампа данных и то, как мы можем инициализировать нашу базу данных некоторыми данными. Это будет очень полезно для целей тестирования, так как вы можете создать новую базу данных с ожидаемыми данными.
Если вы следовали из предыдущей статьи, вот как вы можете создать дамп из своего контейнера докеров:
docker exec postgres pg_dump -U test test > test_020223.sql
Это говорит докеру выполнить команду pg_dump -U test test > test_020223.sql в контейнере с именем postgres. Наша команда предоставляет пользователю test с помощью флага -U, затем указывает имя базы данных, дамп которой мы хотим получить, а затем файл в нашей локальной системе, в который мы хотим вставить sql.
Для того, что мы сделали в предыдущей статье, вы должны создать следующий файл:
--
-- PostgreSQL database dump
--
-- Dumped from database version 15.1 (Debian 15.1-1.pgdg110+1)
-- Dumped by pg_dump version 15.1 (Debian 15.1-1.pgdg110+1)
SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
SET default_tablespace = '';
SET default_table_access_method = heap;
--
-- Name: friends; Type: TABLE; Schema: public; Owner: test
--
CREATE TABLE public.friends (
id integer NOT NULL,
name character varying(80),
age integer
);
ALTER TABLE public.friends OWNER TO test;
--
-- Name: friends_id_seq; Type: SEQUENCE; Schema: public; Owner: test
--
CREATE SEQUENCE public.friends_id_seq
AS integer
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.friends_id_seq OWNER TO test;
--
-- Name: friends_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: test
--
ALTER SEQUENCE public.friends_id_seq OWNED BY public.friends.id;
--
-- Name: friends id; Type: DEFAULT; Schema: public; Owner: test
--
ALTER TABLE ONLY public.friends ALTER COLUMN id SET DEFAULT nextval('public.friends_id_seq'::regclass);
--
-- Data for Name: friends; Type: TABLE DATA; Schema: public; Owner: test
--
COPY public.friends (id, name, age) FROM stdin;
5 Sam 38
6 Fred 21
\.
--
-- Name: friends_id_seq; Type: SEQUENCE SET; Schema: public; Owner: test
--
SELECT pg_catalog.setval('public.friends_id_seq', 1, false);
--
-- Name: friends friends_pkey; Type: CONSTRAINT; Schema: public; Owner: test
--
ALTER TABLE ONLY public.friends
ADD CONSTRAINT friends_pkey PRIMARY KEY (id);
--
-- PostgreSQL database dump complete
--
Интересно читать этот дамп и сравнивать его с запросами, которые мы сделали для создания данных в первую очередь. Дамп намного более подробный, и кажется, что Postgres многое делает для нас, особенно в отношении ключа SERIAL id.
Чтобы воссоздать вашу базу данных из этого дампа, нам сначала нужно убедиться, что у нас нет базы данных с именем test:
docker exec postgres dropdb -U test test
Нам снова нужно указать пользователя здесь, так как он является суперпользователем для нашего экземпляра postgres. Затем воссоздание базы данных из дампа очень похоже на то, как мы создавали дамп в первую очередь:
docker exec postgres pg_dump -U test test < test_020223.sql
Единственное изменение здесь заключается в том, что стрелка указывает в противоположном направлении.
Мы можем сделать простую проверку, чтобы увидеть, сработало ли это. Чтобы напомнить, вот два способа взаимодействия с базой данных:
- Запустите интерактивный терминал psql, запустив
docker exec -it postgres psql -U test - Добавьте запрос в файл sql локально и запустите его с помощью
docker exec postgres psql -U test -f /app/YOUR_FILE_NAME_HERE.sql
Для варианта 2 помните, что путь к файлу, указанный в этой команде, имеет /app из-за тома, который был настроен в файле docker-compose.yml. Файл должен быть родственным вашему файлу docker-compose.yml на вашем локальном компьютере.
Какой бы метод вы ни выбрали, SQL, который я привожу в этой статье, будет работать одинаково.
С учетом всего сказанного, давайте проверим, что наша база данных была восстановлена правильно:
SELECT * from friends;
Если вы сделали это правильно, это вернет:
id | name | age ----+------+----- 2 | Sam | 38 3 | Fred | 21 (2 rows)
Добавляем столбец в нашу таблицу
Ура, у меня появился новый друг. К сожалению, их также зовут Сэм, поэтому мне нужно добавить фамилии в нашу таблицу друзей, чтобы я мог различать их.
ALTER TABLE friends ADD COLUMN surname VARCHAR;
Теперь у нас остались пустые значения для наших существующих друзей.
id | name | age | surname ----+------+-----+--------- 2 | Sam | 38 | 3 | Fred | 21 |
Мы можем использовать оператор UPDATE из предыдущей статьи, чтобы добавить эти фамилии:
UPDATE friends SET surname = 'Brown' WHERE name = 'Sam'; UPDATE friends SET surname = 'Green' WHERE name = 'Fred';
Теперь, прежде чем мы добавим нашего нового друга, я думаю, мы должны изменить имя столбца имени на «first_name». Здесь мы можем сделать это с уверенностью, так как мы только изучаем postgres, однако в реальном приложении нам нужно быть очень осторожными при переименовании имен столбцов, поскольку могут быть запросы, которые зависят от них.
ALTER TABLE friends RENAME COLUMN name TO first_name;
Теперь наша таблица должна выглядеть так:
id | first_name | age | surname ----+------------+-----+--------- 2 | Sam | 38 | Brown 3 | Fred | 21 | Green
Давайте INSERT нашего нового друга, как мы делали раньше.
INSERT INTO friends (first_name, surname, age)
VALUES ('Sam', 'Blue', 29);
Обратите внимание, что мы ввели наши данные в порядке, отличном от порядка, в котором находится таблица. Мы можем сделать это, потому что указали, чему соответствует каждое значение. Теперь наша таблица выглядит так:
id | first_name | age | surname ----+------------+-----+--------- 2 | Sam | 38 | Brown 3 | Fred | 21 | Green 4 | Sam | 29 | Blue
В дальнейшем я хотел бы убедиться, что имя и фамилия всегда вводятся, поэтому я могу ALTER таблицу установить ограничение NOT NULL для этих двух столбцов:
ALTER TABLE friends ALTER COLUMN first_name SET NOT NULL; ALTER TABLE friends ALTER COLUMN surname SET NOT NULL;
Теперь, если мы попробуем неверный оператор вставки, например
INSERT INTO friends (first_name, age)
VALUES ('Phil', 29);
Мы получим сообщение об ошибке, говорящее нам, что мы нарушаем ненулевое ограничение на фамилию.
Добавление связи между таблицами
Теперь, когда у меня целых три друга, я изо всех сил пытаюсь вспомнить, где они живут, поэтому давайте добавим новую таблицу «адреса» и свяжем ее с нашей таблицей друзей. В реальной ситуации у каждого друга будет только один адрес (если только он не очень богат), но по одному адресу может жить несколько друзей. Таким образом, мы хотим, чтобы наши отношения были одним адресом для каждого друга, но много друзей для каждого адреса. Итак, нам нужна связь «один ко многим». Сначала мы создадим нашу новую таблицу адресов:
CREATE TABLE addresses (
id SERIAL PRIMARY KEY,
street VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL
);
Затем мы добавим наш ссылочный столбец address_id в нашу таблицу друзей:
ALTER TABLE friends
ADD COLUMN address_id INT;
Наконец, мы добавим ограничение, которое дает нам желаемое отношение:
ALTER TABLE friends
ADD CONSTRAINT fk_address FOREIGN KEY(address_id) REFERENCES addresses(id);
Теперь наши отношения настроены, мы можем добавить новый адрес для Фреда:
INSERT INTO addresses (street, city)
VALUES ('23 Fake Street', 'Fakeville');
UPDATE friends
SET address_id = (
SELECT id FROM addresses
WHERE street = '23 Fake Street' AND city = 'Fakeville'
)
WHERE first_name = 'Fred';
Большая часть этого должна показаться вам знакомой, однако это первый раз, когда мы делаем запрос внутри запроса. Мы выбираем id из addresses для адреса, который мы только что вставили, чтобы установить правильное значение в нашей таблице friends.
Теперь мы можем выбрать наших друзей с адресами, используя JOIN:
SELECT * from friends LEFT JOIN addresses on address_id = addresses.id;
И мы получим такой результат:
id | first_name | age | surname | address_id | id | street | city ----+------------+-----+---------+------------+----+----------------+----------- 2 | Sam | 38 | Brown | | | | 4 | Sam | 29 | Blue | | | | 3 | Fred | 21 | Green | 1 | 1 | 23 Fake Street | Fakeville
Теперь вам может быть интересно, в чем смысл этого ограничения, которое мы добавили, если нам все еще нужно указать, какие столбцы мы объединяем. Ограничение гарантирует, что существует действительное и подходящее отношение, к которому можно присоединиться. Другое преимущество обнаруживается при использовании ORM с вашей базой данных. ORM позволяет вам взаимодействовать с базой данных более объектно-ориентированным способом, и когда отношение установлено таким образом, это позволит вам сделать что-то вроде friend.address для доступа к адресу в отношении. Я постараюсь более подробно остановиться на этом в другой статье, когда начну писать о Sequelize.
Другие типы соединения
Вы заметите, что в приведенном выше запросе я использовал LEFT JOIN. Это покажет все строки из таблицы LEFT (в данном случае друзей) и покажет только совпадающие значения справа. Есть несколько других типов JOIN, о которых следует знать.
RIGHT JOIN — Если мы изменим наш запрос, чтобы вместо этого использовать ПРАВОЕ СОЕДИНЕНИЕ, мы получим все данные из правой таблицы (адреса) и только те значения, которые совпадают в левой таблице. Чтобы увидеть это правильно, я собираюсь добавить еще один адрес, который не связан с другом:
INSERT INTO addresses (street, city)
VALUES ('42 Test Road', 'Testtown');
SELECT * from friends RIGHT JOIN addresses on address_id = addresses.id;
id | first_name | age | surname | address_id | id | street | city
----+------------+-----+---------+------------+----+----------------+-----------
3 | Fred | 21 | Green | 1 | 1 | 23 Fake Street | Fakeville
| | | | | 3 | 42 Test Road | Testtown
INNER JOIN Это вернет только те строки, которые будут содержать записи в обеих таблицах:
SELECT * from friends INNER JOIN addresses on address_id = addresses.id; id | first_name | age | surname | address_id | id | street | city ----+------------+-----+---------+------------+----+----------------+----------- 3 | Fred | 21 | Green | 1 | 1 | 23 Fake Street | Fakeville
FULL JOIN Это вернет все строки для обеих таблиц:
SELECT * from friends FULL JOIN addresses on address_id = addresses.id;
id | first_name | age | surname | address_id | id | street | city
----+------------+-----+---------+------------+----+----------------+-----------
2 | Sam | 38 | Brown | | | |
4 | Sam | 29 | Blue | | | |
3 | Fred | 21 | Green | 1 | 1 | 23 Fake Street | Fakeville
| | | | | 2 | 42 Test Road | Testtown
Эта диаграмма является полезным наглядным пособием по различным соединениям:
То, что я рассмотрел до сих пор, — это некоторые из наиболее распространенных вещей, которые вы будете делать с базой данных. В своей следующей статье я планирую рассказать о начале работы с Sequelize ORM, о котором я упоминал в этой статье.
