How to Export SQL Schema Without Data

1. Introduction

Exporting a SQL schema without the accompanying data is a crucial task in database management. This is especially useful when preparing to migrate a database, setting up a development environment, or sharing the database structure with a colleague. Exporting just the schema helps transfer the database’s architecture (tables, views, indexes, and other structures) without including the actual data.

In particular, this approach is useful when replicating the database design in a new environment or for testing purposes.

In this tutorial, we’ll explore how to export the SQL schema without data using different methods and tools.  Specifically, we’ll focus on using the mysqldump command, MySQL Workbench, and the pg_dump command.

We’ll use the Baeldung University database as our working example.

2. Using the mysqldump Command

The mysqldump command allows us to export databases and tables in MySQL. This command-line utility tool is commonly used for backups and migrations, but it also provides an option to export only the database schema structure.

2.1. Exporting the Entire Database Schema

We can use the mysqldump command to export the entire schema of a database. For example, let’s export the entire schema of the Baeldung University database without any data:

$ mysqldump -u user -p -h 127.0.0.1 --no-data University > university_schema.sql
Enter password:

The command contains the following information:

• -u root: specifies the MySQL user
• -p: request to enter the MySQL password
• –no-data: tells mysqldump to export only the schema without the data
• University: is the name of the database we’re exporting
• university_schema.sql: the output file of the saved schema

After running this command, the schema is saved in the university_schema.sql file in the current directory.

To verify the success of the export, we can list the contents of the directory:

$ ls -lh
...
-rw-r--r--  1 test test 3.0K Sep  2 19:01 university_schema.sql
drwxr-xr-x  2 test test 4.0K Sep  2 18:13 Videos

The command displays the files in the directory along with their sizes. As seen, the presence of university_schema.sql confirms the export.  The file size shows the file is not empty.

Additionally, we can open the .sql file using a text editor or by using the cat command:

$ cat university_schema.sql 
-- MariaDB dump 10.19  Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: 127.0.0.1    Database: University
-- ------------------------------------------------------
-- Server version       8.4.2
...

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2024-09-02 19:01:09

The file contains the SQL statements that define the structure of the tables, views, and other database objects without any INSERT statements. That’s because only the schema is exported.

2.2. Exporting Specific Tables

If we need to export the schema of specific tables rather than the entire database, we can modify the command to include the table names. For example, let’s export only the Student table:

$ mysqldump -u user -p -h 127.0.0.1 --no-data University Student > university_student_schema.sql
Enter password:

The command creates the university_student_schema.sql file in the current directory.

Now, we can open the file to confirm if the content contains only the Student table:

$ cat university_student_schema.sql 
-- MariaDB dump 10.19  Distrib 10.11.6-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: 127.0.0.1    Database: University
-- ------------------------------------------------------
-- Server version       8.4.2

...
DROP TABLE IF EXISTS `Student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `Student` (
  `id` int NOT NULL,
  `name` varchar(60) DEFAULT NULL,
  `national_id` bigint NOT NULL,
  `birth_date` date DEFAULT NULL,
  `enrollment_date` date DEFAULT NULL,
  `graduation_date` date DEFAULT NULL,
  `gpa` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;
...

-- Dump completed on 2024-09-02 19:42:26

As we can see, the file contains only the schema for the Student table.

2.3. Customizing the Export

The mysqldump command also allows for customization. For example, if we want to exclude specific types of objects, such as triggers or stored procedures, we can add options to the command.

To exclude triggers, we can use the –skip-triggers option:

$ mysqldump -u user -p -h 127.0.0.1 --no-data --skip-triggers University > university_schema_no_triggers.sql
Enter password:

This command exports the schema without including triggers, and the resulting file is saved as university_schema_no_triggers.sql.

3. Using the pg_dump Command

pg_dump is the go-to tool for backing up a database in PostgreSQL. Similar to mysqldump for MySQL, pg_dump offers the capability to export just the database schema without the associated data.

3.1. Exporting the Entire Database Schema

Furthermore, we can export the entire schema of the Baeldung University database without including any data:

$ pg_dump -U user -h 127.0.0.1 -s -f university_schema.sql University
Password: 

The command contains the following information:

• -U postgres: specifies the PostgreSQL user
• -h 127.0.0.1: sets the host address of the PostgreSQL server
• -s: stands for “schema-only,” ensuring that no data is included in the export
• -f university_schema.sql: directs the output to the university_schema.sql file
• University: is the name of the database we’re exporting

After execution, the command saves the schema in the university_schema.sql file in the current directory.

Additionally, we can inspect the contents of the file using the cat command as before:

$ cat university_schema.sql 
--
-- PostgreSQL database dump
--

-- Dumped from database version 16.4 (Debian 16.4-1.pgdg120+1)
-- Dumped by pg_dump version 16.1 (Debian 16.1-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;
...
--
-- PostgreSQL database dump complete
--

Once again, the file contains the SQL statements that define the structure of the tables, views, and other database objects without any COPY or INSERT statements. That’s because only the schema is exported.

3.2. Exporting Specific Schemas

In PostgreSQL, databases can contain multiple schemas. However, if we want to export only a specific schema rather than the entire database, we can use the -n option to specify the schema name.

For example, let’s export the public schema of the Baeldung University database:

$ pg_dump -U user -h 127.0.0.1 -s -n public -f baeldung_public_schema.sql University
Password: 

The command creates the baeldung_public_schema.sql file containing only the schema for the public namespace.

3.3. Customizing the Export

The pg_dump command offers various customization options. For instance, if we want to exclude certain objects, such as indexes or constraints, we can modify the command accordingly.

For example, we can exclude indexes on the exported schema with the use of –no-indexes option:

$ pg_dump -U user -h 127.0.0.1 -s --no-indexes -f baeldung_no_indexes_schema.sql University
Password:

This command will generate a schema file without any indexes, saving it as baeldung_no_indexes_schema.sql.

4. Conclusion

In this article, we’ve explored various methods to export a SQL schema without data, highlighting tools like mysqldump for MySQL and pg_dump for PostgreSQL. This technique is invaluable for database migrations, setting up development environments, or simply sharing the database structure.

Finally, by mastering these commands, we can efficiently manage and replicate our database architecture across different environments.