Entender los fundamentos de la gestión de esquemas es crucial para construir y mantener una base de datos PostgreSQL efectiva. En este artículo, vamos a ver la forma tradicional de gestionar un esquema de Postgres y una forma más nueva y efectiva de hacerlo visualmente, sin tener que escribir ninguna línea de código.
¿Qué es un esquema PostgreSQL?
Primero, para sentar las bases del artículo, vamos a aclarar algo de terminología. En Postgres, el esquema también se conoce como espacio de nombres. El espacio de nombres puede estar asociado a un nombre de familia. Se utiliza para identificar y diferenciar determinados objetos de la base de datos (tablas, vistas, columnas, etc.). No está permitido crear dos tablas con el mismo nombre en un mismo esquema, pero se puede hacer en dos esquemas diferentes. Por ejemplo, podemos tener dos tablas con el mismo nombre en los esquemas public y postgres.
¿Por qué usar esquemas?
Los esquemas son muy útiles para organizar los objetos de la base de datos en grupos lógicos y evitar la colisión de nombres. Además de esto, los esquemas se utilizan a menudo para permitir que diferentes usuarios trabajen con la base de datos sin interferir unos con otros. Un ejemplo común es cuando cada usuario de la base de datos trabaja en su propio esquema, sin interferir con otros usuarios y evitando conflictos.
La forma clásica de gestionar los esquemas de PostgreSQL
Todas las consultas que se realizan a continuación se ejecutarán desde dentro del shell de PostgreSQL.
Creación de un esquema
Cuando se crea una nueva base de datos en Postgres, el esquema por defecto es público. Se puede crear un nuevo esquema ejecutando la siguiente consulta:
CREATE SCHEMA schema_1;
Antes de añadirle algunas tablas, Voy a explicar dos conceptos importantes: nombres calificados y no calificados.
-
Un nombre cualificado es el nombre del esquema y el de la tabla separados por un punto. Esto especificará el esquema en el que queremos crear nuestra tabla:
.
xxxxxxxxxx
CREATE TABLE schema_name.table_name (...);
-
Un nombre no cualificado consiste sólo en el nombre de la tabla. Esto creará la tabla en la base de datos seleccionada que es pública por defecto. Esto se puede cambiar a través de search_path, pero lo detallaremos más adelante. Un ejemplo de nombre no cualificado es:
.
xxxxxxxxxx
CREATE TABLE table_name (...);
Las columnas de las tablas se definirán dentro de los paréntesis de las consultas anteriores (…).
Para crear una nueva tabla en nuestro nuevo esquema, ejecutaremos:
.
xxxxxxxxxx
CREATE TABLE schema_1.persons (name text, age int);
Para dejar el esquema, tenemos dos posibilidades. Si el esquema está vacío (no contiene ninguna tabla, vista u otros objetos), podemos ejecutar:
.
xxxxxxxxxx
DROP SCHEMA schema_1;
Si el esquema contiene objetos de base de datos, insertaremos el comando en cascada:
.
xxxxxxxxxx
DROP SCHEMA schema_1 CASCADE;
En PostgreSQL también es posible crear un esquema propiedad de otro usuario con:
xxxxxxxxxx
CREATE SCHEMA schema_name AUTHORIZATION username;
Ruta de búsqueda
Cuando se ejecuta un comando con un nombre no cualificado, Postgres sigue una ruta de búsqueda para determinar qué esquemas utilizar. Por defecto, la ruta de búsqueda se establece en el esquema público. Para verlo, ejecute:
.
xxxxxxxxxx
SHOW search_path;
Si no se ha modificado nada en su base de datos, esta consulta debería traer el siguiente resultado:
xxxxxxxxxx
search_path
--------------
"$user",public
La ruta de búsqueda puede modificarse para que el sistema elija automáticamente otro esquema si se utiliza un nombre no cualificado. El primer esquema en la ruta de búsqueda se llama esquema actual. Por ejemplo, estableceré el esquema_1 como el esquema actual:
.
xxxxxxxxxx
SET search_path TO schema_1,public;
La siguiente consulta utilizará un nombre no cualificado para crear una tabla. Esto la creará automáticamente en el esquema_1:
xxxxxxxxxx
CREATE TABLE address (city text, street text, number int);
La nueva forma: Gestionar sin el código!
Hay una forma más sencilla de hacer todas las tareas de gestión de esquemas, sin tener que escribir ninguna línea de código. Usando DbSchema puede ejecutar todas las consultas anteriores desde una intuitiva interfaz gráfica de usuario con sólo unos pocos clics. Conectarse a la base de datos sólo le llevará unos segundos. Desde el principio, puede seleccionar sobre qué esquema trabajar.
El esquema o esquemas seleccionados serán revertidos por DbSchema y mostrados en el diseño.
Para crear un nuevo esquema, basta con hacer clic con el botón derecho del ratón en la carpeta del esquema en el menú de la izquierda y seleccionar Crear esquema.
Para crear una nueva tabla en el esquema haga clic con el botón derecho del ratón en el diseño y seleccione Crear tabla.
Se puede soltar el esquema haciendo clic con el botón derecho en su nombre desde el menú de la izquierda.
Para añadir otro esquema desde la base de datos, elija Refrescar desde la base de datos.
Usando DbSchema, no necesitará utilizar la sintaxis show_path porque puede crear las tablas directamente en el diseño. Un layout puede ser comparado con un tablero de dibujo en el que puedes añadir las tablas y editarlas. Cada layout tiene un esquema asociado, así que si estás en el layout schema_1, las tablas se crearán automáticamente allí.
Trabajar sin conexión
DbSchema almacena una imagen local del esquema en un archivo de proyecto local. Esto significa que el archivo de proyecto puede ser abierto sin la conectividad de la base de datos (sin conexión). Mientras está fuera de línea, puede hacer todas las acciones presentadas anteriormente y más, pero sin datos. Después de volver a conectarse a la base de datos, puede comparar el archivo de proyecto con la base de datos y elegir qué acciones mantener o eliminar.
Lo mismo se puede hacer entre dos versiones diferentes del mismo archivo de proyecto. Por ejemplo, si se trabaja en equipo, puede darse el caso de que haya varios esquemas (producción, pruebas, desarrollo) cada uno con su propio fichero de proyecto. Si aparece un cambio en desarrollo y quieres implementarlo sobre los otros dos esquemas, sólo tienes que comparar y sincronizar los dos archivos de proyecto.
Conclusión
Entender los conceptos enumerados anteriormente le ayudará a gestionar fácilmente sus esquemas PostgreSQL. El uso de un diseñador visual como DbSchema le facilitará aún más su trabajo al permitirle hacer todo visualmente, sin tener que escribir una sola línea de código.