Run SQL In A File

Imagine you’re running a bakery where you need to keep track of all your pies—flavors, prices, and stock. Manually writing every detail every time you open the store would be tedious. Instead, you’d prefer a reusable template. This is exactly what a .sql file is for your database—a recipe that lets you prepare your data effortlessly.

What is a .sql File?

A .sql file is like a script for your database. It contains a set of instructions written in SQL (Structured Query Language) that can create tables, insert data, or modify database structures. Think of it as your automated assistant for repetitive tasks in database management.

Why Use a .sql File?

• Reusability: Save time by automating database setup and data entry.
• Consistency: Ensure your database structure and data are identical every time.
• Portability: Share your database setup with others or transfer it across environments.

How to Create a .sql File

Creating a .sql file is straightforward. Open your favorite code editor, write SQL commands, and save the file with a .sql extension. Let’s create a file named create-table.sql to manage a table of pies.

Example: Creating a Pies Table

Here’s the content for create-table.sql, which creates a table and inserts 50 rows of pie data:

CREATE TABLE pies (
  flavor VARCHAR(255) PRIMARY KEY,
  price FLOAT
);

INSERT INTO pies VALUES('Apple', 19.95);
INSERT INTO pies VALUES('Caramel Apple Crumble', 20.53);
-- Additional rows omitted for brevity
INSERT INTO pies VALUES('Milk Bar', 46.00);
    

Save this file in a directory you can easily access from the command line.

Populating Your Database with a .sql File

1. Open SQLite3: In your terminal, navigate to the directory where create-table.sql is saved and run sqlite3 to open the SQLite CLI.
2. Run the File: Execute the command:

   .read create-table.sql

3. Verify the Data: Run:

   SELECT * FROM pies;

   This will display all the rows in your table.
4. Check the Schema: Use:

   .schema

   to view the table structure.

Preventing Duplicate Data

Re-running the .sql file without clearing existing data can lead to duplicates. To avoid this, add a line to drop the table if it exists:

DROP TABLE IF EXISTS pies;

CREATE TABLE pies (
  flavor VARCHAR(255) PRIMARY KEY,
  price FLOAT
);

INSERT INTO pies VALUES('Apple', 19.95);
-- Additional rows omitted for brevity
INSERT INTO pies VALUES('Milk Bar', 46.00);
    

This ensures a clean slate every time the file is executed.

Real-World Applications

Using .sql files is a common practice in software development. Here are some scenarios where they shine:

• Test Databases: Quickly populate a test database for application development.
• Deployment: Set up database schemas and data on new environments.
• Data Analysis: Share reusable queries for consistent analysis across teams.

What You Learned

In this tutorial, you explored the power of .sql files. By automating table creation and data insertion, you save time, ensure consistency, and make your workflow more efficient. Whether you're setting up a test environment, deploying a project, or sharing a dataset, .sql files are your reliable companion.

Try creating your own .sql files for other scenarios—perhaps a database of customers or orders. The possibilities are endless!